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.
I think there is a straightforward answer: the retirement planner withdrawal percentage should not be needed if you have everything planned in the cash flow spreadsheet. A more direct recommendation would be to make the cash flow sheet robust enough to account for all of your anticipated life events and retirement costs.
Wouldn’t it be nice if the cash flow sheet had a questionnaire to capture all of you life’s events, similar to what the big CFP websites have?
For me, I made sure my cash flow sheet reflected a realistic spend rate we will have in retirement. I then put the retirement planner withdrawal percentage at .5% to account for potential spends as a retiree like trip to see grandkids, etc.
My feature request would be to make the cash flow sheet more robust with a questionnaire on life events. This could then result in a spreadsheet that would capture your retirements cash flow, and you wouldn’t have to rely on the retirement planner peanut butter spread of withdrawal percentage.
I think putitng as much detail as possible in the cash flow planner is the way. @cps I really like your new reports, especially the month over month, which I always loved on Mint and Personal Capital. I hope some of these can make their way into the defaut foundation template.
I see there are a number of chains pertaining to the Retirement Planner, but wanted to chime in with my observations and use cases in case they are a solution here. First of all, after working on retirement calculators of my own and looking at the web and app-based stuff that is out there, for this being free, it’s incredible and probably the best thing out there with a little bit of tinkering and thinking about your particular situation. And the feature enabling near-endless possibilities you can easily code in via life events by using the Cash Flow Forecast is brilliant. You can replicate anything that any of the subscription-based model tools that exist can do. So, well done.
That being said, here are a few workarounds I came up with to fit my particular view of what a retirement calculator should be doing and how to make it work within the bounds of this tool:
I’ll echo others that I don’t think this tool is best used with the w/d rate in really any situation. This is consistent with my thoughts about the “4% rule” in general in that you should withdraw what your expenses are, which are well known in the years leading up to retirement (of course there are exceptions - and the rainy day fund concept still applies). While having a rule is nice, it could easily result in unnecessary cash drag.
The tool will assume your income from the current budget year in perpetuity unless you back it out (unless I am missing something). I came up with a simple back-out solution in Cash Flow Forecast roughly equal to “((income based on current budget in C11)*(1+income/budget change/year in E5)^(years until retirement))(-1)” and enter this as a line item starting in the year you retire and lasting until the end date increasing at the rate of your income budget change/year. This seemed to fix issues with very large predicted ending balances nicely.
Do similar entries for for actual “known/non-investment” income in retirement such as expected social security and/or pensions for the same time range (or whenever you start receiving it) having it be based on today’s expected payment (can see this on the SS website for your PIA, for example) and calculated as the future value and increasing in the “change/year” column with inflation or whatever suits you.
Include annual retirement savings entry for current year and every year until the retirement date increasing at the income budget change/year or whatever suits you since your income from the current year likely won’t include this if it’s pre-tax. This will ensure a more accurate starting balance.
Consider leveraging the Simple Investment Calculator in general to help estimate any other investment accounts you want include and add them. I found this to be a very useful complementary tool as it can give you balances as of your retirement date subject to unique return assumptions and cash flow schedules (such as your 401(k)) if you want to treat them separately and with different return assumptions than the more top-down return assumptions native to the tool.
If you want to model a pre-59-1/2 retirement date, plug something that backs out the 401(k) balance (from the Simple Investment Calculator) for example, and then bring it back when you want/can use it at 59 1/2 or later by using single date entries in the Cash Flow Forecast. And if you do this, don’t forget to add in the gap years with compounded returns from the years in between retirement and when you starting withdrawing from your retirement. This will give you a more accurate picture of money available to you before you can take certain savings in retirement.
Taxes are important, but I get why they weren’t accounted for in the tool given that they change nearly every year. But with the way the tools are designed, you can easily add them to get much more accurate numbers. I opted to add an assumptions for them by altering column T in Retirement Planner to account for them based on total balances. Alternatively, I considered an entry for multiple years in the Cash Flow Forecast based on scaled current expenses from cell C12. Or you could base it on individual LTCG estimates from individual accounts from the Simple Investment Calculator. Point being, they are important and easy to model with this infrastructure.
Anyways, those are a few findings from playing with the various tools in a number of ways. What y’all have created here is like I said as good as anything out there because of the flexibility - there’s literally nothing you can’t model with a little bit of thinking and tinkering. Thanks.
For the part that you mention in the second bullet (with regards to income), what I have done is enter a life event starting the year of planned retirement and ending in the year 2100 (long enough) with an amount of zero for Amount/Year to indicate that the income stops.
This yields the same value that your calculation achieves and cancels it out from expected income during retirement.
Interesting - when I tried that (by replacing what I am doing), my ending balance was massively inflated indicating that it continued to use my pre-retirement income with its growth assumptions until the end date. Your set-up might have something else in it that helps back it out, which is great if so. By default, the “unadjusted income” in column O of the Cash Flow Forecast continues until the end date, so the life event has to back to this out with an inverse (i.e. entering 0 wouldn’t do anything in my case).
Just out of curiosity, are you using the same category name in the Life Event/Category column as you have specified in your Categories worksheet for the income item you are trying to cancel out? The category name must be the same, which will turn bold when it finds the match. If it is not bolded, then it will not cancel out.
, 
