Browse By

Value at Risk. VaR models methods & metrics using EXCEL

Calculating Value at Risk (VaR). Comparing VaR models, methods & metrics

Have you ever wondered what Value at Risk (VaR) numbers would look like across the same data set but using the different calculation approaches? In today’s VaR Excel spreadsheet walk through session we will do just that. We will compare VaR results across SMA VaR, EWMA VaR, Variance co Variance VCV 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.

Comparing VaR models, methods & metrics – basic portfolio construction

Consider a portfolio comprising of positions in the following:

Figure 1: Investment Portfolio

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:

  1. Master Class: Calculating Value at Risk: Introduction
  2. Master Class: Calculating Value at Risk (VaR): VaR Methods
  3. Master Class: Calculating Value at Risk (VaR): First Steps
  4. Master Class: Calculating Value at Risk (VaR): Final steps
  5. 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:

Figure 2: Calculating return series for each position

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:

Figure 3: Calculating portfolio return series

Comparing VaR Models, methods & metrics. 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.

Figure 4: Calculating daily SMA volatility

  • Using an exponentially weighted moving average (EWMA) approach where sigma is determined as Depending 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 weights Depending 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.

Figure 5: Calculating weights for determining EWMA volatility

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


Figure 6: Calculating squared returns


Figure 7: Calculating EWMA Variance

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


Figure 8: Calculating EWMA volatility

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:

Figure 9: SMA & EWMA VaR

Comparing VaR Models, methods & metrics. 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

Figure 10: Determining bin range values for histograms

  • 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:

Figure 11: Calculating Cumulative Frequency

  • 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

Figure 12: Histogram of historical Crude Oil price return series

  • 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%.



Figure 13: Daily historical simulation VaR

Comparing VaR Models, methods & metrics. 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.

Figure 14: Histogram of simulated Crude Oil price return series

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

  1. Generate randomly simulated prices
  2. Calculate the daily return series including the portfolio return series.
  3. Determine daily VaR as per the historical simulation histogram approach
  4. 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.
  5. Calculate the average VaR across all simulated runs
    Figure 15: Average MC simulated Daily VaR across all simulated runs
    Here is the summary of results from all the approaches:

Figure 16: Summary of results from all approaches

Comparing VaR Models, methods & metrics. 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:

  1. Full valuation approach
  2. 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:

Figure 1: Investment Portfolio

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:

Figure 17: Revised Portfolio values, weights, volatility and VaR

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.


Figure 18: Calculation of IVaR using the approximate method

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.

Comparing VaR Models, methods & metrics. 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:

Figure 19: Portfolio weights – original and revised positions

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

Figure 20: Portfolio returns – original and revised series

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

Figure 21: VaR – original and revised values

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.

Comparing VaR Models, methods & metrics. 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.


Figure 22: Loss amounts

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:

Figure 23: Conditional loss amounts

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.

Figure 24: Conditional VaR

Comparing VaR Models, methods & metrics. 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%.

Figure 25: Evaluating the probability of short fall for crude oil from the histogram table

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:

Figure 26: Probability of shortfall

Buy now from our EXCEL Download Store

References:

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

4 thoughts on “Value at Risk. VaR models methods & metrics using EXCEL”

  1. Pingback: Comparing Value at Risk (VaR) Calculations across models and types | Finance Training Course
  2. Trackback: Comparing Value at Risk (VaR) Calculations across models and types | Finance Training Course
  3. Martin Mana says:

    Hello,

    Is there somewhere I can find the example´s excel sheet ? is it available for dowload?

    thanks a lot

    best regards

  4. Uzma says:

    Dear Martin,

    Thank you for your interest in our post “Value at Risk (VaR) models, methods & metrics – Excel spreadsheet walk through”.

    We do have an EXCEL sheet available for purchase – Comparing Value at Risk – Model, Methods and Metrics – EXCEL at the store under the “RISK MANAGEMENT” category at the following link http://financetrainingcourse.com/store/#!/~/product/category=1944018&id=17285013.

    Please review the file description to confirm if this is what you are looking for before proceeding with the purchase.

    Regards
    FinanceTrainingCourse.com

  5. sushant says:

    I would like to know where from I can get data associated with RiskMetrics. The riskmetrics doc says that it is freely available but I could fetch from Web. Kindly guide me.

Comments are closed.

Comodo SSL