Modeling Value-at-Risk (VaR) and Conditional Value-at-Risk (CVaR) in Excel (Historical, Gaussian, and Cornish-Fisher)

Value-at-risk (VaR) is a popular risk measure used in financial institutions to measure the risk in their portfolios. It measures the minimum loss within an interval period at a given probability (e.g. 1% or 5% being the commonly used figure). For example, if a portfolio has a one-week, 5% value-at-risk of USD 4 million, then there is a 5% probability that the portfolio would lose more than USD 4 million with a 1-week period (i.e. 1 week out of 20).

There are three approaches to estimate VaR:

1. Historical VaR
2. Parametric VaR
3. Monte Carlo VaR (not discussed in this article)

Then, the concept of Conditional Value-at-Risk (CVaR) was developed to measure the average loss if the VaR is exceeded. CVaR is also called expected shortfall or expected tail loss.

Using Netflix’s historical price data, I will illustrate the estimation of historical, parametric (Gaussian), and Cornish-Fisher VaR and CVaR in Excel.

Historical VaR and CVaR

The historical VaR method uses a large quantity of historical data to estimate VaR. It makes minimal assumptions about the return distribution.

Parametric VaR and CVaR (Gaussian)

For this method, we have to assume a statistical distribution for the returns of the stock. Typically, we will assume a Gaussian or Normal distribution (which I will use in the video).

Modified VaR and CVaR (Cornish-Fisher expansion)

The Cornish-Fisher expansion is useful when the return distribution is non-normal. The formula builds in the sample skewness and excess kurtosis to estimate extreme quantiles.