PDA

View Full Version : Simulating returns



jamesbeau
03-25-2016, 07:57 PM
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
03-26-2016, 09:15 AM
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