Expected Shortfall in Excel

Expected shortfall is a commonly-used tool to measure risk in financial portfolios. It is used to analyze the worst returns within a portfolio at a given confidence level. Fortunately, Excel’s tools make this calculation quite easy. Let’s learn how to calculate expected shortfall in Microsoft Excel.

How to Calculate Expected Shortfall in Excel

To best understand expected shortfall, it helps to review the mechanics of this risk measure. In investment and financial modeling, estimated returns are estimated using a normal distribution. Think back to geometry: at given confidence intervals, a set percentage of returns will be expected to fall in a certain range. Once this analysis is created, you have a forecast for expected returns.

Next, it’s time to consider the meaning of a shortfall. Shortfalls are directly related to another measure, the value-at-risk, or VaR, of an investment. VaR is a probability-based measure used to estimate potential risks of loss. Measuring and quantifying these losses is critically important. In finance, it is imperative to maintain assets (in the form of liquidity) sufficient to cover potential downside losses. It follows, then, that estimating risks in this fashion is of critical importance to the integrity of a portfolio.

VaAR is often calculated at a 95%/5% split. Consider a real-world example. A given portfolio is measured with a 95% confidence level. Losses are estimated to not exceed $100,000 on a given day 95% of the time. Or, to phrase and consider this differently, losses are estimated to exceed $100,000 5% of the time.

To illustrate this in Excel, begin by constructing a simple layout like the one below. The first column lists trading days. VaR can use a variety of time measurements, but trading days are the most commonly utilized. The choice is yours; however, you need to ensure that you apply the time measurement consistently to ensure data is and will remain accurate.

The second column lists the expected returns on the portfolio. Again, this was calculated using the 5% VaR measurement, assuming losses exceeding $100,000 on 5% of trading days. For simplicity’s sake, this example portfolio has 100 trading days. Therefore, there should be five values exceeding -$100,000 in losses.

This simple matrix forms the basis for the purpose of our exercise: calculating the expected shortfall. Again, it helps to know the definition. Remember again that in 95% of cases, we are not expecting to lose over $100,000 in a day. The shortfall lies in the 5% of cases where losses of this magnitude are expected.

Calculating the expected shortfall is actually quite easy. In fact, it’s a simple formula using Excel’s AVERAGE function. Expected shortfall is found by averaging the returns of the 5% of loss cases shown above. Again, in this example, there are five such losses. This ratio – and the affected values – will naturally vary from situation to situation.

In this example, click into an empty cell, then type an = sign to begin a formula. Enter the AVERAGE function. So far, your formula reads:

=AVERAGE(

Then, click into each of the five cells containing losses exceeding $100,000. This is the focus of our analysis. Separate each in the formula with a comma, as follows:

=AVERAGE(B3,B17,B25,B26,B39)

When you’ve finished inputting the formula above, go ahead and hit Enter on your keyboard to wrap it up. Excel will return the average of the five values: $115,826.40. 

This value is your expected shortfall for the portfolio. As you can see, it’s quite simple to calculate, thanks to built-in formulas in Microsoft Excel. While there isn’t a specific formula for expected shortfall, you can still find the value easily with a spreadsheet. Use this technique next time you need to analyze your portfolio for risks.