My Lessons Learned and why everyone should build a custom model with Excel

[freedomatlast]

Probably the biggest benefit of all this intensive modeling is that it shows people are considering all of the details before pulling the plug, which is great. The psychology behind it many times is because the modelers believe this gives them more control over what will happen in the future, which is probably not the case. Personally, I just took a more simplistic approach and made sure I had more than 3X what I estimated I would need, which should help balance out the unanticipated scenarios which are simply impossible to predict or model.

[corn18]

I model expenses in nominal dollars (constant now year) and use real returns. So much easier than plugging inflation guesses in all over the place.

I did take a copy of my model and convert it to real dollars and nominal returns and the answer was about the same. My pension and SS are COLA'd and my healthcare is free so there should be no real inflation impact for me. Except for my mortgage. Sure is nice watching the nominal P&I payment go down by inflation each year.

[zekeboz]

Quote:

Originally Posted by mountainsoft Welcome! I'm not a spreadsheet guy (despite being a computer programmer) but have tried numerous retirement calculators. For me the "Flexible Retirement Planner" (https://www.flexibleretirementplanner.com/wp/) has been my favorite planner. It's easy to experiment with numerous inputs and scenarios to see what the results will be.

ditto - this has been my goto app given the granularity that you can add from expenses, inflation adjustments or portfolio return ranges. extremely flexible!

[reneeh63]

Thanks for providing the details as well as your thought processes - that greatly helps in not only giving a general starting point but many tips and hints on what and how to further customize!

[38Chevy454]

I'm an engineer and do not keep or in the past used any type of spreadsheet with the level of detail that OP and many reply persons here do. I have one basic all-in number for spending each month, and have my regular money set up to cover that. It has two basic categories: fixed and discretionary; which of course discretionary can change a lot. If I do need more to cover a larger one time expense, I sell something and get money. Fidelity Income Planner and Firecalc both give me good results.

I guess that I have "enough" in the nest egg, and confidence in my investments, coupled with a lower than 4% withdrawal rate. I can keep my normal expenses within the regular budget, that I just do not feel any need to get serious with detail on a spreadsheet and micro budgeting. It works for me. I may not optimize to the absolute best, but do use tax rates and conversions to help my short and long term planning.

[Romer]

Just an update about how great this site is. Due to discussions here and links I learned some things, some new lessons that I applied into my spreadsheet. I didnt update the spreadsheet because I think it will be a better predictor for the future. It did help me understand the new tweaks and how it impacts things. I learn by making something. When I got a job on a new spacecraft program, first thing I do is draw out the entire system. This makes me ask questions to understand how things work and by the time I am done, I have learned this new system and am better able to manage it. I could have looked at existing drawings and just been satisfied, its the making it myself that forces me to learn and ask questions. I do use the existing drawings as reference for an end to end drawing. I see the spreadsheet in a similar way helping me understand all the end to end parameters and things to consider, not as an accurate prediction

What did I learn?
- Social Security Spousal Benefits. I had no clue and what a pleasant surprise
- In My situation, taking SS at 67 and my the spousal benefit at 67, is a better options for 25-35 years depending on ROI for investments. Just to far in the future to worry about. If looks the same at 66, go for FRA at 67.
- When RMDs drive you above your needed cash, make sure the extra goes into an after tax savings account vs a ROTH
- Roth Conversions are not an automatic good thing. A consideration for a spouse after death due to different tax brackets makes it a benefit, Tax Free inheritance for your kids, or if it helps avoid going into a higher tax bracket when the RMD comes. A near term example is to convert now if you will be in the same bracket in case Tax law does increase tax rates in 2026

There has been and certainly will be more

Thanks everyone for the great welcome, wonderful site and having a place to politely discuss options and learn

[chassis]

Quote:

Originally Posted by Romer Just an update about how great this site is. Due to discussions here and links I learned some things, some new lessons that I applied into my spreadsheet. I didnt update the spreadsheet because I think it will be a better predictor for the future. It did help me understand the new tweaks and how it impacts things. I learn by making something. When I got a job on a new spacecraft program, first thing I do is draw out the entire system. This makes me ask questions to understand how things work and by the time I am done, I have learned this new system and am better able to manage it. I could have looked at existing drawings and just been satisfied, its the making it myself that forces me to learn and ask questions. I do use the existing drawings as reference for an end to end drawing. I see the spreadsheet in a similar way helping me understand all the end to end parameters and things to consider, not as an accurate prediction What did I learn? - Social Security Spousal Benefits. I had no clue and what a pleasant surprise - In My situation, taking SS at 67 and my the spousal benefit at 67, is a better options for 25-35 years depending on ROI for investments. Just to far in the future to worry about. If looks the same at 66, go for FRA at 67. - When RMDs drive you above your needed cash, make sure the extra goes into an after tax savings account vs a ROTH - Roth Conversions are not an automatic good thing. A consideration for a spouse after death due to different tax brackets makes it a benefit, Tax Free inheritance for your kids, or if it helps avoid going into a higher tax bracket when the RMD comes. A near term example is to convert now if you will be in the same bracket in case Tax law does increase tax rates in 2026 There has been and certainly will be more Thanks everyone for the great welcome, wonderful site and having a place to politely discuss options and learn

Well done. I can’t argue with anything you have posted. It’s good to have you on the site. Please keep posting, particularly your charts showing different scenarios. You can non-dimensionalize the vertical axis if you like to protect your privacy. Directional analysis is helpful to everyone.

[Romer]

I have learned some more so just updating this thread. I was surprised when I ran the numbers when I decided to retire a few months ago how much I actually could spend each year

Taking out expenses that no longer apply and adding in Taxes and Health insurance cost (60-65 are higher) I came up with a range of $130K to $150K over the last 4 years, buying what I want, traveling where we want, etc. Being a Car guy can be expensive. I wanted to make sure I could live the same lifestyle, so I used $150K for planning. I really hope this doesn't come across as bragging as I see here there are a lot of people living off much less and doing fine. I am only using this data point to help explain the graphs and thoughts below

Another good reason to develop a spread sheet as everyone's situations are different. It just seemed right that I should do Roth Conversions. And that still is the right answer in some cases, but not all

The one I will have to ponder is leaving less of a tax burden for my kids by doing the conversions no matter what


I had some errors in my spreadsheet which going through other peoples scenarios helped me think about and correct. It has changed the Roth conversion mix

Since with SS and my Pension starting at 67, I will always be in the 22% Bracket.

The variable on Roth conversions or no Roth Conversions comes down to ROI
Anything below a 4.25% return is better if I don’t convert as this reduces my balance via taxes enough that the investment grows faster without the conversions

At a 2.5% ROI (first Graph) it is always better not to do ROTH. They stay fairly constant after SS because my withdrawals are close to my earnings at 2.5%. Earnings are higher with no Roth because a larger investment balance

At 4.25% (Second Graph), SS at 67 and Roth conversions to the top of the 22% bracket becomes better after 25 years because the higher ROI drives greater RMDs ( way above my spending needs for the no Roth option lowering my balance due to paying taxes earlier and slowing future growth future growth


At an 7% ROI (Third Graph), the Roth conversion options become better in 17 years because smaller RMDs in the ROTH options drive less tax early, leaving a larger investment balance to grow

As a side note with the SS Spousal benefit.
- With my wife talking hers at 67 and me taking mine at 70 and she then going with the 60% spousal benefit
- vs my taking it at 67 with her taking the 50% spouse benefit then (We are the same age)

The payoff waiting until 70 vs both going at 67 is at 88 years old just comparing cumulative payments and not factoring in ROI. That is because her payments at 67 are very small as she was a homemaker. Taking the spouse benefit provides a significant ramp up that the higher 70 values take a long while to catch up

[Romer]

The knob I can turn is expenses. I calculated I needed $125K to live the same lifestyle, with 3 big trips and not having to scrimp. I made my plan $150K to cover big ticket items like a car, new furnace, a big family trip. I wanted to make sure I could fully enjoy life when I decided to retire.

If I reduce spending down around what I think I actually will do, then 2.5% ROI changes to showing Roth conversions are the better option in 16 years, sooner if my expenses are less or my ROI is greater. I did not change the legends to say $130K.

This is because again the higher RMDs drive paying taxes earlier leaving less of a balance to grow. SS at 70 takes off as the higher SS Benefit finally catches up providing more investment to grow

so with Lower expenses, Roth Conversions become better

I should note in my situation I have a Pension and looking at SS as I discussed above. Those two together provide $108K of income at 67. Again your situation is different

The objective of my posts is to provide things to think about as you lay out your initial plan

Then update it every year based on actual performance in ROI and Expenses and other factors that may come up

The other item that may make Roth conversions more attractive is if we assume the tax rates will go up as planned in 2025. Then you would pay less tax now and that is always a reason to do it.

The other thing that would make sense is if you are in a lower tax bracket and expect to be in a higher one. Since I will be in the 22% bracket throughout my retirement, that wasn't a factor for me

Again, not an expert, just a guy with a spreadsheet trying to lay out my first few years

[Out-to-Lunch]

Quote:

Originally Posted by Romer The variable on Roth conversions or no Roth Conversions comes down to ROI Anything below a 4.25% return is better if I don’t convert as this reduces my balance via taxes enough that the investment grows faster without the conversions

What does the y-axis on your graphs represent? I.e., are you distinguishing between the value of a dollar in Roth vs. traditional?

In my view, the performance of your investments does not affect the advisability of Roth conversions (due to the commutative law of multiplication). Only the marginal rate at conversion vs. withdrawal matters.

[Romer]

The total value of my liquid assets. It sums up my accounts; IRA, 401K, Roth, Savings, CDs

so it plots the value of my money over time against the age at the bottom for the 3 scenarios

SS at 67 with no Roth Conversions
SS at 67 with Roth conversions to top of 22% Tax Bracket
SS at 70 with Roth conversions to top of 22% tax bracket

All plotted using the same starting expense escalated every year. Includes a calculation of federal and state taxes as part of expense. Includes RMDs in Tax for expense and unused post tax RMDs go into after tax savings

[Out-to-Lunch]

Quote:

Originally Posted by Romer The total value of my liquid assets. It sums up my accounts; IRA, 401K, Roth, Savings, CDs

Just trying to understand: You simply sum the dollar amounts in these different accounts, making no attempt to assess the embedded tax liabilities on them? So, immediately after making a Roth conversion, the sum that you would calculate would be lower than just before the conversion?

[chassis]

BS alert: embedded tax liabilities are a red herring.

If one accounts for so called embedded tax liabilities then also one must include future entitlements. This means the present value of SS, pension and other sources need to be accounted for as assets.

Embedded tax liabilities on the balance sheet are a red herring. Skip that thought, ignore it.

[Romer]

Quote:

Originally Posted by Out-to-Lunch Just trying to understand: You simply sum the dollar amounts in these different accounts, making no attempt to assess the embedded tax liabilities on them? So, immediately after making a Roth conversion, the sum that you would calculate would be lower than just before the conversion?

Fair point. In my details they are separate. So I take money out of the IRA I include taxes as an expense

I believe you are saying for example $500K of IRA is not the same as $500K of Roth as the IRA also has embedded tax liabilities

The graph just tracks total dollar value left to show me when or if I would run out of money. That was my only objective with it

In the graph below for example
For the No ROTH option the IRAD slowly goes to zero due to RMDs with excess RMDs going into savings, BUT I would have to live until 110 for that to happen.
More realistically at 85, there is 55% in Roth and 45% in after tax investments. Your point I believe is that the 55% of IRA is worth 26% less with federal and state tax applications

Did I capture your comment correctly?

[sengsational]

One thing about complex models is that there can be calculations that the modeler thinks are doing one thing, but they're doing something else. Back when I was obsessing over this kind of thing more often, I had all my assumptions defined, then build a version of my model that tried to replicate a simpler online model. Let me tell you, it was hard to replicate anything, because even if they tell you how it's supposed to be working, and lay out their assumptions, there's still methodological choices undisclosed.

The thing I came away with was if one follows a few principles, you can get most of the benefit. Also, the difference between winging it and putting the finest point on it as possible might not amount to much. That's not to say modeling isn't worth it...it is. It's just to say you should probably enjoy modeling if you do a lot of it. I used to enjoy it more than I do lately.

[pb4uski]

Late to this thread. My basic retirement planning was with Quicken Lifetime Planner and I think it is a great first step for most people because it is easy to use, reasonably flexible, and covers a lot of bases. For those who are not fluent in Excel, and even those that are, it is a great tool. It also includes a What-If function to compare scenarios with different assumptions that is useful. Its weaknesses are that it is deterministic rather than stochastic and that taxes are not particularly sophisticated (a assumed tax rate only), but it is a great basic retirement planning tool.

I then supplemented the basic plan in QLP by running FIRECalc and other stochastic planners using the same assumptions as in QLP (or as near as I can get) to stress test for SORR. Now having been retired for 10 years, I don't bother to do that as often anymore.

I like opensocialsecurity.com for looking at claiming strategy optimization because that tool includes both mortality (the probability of your or your spouse being alive to receive your SS benefit) as well as discounting for the time value of money (don't forget to use only real rates of return as an input).

Before I retired I had an Excel model that essentially did a lot of what QLP does but I could see the results easier.

Since I retired, I built a projection model to assess Roth conversions that has an embedded tax calculation included in it similar to what the OP describes. I have tax brackets that inflate with an inflation assumption (I use the Fed's target of 2%). I use todays tax brackets but can adjust rates to include reversion to pre-2017 tax rates, etc.

The one thing that I have included that I didn't see mentioned is that I have separate tax calulations for ordinary income and for preferenced income (qualified dividends and LTCG).

[Romer]

Quote:

Originally Posted by sengsational One thing about complex models is that there can be calculations that the modeler thinks are doing one thing, but they're doing something else. Back when I was obsessing over this kind of thing more often, I had all my assumptions defined, then build a version of my model that tried to replicate a simpler online model. Let me tell you, it was hard to replicate anything, because even if they tell you how it's supposed to be working, and lay out their assumptions, there's still methodological choices undisclosed. The thing I came away with was if one follows a few principles, you can get most of the benefit. Also, the difference between winging it and putting the finest point on it as possible might not amount to much. That's not to say modeling isn't worth it...it is. It's just to say you should probably enjoy modeling if you do a lot of it. I used to enjoy it more than I do lately.

I agree completely. I do enjoy it as it forces me to learn how things all play together. I keep finding things I missed or need to revise. However, I do not expect my model to be reality, just a planning tool to be updated periodically based on actual performance to help make decisions for the next year. I don't expect it to be prefect, but it does make me feel I am putting in the due diligence to make a good decision (Engineer in me). And if things turn out differently, as Yogi Berra says "it is what it is"

[pb4uski]

Quote:

Originally Posted by chassis BS alert: embedded tax liabilities are a red herring. If one accounts for so called embedded tax liabilities then also one must include future entitlements. This means the present value of SS, pension and other sources need to be accounted for as assets. Embedded tax liabilities on the balance sheet are a red herring. Skip that thought, ignore it.

^^^ That is BS. There is nothing that says that if you include deferred taxes in your planning that you "must" include future entitlements like SS or pensions.

In fact, the professional accounting standards for personal financial statements... what you would need to follow if you were having personal financial statements opined on by a CPA firm... would require deferred income taxes but prohibit recognition of assets for SS or pensions since the receipt of those cash flows are life contingent (though that last part is controversial that is wher they landed back in the 70s and personal financial statement is such a narrow area of practice that it has never been revisited by the standard setters).

I don't include any of those in my measurement of net worth, more due to simplicity. I could easily include deferred income taxes but the difficulties in assessing the right tax rate to use is problematic enough that it isn't worth the effort.

For me, an individual can decide for themselves what to include or not, just like a company can decide what to include or not in their management financial statements (and many do).

[pb4uski]

Quote:

Originally Posted by Romer ... More realistically at 85, there is 55% in Roth and 45% in after tax investments. Your point I believe is that the 55% of IRA is worth 26% less with federal and state tax applications Did I capture your comment correctly?

Not O-T-L, but yes, you captured the idea correctly... basically there is an embedded tax liability aka deferred income tax liability or DTL for things like tax-deferred retirement money because it will be taxed when withdrawn... so you'll have less than $1 to spend for each $1 withdrawn whereas for money in a roth if you withdraw $1 you have $1 available to spend.

In your taxable income calculation are you including only 85% of SS? That would be an easy thing to miss. I didn't bother to build a calculation of taxable SS in my model but I just assumed that 85% of SS would be taxable across the board given the level of our income.

[Romer]

Quote:

Originally Posted by pb4uski For me, an individual can decide for themselves what to include or not, just like a company can decide what to include or not in their management financial statements (and many do).

Gentleman, there is no right answer. No BS here. What everyone is looking for and wants to see in a graph and tool is different

My only goal is making sure I have a viable conceptual plan to not run out of money. I considered tax implications as I moved money between account but not of the accounts themselves.

Others want something else out of the tool and can make their own assumptions based on what they want to see

Then as some point out, the results in later years are all BS since so much is out of our control. Its just a tool not a high reliable predictive analysis like one would use to make sure they are able to reach the orbit of Mars