How to estimate expected shortfall given skewness and kurtosis in Excel

Risk practitioners sometimes use expected shortfall (ES) to quantify the potential future loss of an investment. As a risk metric, ES answers questions like What is the average daily/monthly/yearly return going to be in the worst 1%/5%/10% of times?

Terminology

In statistical jargon, ES is the conditional mean of the return distribution given that returns are less than the percentile at a given confidence level. The above diagram illustrates ES at the 95% confidence level.

Sometimes, ES is defined in terms of a loss (instead of return) distribution. In that case, the above diagram would flip horizontally and ES would be in the right tail.

Mean and variance

If the mean and variance of the return distribution are given, ES can be estimated by assuming returns follow the normal distribution and calculating the conditional mean of the normal distribution that fits the given mean and variance.

To estimate ES at the 95% confidence level in Excel when the mean and variance of the return distribution are 10% and 0.01 respectively, enter the formula =10% - SQRT(0.01) * NORM.S.DIST(NORM.S.INV(1-0.95), FALSE) / (1-0.95) in a cell.

To estimate ES when the previous mean and variance are for the loss (instead of return) distribution, change the formula to =10% + SQRT(0.01) * NORM.S.DIST(NORM.S.INV(0.95), FALSE) / (1-0.95).

Skewness and kurtosis

In reality, returns are more likely to follow a non-normal distribution with negative skewness and large kurtosis. According to journalist Martin Wolf, many investment strategies have a high probability of small gains and a low probability of huge losses.

If the skewness and kurtosis of the return distribution are given in addition to the mean and variance, ES can be estimated by assuming returns follow a non-normal parametric distribution and calculating the conditional mean of the distribution that fits the given moments.

There are many non-normal parametric distributions, but the Pearson and Johnson distributions are the only ones that fit all possible combinations of skewness and kurtosis. No other parametric distribution has such a feature.

Unfortunately, Excel does not come with native functions for using the Pearson and Johnson distributions.

QRS.ES functions

Fortunately, QRS Toolbox for Excel includes functions for estimating ES from not only the normal distribution, but also the Pearson and Johnson distributions. The names of the functions all begin with QRS.ES.

QRS.ES.NORMAL returns ES for the normal distribution. It accepts the confidence level, mean, standard deviation, and choice of tail as parameters. It is simpler to use compared to the two previous formulas involving NORM.S.DIST and NORM.S.INV.

QRS.ES.PEARSON and QRS.ES.JOHNSON return ES for the Pearson and Johnson distributions respectively. They accept the same parameters as QRS.ES.NORMAL in addition to skewness and kurtosis.

Pearson Type IV example

There are 5 main types of Pearson distributions, namely Types I, III, IV, V, and VI. To the best of our knowledge, QRS.ES.PEARSON is the only publicly available Excel function for estimating ES from all 5 types of Pearson distributions.

The Pearson Type IV distribution deserves special mention. It is a natural alternative to the normal distribution in risk applications, because it is not only unimodal and unbounded, but also skewed (squared skewness < 32) and fat-tailed (kurtosis > 3).

To try QRS.ES.PEARSON yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.ES.PEARSON. Then, open the example workbook.

In the workbook:

• Cell B1 contains the confidence level, 99%.
• Cell B2 contains the mean, 10%.
• Cell B3 contains the variance, 0.01.
• Cell B4 contains the skewness, -2.
• Cell B5 contains the kurtosis, 16.

To determine the type of Pearson distribution that fits the given moments, enter the formula =QRS.PEARSON.TYPE(B2, B3, B4, B5) in cell B6. The result is IV.

To estimate ES for the corresponding Pearson Type IV distribution, enter the formula =QRS.ES.PEARSON(B1, B2, B3, B4, B5) in cell B7. The result is -36.7%, which means the investment is estimated to lose on average 36.7% in 1 out of 100 periods.

Johnson SU example

There are 3 types of Johnson distributions, namely SU, SL, and SB. To the best of our knowledge, QRS.ES.JOHNSON is the only publicly available Excel function for estimating ES from all 3 types of Johnson distributions by moment matching.

Software that support fitting the Johnson distributions by moments typically use a method by Hill, Hill, and Holder, but that method does not always return a result. QRS.ES.JOHNSON uses a proprietary method that is more reliable.

To try QRS.ES.JOHNSON yourself, start your free trial of QRS.ES.JOHNSON and reopen the workbook from the previous example.

To determine the type of Johnson distribution that fits the given moments, enter the formula =QRS.JOHNSON.TYPE(B2, B3, B4, B5) in cell B6. The result is SU.

To estimate ES for the corresponding Johnson SU distribution, enter the formula =QRS.ES.JOHNSON(B1, B2, B3, B4, B5) in cell B7. The result is -38.1%, which is in the same ballpark as the result from the previous example.

Final remarks

If you would like to use QRS.ES.PEARSON and QRS.ES.JOHNSON beyond your free trial periods, you may purchase the right to use them indefinitely for as little as USD 19.00. If you find the QRS.ES functions useful, please share this page with other potential users.