• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Expected Shortfall in Excel

October 15, 2021 by Andrew Childress
Expected shortfall formula Excel

Expected shortfall is a commonly-used tool to measure risk in financial portfolios. Let’s learn how to calculate expected shortfall in Microsoft 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.

Expected shortfall in Excel

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.

Calculate expected shortfall in Excel

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(

Excel expected shortfall

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)

Expected shortfall formula Excel

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. 

Portfolio expected shortfall in Excel

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.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Profit Pool Analysis in Excel
Next Post:How to Convert Arabic Numbers to English in ExcelConvert Arabic numbers to English in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top