Have you ever wondered what Value at Risk (VaR) numbers would look like across the same dataset but using the different calculation approaches? In today’s VaR Excel spreadsheet walkthrough session we will do just that. We will demonstrate how to calculate VaR in EXCEL using SMA VaR, EWMA VaR, Variance Covariance VaR, Historical Simulation VaR and Monte Carlo Simulation VaR. We will then dig deeper and calculate incremental value at risk, marginal value at risk and conditional value at risk. And before we close we will take a short stab at probability of shortfall.

## Basic portfolio construction

Consider a portfolio comprising of positions in the following:

We aim to calculate VaR using the following approaches:

- Variance Covariance Approach
- Historical Simulation Approach
- Monte Carlo Simulation Approach

You may like to refresh your memory regarding the description and basic mechanics of each approach by taking some time first to look at the following posts before proceeding ahead:

- Master Class: Calculating Value at Risk: Introduction
- Master Class: Calculating Value at Risk (VaR): VaR Methods
- Master Class: Calculating Value at Risk (VaR): First Steps
- Master Class: Calculating Value at Risk (VaR): Final steps
- Value at Risk – Margin Lending Case study

In addition to going through these approaches we will also look at other VaR related risk measures such as:

**Incremental VaR**which measures the impact of small changes in individual positions on the overall VaR**Marginal VaR**which measures how the overall VaR would change if one position was completely removed from the portfolio**Conditional VaR**which measures the mean excess loss or expected shortfall beyond VaR at a given confidence level**Probability of Shortfall**which measures the probability that investment returns will not reach a given goal or alternatively the probability that investment returns will fall below a given goal

For this exercise we have obtained data for WTI, EUR-USD exchange rate, Gold and Silver for the period January 2004-September 2012 from EIA, OANDA, onlygold.com and kitco.com respectively.

Before we move on to the specifics of each approach we will determine the return time series for each position. This is obtained by taking the natural logarithm of successive prices. This return series is the foundation for all the methods (except Monte Carlo simulation) and metrics mentioned above:

We will also determine the portfolio return series. As you may recall, this return series is a correlation adjusted series, i.e. a series that takes into account the correlation between the various positions in the portfolio. Using the weights of each position with respect to the portfolio we calculated a weighted average sum of the returns for each point in time:

## VaR Approaches

### 1. Variance Covariance (VCV) Approach

This method assumes that the daily returns follow a normal distribution. The daily Value at Risk is simply a function of the standard deviation of the positions return series and the desired confidence level.

Within the approach sigma may be calculated in two ways:

- Using a simple moving average (SMA) approach which places equal importance on all returns in the series. Using EXCEL, sigma can be determined by using the STDEV function on the array of return series.

- Using an exponentially weighted moving average (EWMA) approach where sigma is determined asDepending on the lambda chosen greater emphasis may be placed on more recent durations as compared to the SMA approach.
- We start of by specifying a value for lambda. The smaller the value of lambda the greater is the weight that is applied to more recent observations. We have used a lambda of 0.5.
- Next we determine the weightsDepending on the sample size used the weights may need to be scaled (by dividing the each weight by 1-(lambda^n)) so that the sum of weights equals one.

- Determine the EWMA variance by taking the weight average sum of the weights and the squared returns as given in the formula above.

- Determine the EWMA volatility by taking the square root of the resulting variance.

Once we have obtained daily volatility we determine the daily VaR. This is the product of the volatility and the inverse of the standard normal cumulative distribution for a specific confidence level. For a confidence level of 99% the inverse z-score works out to 2.33. The results for the VCV SMA and VCV EWMA approaches are given below:

### 2. Historical Simulation VaR Approach

Historical simulation is a non-parametric approach for estimating VaR, i.e. the returns are not subjected to any functional distribution. VaR is estimated directly from the data without deriving parameters or making assumptions about the entire distribution of the data. This methodology is based on the premise that the pattern of historical returns is indicative of future returns.

We use the histogram of returns to determine daily VaR. EXCEL Data Analysis histogram function may be used directly on the return series calculated or you may in turn derive the histogram yourself as follows:

- Calculate a minimum and maximum return of the return series
- Calculate the width of each bin for the histogram. This will be the difference between the minimum and maximum divided by the number of bins or buckets that you want to use. We had used 60 buckets.
- Calculate the bin range values as: bin (1) = minimum value of return, bin (t) = bin (t-1) + bin width

- Determine the Cumulative Frequency, i.e. the number of returns that fall with the specified bucket. Using the COUNTIF EXCEL function we determine the number of returns that are less than or equal to the bin value:

- Determine Frequency for Bin (t) as Cumulative Frequency (t) less Cumulative Frequency (t-1)
- Determine Cumulative % as Cumulative Frequency/ total number of return observations
- Determine Confidence Level as 1-Cumulative Frequency

- Determine the VaR value that corresponds to specified confidence level. For example for a confidence level of 99% the VaR will be the interpolated value between -6.98% and -6.49%, i.e.6.565%.

### 3. Monte Carlo Simulation Approach

The approach is similar to the Historical simulation method described above except for one big difference. A hypothetical data set is generated by a statistical distribution rather than historical price levels. The assumption is that the selected distribution captures or reasonably approximates price behavior of the modeled securities. For illustration purposes only we have used the Black Scholes terminal price formula, as our Monte Carlo simulator.

We have replaced mu by r, the risk free rate of 1%, used the annualized scaled daily SMA volatility as our estimate of sigma and taken the initial spot price to be the price available at the end of the period of analysis.

However, for this method to be applied in practice we need to select a model that is able to explain the behavior of that position’s price over time so that the simulated distribution of values converges to the true distribution of values of the positions and portfolio.

The Monte Carlo VaR model is created using the following steps:

- Generate randomly simulated prices
- Calculate the daily return series including the portfolio return series.
- Determine daily VaR as per the historical simulation histogram approach
- Repeat the above three steps a large number of times. EXCEL’s Data Table functionality may be utilized to generate a table of results for each simulated run.
- Calculate the average VaR across all simulated runs

Here is the summary of results from all the approaches:

## VaR Related Risk Measures

### 1. Incremental VaR

Incremental VaR (IVaR) measures the impact of small changes in individual positions on the overall VaR. There are two different approaches to calculating incremental VaR:

- Full valuation approach
- Approximate solution to IVaR or the short cut approach

#### Calculating IVaR – Full valuation approach

In the Full valuation approach the entire process for VaR is repeated based on the revised positions of the portfolio. This means that we are calculating the VaR value twice. Incremental VaR will be the difference between the original VaR calculation and the revised VaR calculation.

Let us review our portfolio once again:

The total portfolio value works out to 31,728.50. The SMA daily VaR was calculated as 3.12%. The portfolio VaR amount worked out 31728.50*3.12% = 991.23

Assuming a 1% increase in all positions with respect to the total portfolio value the revised portfolio (all position increase by 317.285) will be as follows:

The revised portfolio VaR works out to 32997.64*3.10% = 1023.89

The incremental VaR therefore is 32.66.

This process may not seem too tedious with just 4 positions but a financial institute may have a portfolio comprising of hundreds of different positions which could make the full valuation process a time consuming one.

There is however a short cut solution to IVaR that does not require two separate VaR valuations.

#### Calculating IVaR – Approximation to IVaR

The incremental VaR using the short cut method is approximately equal to:

**Absolute value of (Change in position relative to original portfolio value *Original Portfolio Value) * inverse z-score of selected confidence level * Covariance between original position returns & original portfolio returns/ Original Portfolio volatility.**

If you look at the above formula you will notice that we have attached a condition on the calculated Covariance. If we are long on the original position and the incremental position is an increase in the position or if we are short of the original position and incremental position increases the short position then we take the covariance as is. However if we are long on the original position and the incremental position is a decrease in the position or if we are short of the original position and incremental position decreases the short position then we take the covariance as -1*covariance.

Using the approximate method we arrive at an incremental VaR of 32.58 which is 0.09 less than the IVaR figure derived using the full valuation approach.

**Note: **The Covariance for the EWMA VaR approach is calculated as the sum product of the following series- the return series of a given position, the return series of the original portfolio and the scaled weights.

### 2. Marginal VaR

Marginal VaR measures how the overall VaR would change if one position was completely removed from the portfolio.

As a first step in this process we determine the weights of the portfolio assuming all four positions are present and then again if one position (at a time) were to be eliminated as follows:

Next we recalculate the portfolio return series using the revised weights as follows:

We then recalculate the volatility, VaR % and VaR amount based on the revised return series:

The marginal VaR if crude oil was removed from the existing portfolio is 226.51, i.e. the VaR amount will reduce by this figure. Note that in % terms portfolio VaR has increased but the portfolio value to which the VaR% applied has reduced hence there is a reduction in the overall VaR amount.

### 3. Conditional VaR

Conditional VaR measures the mean excess loss or expected shortfall beyond VaR at a given confidence level.

For our illustration purposes we assume that VaR % has been calculated using the Historical Simulation approach. The VaR amount is calculated as VaR % * Portfolio or Position Value.

For each data point we first calculate the amount of loss which is the Portfolio or Position Value * Return. If the return is negative it will be taken as a loss whereas if it is positive zero losses will be recorded.

We next determine the conditional losses. In effect this is the loss amount already calculated above but subject to a restricting condition, i.e. only those losses than exceed the VaR amount will be considered:

We next calculate a weighted average VaR % where the weights are based on the conditional losses. This is calculated as the sum product of the Returns times the Conditional Losses. The result is then divided by the sum of the Conditional Losses. This average VaR% is the Conditional VaR %. When multiplied with the portfolio or position value we arrive at the Conditional VaR amount or the average loss amount given that VaR is exceeded.

### 4. Probability of Shortfall

**Probability of Shortfall **measures the probability that investment returns will not reach a given goal or alternatively the probability that investment returns will fall below a given goal. For illustration purposes we assume that our goal is that position or portfolio returns should never be negative, i.e. they should be greater than zero.

Given our distribution of returns what is the probability that returns will be negative?

Using the histogram methodology of the Historical Simulation approach we have already determined the cumulative frequency of returns being less than or equal to a particular value. We just need to identify this cumulative frequency for returns less than or equal to 0%.

For Crude Oil we can see that the probability that returns will be negative is 44.36%.

For the other positions the probability of shortfall is given as:

**Buy now from our EXCEL Download Store**

**References:**

- “Beyond Value at Risk – The New Science of Risk Management” – Kevin Dowd