How to Calculate VaR: Finding Value at Risk 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.

Key Takeaways

• Value at Risk statistically measures the likelihood of a specific loss occurring.
• Value at Risk is an industry-wide, commonly-used risk assessment technique.
• The confidence interval of a VaR computation is the chance a specific outcome will occur. The higher the confidence interval, the more likely the outcome.
• There are several different ways to calculate VaR with the historical method being among the easiest to manually calculate.
• Excel can be very helpful in calculating the mean return, standard deviation, and VaR outcomes for various confidence intervals.

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.

Important

VaR is an estimation technique—it is not useful when attempting to determine what will occur. VaR indicates the chance something might happen and what the dollar impact will be at that likelihood.

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.

Important

There are typically 252 trading days in a year, which is why we use that figure instead of 365 to calculate VaR for a daily timeframe.

Finding VaR in Excel

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

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.

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.

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

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.

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.

What Is the Formula for VaR?

There are several different methods to calculate VaR, each with a different formula, The most simple method to manually calculate is the historical method where m is the number of days from which historical data is taken and v_i is the number of variables on day i.

Value at Risk Formula (using the historical method): v_m (v_i / v_{(i – 1)})

What Does Value at Risk Indicate?

Value at Risk measures the statistical likelihood that an investment might experience a loss given a specific probability. Value at Risk also quantifies what that loss might be at various confidence intervals.

Is a High VaR a Good Thing?

When analyzing VaR, a high value for the confidence internal percentage is a good thing. This means we are more confident in the likelihood of the projected outcome. Alternatively, a high value for the projected outcoming is not ideal. This means we statistically anticipate a higher dollar loss to occur.

Read the original article on Investopedia.