While I was playing with the tutorial here, I needed
a simple-minded Monte Carlo spreadsheet. It looks like this:
>Don't you have Monte Carlo spreadsheets? Why do you need ...?
Yes. There are several here,
but they're more complicated and I wanted a simple one.
Anyway, to download the spreadsheet, RIGHT-click on the picture and Save Target (or you can try just clicking on the picture).
Many of our spreadsheets have macros so ...
>Many of our spreadsheets? Don't blame me for those ...
I get often e-mail asking for modifications to certain spreadsheets and that usually means modifications to a macro or two,
so I thought it'd be good if the I showed the macro that's associated with the above spreadsheet.
Clicking the spreadsheet button calls a macro.
The macro is just a bunch of Visual Basic program instructions that look like this:
Dim R As Double, S As Double, N As Integer, I As Double, iter As Long, W0 As Double, j As Long, k As Integer
Dim P As Double, m As Integer, W As Double, e As Double, count As Long, survival As Double
R = Range("D1") ' Mean reurn
S = Range("D2") ' Standard Deviation
N = Range("B3") ' Number of years
I = 1 + Range("B4") ' Inflation factor
iter = Range("B6") ' Number of MC iterations
For m = 1 To 11
count = 0 ' set failures to zero
W0 = Cells(9, 1 + m) ' select initial withdrawal rate
For j = 1 To iter
W = W0 ' set initial withdrawal
P = 1 ' set Portfolio to $1
Randomize ' set random seed
For k = 1 To N
W = W * I ' increase withdrawal
e = Cells(1 + 999 * Rnd, 17) ' table lookup for random gains
g = Exp(R + e * S) ' random (lognormal) gain factor
P = P * g - W ' increment portfolio, subtract withdrawal
If P <= 0 Then ' count dead portfolios
count = count + 1
k = N ' end this simulation
survival = 1 - count / iter
Cells(10, 1 + m) = survival
Don't like the macro? Change it ... like this:
- RIGHT-click on the button that calls the macro into service.
- In the menu that pops up you should see Assign Macro.... Click it.
- Then click on the Edit button which appears.
- Then the macro code appears in a window. (You may have to enlarge the window.) Modify to your heart's content.
- When you're finished, click: File / Close and Return to Microsoft Excel
>What about all them other spreadsheet macros?