First want to say that the Retirement Planner combo with the Cash Flow Forecast is the single best retirement planner spreadsheet I’ve ever used on top of the best budgeting platform I’ve ever used.
One quick question: It’s possible I’m just completely missing something, but it seems to me that when the withdrawal kicks in, this amount isn’t being accounted for in the cash flow formula?
For example, if I’m projecting zero income from paychecks in retirement, the expectation would be that my 3.0% withdrawal amount would be used towards expenses from a cash flow perspective. It seems like my spreadsheet is deducting the withdrawal amount from my total investments but then not including this amount when calculating my cash flow. Is there any way to capture the withdrawal as “income” in the cash flow amount? Given that the number is right there it seems burdensome/inefficient to try to project this income as a line item on the Cash Flow Forecast tab.
Hi @sethrwebster12 ,
Thanks for the kind feedback on the Retirement Planner / Cash Flow Forecast combo sheets.
I think I follow what you are saying. But the sheets were designed to the results from the Cash Flow Forecast flow one-way to the Retirement Planner sheet Cash Flow Forecast column.
Any actions you take on the Retirement Planner sheet won’t have any impact on that sheets Cash Flow Forecast column.
However, those cash flows will be included in the Net Gain/Loss yearly totals.
We tried to design the sheet so it was as flexible as possible and could work for many individual scenarios. But, I don’t think it can work the way you are suggesting.
@heather I have registered for the workshop! Looking forward to it.
@jono Appreciate the follow up! Not sure if feature requests are a thing and no worries of course if not, but I’m wondering if you couldn’t just add a toggle to the Retirement Planner to the effect of “Use Withdrawals in Cash Flow” (with IF statement the Cash Flow Forecast column?) and if its selected it adds the withdrawal amount to whatever is coming in from the Cash Flow tab?
Hi @sethrwebster12 ,
We certainly take feature requests into consideration and act on them when we think they are worthwhile for the majority of the sheet users.
However, there is always a tradeoff on making a sheet too complex and building in lots of features vs making the sheet too complicated and harder to understand and start using. That can be a grey area with different customer knowledge levels. That said, we sometimes build toggles for customizations in the hidden section.
Regarding your idea about the toggle for “Use Withdrawals in Cash Flow”, you are welcome to add that customization on your own version. I’ll think about adding it to an update, but that Cash Flow column was solely designed to pull the data from the Cash Flow Forecast.
If it not pulling from the cash flow sheet, what formula do you want that column to have? I don’t think it should duplicate the Net Gain/Loss existing column. Perhaps you want it to be the Net Gain/Loss prior to Investment Adjustments?
First, thank you for this awesome retirement planner template. I am late to the party and just discovered Tiller and the Tiller Community, this is exactly what I have been searching for so long.
I do have a question about the way that the Retirement Planner sheet works with regards to withdrawals and the cash flow.
If you look at the two example rows below. I set retirement and withdrawals to start in 2033. The Net Cash Flow Forecast for 2033 is $168,221 and the withdrawal amount is $168,275. The withdrawal amount should therefore cover the Cash Flow Forecast with a few dollars to spare.
Sticking with the same row for year 2033, the Investment Gain is $368,101, however, the Net Gain/Loss is $31,605 because it is calculated by deducting both the withdrawal and the Cash Flow Forecast.
Am I missing something? I added the two values to the right for Expected Net Gain/Loss and Investment End of Year. In the example, if the withdrawal amount is equal to or larger than the Net Cash Flow it simply deducts the withdrawal amount from the Investment gain (this applies to year 2033). If the withdrawal amount does not fully cover the Cash Flow Forecast, it deducts the withdrawal amount and the unfavorable delta to calculate the Expected Net Gain/Loss and the Investment End of Year balance.
The way the spreadsheet currently does the calculation, it looks to me like it is double counting the withdrawal and the Cash Flow Forecast and prematurely consuming the Investment balance.
I might be completely misunderstanding this, so I appreciate any feedback you might be able to provide.
PS: for some reason, it won’t let me include a screenshot.
I know it’s 18 months later but just wanted to +1 this. This seems to be a big gap in the functionality of the retirement planner. If the withdrawals aren’t covering cashflows, what are they doing? If the withdrawal is exceeding the cashflow forecast, then all expenses are covered and the excess is presumably going toward living well. If the withdrawal does not meet or exceed cashflow forecast, then greater than the intended withdrawal rate is being taken from savings, which isn’t ideal but such is life. Thus, it seems as though net gain should be rewritten so that it substracts the “larger” (the more negative) value between the withdrawal column and the cash flow forecast column. Adding seems like double counting expected expenses.
Hi @fhsermanfrnd ,
Thanks for your feedback. I’m trying to wrap my head around what you are saying. It does seem there might be an issue.
Is it possible to share an example with numbers?
Or maybe just explain how this formula fails.
Yearly expected Investment Gain
MINUS Withdrawal (i assume you make the withdrawal; if not needed, its just positive net gain)
PLUS OR MINUS Cash Flow Forecast
PLUS OR MINUS Adjustments
should equal Yearly Net Gain/Loss
Start-of-year investments PLUS OR MINUS Yearly Net Gain/Loss should equal Investments at End of Year.
One other point:
For the Cash Flow Forecast, it should be the Cash Flow NOT including Withdrawal OR Investment Gain. More like the operating cash flow; or the income minus expenses cash flow.
If you consider the withdrawal or investment gains as part of that cash flow, you might wind up double counting.
I know this is an old thread but I am working through this spreadsheet (which is amazing) and feel like I am finding this same issue. Currently my cash flow forecast does not have any withdrawals or investments gains. When I get to my retirement years and start withdrawing, on the retirement planner sheet, it does not appear like the net/gain loss column is figuring in that you would utilize the withdrawal amount to cover my cash flow deficit (since I would no longer have an income). I am showing that I would be gaining 4% and withdrawing 4% so those amounts cancel eachother out and they my cashflow negative amount is essentially what is my net gain/loss. Can you help me out with this?
Hi @mve5008 ,
I know some people have been having some issues with this sheet. But i haven’t been able to find the exact issue. See the note from my last comment about not including withdrawal or investment gain in cash flow.
I think the formula would be working correctly, if you set the yearly gain to be 4% and a 4% withdrawal, both those settings would cancel each other out, in terms of their effect on your next year’s investment value.
If you had $1,000,000 of investments at the start of the year.
With 4% investment gain, you would add $40,000 of value to your investments
With 4% withdrawal, you would reduce the value of your investments by $40,000, t
Net effect on your investments value is 0.
If you had negative cash flow, you would need to pull additional investment value and that would reduce your investment value by the negative cash flow amount at the end of the year.
