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

[Romer]

I know first post so take it for what it is worth. I also apologize in advance for any spelling errors

Lesson learned will actually come from how well my model tracks to reality in the future. This is about how I learned to create and update my model to hopefully cover the basis and help me make decisions ahead of time.

First off I am not a Tax expert, Financial expert and make no claims to the validity of what I am posting below. My goal here is to share the lessons learned I have from developing my own model over several years and illustrate its all about finding a balance based on your unique set of variables. You can do all this from an Excel spreadsheet fully understanding your choices rather than paying a financial planner to do this wondering if they considered everything they should

I have been modeling my retirement for a while now. Continually updating my excel spreadsheet as I saw things I didn’t consider. I am not going to share my spreadsheet as its pretty custom to my situation as yours should be. I will be retiring the end of the year

The biggest decision besides when to retire for me is what to do about my IRA for Roth Conversions and when to take Social Security

My goal is to maintain our current lifestyle and fortunately I have a pension that starts this month at 60 that helps that. That makes the choice as to when to take social security more flexible. I am down to 67 or 70

Planning Expenses:
• Before I created the detailed spreadsheet I reviewed 4 years of expenses.
• I then removed Mortgage (house paid off), 401K contributions, Health Insurance and taxes
• I then added in the Health Insurance cost I have from my employer for retiree Insurance. Just under $20K, very expensive. I wont qualify for any ACA subsidies
• II then added in other items like adding to the travel budget
• I then used 2021 Tax rates to estimate 2022 taxes
• I took the 4 years of adjusted expenses and added in Taxes and Health insurance and have a range of expenses for our current lifestyle.
• One of the years was much higher and went back in and removed wedding costs for my daughter that I hadn’t caught the first time.
• This resulted in a range that was +/- 10% from the average and so used these for planning
• I rounded up and down to provide 4 expense ranges that were equally separated but bounded the range. NOT The Graphs at the bottom
• I added these to the spread sheet column and escalated every year for inflation. I used 2%, but most others would likely do 3%. Its what you feel comfortable with reviewing what you spend money on
• Then I looked forward and how things would modify my expenses, Medicare at 65 will reduce Health care by $14K per what I have found, maybe more and I don’t expect to be doing all my same hobbies and traveling as much after 85, so slightly reduced income in that year
• This resulted in a 40 year estimation of my expenses over 4 expense rates. I have a goal rate (Expense per year) to work to, but wanted to make sure I had margin

Taxes:
• It’s hard to know if Tax Rates will change, I assumed they would not
• Tax Brackets due change. They go up 1 – 1.5% a year based on my quick estimation
• I created 4 columns for the 10, 12, 22 and 24% tax brackets and increased each year by 1%
• This was a later addition to my spreadsheet and because these brackets change the amount to convert to Roth staying in one bracket changes. Also, the impacts of RMD’s change based on where the tax brackets land. Again everyone’s situation is different that is why you need to develop and customize to your situation
• My state is a flat tax so I just modeled that in the expense

Return on Investment:
• This is the area that provides the greatest variance and one you have little control over in reality
• I see some like a 6% return. I have averaged over 12% the last 15 years, but I intend to go more conservative
• This is what I picked and you likely will pick something else. I modeled 1% (CD Rates), 2.5% and 3.9%. 3.9% was what I saw as a conservative long term rate that several “experts” that was a safe bet. I did not cover negative returns, which is a risk. I will just need to have enough in cash to work through a downturn
• I also did a stress test at 0% and verified my money would last but at a slightly reduced income until 95
• To be extra conservative, I did not consider selling my house. Again your situation will be different

Roth Conversions:
• At a high level, converting all the IRA into a Roth sounded great. I would eliminate the need for RMDs and always be in the 12% tax bracket
• In my situation, the only time this worked out to be the best option is if the ROI was 1% or lower
• That is because your ROI growing your savings is almost nil and it ends up being down to expenses
• At higher ROIs, doing the mass conversion up front lowered the amount and time my money would last
• Doing no conversions didn’t work either as the RMDs drove me to a higher tax bracket
• I modeled two types of Roth conversions: MIN: capped to the 22% bracket, MAX: Capped to the 24% tax bracket. I so no reason to model to the 32% bracket
• This why having the tax brackets escalate each year by 1% is important as it changes the thresholds for MIN, MAX or however you want to do it
• For Min, I modeled to the 22% bracket until RMDs started. I will reevaluate if I should continue them after RMDs start. The RMDs of course reduce the amount to convert to get to the 22% bracket. Being 12 years from RMD’s I figured this was good enough and my model shows the answer is different at 72 based on the ROI I actually achieve
• For Max, I did conversions to the 22% bracket until they conversion was complete resulting in no RMDs, but a significantly reduce investment balance to gain returns on
• For Max, I had a significantly reduced tax bill lowering expenses, but had a major smaller investment base to pull it from
• For Min, I had a lower tax bill than no conversions, a small RMD that fit within my expenses. Meaning I did not have to have more income than I needed
• For no conversions, the RMD drove me to have more income then I needed with the pension and social security and as a result a higher tax bil


Social Security:
• I came down to two points after running through models early on, 67 and 70
• Early models clearly showed SS at 70 is the right answer
• When you factor in Roth conversions, ROI and RMDs, in some situations 67 is actually better until about age 95 (Changes based on variables)
• I used my pension, RMDs and 85% of social security to define the taxable portion of my income along as anything else I may or may not need will now come from the ROTH
• In some cases in my situation, the Pension, RMDs and SS at 70 drove my taxable income above what I needed where as taking it at 67 would not. In these situations; Pension, RMDs and SS @67 required almost nothing from my Roth which is able to sit and grow creating a larger investment balance
• This sweet spot all changes based on your IRA to Roth situation, Resultant RMDs, having a pension and ROI

Conclusion:
It all comes down to finding the right balance for your situation. Using FireCALC is great to do a sanity check, but it doesn’t allow you to fully model your unique situation, tweaking individual variables to find the best balance and sweet spot for you

Having your own model allows you to go in and update for progress and make any modifications. I expect the answer for 67 to 70 may change based on actual performance and expenses and I know I don’t have to make that decision for a few years.

Based on my situation and models, I have decided to do min Roth conversions to the 22% bracket until age 72. At that time I can always make the decision to continue them on top of RMDs. Or, if things significantly change with a higher ROI than I expect, I may decide to switch to the max conversions (24% bracket)

I am no tax or financial expert. Just an engineer who is use to modeling all kinds of scenarios. Please don’t take anything I said her as gospel as to what you should do. My entire point in sharing this si everyone’s situation is different and using Excel to model your unique situation will help YOU find the right balance.

The bottom 3 graphs are of my situation to illustrate how things vary. For the 1% ROI you can see that at age 85, SS at 70 and Max Roth conversion is the best option because investment isn't growing so lower Tax expense is the better outcome for funds lasting longer

the 2.5% graph shows that the ROI is significant enough to outweigh the benefit of the lower tax of the full conversions. It shows SS at 70 overtakes SS at 67 at about age 86. No Roth conversions is the worst option as RMDs drive income above what is need

The 3.9% Graph shows the min ROTH option is the better solution as it has more base to grow with the higher ROI. Also the Higher ROI moves the date SS@70 overtakes SS@67 to about 93 years old. This shows even higher rates will move that later

Again these graphs are MY SITUATION. The answers will be different for you and to just illustrate why you should develop your own model and tweak it to evaluate multiple scenarios to find the sweet spot

Sorry for the long first post

 

[Montecfo]

hi and welcome.

I do agree that doing your own spreadsheet is the way to go. It also avoids using a planner's "black box". Flipside is you have to be really diligent, but sounds like you are that type, as I am.

I think you can fool yourself into thinking you can plan this out with precision. You may optimize SS if you know when you will pass. Otherwise it is more based on conditions on the ground and and overall understanding of how the numbers work over time.

You make an excellent point on tax brackets, but you probably want to model current tax rates expiring for 2026. That in essence is current law.

You may also want to model a cutback in SS when funds start running short. Some folks do that.

One other advantage of doing your own models is it gets you really familiar with the mechanics and keeps you close to the numbers. You likely will become rather expert on your taxes.

Again, welcome and wish you luck on your models and your retirement.

 

[Romer]

hi and welcome.

I do agree that doing your own spreadsheet is the way to go. It also avoids using a planner's "black box". Flipside is you have to be really diligent, but sounds like you are that type, as I am.

I think you can fool yourself into thinking you can plan this out with precision. You may optimize SS if you know when you will pass. Otherwise it is more based on conditions on the ground and and overall understanding of how the numbers work over time.

You make an excellent point on tax brackets, but you probably want to model current tax rates expiring for 2026. That in essence is current law.

You may also want to model a cutback in SS when funds start running short. Some folks do that.

One other advantage of doing your own models is it gets you really familiar with the mechanics and keeps you close to the numbers. You likely will become rather expert on your taxes.

Again, welcome and wish you luck on your models and your retirement.

Thanks for the reply and you are correct. I have no illusions that I have planned this out with precision. I have modeled my income growth over the last 20 years and every year my model was wrong and I performed better.

I see this as a starting point to work from that needs to be maintained, and updated based on changing conditions

I need to monitor my progress along with adjustments to determine if I need to modify anything as I move forward

The only real conclusion I feel I can take from my model is I can afford to retire (Yay) and next year I should do a Roth conversion to the 22% tax bracket. I will have to wait until the end of next year to decide what to do with the year after and won't make a decision on Social Security until I get close to 67

But the thing about the plan is it isnt going to be the way things turn out. It is just a tool to be used pro-actively year to year to manage things going forward. That is another reason to develop your own as it forces you to understand the data and actually be able to modify your plans

 

[RunningBum]

For expenses, did you factor in large irregular expenses you might not have hit in those last 4 years? Like, replacing a car? Repainting your house or other home maintenance? By 85 you might be in an independent or assisted/memory living situation, which may change your expenses, possibly by a lot.

Return on investment and inflation rates are almost certainly not going to be a straight line, whether high or low. Averaging out to 0% ROI may be a valid worst case for returns, but it looks like you didn't vary inflation. This is where tools like FIRECALC are useful, for showing how you'd do in historical situations.

My calcs have taxes reverting to higher brackets in 2026. This may not happen but I'd rather be conservative with my estimates. I won't be paying that much in fed taxes anyway so it's not a big impact.

I'm not going to get into the Roth conversion or when to take SS decisions. Just use Advanced Search and search for Roth in a thread title and look for threads with high post counts. SS is a little harder because search doesn't return results for 2 character searches. I'm worn out from those previous threads and I'm set with my plans. Will try not to get sucked in by other responses.

 

[Romer]

I did run Firecalc and came out at 100%

My expense level is high maintaining my current lifestyle with margin. My selected expense level has $30K of margin. My situation is different and I am comfortable with what I had

Those are all good things people will consider. I commented on the tax brackets and decided to not change them and also on the Inflation value I used

I also was not trying to suck you into another thread. I am glad you have a plan and path forward. My lessons learned are intended for those who do not and maybe what I have done will help them

I did read the roth conversion threads. Some good info in there, but it all depends on your situation

 

[nico08]

Hi Romer and Montecfo. I know the basics of how to use Excel, but I am not an expert user. Can you tell me what variables you use in your columns and rows, and the appropriate equations, for the Excel spreadsheets that make up your custom models? Thanks.

 

[aja8888]

I'll be 78 in a couple of months and I agree setting goals and planning is a good exercise, but in the end, "Nobody Knows Nuthin"....LOL!

I've had more unplanned surprises along the way....but glad I had a "contingency plan"!

 

[Midpack]

Whatever you need to be comfortable with your plan. I’m also an ex-Engineer and I’ve used spreadsheets daily since long before Excel. Building elaborate spreadsheets can be a good exercise (was/is for me), if only to convince the user that retirement planning is an axe, not a scalpel - and an elaborate spreadsheet won’t change that. No matter how much you put into it there are so many unknowns acting that you can’t account for (e.g. SORR/returns, inflation, HC inflation, tax rates, longevity, geopolitics, unexpected expenses, etc.) over a period of 30 years +/-!

There’s a good reason FIRECALC results are so variable, and it’s not because “it doesn’t allow you to fully model your unique situation, tweaking individual variables to find the best balance and sweet spot for you.” I still maintain detailed spreadsheets on spending/budget, net worth and short term tax planning, but I’ve come to believe running something like FIRECALC every 5 years or so, with considerable safety factors and a plan B/C/D, is as good a predictor as anything. FIRECALC does allow for quite a few inputs, though not taxes understandably. YMMV

 

[Romer]

Hi Romer and Montecfo. I know the basics of how to use Excel, but I am not an expert user. Can you tell me what variables you use in your columns and rows, and the appropriate equations, for the Excel spreadsheets that make up your custom models? Thanks.

Probably the one that took me the longest time to get right was to calculate the tax in an equation that worked every year
You can change these based on the potential; for them to increase in 2026 based on current law

=IF(K7-D7<0,(0),(K7-D7))*0.22+(C7*0.1)+((IF(V7>D7,D7,K7)-C7)*0.12)+((K7-20000)*0.0465)

K7= My calculation column for Taxable income
C7= 22% Bracket escalated every year by 1%
D7= 12% Bracket escalated every year by 1%
E7=24% Bracket escalated every year by 1%
F7 = 22% Bracket escalated every year by 1% (I know its out of order)
The last part is for Colorado as they take off the first 20K and then have a flat rate of 4.65%

The c7 times .1 is to cover the base 10% tax before the 12% bracket starts

The =IF(K7-D7<0,(0),(K7-D7)) is pretty useful. Essentially IF K7-D7 is negative then it will use 0, otherwise it uses the equation. The negative values were messing me up

 

[rodi]

The OP has done a great job of figuring out spending and modelling it for retirement.

I used a variety of calculators... Including a fairly complicated spreadsheet that I created for various "what if" scenarios.

One of the things I did was similar to the OP - Take my gross pay, subtract out the things that wouldn't apply in retirement (401k contributions, employer health care premiums, SS and medicare contributions) Add in the things that would be new in retirement (healthcare premiums on the open market, bigger travel budget). That gave me my working budget (SWAG) for retirement planning. I then used some other calculators that detail spending to see if I came up with similar numbers. After a few iterations of figuring out what I missed, I had the same ballpark number from all sources as to what I needed to spend in retirement.

Here are the calculators I used.

Firecalc is good for showing how market variations, asset allocation, and longevity can impact your plan.

Quicken lifetime planner is good for figuring out typical, but lumpy expense (college tuition, wedding expenses for kids, etc.) but it's deterministic... you choose the rate of return and the rate of inflation... So not real world with variations. But it's great for making you think about your expenses.

Fidelity Retirement Planner can be very detailed - you can break out expenses that inflate at a higher rate than everything else (healthcare, college, for example). You can be as detailed as you want on expected expenses. Or you can just input a number and have the same inflation applied to the total and hope your number was correct.

I found the usefulness to be in stress testing my plan... making sure I'd thought of most things, and had a plan that accounted for market changes, spending changes, etc.

 

[Romer]

Whatever you need to be comfortable with your plan. I’m also an ex-Engineer and I’ve used spreadsheets daily since long before Excel. Building elaborate spreadsheets can be a good exercise (was/is for me), if only to convince the user that retirement planning is an axe, not a scalpel - and an elaborate spreadsheet won’t change that. No matter how much you put into it there are so many unknowns acting that you can’t account for (e.g. SORR/returns, inflation, HC inflation, tax rates, longevity, geopolitics, unexpected expenses, etc.) over a period of 30 years +/-!

There’s a good reason FIRECALC results are so variable, and it’s not because “it doesn’t allow you to fully model your unique situation, tweaking individual variables to find the best balance and sweet spot for you.” I still maintain detailed spreadsheets on spending/budget, net worth and short term tax planning, but I’ve come to believe running something like FIRECALC every 5 years or so, with considerable safety factors and a plan B/C/D, is as good a predictor as anything. FIRECALC does allow for quite a few inputs, though not taxes understandably. YMMV

I agree completely

Firecalc did not allow me to model taxes or adjust expenses base on my situation like change in Health care costs from 60-65 or a reduction based on taxes or RMD values and playing with my Roth conversion to see impacts


Firecalc is a good tool to use

 

[Midpack]

Firecalc did not allow me to model taxes or adjust expenses base on my situation like change in Health care costs from 60-65 or a reduction based on taxes or RMD values and playing with my Roth conversion to see impacts.

FIRECALC doesn’t do taxes because no one knows how they’ll play out over the next 30 years. Users just need to treat taxes as another expense, including RMD and/or Roth conversion impacts. That’s a much easier DIY spreadsheet exercise.

And under the Spending Model tab “FIREcalc supporters can make yearly spending revisions throughout the duration of their plan” - so you have lots of flexibility to model spending any way you’d like (pic below). There’s no minimum donation for supporters.

There are many features in FIRECALC that users don’t realize are there, or don’t understand how they can be used to model all sorts of situations. The authors were incredibly clever, not fully appreciated by most.

Again I learned a lot building complex spreadsheets, so it wasn’t time wasted by any means, I learned a lot. But without something to model SORR variation, I didn’t see any spreadsheet as useful for probabilities - which is the crux of planning. Whatever happens, we all know there won’t be any straight line returns, inflation, taxes, etc.

I still have the spreadsheet I did in 2009 that modeled everything (while still at my peak Excel skills), with tons of input variables, I spent months developing it. Looking back there aren’t any built in errors, but it wasn’t even close in predicting our path to 2021. I opened it a few days ago, and just laughed at how naive I was to believe I could be more precise. Look again at the FIRECALC results charts, that’s the unknown variation you’re trying to tame…

 

[Romer]

Thanks for highlighting the spending by year. I missed that one.

I am a car enthusiast. Why I am telling you this? I am a long time member and moderator for a Toyota Land Cruiser Forum. That is valuable resource and when I started really helped me get going. I was able to diagnose problems and incorporate modifications leveraging the lessons from others. As I grew more experienced, I gave back to the forum creating lots of how to's like a thread with instructions and pictures on how to rebuild a front axle. Learning and giving back is the beauty of these forums.

I have been researching and laying out my retirement plans for a long time. Lots of sources. I didnt find one place that meet my needs. Much of what I included in the first post came from iterations as I matured my model over several years.

I found this web site last month and found it to be a great resource.

My intent/objective with this thread was not to say everyone should do it this way, but to share what I learned along the path as maybe it will help someone who is just starting to dig in.

I did conclude that having a custom model forces you to better understand the interplay of all the factors and enables easier customization to your scenario. As mentioned, these need to be augmented with Firecalc or other options for a stress check

The internet is a 2 dimensional forum making communication of intent and meaning harder to gleam. I just wanted to state my objective with starting this thread which is likely of little value to those who already have models or sources of data they are happy with

This discussion has been valuable to me and opened up other ideas and thoughts to consider

Have a great 4th of July

 

[OldShooter]

Comments from someone who has done about a jillion models.

... =IF(K7-D7<0,(0),(K7-D7))*0.22+(C7*0.1)+((IF(V7>D7,D7,K7)-C7)*0.12)+((K7-20000)*0.0465) ...

Embedding parameters like 20000 and 0.0465 leads at best to inconvenience and at worst to errors. The parameter values are essentially invisible to someone using the sheet, thus subject to typos or worse, and they are difficult to find and update when things change. You will be better off putting all the parameters into a section of cells with labels, then referencing the parameter cell when you need to use the value. Then everything is very visible and easy to change. My rule is: Never embed a parameter in an equation; always use a cell reference or use table lookups if necessary.

 

[levindb]

I have done some detailed spreadsheets on when to take SSA benefits and learned that the ROI forecast had a significant impact...larger ROI's suggested to take SSA sooner. I assumed smaller ROI and therefore took SSA at age 70...now SSA covers most of our necessities. IRA's can handle the joys of retirement and the chunky expenses.

I had a hard time dealing with inflation, so I did everything in TODAY'S Dollars. I know SSA benefits will increase with inflation, as will most expenses. My mortgage (I decided to keep it at less than 3% interest), would not change, and my health care expenses would increase greater than expenses, so they may offset each other. My retiree healthcare has annual out of pocket maximums, so I have some protection there as well.

I assume ROI minus inflation would be about 3%...both numbers are out of my control, and I can adjust the 3% if I want to be more or less conservative.

To calculate my annual spending, I assume I will run out of funds at age 105, and then solve (Goal Seek) for how much I can spend each year. Our actual spending is always much below that number, but it gives me a max which is helpful. On RMD's, I will do Roth conversions to keep RMD's below this max spend...since I only want to eliminate an RMD's impacting my annual withdrawal (I hope that make sense).

I know I could get into a lot more detail, but there are too many variables that we cannot control...I just do not want to run out of funds while alive and I do not want to be a burden to my children.

 

[Out of Steam]

Comments from someone who has done about a jillion models.

Embedding parameters like 20000 and 0.0465 leads at best to inconvenience and at worst to errors. The parameter values are essentially invisible to someone using the sheet, thus subject to typos or worse, and they are difficult to find and update when things change. You will be better off putting all the parameters into a section of cells with labels, then referencing the parameter cell when you need to use the value. Then everything is very visible and easy to change. My rule is: Never embed a parameter in an equation; always use a cell reference or use table lookups if necessary.

Your rule is best practice for MS Excel spreadsheets.

I maintained a pension estimator spreadsheet for at least 5 years before I retired. Such parameters had to be repeatedly changed, and were best left visible in cells.

While I don't have a lifelong spreadsheet model for retirement income, I do have a cash flow model that extends through the expected arrival of our first Social Security payment in late 2022. I plan to extend it through the arrival of the second stream of payments in 2025.

After that, I expect to be able to meet expenses with no more than a 2% draw on retirement savings, though I'm perfectly willing to take 3% for more disposable income or savings outside of retirement accounts.

 

[jollystomper]

I did something similar before I retired with Excel.

I modeled my expenses, looking back at 5 and 10 year averages (thank you, Quicken) to come up with a ballpark for my categories. Then adjusted by taking out expenses I no longer had (e.g college costs) and adding in additional expenses (e.g. additional car, travel, future needed repairs/improvements). This because my "target" retirement expenses that I used in various models (FIRECalc, Megacorp retirement planning tools. etc.).

I then did a five year projection, adjusted for inflation, of my overall expenses to determine the required cash flow. I used 3 and five 5 year projections because (a) that is the most I would consider having in cash to cover expenses, without the need to touch equities, and (b) 3-5 years from retirement would be the earliest I would look at taking SS (this was also something I modeled to project the yearly amounts and breakeven points for DW and I). This gave me an estimate of the cash I would build to have during retirement.

I did not look at Roth conversions before retirement, as our income made us ineligible for them. But once retired and becoming eligible, I built a model to project out the yearly impact of Roth conversions based on our expected income.

Other things I modeled where health insurance costs (ACA vs. Megacorp retiree subsidized/non-subsidized, with and without COBRA), federal and state taxes, etc.

In retirement I still have a "main" Excel workbook for both tracking/modeling some of these items. It currently contains about a dozen tabs (some are just constants for aid calculations, like tax tables).

In sum, my errors have been on the good side so far... overestimating expenses and underestimating income. Hey, I'm not perfect at this .

 

[RobbieB]

I don't have a clue as to building a custom model with excel. I don't even know what excel is.

Oh, I know, it's the little one on the right -

 

[Romer]

And I thought I had a plan

This site has already helped me significantly

I bought the recommended Jane Bryant Quinn book on how to make your retirement last and read the section on spousal social security benefits. First I was confused because I thought it was talking about death benefits and that I thought was 100%

I reread it and searched this site and learned something I had no idea about

Here I thought it was my benefits plus my wifes benefits. Hers calculate out to 25% of mine based on her work history

The spouse benefit taken after FRA will add $10K in income that I wasn't expecting and I am not use to good surprises from the government

Per a recommendation I saw here I went to https://opensocialsecurity.com/

It provided the following strategy

• your spouse files for his/her retirement benefit to begin 9/2026, at age 65 and 5 months.
• You file for your retirement benefit to begin 6/2031, at age 70 and 0 months.
• Your spouse files for his/her spousal benefit to begin 6/2031, at age 70 and 2 month

I will have to research that more because from what I read I thought she should wait until FRA at 67 so she gets the full 50% when I file

BUT a new lesson learned

 

[martyp]

i built my own spreadsheet to decide whether I could retire or not. I was never one to track my expenses because my year to year expenses were the same year to year and I always had savings to cover any unexpected expense. What I learned by building the spreadsheet was that there were 3 categories of expenses:

1. Expenses that occurred monthly: mortgage, phone, internet, groceries, gas, savings, etc.
2. Expenses that were regular but intermittent: these were mostly various insurance premiums but also vehicle registration and taxes.
3. Expenses that are unexpected (though planned for): A new roof, a new washing machine or water heater, etc. I looked at these expenses as a few occurances over a period of say, three years.

I started tracking my expenses in detail in the few months before I left work and then continued for two more years at which point I wasn't learning anything new. I built the spreadsheet out for 30 years and could include when I paid off my mortgage and when I started Social Security. I could also play what-if by adjusting various interested rates and pension cost of living adjustments. In reality though I don't have confidence in what the world is going to look like more than 5 years out. (Just consider the last 2 years!). I estimate a lifespan from my family health history and mine. Pick a number, any number.

 

[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.

Before I created the detailed spreadsheet I reviewed 4 years of expenses.

Yep, I download and categorize all of our expenses every year to determine where we actually spend money. No guessing or making incomplete estimates. For retirement planning purposes I take our highest spending year and add a few thousand for new unknown expenses (healthcare, etc.). Better to plan for more than we will probably need.

It’s hard to know if Tax Rates will change, I assumed they would not. Tax Brackets do change.

Tax brackets have changed in the past and I assume they will do so in the future. While we are in the 12% tax bracket, our effective tax rate has been around 7-8% for the last decade or so. Still, I use a 12% effective rate in my estimates just to be safe.

I see some like a 6% return. I have averaged over 12% the last 15 years, but I intend to go more conservative

I use a 7% return with a 7% deviation in my estimates. Historical values for the funds I use have performed better, but I like to err on the side of caution. I also only use a .4% return for all taxable savings as that's the current rate on my savings account.

converting all the IRA into a Roth sounded great. I would eliminate the need for RMDs and always be in the 12% tax bracket

We expect to be in the same 12% tax bracket after retirement as we are now, so financially a Roth conversion basically breaks even. However, having tax free income will allow us to qualify for additional benefits like ACA health care subsidies, reductions on property taxes, etc. I hope to convert the last of my traditional IRA next year.

 

[Perryinva]

The bottom line is it is all an EDUCATED SWAG. Lots of engineers on this site, and we all consider the numbers and assumptions. Its what we do. Hopefully, it makes you feel better before you retire and in the early years. It IS a good base from which to embark from. From a strictly practical and realistically reasonable standpoint though, there are millions of people over the last hundred years that retired just fine with no real plans, and never ran out of money. Analysis paralysis can drain the fun out of life.

You did well, and you know it. You’ve lived your lifestyle for many many years, as has most everyone here. Without exception (may be a small stretch, but not by much) everyone here has more money in their portfolios and more spendable income than they expected/planned for.

The nature of the beast is that going in with eyes wide open, and living with in your means is basically all it takes to have a successful retirement. It’s what gor you here in the first place. I think pb4uski said “I’m sticking with the date I brought to the prom”.

The vast majority of people that post here are in the top 5%-10%. And always will be, so why worry? You will deal with variables as they emerge. Realistically there is a higher likelihood of some unknown factor (cancer, pandemic, terrorist attack, natural phenomenon) that will upset your plans in unexpected ways than your own normal way of handling and variations in tax rates, ROI, and spending will, mainly because you’ve been covering all those factors your whole adult life, while the vast majority of people have not, and still get by.

Don’t worry. Be happy.

 

[DrRoy]

I found it interesting that I did almost the exact same thing for estimating future expenses. What I did not know is that the unexpectedly rising market would let me remodel the house 3 years in a row and pad the travel budget a lot. This year's spending will be nearly double my first year baseline (4 years ago).

 

[ImThinkin2019]

The bottom line is it is all an EDUCATED SWAG. Lots of engineers on this site, and we all consider the numbers and assumptions. Its what we do. Hopefully, it makes you feel better before you retire and in the early years. It IS a good base from which to embark from. From a strictly practical and realistically reasonable standpoint though, there are millions of people over the last hundred years that retired just fine with no real plans, and never ran out of money. Analysis paralysis can drain the fun out of life.

You did well, and you know it. You’ve lived your lifestyle for many many years, as has most everyone here. Without exception (may be a small stretch, but not by much) everyone here has more money in their portfolios and more spendable income than they expected/planned for.

The nature of the beast is that going in with eyes wide open, and living with in your means is basically all it takes to have a successful retirement. It’s what gor you here in the first place. I think pb4uski said “I’m sticking with the date I brought to the prom”.

The vast majority of people that post here are in the top 5%-10%. And always will be, so why worry? You will deal with variables as they emerge. Realistically there is a higher likelihood of some unknown factor (cancer, pandemic, terrorist attack, natural phenomenon) that will upset your plans in unexpected ways than your own normal way of handling and variations in tax rates, ROI, and spending will, mainly because you’ve been covering all those factors your whole adult life, while the vast majority of people have not, and still get by.

Don’t worry. Be happy.

+1 on this.

I too am an engineer. Made two spreadsheets:

• A long term plan, and
• First 10 years cash flow plan

The first one was like I used when managing a small company division. That was a global view, but the way I built it, it depended on a fixed return. Of course, it could be modified to have adjustable returns but given our financial situation I did not see the need for that precision. (All the engineers here will completely understand the term "adequate precision" ).
The second one, learned about here, is actually much more useful. It's "where is the actual cash going to come from every year in the beginning decade."
Since I started building the models I have seen the stock market increase a lot, which I did not predict. And interest rates have dropped significantly, again, which I did not predict. This reinforces the need for flexible plans with moderate (or imperfect ) precision and as PerryinVA says, the ability to adjust your spending to compensate.
I do also use

• Firecalc
• Fidelity Retirement Planner
• And finally, Pralana Retirement Gold

We use the first two as reality checks, and the last for modeling Roth rollovers . Having tried various ways to model rollovers, this is the one we like the best. In our case we're doing the rollovers to give our kids money with the taxes already paid, so our situation does not depend on a pure ROI calculation.....
Thank you, though, for the excellent first post. It started a great discussion that hopefully will help others.

 

[Midpack]

It's interesting there are several people here with elaborate DIY spreadsheets for retirement planning accumulation and/or distribution. I don't recall ever seeing anyone report back on how they fared in retrospect? I did one in 2008, and it doesn't have any built in errors, but where we are in 2021 bears little resemblance to what my spreadsheet predicted...

I learned a great deal building budget/spending, investing/AA/net worth, tax planning/est taxes, and long range retirement planning spreadsheets, so it wasn't time wasted - but I recognized going in there was no way to get all the assumptions right, so they're just ROM results. I still use/maintain the first three but the long range retirement planning spreadsheet hasn't proven to be a great tool...and I don't maintain it.