Quick guide
0. Geometric Brownian motion (GBM)
Formulas:
$$\begin{equation} \Delta S = \mu S \Delta t + \sigma S \epsilon \sqrt{\Delta t} \end{equation}$$
$$\begin{equation} S_{t + \Delta t} = S_t \exp \left[ \left( \mu - \frac{\sigma^2}{2} \right) \Delta t + \sigma \epsilon \sqrt{\Delta t} \right] \end{equation}$$
1. the VBA code
1.1 Generate epsilon
Write values to WS, column Eps
Code 1: Sub
GenerateEpsilon
random drawings from N(0,1) distribution
Option Explicit Const Num As Long = 100 Sub GenerateEpsilon() Dim i As Long Dim App As Application: Set App = Application VBA.Randomize With Range("Eps") For i = 0 To Num .Offset(i, 0).Value = App.Norm_S_Inv(Rnd) Next i .Resize(Num + 1, 1).NumberFormat = "0.000000" End With End Sub '
1.2 Generate GBM trajectories
Write values to WS, columns GBM.1, and GBM.2
Code 2: Sub
demoGBM
generate GBM from equations 1 and 2
Sub demoGBM() Dim i As Long Dim Mu As Double Dim Sigma As Double Dim Dt As Double Mu = 0.05 Sigma = 0.2 Dt = 1 / CDbl(Num) ' equation 1 [GBM.1].Offset(0, 0) = 20 For i = 1 To Num [GBM.1].Offset(i, 0) = [GBM.1].Offset(i - 1, 0) + _ Mu * [GBM.1].Offset(i - 1, 0) * Dt + _ Sigma * [GBM.1].Offset(i - 1, 0) * [Eps].Offset(i, 0) * (Dt ^ 0.5) Next i [GBM.1].Resize(Num + 1, 1).NumberFormat = "0.000000" ' equation 2 [GBM.2].Offset(0, 0) = 20 For i = 1 To Num [GBM.2].Offset(i, 0) = [GBM.2].Offset(i - 1, 0) * VBA.Exp( _ (Mu - (Sigma ^ 2) / 2) * Dt + _ Sigma * [Eps].Offset(i, 0) * (Dt ^ 0.5) _ ) Next i [GBM.2].Resize(Num + 1, 1).NumberFormat = "0.000000" End Sub
- Download: xlf-gbm.xlsm [31 KB ]
- Development platform: Office 365 ProPlus - Excel 2016 MSO (16.0...) 64 bit
- Published: 16th May 2018
- Revised: Friday 24th of February 2023 - 02:38 PM, Pacific Time (PT)