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 S_t 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