## Value at Risk – VaR Historical Simulation approach in Excel

*in*Editors Choice

# VaR Historical Simulation approach in Excel.

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 and see what a tool like Value at Risk can tell us about the likely (most common) as well as the worst case (extreme) movement for this exchange rate.

We will calculating Value at Risk Historical Simulation approach using Excel.

**Introducing Value at Risk**

For example take a look at the following Excel histogram.

This histogram is calculated using a series of daily prices 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.

Within the supporting excel sheet under the USD-EUR tab you will a table that takes the daily exchange rate series and calculates the daily returns. 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.

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

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, go to Excel Options, chose Addins and then simply add the Data Analysis Addin to enable this tab.

To generate the Histogram select the daily return series by calculating the percentage change in prices from one day to the next and use that 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.

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

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+%

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.

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 any one 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 our Excel histogram output worksheet already has a table with these probabilities and numbers in it.

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 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 and it is used as a proxy for the amount of capital required to support such losses.

**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.

## Value at Risk – Using the right models

As is the case with practitioners, as we solve new problems (sometime 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. 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 in something that is literally stitched together by chewing gum and baling wire.

While Historical simulations had its own issues, atleast 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 more 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 *calculating value at risk case study and example*,**Portfolio Var – Shortcut approach without VCV****lessons. **