Retirement Planner / FIRE Sheet - Request for Feedback

Finally got a chance to play with the Cash Flow forecast and Retirement Planner spreadsheets and they are great! I don’t have budget data so I had to enter estimates using the life planner section. took me a while to work out MY bugs but I think I have a fairly good representation of my situation. I really like playing with the investment and withdrawal rates. Since I used the life events instead of income and expenses, I can’t see how inflation will affect my spending ability in retirement. Any way to play with having income stop at a certain year and expenses continuing on?

thanks for all you do.
Mark

Hi @markchuteski ,
I’m glad you are enjoying the sheets.

It certainly can work without using budgets.

There are a number of ways to do it. For example you can set-up a life event of Income and another of Expense.

The Income could have a year range so you could adjust the end year. That would let your income stop.

The negative Expense value could have a growth rate which would match your estimated inflation rate.

Does that make sense?

Jon

1 Like

So I have it set up with expenses and income in the cash flow, but here is my problem. When I get to the retirement planner I have both the withdrawal rate and the expense part coming out of my investments when in reality it is the withdrawal rate that is paying for the expenses. does that make sense? So I can get rid of expenses once retired and see what withdrawal rate I can support or I can keep expenses in and leave withdrawal rate at 0 and see how my expense effect year end investment amounts.

Hi @markchuteski ,
I’m not fully clear on what you are saying

I have both the withdrawal rate and the expense part coming out of my investments when in reality it is the withdrawal rate that is paying for the expenses.

Are you saying it is double-counting?

Would it be possible to make in an adjustment in the Investment Adjustments section to account for what’s happening? I realize those adjustments don’t include a growth rate. But maybe you can approximate it?

Jon

It is possible to make adjustments in many places, but the best way is to focus on what you want the sheet to analyze, I guess.

Look at this example:

Year Age Spouse Age Current OR Investments Start of Year Investment Growth Rate Investment Gain 5.0% Withdrawal Cash Flow Forecast Investment Adjustments Net Gain/Loss Investments End of Year
2021 59 51 $1,323,270 6.0% $63,738 - $0 + $75,000 + $0 = $138,738 $1,462,008
2022 60 52 $1,462,008 6.0% $87,720 - $0 + $78,075 + $0 = $165,795 $1,627,803
2023 61 53 $1,627,803 6.0% $97,668 - $0 + $81,232 + $0 = $178,900 $1,806,704
2024 62 54 $1,806,704 6.0% $108,402 - $0 + $59,473 + $0 = $167,875 $1,974,579
2025 63 55 $1,974,579 6.0% $118,475 - $0 + $62,425 + $0 = $180,899 $2,155,478
2026 64 56 $2,155,478 6.0% $129,329 - $0 + $65,458 + $0 = $194,787 $2,350,265
2027 65 57 $2,350,265 6.0% $141,016 - $117,513 + -$176,142 + $0 = -$152,639 $2,197,626
2028 66 58 $2,197,626 6.0% $131,858 - $109,881 + -$179,534 + $0 = -$157,558 $2,040,068
2029 67 59 $2,040,068 6.0% $122,404 - $102,003 + -$181,060 + $0 = -$160,659 $1,879,409
2030 68 60 $1,879,409 5.0% $93,970 - $93,970 + -$160,744 + $0 = -$160,744 $1,718,665

In the year 2027 I retire. The spreadsheet starts calculating the withdrawal rate of -$117513 and it brings over the Cash flow forecast of -$176142. Both of these are subtracted from the investment gain to give us the gain/loss (-$176172) and ultimately the investment value at the end of the year.

But that is not the true picture of the year. My investments make $141016, I spend $176142 so I need to take an additional $35126 out of my investments to pay for the expenses, so my end of the year investment value is only reduced by $35126, not $152639.

It just seems like looking at both withdrawal rate and expenses at the same time does not show the true picture. I can leave out expenses and see what withdrawal rate (which pays my expenses) I can sustain or I can include retirement expenses in the cash flow and leave the withdrawal rate at zero to see how that affects my balances, but not both.

Mark

Sorry, this should read:

Both of these are subtracted from the investment gain to give us the gain/loss (-$152639) and ultimately the investment value at the end of the year.

@markchuteski ,
Thanks for sharing all this. It will take me some time to get my head around what you are saying, but I think I will get there.

Do you see a way to solve this issue?

Jon

I am not sure if there is anything to solve or if it is just better to be aware of what you are trying to determine. Both spreadsheets offer great insight.

Hi again @markchuteski ,
I’ve taken some time to try to digest what you wrote and want to share a few thoughts.

You state

My investments make $141016, I spend $176142 so I need to take an additional $35126 out of my investments to pay for the expenses

But, it’s not just how much you make or gain on your investments. You also decided to withdraw 5% of your investments, or $117,513, to help pay for your expenses. You are not counting that amount and I believe you should.

Look at it this way. Your investment value at the start of the year is $2,350,265. The 6% investment growth rate raises that value by $141,016. The 5% withdrawal reduces your investment value by $117,513.

The $141,016 gain minus the $117,513 withdrawal gives you a net $23,503 investment value.

But you have a negative cash flow of $176,142. You can use $23,503 of that gain to help pay the negative cash flow. But you will still be short $152,639. You will need to take the $152,639 out of your investments. That’s why the Net Gain/Loss is -$152,639, which will reduce your investment value by that amount for the year.

Does that make sense?

Jon

1 Like

No it does not because I’m using the 5% withdrawal to pay for all the expenses. Which are part of the cash flow. What else would I do with the money?

Right. You are using your 5% withdrawal. But you are short. So you need to take the rest out of the short-fall out of your investments somewhere.

Some years, you might not need all the withdrawal amount and you could put that money, or really just keep it in your investments.

@markchuteski

Is the 117,513 already in the 176,142 amount?

If I am following everything, I believe the answer is yes.

If so, then you are really subtracting the 117,513 out of the asset twice. To eliminate this double subtraction, add a positive amount in the Investment Adjustments column which actually gets input over in the hidden columns on the right.

Does the output make sense now?

Blake

I still think the planner calculation needs to either look at only withdrawal rate or cash flow but not both. The only reason to withdrawal is for negative cash flow. They are one and the same and cannot be accounted for together. It would be better to have the spreadsheet either use cash flow and calculate the actual withdrawal rate or put in a fixed withdrawal rate and determine what the cash flow would be.

If I earn $50 and spend $150 then my cash flow is a loss of $100. I now need to “withdrawal” that amount from my investment and/or investment earnings to support my spending.

The net gain/loss formula (=IF(ISNUMBER(E25),J25-L25+N25+P25,IFERROR(1/0))) needs to either use just the withdrawal rate (L25) or it needs to use cash flow (N25), but not both.

When I use the spreadsheets together, I either un-Activate my expenses in the cash flow sheet and set a withdrawal rate (what I can spend) to see the effects on my investment balance or I keep the expenses active and have a 0% withdrawal rate to see the effects. This works great for me but others might have negative cash flow and set withdrawal rate which would be subtracting from their investments twice. Hope this sheds some light.

Thanks,
Mark

1 Like

Please let me explain. Let’s refer to the example you provided above. For the years 2027-2030, you used both the withdrawal column and the cash flow column. For 2027, the 117,513 is already included in the 176,142. Yes, it is being double counted. So, as I stated in my prior post, we need to reverse that double counting. Click on the “+” above the S at the top of column S, enter 2027 in cells U3 and V3, put a check mark in cell W3, put (117,513) in cell Y3, and this will put the (117,513) in column P (heading called Investment Adjustments) on the row will all of your other 2027 numbers.

I already hear what you are saying, why was it designed that way? Double count the number and then need to adjust out the doubled up amount. Bad design, right? No, it was actually intentional. Intentional? Why?

Let me explain further. Jon designed this to provide maximum flexibility for the users. Let’s say your 2027-2028 negative cash flow numbers are 100,000 less than you show in your example. Also, let’s assume you want to withdraw at least 5% each year (and spend it), even if your cash flow needs are less. You will need to use both the withdrawal and cash flows columns to accomplish this. So, for 2027, the double counted amount is now 76,142 and that needs to be adjusted out. For 2028-2030, adjust out 79,534; 102,003; and 93,970, respectively.

You say the only reason to withdraw is for negative cash flow. That is not correct. RMD’s are one example of withdrawing even if you do not need the funds.

I hope this makes some sense. I reviewed this while it was in production and provided comments and feedback. I told Jon then and I will tell him again now, he was nothing short of a genius for the way he designed this thing. Kudos to Jon.

Cheers,

Blake

@Blake and @markchuteski ,
Thanks for engaging in this conversation to help me and others gain a greater understanding of these sheets. And @Blake, thanks for your kind and accurate :slight_smile: words.

One of the goals of the sheet, unlike other Retirement Planners that I’ve seen, is to let everyone use it in a way that works best for their situation. I’m glad you found a way to use it that works for you @markchuteski.

Regarding the Withdrawal Rate, I didn’t want to only include a withdrawal rate if cash flow is negative because you might want to know how much you can withdrawal without taking cash flow into account. And @Blake is correct that you might have RMD’s which require withdrawals even if you don’t need the funds.

To me, the withdrawal amount is seen as a Transfer from Investment to Banking accounts, and then used to pay expenses. I don’t consider it Income or Expense.

Regarding the Investment Growth rate, i see that as Unrealized Investment Gain. It is not automatically transferred out of the investment account.

If your Cash Flow planner includes a Dividend Income category, because you don’t reinvest your Dividends and you include Dividend gains in your Investment Growth rate, then you might be double-counting your Income.

There are two solutions I see to fix this double-counting. Don’t include dividends that are not reinvested in your Investment Growth rate. Or create an adjustment in the Cash Flow planner so your Dividend Income is Zero, so it is not counted twice.

Jon

Great discussion on this subject. Thanks for all the input by everyone. This is a good community and open dialog makes it work for all.

2 Likes

One thing I would really like to see are forecasts that can break out when you can use tax-advantaged accounts and when you can’t. For example, if I retire at 45, I don’t want to dip into my 401k until I’m 59.5. So, how do I determine if I have enough money to cover those early years? Maybe there’s a way to do this with Life Events, I’ll play around… but this is the thing that always seems to be left out of early retirement calculators - I can’t actually use all of my saved money right away.

1 Like

Hi @sktglacier ,
You make a very good point about the impact of taxes on retirement planning. The tax impact can be very important and may affect how you do things.

When we started to build the new Retirement Planner sheet, we thought we might include a way to manage different Retirement Assets that have different tax treatments. But there were just too many different scenarios.

Some assets can be withdrawn with no taxable income. Others might quality for a capital gains rate which will vary, and others might use ordinary income rates which will also vary. And the rates depend on other income. The tax rates are graduated and will likely change in the future.

So, we decided to keep taxes and their effect out of the sheet.

You might want to make your own sheet showing which assets you want to withdraw and at what times and in what amounts. From that, you might be able to make a calculation for what the tax expense might be. You could then use the Life Events feature on the Cash Flow forecast sheet to capture the tax impact.

I wish there was a simple way to do it. And if anyone has a suggestion, please share. But it seems to be a very complicated problem to build a solution that will work for everyone’s or even most people’s situation.

There are some commercial products out there that use higher-level programming than just spreadsheets to make some of these calculations based on each of your asset’s tax type and your other taxable income. Many of them have a large price tag attached to them as well.

Jon

Hi @jono,

My suggestion would be to change the way the portfolio target is approached. Currently you are setting a single amount that never changes throughout a lifetime. However a portfolio goal is often increased throughout a lifetime due to inflation.

For example:
In the FIRE community a common approach to a portfolio goal is 25 x expenses. Assuming 40k for expenses in the current year our goal is 1mil. However, in the second year the expenses get affected by inflation, lets say 2%, making our expenses 40,800 which in turn makes our portfolio goal 1,020,000. Each subsequent year is also affected, gradually increasing the target goal.

You already have inflation support in place through the Cash Flow Forecast sheet in cell E6, therefore the only change you need is to modify the table in row AA of Retirement Planner with something like this:

=if(isblank($E25),iferror(1/0),$E$2*(1+'Cash Flow Forecast'!$E$6)^(ROW(AA25)-ROW($AA$25)))

Hi @luckie ,
Thanks for your suggestion. If others want to have a portfolio target adjusted by inflation, they can update the formula as you suggest.

In making the sheet, we tried to create the right balance between simplicity (such as a simple target) and complexity (dealing with lots of inputs and rates).

We also encourage anyone to use the sheet as a starting point and make their own modifications to meet their own financial reporting needs.

Jon

1 Like