Simulating returns

[jamesbeau]

Hi Guys,

For an assignment i'm doing, I have to simulate returns which are normally distributed mu, sigma for t periods and then calculating the simulated annualised returns i.e ((1+r1)(1+r2)...(1+rt))^(1/t) -1.

I have attempting to set up a loop to aggregate the returns but am struggling to get the loop working. Any help would be much appreciated.

Function RandReturn(mu, sigma)


RandReturn = WorksheetFunction.Norm_Inv(Rnd, mu, sigma)
End Function


Function SimAnnualReturn(mu, sigma, t)


For i = 1 To t

SimReturn = SimReturn * (1 + RandReturn(mu, sigma))

Next

SimAnnualReturn = (SimReturn ^ (1 / t)) - 1

End Function

[Paul_Hossler]

Not really allowed to do homework assignents here (otherwise how would people learn), but since you did have a specific question about VBA .....


1. I rearranged the code a little and I like to define everything

2. Sometimes variables need to be initialized

3. Hint -- 0.0 x N = 0.0 no matter how many times you do it

'I like this since it makes me careful with typing and choosing / using variables
Option Explicit

'I like to define everything as specifically as possible
Function RandReturn(mu As Double, sigma As Double) As Double
    RandReturn = Application.WorksheetFunction.Norm_Inv(Rnd, mu, sigma)
End Function

Function SimAnnualReturn(mu As Double, sigma As Double, t As Long) As Double
    Dim i As Long
    Dim SimReturn As Double
    
    SimReturn = 1#    ' 1.0
    
    For i = 1 To t
        SimReturn = SimReturn * (1 + RandReturn(mu, sigma))
    Next i
    SimAnnualReturn = (SimReturn ^ (1 / t)) - 1
End Function


'just a way to test
Sub Driver()
    MsgBox SimAnnualReturn(0.5, 0.1, 10)
End Sub