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.

>What about all them other spreadsheet macros?
Change 'em