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

[Rustic23]

I did a spreadsheet! It forced me to look at expenses, and income, think about future income etc. It was a 35 year model. I created a section for big stuff, I.e.roof, Cars, etc. I handled taxes by using after tax income, as I planned for small refunds each year.

It was a good exercise and, along with post on here, Firecalc, and all this showed me we would be OK, and 16 years later we are.

One major expense I dodged a bullet on, is a new roof. Hail damage and insurance paid for it. I also have not purchased new cars as often, don't go on as much, and don't save for retirement. One thing not in my spreadsheet was reduced spending.

All in all, it was a good exercise, and I should have done it 10 years earlier.

For the first three years of retirement, I updated the model. For the next 3 or 4 years I tracked spending with Quicken. I am at the point now that I know that I am spending the kids money and I won't spend all of it. I check the bank statements and credit card charges to make sure they are right, but I don't model anything. Oh, I lost my original spreadsheet, but I think it was close.

 

[AbbA]

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.

Thanks for this post. Now I feel definitely relieved
Just opening this thread and looking at the extreme detail of building your own Excel file from scratch made me shudder. I'm OK with simple Excel spreadsheets, but not this kind of stuff. I know my limitations not to trust myself to build a model that would plan my future 40-50 years.

Glad I saw Perry's thoughts above. I'll stick with calculators or spreadsheets that people sometimes share with others.

 

[2177V]

I'm a retired engineer similar to a few others here.
I never had a budget or bothered to track expenses back in my working years. But I had a decent income, saved a lot in tax-deferred off the top, and wasn't a spendaholic, so it worked out.

As I got close to retirement, the forums I read recommended recommended getting a handle on your "expenses". So I put together a detailed spreadsheet, updated many times over a period of several weeks.
I focused mainly on Basic Expenses, since discretionary travel expenses would hopefully be a lot higher in retirement.

Turned out my Basic Expenses were a modest fraction of my pre-retirement income, so I just decided: let's just aim for the same Net Monthly Income in retirement as when working, meaning that my checking account would hardly know the difference.

So I did that and my AGI and resulting income taxes have been higher than my employment years each year since retiring in 2013.

Now I do maintain a different spreadsheet in retirement to project and manage my AGI. This comes down mainly to figuring how much to Roth convert to get AGI up close to but not over the next higher Medicare IRMAA tier. I don't try to estimate income taxes or growth of my investments in this spreadsheet, just my AGI.

I do my own income taxes each year, so I know my taxes for this year will be just a bit more than last year, provided I don't do something silly and get a big jump in AGI...

Wow. I have a lot in common with this. Retired engineer, used a spreadsheet to manage my expenses, do my own taxes, etc. My wife is 20 younger than me and she opened a business so I used excel to track monthly business expenses, payroll for my wife’s workers, etc. Since I am retired, I do all the bookkeeping while my wife supervises the workers. Business taxes can get complicated but I figured it out thanks to Turbo Tax for partnerships. As far as planning for the future, planning spreadsheets are nice but “life happens” which may make a planning spreadsheet obsolete. I also use spreadsheets to manage my income producing properties but it is more expense oriented than predicting the future. Once I understand my expenses: (1) household, (2) wife’s business expenses and (3) income producing real estate expenses, I can make better strategic financial decisions…like whether I can afford to buy a $80,000 C8 Corvette.

 

[WWDog]

Thank you all. I have maintained a spread sheet for many years on my finances making a different version each year and possibly adjusting or adding features. Each new spread sheet incorporates the history of my inputs and calculations along with projections for the current year and projections for years to come. I take a pessimistic approach to how things will go for the year and thus each year turns out better than projected (which is what I intended to be safe.) The one new feature this year is a projection of how many years of expenses (using my current spend rate) I have if my retirement income should stop suddenly and totally (not including any increase due to inflation). The numbers will be alright even though I have had a lot of significant expenses (new heating and air, new hot water heater.)



The one thing I haven't taken into account is the fact that my wife is not a excel person and will not be able to maintain it if anything should happen to me. Then again, she will not have to worry about tracking money.


The reason for the thank you is that my wife thinks I am engineering this too much and I found that I am not alone!

 

[TheWizard]

I mentioned previously that I don't track or project (budget) my expenditures.
My spreadsheet just tracks and projects taxable income (AGI) from various sources.

I get away with this approach in retirement by keeping a decent checking account balance around $10k or so.
Excess retirement income beyond that goes into my taxable account which is mostly stock funds, no savings account or "cash" beyond my checking account.

In year nine of retirement now, what's been happening is that excess income has been piling up nicely in that taxable account.
This is partly by design: this is where the money will come from for larger expenses, like a new car.

Due to the nearly over pandemic, my taxable account has gotten lots bigger than I might have expected, due to inability to spend $$ on travel.

Point is: none of this was forecast or projected by any spreadsheet. It's just the way it's turned out so far...

 

[cbbennet]

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"!

Would you share your unexpected expenses and income with the folks following in your footsteps? I'm 12 years behind you. Thanks

 

[JJpop]

Really

I am almost 70. What is Excel? I somehow managed very nicely without all the computer programs. Imagine that
I do know what Excel is.
Imagine how people planned and survived financially 50 years ago.
It is amazing how complex you can make something that is really straightforward.

 

[FIREarly]

Wonderful first post!

Lots of reading and was not mentally ready for how long it is. You made some solid points and also agree with the 2md post about some variables that may/may not be able to fully plan for.

You sound like you're taking this very seriously, as we all should, and believe you'll do very well!

PS Invest in very popular index funds AND, if you have a long runway to retirement, possibly Tesla! Whenever the heck they release FSD... possibly 1-10+ years.

 

[MasterBlaster]

So I'm a bit late to this thread. I haven't read all the posts so forgive me if this has been mentioned.

What the OP wants is all covered by the "Optimal Retirement Planner"
https://www.i-orp.com/Plans/extended.html . There is little need to do your own spreadsheet to model when to take SS or how to optimize Roth conversions.

Take a loop at ORP. It seeks optimal spending over your retirement given present (and known future) taxation. When I looked at ORP results in detail it was very informative.

The take-aways for my situation were that Roth conversions and SS start dates were secondary to when I quit working and nest-egg growth rates. The suggested pre-tax Roth Conversion taxes were eye-popping large numbers. And these large Roth conversion taxes were for a for an arguably modest spending benefit given modest future nest-egg growth rates.

Take a look at the ORP calculator and draw your own conclusions.

 

[Romer]

It has been 2 months since I made this first post which looks to me to be rather arrogant in retrospect. I haven't changed my opinion about having a spread sheet. Just that I came here thinking I had it mostly figured out after developing it over several years and lurking for a few weeks. Since then I must have modified and enhanced it multiple times by what I have learned here in the last few months

- Roth conversions only make sense (to me now) if I am going to save taxes. If I can use after tax funds for a couple of years to be in the 12% tax bracket then that is when it would make sense. Otherwise, I will be in the 22% bracket even with RMDs at 72. I know there are differing opinions
- Nothing now should be decided for more than a year or two. I should continue to look at my ROI to see if RMDs will increase into another bracket or if it looks like Congress wont extend the tax law, it may make sense to do more conversions before they go up
- You should look at present and future tax consequences to anything you do such as Roth conversions, hitting the Medicare limit that raises your payments, and IRA balance bs RMDs
- I didnt understand about Social Security Spousal Benefits and the different rules associated with it
- I did know about firecalc before I came here, but not about https://opensocialsecurity.com/ its a nice tool as well to provide data on most effective SS date. I also learned more about expanding the options in Firecalc to get more information such as how much can I spend vs looking at my plan
- As stated earlier, the tools should be used to inform decisions for the next year or two. Such as, it is good to plan SS at 70, but I can make the decision to take it earlier anytime before then and should do a check every year
- I needed to lay out a plan for my wife if I should pass first with single tax rates, SS death benefit and estimated spending against various ROIs to make sure she will be all right

There is a lot more I learned and want to thank the forum members for making this a very welcome place. There is a broad spectrum of members with different perspectives and the discussion back and forth has been very beneficial.

There is no right answer, it depends on everyone's unique situation, perspective and what is right for you and me. For example, I followed the thread on taking a low interest home loan. If I took a 15 year loan for $400K and invested it, at the end of 15 years I could be $240K ahead at a 3% ROI. That seems like a smart thing understanding there are always risks with investments. However, I personally have had the goal my entire Adult life to have my house paid off and that would be one of the three pillars I needed to retire (House PO, Pension start and Target investment reached). I know its emotional, but I am not going to do that. It feels great to me my house is paid off. I look around and now its all ours. Money doesn't buy a happy retirement, it just makes it easier. Since I have more then enough money to last 50 years if I lived that long, why chase something if it doesn't sit well with me. Again, I know its more emotional than analytical

My original plan said I wouldn't have had any issues, and with what I have learned here I am in better shape then I thought.

The only major change to my plan is to extend my date two months. The person I hired and have been grooming to replace me will be out in December for surgery and I want to not only cover but make sure we have a week to transition in January before I head out. I have a lot invested here in this organization I built, the team I assembled and the product we are developing. A couple months to help ensure something I helped start continues on a successful path is worth it to me, even with the political BS from Megacorp Leadership.

 

[target2019]

Romer,
I enjoyed reading your initial analysis and reading about adjustments based on other thoughts.

Another tool (yes you always need another one!) is Flexible Retirement Planner. It is an applcation that I use from time to time. I just updated my spending inputs due to a recent modification (wedding), and the result is still 100% assurance we'll be ok. Actually, the spouse will be ok for a very long time.

Good luck with your upcoming date.

 

[Out-to-Lunch]

Nice.

Seems to me that you had a pretty good handle coming in, and were able to make a few worthwhile refinements and new considerations. (Which is basically what you just said!) Good all the way around.

 

[qwerty3656]

Romer,
I enjoyed reading your initial analysis and reading about adjustments based on other thoughts.

Another tool (yes you always need another one!) is Flexible Retirement Planner. It is an applcation that I use from time to time. I just updated my spending inputs due to a recent modification (wedding), and the result is still 100% assurance we'll be ok. Actually, the spouse will be ok for a very long time.

Good luck with your upcoming date.

Is there anything you get from Flexible Retirement Planner that you don't get from Firecalc?

 

[corn18]

Is there anything you get from Flexible Retirement Planner that you don't get from Firecalc?

Flexible Retirement Planner is Monte Carlo, so a completely different model. Always nice to see a different model. But be aware that all Monte Carlo sims suffer from the dreaded tail. The worst case is worse than historical (firecalc) and the best case is better than historical. Generally, 95% in a Monte Carlo sim is about equal to 99.9% in firecalc. Generally. So I always recommend folks don’t chase the Monte Carlo tail all the way to 100%. They will have to work longer than required.

 

[camfused]

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

@Romer, I might have missed it, but it appears as though you are not using macros for your calculations. I highly recommend this.

For example, in my model, I have the following macro:

Public Function FederalOrdinaryTaxes(amount As Double)
    Dim standardDeduction As Double
    Dim salt As Double
    Dim taxableAmount As Double
    
    If amount = 0 Then
        FederalOrdinaryTaxes = 0
        Exit Function
    End If
    
    standardDeduction = 25100
    salt = 0 ' State and Local Taxes deduction.  TODO: look into
    taxableAmount = amount - standardDeduction - salt
    
    If taxableAmount < 0 Then
        FederalOrdinaryTaxes = 0
        Exit Function
    End If
    
    FederalOrdinaryTaxes = Application.WorksheetFunction.VLookup(taxableAmount, Worksheets("Taxes").Range("$A$2:$D$8"), 4, True) + (taxableAmount - Application.WorksheetFunction.VLookup(taxableAmount, Worksheets("Taxes").Range("$A$2:$D$8"), 1, True)) * Application.WorksheetFunction.VLookup(taxableAmount, Worksheets("Taxes").Range("$A$2:$D$8"), 3, True)
    
End Function

To use, I have cells like this:
=@FederalOrdinaryTaxes(D3+E3+J3+K3)

I pass in everything that is taxable for that year.

I have a fed tax table that the macro looks amounts up in, in another sheet.

My macro may or may not be perfect, but if it is wrong, I can change it in one easy place.

 

[target2019]

Romer,
I enjoyed reading your initial analysis and reading about adjustments based on other thoughts.

Another tool (yes you always need another one!) is Flexible Retirement Planner. It is an applcation that I use from time to time. I just updated my spending inputs due to a recent modification (wedding), and the result is still 100% assurance we'll be ok. Actually, the spouse will be ok for a very long time.

Good luck with your upcoming date.

Is there anything you get from Flexible Retirement Planner that you don't get from Firecalc?

TBH I've never made a comparison to find out. I haven't used Firecalc in quite a while, so I'm not up to date on any newer bells and whistles.

 

[Romer]

@Romer, I might have missed it, but it appears as though you are not using macros for your calculations. I highly recommend this.

For example, in my model, I have the following macro:

Public Function FederalOrdinaryTaxes(amount As Double)
    Dim standardDeduction As Double
    Dim salt As Double
    Dim taxableAmount As Double
    
    If amount = 0 Then
        FederalOrdinaryTaxes = 0
        Exit Function
    End If
    
    standardDeduction = 25100
    salt = 0 ' State and Local Taxes deduction.  TODO: look into
    taxableAmount = amount - standardDeduction - salt
    
    If taxableAmount < 0 Then
        FederalOrdinaryTaxes = 0
        Exit Function
    End If
    
    FederalOrdinaryTaxes = Application.WorksheetFunction.VLookup(taxableAmount, Worksheets("Taxes").Range("$A$2:$D$8"), 4, True) + (taxableAmount - Application.WorksheetFunction.VLookup(taxableAmount, Worksheets("Taxes").Range("$A$2:$D$8"), 1, True)) * Application.WorksheetFunction.VLookup(taxableAmount, Worksheets("Taxes").Range("$A$2:$D$8"), 3, True)
    
End Function

To use, I have cells like this:
=@FederalOrdinaryTaxes(D3+E3+J3+K3)

I pass in everything that is taxable for that year.

I have a fed tax table that the macro looks amounts up in, in another sheet.

My macro may or may not be perfect, but if it is wrong, I can change it in one easy place.

I have never used macros in excel. Right now I have people who do that for me at work but it does give me something else to learn for my personal future use

Thanks for the suggestion

 

[target2019]

It's not really a macro, but a function. And it can be called by a macro or simply by your formula in a cell.

 

[camfused]

^^^ technically yes, but in my shop we called everything a macro, since you get to them by clicking View Macros. Force of habit I guess.

I was pretty good at creating macros and functions when I was working. Several of them were thousands of lines long and did some pretty nifty data transformation.

 

[target2019]

^ Yeah, in Windows you can get to the VB editor with Alt + F11 and look at the modules which contain macros and functions. I'm not a programmer, but developed an Excel app in the 90's. It's been a useful tool over the years. Once in a while I'll add a short macro to my toolbar.

 

[Ronstar]

I've used my own financial spreadsheets for decades - since the Lotus 1-2-3 era. Had a macro driven Excel sheet, now an Apple numbers spreadsheet.

The Excel one I had 10-20 years ago was great - it massaged all of my financial data to give me the answer as to when I could retire. Later I found that there were non number factors involved in retirement planning. So while I thought that my spreadsheet was the only tool governing my planning, it really was just a part of the process.

Now 7.5 years into retirement, my Apple numbers spreadsheet is less robust (no macros) but gives me what I need to keep current retirement finances and projections in order.