VaR Historical Simulation Approach – EXCEL

5 mins read

In this post, we will calculate Value at Risk in EXCEL using the VaR Historical Simulation approach.

1. What is Value at Risk?

Perhaps the simplest and common concept you are likely to see when it comes to financial risk management is Value at Risk or VaR for short.  It represents an educated estimate for:

a)      How bad can prices get when they really get bad? or

b)      What is the most that you can lose on a really bad day on account of price changes? or

c)       What is the worst that can happen when the market hits free fall?

While VaR has received a great deal of negative coverage post 2008, before we discuss issues, it would be useful to first determine how to calculate Value at Risk.

There are three methods for calculating Value at Risk. Variance covariance (VCV), Historical Simulation and Monte Carlo Simulation. In this post,  we will start off with a data series for the USD-EUR Foreign currency exchange rate. Then see what the Value at Risk measure can tell us about the likely (most common) and the worst case (extreme) movement for this exchange rate.

We will calculate Value at Risk using the Historical Simulation approach in EXCEL.

2. Guide to the Value at Risk Historical Simulation Approach

For example, take a look at the following EXCEL histogram.

VaR Historical Simulation Approach - Histogram
Calculating Value at Risk – Histogram the first step in the VaR Historical Simulation approach

This histogram is calculated using a series of daily price changes for a given financial security. Within risk terms, we call daily price changes, daily returns, and these returns could be positive or negative.

The histogram above takes a daily return series, sorts the series and then slots each return in a given return bucket.

a. Step 1 – Calculate daily returns

We will first calculate a table of daily returns from the daily USD-EUR exchange rates daily exchange rate series.  Each return is calculated by the application of LN(P1/P0) where LN is the natural log function in EXCEL, P1 is the new exchange rate, P0 is the old exchange rate.  This is approximately equal to the daily % price change in the underlying exchange rate.

VaR Historical Simulation approach - calculating daily returns
Daily returns

We will take this return series and use it to calculate a histogram similar to the one above.

b. Step 2 – Set up & run EXCEL’s Data Analysis Histogram tool

You can find the Histogram tool under the Data Analysis tab in Excel.  If for some reason you don’t see a data analysis tab in your version of EXCEL, to enable the tab do the following:

  • Go to EXCEL Options
  • Choose Addins, and
  • Add the Data Analysis Addin.
Data Analysis - Histogram functionality in EXCEL
Data Analysis – Histogram functionality in EXCEL

To generate the Histogram select the daily return series by calculating the percentage change in prices from one day to the next. Use that return series as your input range.

Opt for New worksheet ply, cumulative percentage and chart output (see below) to see a graphical representation of the Histogram as well as a supporting table.

Setting up the Histogram functionality

Setting up the Histogram functionality

When you press ok, EXCEL will create a new tab for you and show the histogram that you see below.

VaR Historical Simulation approach - Histogram
Histogram generated

c. Step 3 – Interpreting the results of the histogram

As a risk professional our focus is on the downside which in the histogram below is marked at -2.77%. The upside is about 1.99+%.

i. Worst case loss?

So if I asked you what is the worst that can happen, you can easily tell me that my worst scenario based on historical returns as shown by the above histogram is a loss of over -2.77% (the extreme left corner on the bottom) if I am long (bought) Euro or a loss of 1.99% if I am short (sold) Euro versus the US dollar.

ii. Time period? Odds?

Your next two questions should be, over what time and with what odds?

The returns are calculated on a daily basis hence the answer to the first question is over anyone given trading day.

The second answer requires a bit of work.  There are approximately 300 days (frequency count) in the graph above. Your worst case loss is a once in 300 day event. The probability of you seeing a loss greater than this number is 1/300 or 0.33%.

Fortunately, the EXCEL histogram output worksheet already has a table with these probabilities and numbers in it.

Histogram - Table Output
Histogram – Table Output – Odds
iii. Putting it together

If you put it together there is only a .55% chance that you or I will see a worst case loss of over -2.77% on any given trading day if you bought Euros and a 1.1% chance that you will see a loss of over 1.99% if you have sold Euros.

Congratulations. By adding a holding period (a given trading day) and a probability (0.55% / 1.1%) you have converted your simple what is the worst that can happen statement into a value at risk estimate.

Typically Value at Risk (VaR) is expressed in dollar terms. However, since here we only had percentile returns to work with we have expressed it in percentage terms.

Conceptually Value at Risk represents the projected level of losses a trading desk needs to protect itself against under extreme conditions. It is used as a proxy for the amount of capital required to support such losses.

VaR Historical Simulation Approach
VaR Historical Simulation Approach

3. Alternative Value at Risk methods

The approach that we have just used to calculate Value at Risk is also known as the VaR Historical Simulation approach.

You can also calculate Value at Risk using the Variance covariance (VCV) approach or using the Monte Carlo simulation approach.

The VaR Historical simulation approach works with the actual distribution of results (the price and return series), the VCV approach assumes that returns are normally distributed (it imposes the normal distribution) while the Monte Carlo Simulation technique uses a generator function to first simulate a series of prices and then applies the same process we have used above for Historical simulation.

a. Using the right models

As is the case with practitioners, as we solve new problems (sometimes creating other more difficult to solve problems), we build up an inventory of biases and prejudices.

I greatly like Monte Carlo simulation as a tool for dissecting structures and testing pricing and hedging strategies. However, I hate it (Monte Carlo simulation) when it comes to modeling risk. I realize that the Historical Simulation approach has many significant limitations. But over the years, it’s the only approach that has shown some sense of stability. As Nassim Taleb puts it, history is not going to repeat itself, you are not going to be hit by your last worst loss and the next big wave that will wipe you out will not come from where you expect it.

My respect for historical simulation grew when we started modeling Value at Risk for cross currency swaps. Two separate currencies, two separate interest rates, two separate term structures and about a thousand assumptions in between. I would look at the end number our Monte Carlo simulator would throw out in amazement and wonder how could any rationale being put any reliance on something that is literally stitched together by chewing gum and baling wire.

While Historical simulations had its own issues, at least it reduced the assumption set and the usage of historical price data set across currency pairs, rates, term structures and markets made it a lot easier to explain to traders. Traders understand and respect price and price histories. They question assumptions. A model that uses raw prices unfettered by drivers and assumptions for a trader is infinitely superior to a model with more assumptions than equations.

For additional examples and instruction please see the following lessons:


Comments are closed.