Monte Carlo stuff

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).

 Macros

Many of our spreadsheets have macros so ...

>Many of our spreadsheets? Don't blame me for those ...
Pay attention!
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:

 Sub MC() 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 Range("B10:L10").Select Selection.ClearContents 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                     End If                 Next k         Next j         survival = 1 - count / iter         Cells(10, 1 + m) = survival Next m End Sub

Don't like the macro? Change it ... like this:

1. RIGHT-click on the button that calls the macro into service.
2. In the menu that pops up you should see Assign Macro.... Click it.
3. Then click on the Edit button which appears.
4. Then the macro code appears in a window. (You may have to enlarge the window.) Modify to your heart's content.
5. When you're finished, click: File / Close and Return to Microsoft Excel
6. Pray.