Expected shortfall is an extension of value at risk (VaR). For a discussion on VaR, refer to the article where VaR is determined using Monte Carlo simulation. Expected shortfall is also known as conditional VaR.

Suppose we have determined VaR for our portfolio. Let's say we have a VaR for monthly returns at 95% confidence level. 95% of the time, the maximum loss will be VaR. 5% of the time, losses will exceed VaR. This begs the question, how much can be lost in these extreme events? Expected shortfall attempts to provide somewhat of an answer.

Given the 5% where we exceed VaR, expected shortfall is the expected value of the losses exceeding VaR. In other words, given losses greater than VaR, what is the expected value of this 5% of outcomes?

We will use our simulation from the VaR article to determine expected shortfall. The model for determining VaR from our portfolio of five investments is shown below.

After running the simulation, the VaR is determined to be 49,706. Now let's look at the 5% of outcomes that exceed VaR. To determine the expected shortfall, we need to find the expected value of the 5% of outcomes where losses exceed VaR.

Using the raw data from the simulation we can filter out any outcome greater than or equal to VaR. Part of the simulation data sheet is shown below.

Now let's filter out any losses greater than or equal to the 5th percentile of outcomes. The 5th percentile gain/loss is -49,706 (-49,705.51 to be exact). Set up the formula shown in cell J2 below where any value ≤ -49,705.51 is shown and any value greater than this value is left blank.

Copy cell J2 down to the last data row.

Now we calculate the average of the filtered data points in cell K2 as shown below.

To calculate expected shortfall, we find the expected value (average) of the bottom 5% of portfolio gains/losses.

Expected Shortfall = 101,942

As with VaR, we are using a sign convention that losses are stated as a positive number.

To interpret expected shortfall, given that our losses have exceeded the VaR of 49,706, our expected losses will be 101,942.

It's important to note that since we are dealing with a small number of the total iterations (5% in this case), we need to run enough iterations to sample a sufficient number of extreme data points. This is also where selection of the return probability distribution is extremely important. The distribution may over or under estimate the magnitude of losses that are possible.

Expected shortfall expands on VaR to give an idea of extreme losses that VaR fails to show, but it is also imperfect at showing absolute risk. It is merely an average of the extreme losses. Nevertheless, it can be a useful next step beyond VaR.

The steps to calculate expected shortfall were done to illustrate how it's calculated. Simulation Master has a VaR | Expected shortfall tool that easily calculates these measures for any confidence level. Simulation Master also has worksheet functions to calculate both measures instantly in a worksheet.

To learn more about the VaR | Expected Shortfall tool, refer to the tutorial that shows how it operates.

Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.

Pingback: Determining Value at Risk Using Monte Carlo Simulation - Vortarus Technologies LLC