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.
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,
- 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
The entire VBA function is
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