An Optimized Solution for Modeling Account Growth in a Retirement Calculator

by Stuart Matthews

The Dilemma for a Designer

In February 2017, Darrow Kirkpatrick (caniretireyet.com) posted an article describing a mysterious problem related to the size of the “time step” used by most retirement calculators in modeling decades of a user’s financial future.  He pointed out that most calculators use years as their time step because using days, the time step unit of the real world, is impractical and then went on to elaborate on the potential modeling errors that could result from this design simplification.  This article elaborates further on this issue and the practical solution that we developed in the course of our collaboration on this topic.

The dilemma Darrow described was in determining the “correct” way to sequence income, expenses and growth of accounts in modeling the increase and/or decrease of account balances over time.  In the real world these are interwoven on a daily basis, but how to do this in a mathematical model that uses years as its time step wasn’t clear.  One way to go is to apply the account growth at the start of the year based on its rate of return and balance at the end of the prior year, and then add/subtract income and expenses.  Another way to go is to add/subtract income and expenses from the last year’s ending balance and then calculate growth based on that adjusted balance.  On the test cases that Darrow used, he reported observing up to a 20% difference in the total long term projected account balance between these two methods of modeling account growth.

More Testing to Further Characterize the Problem

All models of the Pralana Retirement Calculator use the start-of-year growth algorithm but I became interested in the modeling errors this was introducing into long term projections when Darrow approached me with the dilemma he had encountered in his study. So, using a simple model of my own that implemented both methods of calculating account growth and a time step of years, I ran three tests cases: Case 1 involved a positive cash flow of $50,000 every year over a 40-year period, Case 2 involved a negative cash flow of $50,000 every year over a 40-year period, and Case 3 involved neutral (i.e., zero) cash flow every year over a 40-year period.  I measured a 2.5% difference in ending balances between the two methods for Case 1 (long term positive cash flow), an 18% difference for Case 2 (long term negative cash flow) and 0% difference for Case 3 (neutral cash flow).  (See the note below for an explanation of why case 2 exhibits a larger difference than case 1).  I can now characterize the issue as follows:

  • The start-of-year growth algorithm understates long term projections (i.e., is too pessimistic) when positive cash flows are dominant and overstates those projections (i.e., is too optimistic) when negative cash flows are dominant
  • The end-of-year growth algorithm overstates long term projections when positive cash flows are dominant and understates those projections when negative cash flows are dominant, and this understatement can be quite significant
  • Cases 1 and 3 were not at all bothersome to me, but Case 2 is a definite concern.  So, I decided to make the model more complex so that I could study the differences between the growth application methods using smaller time steps to get closer to the daily time steps in the real world.  I elected to use months instead of years, so the model required 12 times as many calculations/steps but, as expected, it produced considerably smaller variations between the growth methods.  Using the same test cases as described above, I measured less than 1% difference in ending balances between the two methods for Cases 1 and 3 and just under 2% difference for Case 2.  We can readily conclude from these tests that the smaller the step size, the less it matters which growth method is used.

Note: Case 2 (negative cash flow) exhibits a much greater long term difference because the portion of the year-to-year account balance differences associated with the growth attributed to annual cash flow is much larger relative to the total year-to-year difference than with a positive cash flow of the same magnitude.

Solutions

Is the smaller step size the answer for retirement calculators?  Before we can answer that we need to consider two things: 1) the practicality of using the smaller step size and 2) other alternatives. 

Generally speaking, going from a step size of years down to months requires 12X the number of calculations and 12X the amount of time.  Doing this for fixed rate projections is still no big deal; however, for Monte Carlo simulations which can involve hundreds or even thousands of scenario executions, this 12X factor can become significant indeed and could render the calculator to be impractical on many computer platforms.  Still, a potential error in the range of 18% from just this one contributor is too large to ignore. There are other considerations and complexities associated with moving to a smaller time step size, but they’re beyond the scope and tangential to the point of this article. 

What about other alternatives?  Darrow and I discussed this and came up with one that retains the yearly step size while generating the same long term projection as a theoretical model with an infinitely small step size (all else being equal).  This alternative applies growth in the middle of the year rather than at the start or the end.  More precisely, the account balance at the end of the prior year is adjusted up/down based on half of the current-year cash flow, then a full year of growth is applied based on that mid-year account balance, and then the other half of the current-year cash flow is added.  To evaluate this, I modified my simple model to implement this alternative.  As expected, it produced long term results that were not affected by step size and that were always exactly half way between long term projections calculated using the start-of-year and end-of-year growth models regardless of step size.

This, then, allows me to reach this conclusion: Assuming that income and expenses are evenly distributed throughout the year, the mid-year growth algorithm results in the most accurate projection mathematically possible and the size of the time step is immaterial.  Simply stated, it is more accurate than the other alternatives and more efficient in terms of calculations/ scenario than trying to increase accuracy by using smaller step sizes.

Incorporation into the Pralana Retirement Calculator

The mid-year growth algorithm is now part of PRC2017 and produces long term projections similar to my simple model when running the same three test cases; however, I’ve discovered that real world test cases can produce much more dramatic results.  Darrow and I collaborated on the definition and evaluation of five realistic retirement test cases using PRC2017 Gold and his modeling framework.  On four of these test cases our long term projections matched within 1% and within 5% on the fifth case using the start-of-year growth algorithm, so I’m confident of PRC results.  At the time of our joint testing, Darrow hadn’t yet implemented the mid-year growth algorithm, so I went it alone in evaluating these same test cases with PRC.  The differences between the start-of-year growth and mid-year growth algorithms was considerably more dramatic than I observed with the simple model and simple test cases described above.  With our realistic test cases, I observed 11%, 9%, 0.5%, 1% and 21% differences!  Note than PRC doesn’t and never has used the end-of-year growth algorithm, so I don’t have any related data.

I believe the mid-year growth algorithm in PRC2017 is more accurate than any alternatives in virtually every case while retaining the yearly step size to make it a practical solution on the typical computers of average users.  PRC2017 still has the start-of-year growth algorithm, though, and allows users to specify which of the two growth algorithm options to use.  Depending on your particular scenario, use of the mid-year growth algorithm may produce similar or significantly different long term projections.  If you have a long period of negative cash flows, I strongly advise you to reanalyze your plan using this optimized algorithm.

Final Words

As of this writing, I have not yet identified the specific characteristics of the five realistic test cases that result in the much larger differences between start-of-year and mid-year growth algorithms as compared to those using the simple test cases.  That’s a task for another day and the findings of that investigation will be reported when they’re available.

Pralana Consulting LLC, Plano, TX

© 2017 Pralana Consulting.  All Rights Reserved.

PRALANA RETIREMENT CALCULATOR | The Mercedes-Benz of Retirement Calculators

 

New Visitor? Start Here.

ADVANCED PERSONAL FINANCIAL MODELING  |  MONTE CARLO & HISTORICAL ANALYSIS  |   INTERACTIVE WHAT-IF ANALYSIS