Conditional Value At Risk Calculator

Value At Risk

Value at risk or VaR is a statistical measure of the amount of funds a investment, portfolio, or a company might expect to lose over a specified time horizon with a given probability. If you have a portfolio that is expected to lose no more than $100,000, 90% of the time (or 18 out of every 20 days) is said to have VaR of $100,000. But on the flip side, 10% of the time, or 2 out of 20 days, your portfolio has potential to lose at least $100,000. VaR’s limitation is that it cannot give further information on how much the portfolio could lose beyond the $100,000 during 10% of the time.

Limitations of VaR

Compare two portfolios – X and Y. Assume when Portfolio X loses more than $100,000, it loses can go up to $150,000. Portfolio Y also has a VaR of $100,000, at 95% confidence level. When Portfolio B’s losses exceed its VaR, it can loose as much as $500,000 in a single day. Although both the portfolios have different risk profiles, they have the same VaR. VaR ignores the total amount at risk in the left tail, or the risk of huge loss.

First problem with VaR is the assumption of a normal or symmetric return distribution. Real world investments are full with nonlinear risks such as credit swaps and derivatives. The second problem VaR lacks is subadditivity, which is based on the principle of diversification. You can read an interesting commentary on VaR: The number that killed us

Conditional Value Risk Calculator – Background

Value at Risk (VaR) is a measurement of the incurred risk of an investment expressed as the most likely maximum loss of a portfolio or an asset give a confidence interval (CI) and time horizon. VaR allows investors to calculate the most probable amount of money they would lose within the defined time horizon.  An intuitive to way to think about VaR is in terms of its position as in the distribution function of returns; a distribution of rate of returns, if we are interested in the negative rate of return instead of the actual amount of lost dollars. In such scenario of a distribution of prices or rate of returns, VaR will be the frontier line that delimits the CI expressed as the area under the curve of the probability density function.

If we were to numerically recreate the distribution as histogram, either from a Monte Carlo simulation, or from actual price data, the VaR can be interpreted as the quantile in which the accumulated histogram has reached the CI percentage. This is true as we approach the VaR frontier from right, or if approached from the left, it corresponds to (1 – CI) value. This formulation of the VaR allows for an easy computation which is analytically expressed as:

From this VaR definition of risk, one apparent problem becomes obvious.  Although, the VaR frontier quantile is the most likely monetary loss or negative rate of return, there are other less likely scenarios with much higher losses; any point to the left of the VaR frontier falls into high loss territory. This limitation can partially be addressed if we take into account the information about the whole “loss tail” after the VaR frontier to obtain a measure of risk.

CVaR

CVaR (sometimes called a Expected Shortfall) can be viewed as the expected value of the portfolio or asset losses given that we have surpassed the VaR frontier in the distribution. It is the conditional probability of the expected value of the “extreme tail” given that we have exceeded the VaR quantile in the distribution.

where, r is the portfolio or asset rate of return. As you can see intuitively that CVaR is more sensitive to extreme events. For example, if we compute a distribution with high kurtosis i.e, with fat tails, the CVaR value would move more and more to the left of the -VaR quantile line.

Is CVaR Superior to VaR?

CVaR can be viewed as a complementary tool to VaR. It gives us the total amount of loss given a loss event. While a VaR inclined investor may ask “How often could my portfolio lose at least $100K? A CVaR inclined investor would ask “When my portfolio loses more than $100K, how much could I lose? CVaR is calculated as a portfolio’s VaR plus the probability-weighted average loss expected in excess of VaR. A CVaR estimate cannot be lower than a VaR estimate. It can quantify tail risks and capture minimal probability of substantial loss for a portfolio with asymmetrical risk profile.

As a savvy investor, you should understand CVaR’s own limitations. VaR estimates tend to be more stable than CVaR which often requires thousands, even millions of trials to generate a reliable estimate. Also, the reliability of CVaR depends mainly on the accuracy of tail model used. You should also understand that because CVaR is based on an average loss beyond VaR, it is not a measurement of extreme losses.

Conditional Value At Risk Calculator in Excel

The spreadsheet attached to this post does the following:

1. Simulates the price of a given symbol from a Normal Distribution based on the user supplied parameters
2. Imports the historical (actual) closing prices of a stock in question from a web service
3. Calculates Normal and Historical VaR
4. Calculates Conditional-Value at Risk or CVaR
5. Plots Actual vs Simulated returns

The user needs to fill in the above parameters. Annual Volatility, Risk Free Rate, and Confidence Level parameters are required for simulating the prices. They are also needed for computing historical VaR and CVaR. Convenience yield q has been defaulted to 0. For risk free rates, refer to US Treasury.

Results Summary

CVaR came out to be -5.24% which is higher than both the Normal and Historical VaR. You can phrase the CVaR’s outcome as – if the worst 10% comes true (on a 90% CI), you are going to lose 5.24% on the portfolio. If you owned an investment on “JCP” for $100K, your potential loss could be $5,240. This is the maximum potential risk you are bearing for holding the stock for an additional day. The excess kurtosis came out to be 2.52. In a normal distribution, the kurtosis should be close to zero. The distribution of the historical prices over 365 trading days is negatively skewed which is a possibility with actual return of stocks.

The program then scatter plots Actual vs Simulated prices as shown below. You can apply similar these CVaR strategies for commodities such as oil and gold.

Download Conditional Value At Risk Simulation