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, to measure that risk, and then apply the knowledge to address those risks.

Value at Risk (VaR) Explained

The VaR measurement shows a normal distribution of past losses. The measure is often applied to an investment portfolio for which the calculation gives a confidence interval about the likelihood of exceeding a certain loss threshold. That data is used by investors to make decisions and set a strategy. Stated simply, the VaR is a probability-based estimate of the minimum loss in dollar terms expected over a period.

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, as a measure, it is easy to understand. The VaR offers clarity. For example, a VaR assessment might lead to the following statement: "We are 99% confident our losses will not exceed $5 million in a trading day."

Regarding the drawbacks to VaR, the most critical is that the 99% confidence in the above example is the minimum dollar figure. For the 1% of occasions where our minimum loss does exceed that figure, there is no indication of how much. The loss could be $100 million or many orders of magnitude greater than the VaR threshold. 

Surprisingly, the model is designed to work this way because the probabilities in VaR are based on a normal distribution of returns. But 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. 

Finally, the VaR calculation 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.

What Is the Formula for VaR?

VaR is defined as: 

V a R = [ Expected Weighted Return of the Portfolio   ( z -score of the confidence interval ×  standard deviation of the portfolio)] ×  portfolio value \begin{aligned}VaR &= [\text{Expected\ Weighted\ Return\ of\ the\ Portfolio}\\&\quad -\ (z\text{-score\ of\ the\ confidence\ interval}\\&\quad\times\ \text{standard\ deviation\ of\ the\ portfolio)]}\\&\quad\times\ \text{portfolio\ value}\\\end{aligned} VaR=[Expected Weighted Return of the Portfolio (z-score of the confidence interval× standard deviation of the portfolio)]× portfolio value

Typically, a timeframe is expressed in years. However, if the timeframe is being measured in weeks or days, we divide the expected return by the interval and the standard deviation by the square root of the interval. For example, if the timeframe is weekly, the respective inputs would be adjusted to (expected return ÷ 52) and (portfolio standard deviation ÷ √52). If daily, use 252 and √252, respectively. 

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

As with many financial applications, the formula sounds easy—it has only a few inputs—but calculating the inputs for a large portfolio is computationally intense. You must estimate the expected return for the portfolio, which can be error-prone, calculate the portfolio correlations and variance, and then plug in all the data. In other words, it is not as easy as it looks. 

Finding VaR in Excel

Outlined below is the variance-covariance method of finding VaR [please right-click and select open image in new tab to get the full resolution of the table]: