How Do You Calculate Value at Risk (VaR) in Excel?

Value at Risk (VaR) is one of the most widely known measurements for risk assessment and risk management. The goal of risk management is to identify and understand exposures to risk, measure that risk, and then apply the knowledge to address those risks.

What Is Value at Risk (VaR)?

Value at Risk (VaR) is a measurement showing a normal distribution of past losses. The measurement is often applied to an investment portfolio for which the calculation gives a confidence interval about the likelihood of exceeding a certain loss threshold. The VaR calculation is a probability-based estimate of the minimum loss in dollar terms expected over a period. This data is used by investors to strategically make investment decisions.

Pros and Cons of Value at Risk (VaR)

There are a few pros and some significant cons to using VaR in risk measurement. On the plus side, the measurement is widely used by financial industry professionals and is easy to interpret. VaR computations can be compared across different types of assets or portfolios. Due to its popularity, VaR is also frequently included and calculated for you in various financial software tools such as a Bloomberg terminal.

VaR is often criticized for offering a false sense of security as VaR does not report the maximum potential loss. VaR is also highly dependent on the confidence interval, and the outcome of models calculated with 95% confidence may still occur.

Probabilities in VaR are based on a normal distribution of returns. Unfortunately, financial markets are known to have non-normal distributions. Financial markets have extreme outlier events on a regular basis—far more than a normal distribution would predict. For this reason, a limitation of VaR is the statistically most likely outcome isn't always the actual outcome.

The VaR calculation also requires several statistical measurements such as variance, covariance, and standard deviation. With a two-asset portfolio, this is relatively straightforward. However, the complexity increases exponentially for a highly diversified portfolio. As with other quantitative analysis techniques, the calculation will only be as good as the underlying assumptions of the calculation.

Value at Risk Formula

As with many financial applications, the formula sounds easy as it only has a few inputs. However, manually calculating the VaR for a large portfolio is computationally intense. Also, though there are several different methods of calculating VaR, the historical method shown below is the most simple:

Value at Risk = v_m (v_i / v_{(i - 1)})

M is the number of days from which historical data is taken, and v_i is the number of variables on day i. The purpose of the formula is to calculate the percent change of each risk factor for the past 252 trading days. Each percent change is then applied to current market values to determine 252 scenarios for the security's future value.

Finding VaR in Excel

Below is the process of calculating VaR using a different method called the variance-covariance approach.

1. Import relevant historical financial data into Excel. For a single security, you'll need the current price as well as the historical closing price for the specific period you want to analyze.

2. Calculate the daily rate of change for the price of the security. For each day, this is calculated by dividing the change in price over two days by the old price. For example, to find the return for today, subtract today's price by yesterday's price, then divide that calculation by yesterday's price. Repeat this process for each historical day.

3. Calculate the mean of the historical returns from Step 2. In Excel, this can be achieved by using the average function.

4. Calculate the standard deviation of the historical returns compared to the mean determined in Step 3. In Excel, this can be achieved by using the STDEV function.

5. Calculate the VaR for various confidence intervals. In Excel, this can be achieved by using the NORM.INV function. This function requires the probability of an event occurring, mean, and standard deviation. For multiple calculations of VaR, repeat Step 5 using different probabilities.