Value at Risk with Monte Carlo Simulation

This Excel spreadsheet calculates Value at Risk through the Monte Carlo simulation of geometrical brownian motion in VBA.

This post describes the code, but if you just want to download the spreadsheet scroll down to the bottom.

Value at Risk Monte Carlo Simulation Excel

The price St of a stock undergoing Brownian motion obeys this equation

The notation is given at Wikipedia, so I won’t cover it here. Now, in the spreadsheet I want to calculate the stock return, so I make a small modification to this equation as follows.

This is translated into VBA like so,

stockReturn(i) = Exp((RiskFree – 0.5 * StDv ^ 2) + StDv * Application.NormInv(Rnd(),0, 1)) – 1

where

  • StDv is the standard deviation,
  • RiskFree is the risk free rate,
  • Application.NormInv()is the quantile function (i.e. the inverse of the cumulative probability distribution). Application.NormInv(Rnd(),0, 1)) models Brownian motion (i.e. a Wiener process),and
  • and i is a counter variable that increments from 1 to 10000, and represents multiple Monte Carlo runs.

Hence the Value at Risk is

ValueAtRiskMC = -(horizon) ^ 0.5 * Application.Percentile(dailyChange, 1 – confidence)

The entire VBA function is

Function ValueAtRiskMC(confidence, horizon, RiskFree, StDv, StockValue)
Dim i As Integer
Dim stockReturn(1 To 10000) As Double
For i = 1 To 10000
stockReturn(i) = Exp((RiskFree – 0.5 * StDv ^ 2) + StDv * Application.NormInv(Rnd(), 0, 1)) – 1
Next i
ValueAtRiskMC = -(horizon) ^ 0.5 * Application.Percentile(stockReturn, 1 – confidence)
ValueAtRiskMC = StockValue * ValueAtRiskMC
End Function

This technique for calculate VaR is very flexible, and can be extended to any other returns probability distribution.  I’ve previously written about some of the limitations of Value at Risk – you might want to read the article for some background

Download Excel Spreadsheet to Calculate Value at Risk with Monte Carlo Simulation


One thought on “Value at Risk with Monte Carlo Simulation

  1. i think that the first equation is false (but the code is still correct). The equation was probably already false on Wikipedia and has been corrected in the meantime.

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 9 + 6 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)