Retirement Planner / FIRE Sheet - Request for Feedback

Conversation
62

Hi there, thanks for building this sheet! I noticed that the withdrawal amount in column L is based on a simple formula of balanceForYear * withdrawalRate, which means that the amount is always dwindling down towards 0 if the withdrawal rate is higher than the growth rate. This formula does not match what is typically considered the safe withdrawal rate (SWR), which is calculated as initialRetirementBalance * inflationAdjWithdrawalRate.

I’ve updated my own sheet to use the calculation above by:

  • adding a calculation in AN2 for initialRetirementBalance which is formula =INDEX(H25:H, MATCH(VALUE(C22), E25:E, 0))
  • adding a calculation in AN4 for initialWithdrawalAmount which is formula =AN2*C21
  • adding a user-entered inflation rate in AD4 (default 3%)
  • for each row starting at 25, change column L to formula =IF(ISNUMBER($E25),IF(value($C$22)<=$E25,($AN$4 * (1 + $AD$4)^($E25 - $AS$3 - 1)),0),IFERROR(1/0)), and autofill to the bottom
    • Replacing cells with variable names for human readability: =IF(ISNUMBER(rowYear),IF(startWithdrawalYear<=rowYear,(initialWithdrawalAmount * (1 + inflationRate)^(rowYear - currentYear - 1)),0),IFERROR(1/0))

To demonstrate the difference, I made an example sheet here. With the template fresh from the box and most values left default, a retirement year of now, balance of $3M, withdrawal rate of 4%, and a 60 year retirement, the template predicts that the account balance will grow over time and, because the default growth rates never drop below the withdrawal rate, the withdrawal amount goes up over time (although not as much as average inflation). In contrast, the inflation adjusted withdrawal amount I described predicts that you’ll run out of money in 2071. This is a very significant difference so I think is worth considering a change. Using a FIRE calculator with similar inputs confirms that there’s a shortfall and actually shows you running out of money a couple of years earlier.

63

Hi @bouncycharm ,

I’m glad you are enjoying the sheet and extending it.

I agree with you that the typically 4% safe withdrawal rate, proposed by Bill Bengen as a rule of thumb, includes an adjustment every year after the first year based on inflation. And inflation can have a big effect on the planning.

I think when I built the sheet more than 4 years ago, I was weighing the simplicity vs the complexity of the model. And this adjustment in the withdrawal rate didn’t make it into the design.

I’m glad you were able to create an update to the sheet to account for this.

I’ll discuss this with the Tiller folks on whether we should revised the template.

64

Thanks! I think at least it should freeze the amount at the retirement year. With the way it’s set up now, it’s practically impossible to get to $0, which of course is not true.

65

Thanks for digging into this, @bouncycharm. As you are aware, the template is a little complex and I remember @jono working hard to strike the right balance of accessibility vs power. I like your improvement and you make a great point.

Is it possible to share an updated version with refinements you’d make to freeze the amount at the retirement year (your last comment) and manage the complexity of the new feature? I’d love to see where you’re headed with that.

Thanks,
Randy

66

Hey @Randy, since I already have the more complex version set up in my spreadsheet and that’s my preferred setup I will probably not do something like that for my own purposes. If I get a bit to spend on making that for the community I’ll share it back here.

67

Ok. We can pick this back up if/when you have time. Thanks for sharing your insights.