Standard Deviation

[Retired]

Trying to understand the implementation of standard deviation in the simulation. Just with focus on SD and not taking any withdrawals, the variance of each year Bottom, Top, port value becomes wider and wider. Using a 5% return and 10% SD, starting taxable balance of 1000, and a 20 Year run, .....year one is 256, year 20 is 3070, ... difference between bottom and top values. Is SD calculated for each year? I am not understanding the numbers here. Thanks for any insight.

[jimr]

This is tough to explain with a concise answer, but in general, standard deviation roughly controls the amount of variation in annual return from year to year within each run through the simulation. The greater the year-to-year variation in returns, the greater the cumulative impact on ending portfolio size. Variation in returns builds or compounds considerably over the course of a retirement plan. The overall amount of variation in the ending portfolio value is dependent on the standard deviation and on the number of years in the plan.

The goal of the simulation is to estimate the robustness of a retirement plan by creating thousands of retirement trial-runs, which hopefully resemble possible realities based on what we know and can model. To accomplish this, each year of each trial run is given a pseudo-random number between 0 and 1 that is converted into a rate of return for that year, based on the average return and the standard deviation that's specified in the inputs. That last sentence is the heart of a Monte Carlo simulation. Everything else in the code is basically just housekeeping to manage cash flows, taxes, and other misc. variables.

Each trial run through the retirement plan produces a sequence of returns from the start of the plan to the end of the plan that determines whether the plan succeeds (eg doesn't run out of money) and the ending portfolio value. For any one trial run or path through the retirement plan, this sequence of returns may appear non-sensical. The year to year returns, taken by themselves in isolation, usually will not conform at all to the return distribution that was specified (eg the return avg/std dev inputs). This is because there are not enough samples in a single retirement path (say 30-40 years) to create a statistically valid sample that looks like the distribution being modeled. This is why we run 10,000 trial runs through the retirement plan. 10,000 runs should give us enough overall samples to roughly see the true contours of the retirement plan results, and especially to generate estimates for likely best-case and worst case results.

Please don't hesitate to post followup questions on any parts of this explanation that don't make sense or need further clarification.

Jim

[Retired]

Jim,
Thanks very much for the quick reply. Yes, I can see each year port value change by clicking run simulation repeatedly. This is the effect of the random number between 0 and 1 that you explained. For one year, is there ~250 days of data for that years calculations? I do not fully understand the calculation of SD, but just trying to recreate something close in excel to help understand this. For example, if just use a couple of years in simulation, the inputs are port balance, return, SD, and the random number. In one year, how are these used? I noticed that median port value is shown, but SD would use mean value. Thanks for helping to understand this very helpful tool.

Regards,
Retired

[jimr]

Retired,

Not sure how much experience you have with Excel, but you might want to take a look at the Portfolio Withdrawal Simulator spreadsheet that's up on the planner's tools page:

http://www.flexibleretirementplanner.co ... nal-tools/

This spreadsheet has an early version of the retirement simulation written in visual basic inside a spreadsheet. You need to enable macros when you open the spreadsheet and can run the simulation by clicking Run on the main sheet.

To see the visual basic code, select Tools->Macro->Visual Basic Editor.

If you click on 'SimulatorMain' in the Visual Basic Editor, you'll see the code for the main simulator including the following:

Code: Select all

  For Iteration = 1 To Iterations
    PortfolioBalance = StartBalance
    PreviousBalance = StartBalance
    PreviousWithdrawal = StartWithdrawal
    Withdrawal = StartWithdrawal
    
    For Year = 1 To YearsInPlan
    
     ' Get normally distributed random values for inflation and return
     Inflation = (NormRnd() * InflationStdev) + InflationAvg
     PortfolioReturn = (NormRnd() * ReturnStdev) + ReturnAvg

     PortfolioBalance = ComputeTaxableBalance(PreviousBalance, PreviousWithdrawal, PortfolioTaxRate, _
                                            PortfolioReturn, Inflation)
     If (PortfolioBalance <= 0) Then
         PortfolioBalance = 0 ' Bummer, we ran out of money on this run!!!
     Else
     If (WithdrawalPolicy = ConservativeWP) Then
       Withdrawal = ComputeConservativeWithdrawal(PortfolioBalance, PreviousBalance, StartBalance, _
                      PreviousWithdrawal, StartWithdrawal, Inflation)
     Else
       If (WithdrawalPolicy = FlexibleWP) Then
         Withdrawal = ComputeFlexibleWithdrawal(PortfolioBalance, PreviousBalance, StartBalance, _
                      PreviousWithdrawal, StartWithdrawal, Inflation)
       Else ' For Stable PP, there's no change in withdrawal,
            ' which means it gets to exactly keep up with inflation
       End If
     End If
     End If
     PreviousWithdrawal = Withdrawal
     PreviousBalance = PortfolioBalance
    Next Year
        EndingBalanceArray(Iteration) = PortfolioBalance
    EndingWithdrawalArray(Iteration) = Withdrawal

    If (Withdrawal > 0) Then
      EndingWithdrawalTotal = EndingWithdrawalTotal + Withdrawal
    End If
    
    If (PortfolioBalance > 0) Then
      SuccessCount = SuccessCount + 1
      EndingBalanceTotal = EndingBalanceTotal + PortfolioBalance
    End If
   
    ' Only call out to update progress in the UI periodically
    ' This check is essential for decent simulation progress
    If ((Iteration Mod ResultRefreshPeriod) = 0) Then
      UpdateSimulationProgress (Iteration)
    End If
    
  Next Iteration

If you can follow it, this shows exactly how a Monte Carlo simulator works.

[Retired]

Jim,

Thanks for the pointer, I will take a look at the code and see if I can figure it out. I have to travel for a few days, but will take a look when I return. Thanks again. Will follow up after I have a chance to look it over.

Regards

[Retired]

Jim,

Took a look at the spreadsheet. It does not run correctly and gives an error when open. Maybe something in this old spreadsheet is not supported in 2013 version of excel. Not able to run any simulation but still able to look at the VBA code and get an idea. I can see where the STD is multiplied by the random number and then added to the average return. Will play around with when have more time. Thanks for help in trying to understand.

Regards