Geometric Brownian Motion simulation?

[szesz]

I want to simulate the stock price movements that follow geometric brownian motion with user-given parameters (initial stock price, volatility, drift, number of simulations) with time steps of 5 mins (so for 1 year 1*365*24*60/5=105120 no. of simulations are needed).
E.g. with the parameters S0=100$, μ(exp. return)=10%, σ(vol)=25%, Δt(time step)=5 mins=1/365*24*60/5 year=1/105120:
the first part of the change is deterministic: the μ expected return for 1 step (5 mins):
μSdt = 10%*100*1/105120
the next part is a random element, what we estimate with a Wiener-process for 5 mins:
σSdW =25%*100*(1/105120)0,5 * NORM.S.INV(RND())
so altogether the stock price for the next period:
S1=100 + 10%*100*1/105120 + 25%*100*(1/105120)0,5 * NORM.S.INV(RND())
and so on for each following value of S.
How can I simulate this easily in VBA?
Also, I'd like to illustrate the distribution of path minimums, path maximums and averages for each trajectory/realization of the simulation, how can I do that?

(The problem is that I can do this more or less in Excel with functions and etc. but I am completely lost as in how to implement this into VBA to make it simpler and more user-friendly...)
Any help is greatly appreciated, I hope I was clear! (English is not my 1st language sorry)

[Paul_Hossler]

Not sure about some of your fomulas, but this is the way I read them

Easily changed if I guessed wrong


'InitialStockPrice = $100
'Volatility = 25%
'Drift = 10%
'Number of iterations = 105120
'µSdt = Drift * Initial Stock Price / N
's0 = ISP
'Sn = ISP + µSdt + (Volatility x ISP * .5 * NORM.S.INV(RND())






Option Explicit

Sub Calc()
Application.ScreenUpdating = False
Call ManyTimes(Range("g1"), Range("a2"), Range("b2"), Range("c2"), Range("d2"), Range("e2"))
Application.ScreenUpdating = True
End Sub

Sub ManyTimes(r As Range, InitialStockPrice As Double, Drift As Double, Volatility As Double, NumMin As Double, NumberOfRuns As Long)
Dim iRow As Long, iCol As Long
Dim DriftStd As Double, PartOfYear As Double

PartOfYear = 365# * 24# * 60# / NumMin


'µSdt = 10 * 100 * 1 / 105120
DriftStd = Drift * InitialStockPrice / PartOfYear

With r.Cells(1, 1)

For iCol = 1 To NumberOfRuns

'InitialStockPrice=100$
.Cells(1, iCol).Value = InitialStockPrice

For iRow = 2 To PartOfYear
'Sn = 100 + 10%*100*1/105120 + 25%*100*(1/105120)0,5 * NORM.S.INV(RND())
.Cells(iRow, iCol).Value = InitialStockPrice + DriftStd + (Volatility * InitialStockPrice * 0.5 * Application.WorksheetFunction.Norm_S_Inv(Rnd()))

If iRow Mod 1000 = 0 Then Application.StatusBar = "Run Number = " & iCol & " Iteration Number = " & Format(iRow, "#,##0")
Next iRow

DoEvents

Next iCol
End With


r.CurrentRegion.NumberFormat = "##0.00"


Application.StatusBar = False
End Sub

[p45cal]

I agree with Paul about the formulae; not knowing anything about them though, might mean I'm awry on all this:

1. In your formula μSdt = 10%*100*1/105120 what is the 100?; is it 100 converting 10% to a real number or is it the initial stock price of 100?
2. In the formula: S1=100 + 10%*100*1/105120 + 25%*100*(1/105120)0,5 * NORM.S.INV(RND())
when calculating S2 would you substitute (some of) the 100s with S1?
3. Regarding showing distribution of minima, maxima and averages, could you give a bit more detail? (Distribution over time, value, both? How might you want it to appear?)

I've got time to kill today so I'd be happy to play around with it.
You say you've sort-of done it with formulae; providing a file here with that solution would answer a lot of questions.

[szesz]

Thank you both for the answers!
Regarding the questions:
1. Yes, 100 is the initial stock price (S0)
2. Exactly, for each n run in the formula it would be S(n-1)
3. I would need the distribution over time!

I've found this spreadsheet file just now which is exactly what I need, and I think it is more straightforward/self-explanatory than my version, so I hope it can give more details on what I want with better explanations:

[p45cal]

I'm not able to open the attachment - says it's not in the correct format or may have been corrupted. Can you link to a site where you got it from perhaps?

[szesz]

15995
Does it work now?

[p45cal]

Does it work now?Yes. (The chart source data is still from the workbook you copied it from but that's OK, I can adjust it.)

[Paul_Hossler]

I've found this spreadsheet file just now which is exactly what I need, and I think it is more straightforward/self-explanatory than my version, so I hope it can give more details on what I want with better explanations:
15994


That's the temporary file Excel opens during an editing session.

It normally gets cleaned up when Excel exits

[Paul_Hossler]

Having the formulas helped

Here's a version

1. I made it so that if you change the imput parameters, it will regenerate

2. I added a number of steps parameter

3. The Happy Face will also regenerate the numbers





Option Explicit

Sub Brownian()
Dim Szero As Double, Volatility As Double, Drift As Double, TimeStep As Double, NumSteps As Long
Dim rData As Range
Dim iStep As Long
Dim vData() As Double
Dim sFormula As String


Szero = Range("C5").Value
Volatility = Range("C6").Value
Drift = Range("C7").Value
TimeStep = Range("C8").Value
NumSteps = Range("C9").Value
'clear previous
Set rData = Range("A12")
Range(rData, rData.End(xlDown).End(xlToRight)).Clear

'init
Set rData = rData.Cells(1, 1).Resize(NumSteps, 5)

ReDim vData(1 To NumSteps, 1 To 5)

vData(1, 1) = 0
vData(1, 5) = Szero

For iStep = 2 To UBound(vData, 1)
Randomize
vData(iStep, 1) = vData(iStep - 1, 1) + TimeStep
vData(iStep, 2) = Drift * TimeStep * vData(iStep - 1, 5)
vData(iStep, 3) = Application.WorksheetFunction.Norm_S_Inv(Rnd) * Sqr(TimeStep) * Volatility * vData(iStep - 1, 5)
vData(iStep, 4) = vData(iStep, 2) + vData(iStep, 3)
vData(iStep, 5) = vData(iStep, 4) + vData(iStep - 1, 5)
Next iStep

rData.Value = vData
rData.Columns(1).NumberFormat = "0.00"
rData.Columns(2).NumberFormat = "0.000"
rData.Columns(3).NumberFormat = "0.000"
rData.Columns(4).NumberFormat = "#,##0.00"
rData.Columns(5).NumberFormat = "#,##0.00"

sFormula = "=SERIES(,'"
sFormula = sFormula & ActiveSheet.Name & "'!"
sFormula = sFormula & rData.Columns(1).Address & ",'"
sFormula = sFormula & ActiveSheet.Name & "'!"
sFormula = sFormula & rData.Columns(5).Address & ",1)"

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).Formula = sFormula
End Sub

[p45cal]

The attached is an adaptation of Paul's last attachment.
It goes towards an analysis of maxima, minima and averages; so far only maxima and minima.
I've added 2 more columns (max and min) and plotted these on the chart: red points for minima and green points for maxima. This is just for a visual test of the code.
Currently the new columns contain the prices at those max./min. values, whereas they should probably contain the times. We could probably split the total timeline into say 10 segments and count the number of maxima/minima in each segment and produce a histogram, but I've not gone further since I'm still unsure of how you might want to summarise the data. Then there's the averages too; perhaps a similar treatment where we calculate the average price over each of the 10 segments?

Note that I've only developed this on Excel 2003 as I'm away from my usual machine just now (hence time to kill) and that's all there is here. This means that I've had to change some newer code which is incompatible with Excel 2003; the likes of FullSeriesCollection to SeriesCollection and Norm_S_Inv to NormSInv, and I took out the Option Explicit 'cos I was being lazy.

[Paul_Hossler]

Updated _02 to use post-2003 functions

I did change the graph to not show markers since IMHO it was cluttering up the display

If I were doing this, I'd consider automating a series of runs, capturing only the fiinal on a summary sheet, and doing an analysis on the ending prices