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:
- Historical VaR
- Parametric VaR
- 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.