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