Hello!
I am currently having some troubles on a VBA monte carlo simulation and wanted to reach out to see if anyone was able to help out. I apologize for the length but my question is a bit complicated. I am trying to compare monthly return data on four different assets between May 1996- November 2014, I also have the monthly inflation index for this period. This data is held in cells C2:G223. I am constructing four different portfolios (a conservative, balanced, growth, and aggressive portfolio) that hold varying weights of each asset. I have the necessary inputs on my spreadsheet linked to the appropriate cells using "data validation".
For the VBA, I am trying to simulate the success rate for an investor to end with wealth of greater than or equal to zero at a fixed yearly withdrawal rate, also taking into account inflation. The inputs are starting wealth, choosing a portfolio type, [the weight in each asset changes], select the number of years the person is planning for (20, 25, 30, 35, 40), & enter a withdrawal rate. The outputs of the simluation should be average ending wealth, standard deviation of average wealth, & success rate of ending wealth being greater than or equal to zero.
The Problem: I would like to simulate the effect of "rolling returns". For example, on the input page, if someone puts "20" years, I want vba to first translate it into months: 20*12=240, then I want it to go through the list of returns for each asset class' column starting from C2:C224 then continue back at C2:C20 (so output a return for each of the 240 months). For the next i, I want it to start at C3:224 then continue back at C2:C19. The next step is multipling the returns by the specific weights for the asset in the chosen portfolio type. My code is below, you'll see that I was trying to use VLookup, but I am starting to think I should be using a different function.
Public Sub annuities()
'Parameters
years = Range("years")
InitialWealth = Range("InitialWealth")
withdrawal = Range("withdrawal")
fee = Range("fee")
PortfolioType = Range("PortfolioType")
asset1 = Range("Asset1")
asset2 = Range("Asset2")
asset3 = Range("Asset3")
asset4 = Range("Asset4")
returnC = Range("returnC")
returnB = Range("returnB")
returnG = Range("returnG")
returnA = Range("returnA")
returnI = Range("returnI")
sigmaC = Range("sigmaC")
sigmaB = Range("sigmaB")
sigmaG = Range("sigmaG")
sigmaA = Range("sigmaA")
sigmaI = Range("sigmaI")
TotalMonths = years * 12
totalsims = 0
success = 0
SumSq = 0
SumZ=0
'Number of Repititions
Const simrun = 1000
Dim Portvalue(simrun - 1) As Double
Dim inflationrate As Double
For s = 0 To simrun - 1
Portvalue(s) = InitialWealth
For i = 0 To TotalMonths
'asset class 1
monthlypick1 = Int(223 * Rnd + 1)
ratelookup1 = Application.VLookup(monthlypick1, Worksheets("Returns").Range("A2:H224").Value, 3)
weightedret1 = ratelookup1 * asset1
monthlypick2 = Int(223 * Rnd + 1)
ratelookup2 = Application.VLookup(monthlypick2, Worksheets("Returns").Range("A2:H224").Value, 4)
weightedret2 = ratelookup2 * asset2
monthlypick3 = Int(223 * Rnd + 1)
ratelookup3 = Application.VLookup(monthlypick3, Worksheets("Returns").Range("A2:H224").Value, 5)
weightedret3 = ratelookup3 * asset3
monthlypick4 = Int(223 * Rnd + 1)
ratelookup4 = Application.VLookup(monthlypick4, Worksheets("Returns").Range("A2:H224").Value, 6)
weightedret4 = ratelookup4 * asset4
inflationpick = Int(223 * Rnd + 1)
inflationrate = (Application.VLookup(inflationpick, Worksheets("Returns").Range("A2:H224").Value,8) + 1) ^ 12 - 1
monthlyret = (1 + weightedret1 + weightedret2 + weightedret3 + weightedret4)
EndWealth = Portvalue(s) * (1 + monthlyret)
Portvalue(s) = (Portvalue(s) / inflationrate) - (Portvalue(s) * withdrawal)
Next i
totalsims = totalsims + Portvalue(s) - (Portvalue(s) * SumZ)
Next s
avg = totalsims / simrun
For i = 0 To simrun - 1
SumSq = (Portvalue(i) - avg) ^ 2 + SumSq
Next i
For p = 0 To simrun - 1
If Portvalue(p) >= 0 Then success = success + 1
Next p
Range("SigmaEndWealth") = (SumSq / (simrun - 1)) ^ 0.5
Range("AverageEndWealth") = avg
Range("success") = success / simrun
End Sub
Thanks for your time!
I am currently having some troubles on a VBA monte carlo simulation and wanted to reach out to see if anyone was able to help out. I apologize for the length but my question is a bit complicated. I am trying to compare monthly return data on four different assets between May 1996- November 2014, I also have the monthly inflation index for this period. This data is held in cells C2:G223. I am constructing four different portfolios (a conservative, balanced, growth, and aggressive portfolio) that hold varying weights of each asset. I have the necessary inputs on my spreadsheet linked to the appropriate cells using "data validation".
For the VBA, I am trying to simulate the success rate for an investor to end with wealth of greater than or equal to zero at a fixed yearly withdrawal rate, also taking into account inflation. The inputs are starting wealth, choosing a portfolio type, [the weight in each asset changes], select the number of years the person is planning for (20, 25, 30, 35, 40), & enter a withdrawal rate. The outputs of the simluation should be average ending wealth, standard deviation of average wealth, & success rate of ending wealth being greater than or equal to zero.
The Problem: I would like to simulate the effect of "rolling returns". For example, on the input page, if someone puts "20" years, I want vba to first translate it into months: 20*12=240, then I want it to go through the list of returns for each asset class' column starting from C2:C224 then continue back at C2:C20 (so output a return for each of the 240 months). For the next i, I want it to start at C3:224 then continue back at C2:C19. The next step is multipling the returns by the specific weights for the asset in the chosen portfolio type. My code is below, you'll see that I was trying to use VLookup, but I am starting to think I should be using a different function.
Public Sub annuities()
'Parameters
years = Range("years")
InitialWealth = Range("InitialWealth")
withdrawal = Range("withdrawal")
fee = Range("fee")
PortfolioType = Range("PortfolioType")
asset1 = Range("Asset1")
asset2 = Range("Asset2")
asset3 = Range("Asset3")
asset4 = Range("Asset4")
returnC = Range("returnC")
returnB = Range("returnB")
returnG = Range("returnG")
returnA = Range("returnA")
returnI = Range("returnI")
sigmaC = Range("sigmaC")
sigmaB = Range("sigmaB")
sigmaG = Range("sigmaG")
sigmaA = Range("sigmaA")
sigmaI = Range("sigmaI")
TotalMonths = years * 12
totalsims = 0
success = 0
SumSq = 0
SumZ=0
'Number of Repititions
Const simrun = 1000
Dim Portvalue(simrun - 1) As Double
Dim inflationrate As Double
For s = 0 To simrun - 1
Portvalue(s) = InitialWealth
For i = 0 To TotalMonths
'asset class 1
monthlypick1 = Int(223 * Rnd + 1)
ratelookup1 = Application.VLookup(monthlypick1, Worksheets("Returns").Range("A2:H224").Value, 3)
weightedret1 = ratelookup1 * asset1
monthlypick2 = Int(223 * Rnd + 1)
ratelookup2 = Application.VLookup(monthlypick2, Worksheets("Returns").Range("A2:H224").Value, 4)
weightedret2 = ratelookup2 * asset2
monthlypick3 = Int(223 * Rnd + 1)
ratelookup3 = Application.VLookup(monthlypick3, Worksheets("Returns").Range("A2:H224").Value, 5)
weightedret3 = ratelookup3 * asset3
monthlypick4 = Int(223 * Rnd + 1)
ratelookup4 = Application.VLookup(monthlypick4, Worksheets("Returns").Range("A2:H224").Value, 6)
weightedret4 = ratelookup4 * asset4
inflationpick = Int(223 * Rnd + 1)
inflationrate = (Application.VLookup(inflationpick, Worksheets("Returns").Range("A2:H224").Value,8) + 1) ^ 12 - 1
monthlyret = (1 + weightedret1 + weightedret2 + weightedret3 + weightedret4)
EndWealth = Portvalue(s) * (1 + monthlyret)
Portvalue(s) = (Portvalue(s) / inflationrate) - (Portvalue(s) * withdrawal)
Next i
totalsims = totalsims + Portvalue(s) - (Portvalue(s) * SumZ)
Next s
avg = totalsims / simrun
For i = 0 To simrun - 1
SumSq = (Portvalue(i) - avg) ^ 2 + SumSq
Next i
For p = 0 To simrun - 1
If Portvalue(p) >= 0 Then success = success + 1
Next p
Range("SigmaEndWealth") = (SumSq / (simrun - 1)) ^ 0.5
Range("AverageEndWealth") = avg
Range("success") = success / simrun
End Sub
Thanks for your time!
Last edited:
