How to estimate value-at-risk given skewness and kurtosis in Excel

Risk practitioners often use value-at-risk (VaR) to quantify the potential future loss of an investment. As a risk metric, VaR answers questions like What is the worst daily/monthly/yearly return going to be 90%/95%/99% of the time?

Terminology

In statistical jargon, VaR is simply the percentile of the return distribution at a given confidence level. The above diagram illustrates VaR at the 95% confidence level.

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

Mean and variance

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

To estimate VaR 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 =NORM.INV(1-0.95, 10%, SQRT(0.01)) in a cell.

To estimate VaR when the previous mean and variance are for the loss (instead of return) distribution, change the formula to =NORM.INV(0.95, 10%, SQRT(0.01)).

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, VaR can be estimated by assuming returns follow a non-normal parametric distribution and calculating the percentile 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.VAR functions

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

QRS.VAR.NORMAL returns VaR 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.INV.

QRS.VAR.PEARSON and QRS.VAR.JOHNSON return VaR for the Pearson and Johnson distributions respectively. They accept the same parameters as QRS.VAR.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.VAR.PEARSON is the only publicly available Excel function for estimating VaR 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.VAR.PEARSON yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.VAR.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 VaR for the corresponding Pearson Type IV distribution, enter the formula =QRS.VAR.PEARSON(B1, B2, B3, B4, B5) in cell B7. The result is -23.7%, which means the investment is estimated to lose at most 23.7% in 99 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.VAR.JOHNSON is the only publicly available Excel function for estimating VaR 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.VAR.JOHNSON uses a proprietary method that is more reliable.

To try QRS.VAR.JOHNSON yourself, start your free trial of QRS.VAR.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 VaR for the corresponding Johnson SU distribution, enter the formula =QRS.VAR.JOHNSON(B1, B2, B3, B4, B5) in cell B7. The result is -24.3%, which is in the same ballpark as the result from the previous example.

Final remarks

If you would like to use QRS.VAR.PEARSON and QRS.VAR.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.VAR functions useful, please share this page with other potential users.