In investment analysis, value-at-risk, called VaR, is a measure of investment risk. In essence, it takes a confidence interval and assigns it to a maximum level of potential loss. VaR is used by investors and institutions to help determine how much cash to keep on hand to cover potential losses. Thus, it’s very important to ensure the accuracy of VaR calculations. This is done with a simple process called backtesting. Let’s learn how to backtest VaR in Microsoft Excel.
How to Backtest VaR in Excel
Imagine that you have a 99% daily VaR of $350 for a given investment. It helps to understand exactly what this measure is telling you. It means that on 1% of trading days, you expect to lose $350 or more. Now, you want to evaluate this model to see if it is accurate. To do so, you’ll need to use past history of actual losses.
Investment professionals will typically use large volumes of data. The more data there is, the more accurate the evaluation of the model. For this example, we’ll use ten trading days, but a full year (around 252 days) is more common.
For a full trading year, you’d expect to lose $350 on 2.52 days. For ten days, you’d expect to lose $350 only 0.1 times – not a likely outcome.
Consider your table of actual returns, shown here in cells A1:B11. To perform a backtest of VaR, you need to count how many trading days incurred losses of at least $350.
To backtest, enter a COUNTIF function in a blank cell:
=COUNTIF(B2:B11,">350")
Hit Enter on your keyboard, returning the result of 4 trading days with a loss of at least $350. This is a clear indication that you should reject your VaR calculation and calculate a more accurate value.
As you can see, backtesting VaR in Excel is a quick and easy way to evaluate investment risk measurements.
Formula for Income Tax Calculation