I’ve been playing with the retirement planner along with the cashflow forecast and have found it really useful for planning up to retirement (which for me is only 5 or 6 years away, hopefully). I’ve also modeled in some known expenses, income and changes on a year by year basis which is really powerful and up to the retirement date seems accurate. The problem starts after that date.
One thing I am struggling to work around is the arbitrary need to select a withdrawal percentage of the entire amount, rather than an amount to match the needs of the budget for that year, which is basically the difference between my budget and any other income (TRS, Social Security, Rental Income, etc.)
The second issue is that the withdrawal amount doesn’t go back to the cashflow sheet (you can’t reference it either because it says it’s a circular formula) so the net investment amount goes down every year even if the investment gain plus other income plus drawdown is greater than the budget of any year, which for me it is for at least 10 years. Surely it should remain in the accumulated cashflow amount, even if I don’t reinvest it?
Are there any thoughts on how to work with this? If I use Personal Capitals Retirement Planner, I basically never run out of money with the budget a picked, barring a huge emergency. With the Retirement Planner Community Sheet, I run out of money in about 25 years because the above.
I have run into the same issue that you mention and have done a similar comparison with Personal Capital, with the same result. What I ended up doing is set the Withdrawal Rate to zero and let the negative cash flow rate basically dictate the withdrawal amount.
Any additional income events I have recorded on the Cash Flow worksheet with applicable date ranges. This seems to work good for me, hope it helps you as well.
Note: it appears that when you specify a Withdrawal Rate, that amount is considered separate from the Cash Flow Forecast value and is considered as spent money.
That’s the part that doesn’t make sense to me. I appreciate they’re separate sheets but the Retirement Planner recommends using the Cash Flow Forecast, but doesn’t feed back to it.
Your other suggestion works though and I now get a very similar outcome to other sources.
Thanks for sharing this riddle @Wooloomooloo2. @jono wondering if you have thoughts on how to work around this or whether you’d consider refining the Retirement Planner sheet? Not sure how complex this is, that sheet is way above my head!
@Wooloomooloo2 , As far as I know, the solutions to this using Google Sheets will create a circular formula. When the results of one set of formulas is then used to generate something else, then you can’t use the secondary results to go back and adjust the original formulas without circular formulas.
Any excess or deficit cash flow on the retirement sheet does flow to the following year as a change in Investment Value. So that value is not lost. Ideally, it would flow back to the cash flow sheet too. Even if there was only one sheet involved, there would be a circular formula issue.
There are some ways to work with circular formulas by adjusting a spreadsheet settings (File Menu → Settings), turning on Interative calculation and then setting a Max number of iterations and a Threshold. But I think I tried adjusting whose numbers when I was making the sheet and couldn’t get it to work. Maybe someone else in the community can?
A website like Personal Capital can use a lot more backend scripts to program the results than we can in Google sheets just using formulas.
I’m glad you have found the sheet useful. We knew it wasn’t going to be able to address every possible situation but hopefully it provides some value.