How can you calculate Value at Risk (VaR) in Excel?

Value at risk (VaR) is one of the most widely known measurements in the process of risk management.  Risk management's aim is to identify and understand exposures to risk, to measure that risk, and then use those measurements to decide how to address those risks. Topically, VaR accomplishes all three; it shows a normal distribution of past losses – of, say, an investment portfolio – and it calculates a confidence interval about the likelihood of exceeding a certain loss threshold; the resulting info can then be used to make decisions and set strategy.

Stated simply, the VaR is a probability-based estimate of the minimum loss in dollar terms we can expect over some period of time.

There are a few pros and some significant cons to using VaR in risk measurement.  On the plus side, the measurement is widely understood by financial-industry professionals and as a measure, it's easy to understand. Communication and clarity are important, and if a VaR assessment led us to say "We are 99% confident our losses won't exceed $5 million in a trading day," we have set a clear boundary that most folks could comprehend. 

There are several drawbacks to VaR, however.  The most critical is that the "99% confidence" in this example is the minimum dollar figure. In the 1% of occasions where our minimum loss does exceed that figure, there's zero indication of by how much. That 1% could be a $100 million loss, 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, meaning they have extreme outlier events on a regular basis – far more than normal distribution would predict.  Finally, the VaR calculation requires several statistical measurements like variance, covariance, and standard deviation. With a two-asset portfolio, this is not too hard, but becomes extremely complex for a highly diversified portfolio. More on that below.

VaR is defined as: 

VaR = [Expected Weighted Return of the Portfolio - (z-score of the confidence interval * standard deviation of the portfolio)] * portfolio value

Usually, a timeframe is expressed in years. But if it's being measured otherwise (i.e., by weeks or days), then 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. 

Like many financial applications, the formula sounds easy – it has only a few inputs – but calculating the inputs for a large portfolio are computationally intense,  You have to estimate the expected return for the portfolio, which can be error-prone; you have to calculate the portfolio correlations and variance; and then you have to plug it all in. In other words, it's not as easy as it looks. 

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