Calculate Value At Risk in Excel

Today I’d like to clarify the concept of Value At Risk.  I’ll demonstrate how you can calculate VAR in Excel, but I’ll also discuss some of its limitations.

Value at Risk, or VaR as it’s commonly abbreviated, is a risk measure that answers the question “What’s my potential loss”. Specifically, it’s the potential loss in a portfolio at a given confidence interval over a given period.  There are three significant parts to VAR.

• A confidence level.  This is typically 95% or 99%.
• A time period. This could be a day, month or a year.
• Your potential loss over the chosen time period at the given confidence level, under normal market conditions (the final qualifier is often forgotten!)

There are several approaches to calculating value at risk, but here I’ll explore the simplest (other techniques, including Monte-Carlo simulations and delta-gamma methods are described here).

Calculating Value at Risk Based on a Normal Distribution

First you’ll need to specify several parameters, as illustrated in Figure 1.

1. The value of your portfolio
2. Average return for a single time period (this could be over a day, month or year)
3. Standard deviation of the returns for a single time period
4. Your desired confidence level

Figure 1

Now perform the calculations as specified in Figure 2

Figure 2

1. Calculate the minimum expected return with respect to the confidence level (i.e. if your confidence level is 99%, then you’re 99% sure that your return will be above this).  This is done with Excel’s NORM.INV() function.
2. Calculate the minimum expected return (at the given confidence level)
3. Now calculate the value at risk for a single time period

You now have your value at risk for a single time period. Let’s say that time period is a single day.  To convert the value at risk for a single day to the correspding value for a month, you’d simply multiply the value at risk by the square root of the number of trading days in a month. If there are 22 trading days in a month, then

Value at risk for a month = Value at risk for a day x √ 22 

Limitations and Disadvantages to Value At Risk

There are two major limitations to using VaR as a risk measure.

VaR is not your worst case loss. At a confidence level of 95%, the VaR is your minimum expected loss 5% of the time. It’s not your maximum expected loss. This is the most overlook limitation, can can lead to a false sense of security.  The actual loss on those 5% of trading days may just be a few dollars, or enough to overwhelm your company.

VaR is not necessarily valid when you need it the most. VaR typically assumes that investment returns have a normal distribution, and the investment behaviour is well-predicted at the tail. But investments tend to be fat-tailed and don’t always follow a normal distribution.  So the circumstances VaR was designed for (i.e. events which do not occur often but have a high impact) are those circumstances in which these assumptions is not valid.

In fact, David Einhorn, president of Greenlight Capital remarked that Value at Risk is “an airbag that works all the time, except when you have a car accident”

In summary, Value At Risk should only be be one of several risk measures tools you use.

Download Spreadsheet to Calculate Value at Risk in Excel