My Own Retirement Calculator

[RunningBum]

Perhaps I was too wordy, but I am still trying to understand why one would model investment returns seperately from cost of living aka inflation adjustment?

For example, let's say I am assuming my portfolio will have a real (inflation adjusted) return of 6% over time (9 pct nominal and 3% inflation. Is it productive to model your cost inputs as growing at 3% and investment returns at 9%?

I do not see a reason to do this unless you just want to play with some unusual relationships between investment returns and inflation, which I would view as not predictable with any accuracy.

If nothing else, it documents the assumptions you are making. Those are both pretty important values, so maybe you want to clearly see that you modeled this at 9% return and 3% inflation. You might also have other runs of the spreadsheet with different assumptions.

Also, and I'm not clear whether the math works this way and don't feel like figuring it out this evening, but the return works on your entire portfolio, while expenses are a yearly amount. Does 4% investment growth with 2% inflation work the same as 10% growth with 8% inflation? At different withdrawal rates?

 

[Montecfo]

If nothing else, it documents the assumptions you are making. Those are both pretty important values, so maybe you want to clearly see that you modeled this at 9% return and 3% inflation. You might also have other runs of the spreadsheet with different assumptions.

Also, and I'm not clear whether the math works this way and don't feel like figuring it out this evening, but the return works on your entire portfolio, while expenses are a yearly amount. Does 4% investment growth with 2% inflation work the same as 10% growth with 8% inflation? At different withdrawal rates?

You are correct. But using the real return and not inflation-adjusting your expenses is thus conservative. You would show larger assets over if you model nominal returns and inflated costs (assuming investment returns exceed inflation).

But you will not model outliving your money in either case, unless you assume investment returns are less than inflation for an extended period. And I am not sure why anyone would model that in a static spreadsheet. To me that is more the purpose of firecalc and Monte Carlo.

But I have now convinced myself that modelling investment returns nominally and expenses with inflation is more accurate, though it does not for my purposes (do I have enough money?) provide more information.

I definitely clearly document investment return assumptions.

 

[karen1972]

You might want to look also at Flexible Retirement Planner (there is a free version) and see if you find anything in there you might not have thought of. That is one of the only calculators I found where I could do a lot of start/stop income/expense items and fine tune inflation/tax rates, etc. I have a lot of items listed under additional inputs, including that I assume my health care costs extra $6k/yr+inflation until I hit 65 and I can list my mortgage separate which has zero inflation, and I have lines for ROTH conversions, etc.

There is a Detailed View tab that shows the breakdown by year, then you can also turn on "show more detail" and it gives more detail of taxes owed, etc.

 

[SecondCor521]

You are correct. But using the real return and not inflation-adjusting your expenses is thus conservative. You would show larger assets over if you model nominal returns and inflated costs (assuming investment returns exceed inflation).

But you will not model outliving your money in either case, unless you assume investment returns are less than inflation for an extended period. And I am not sure why anyone would model that in a static spreadsheet. To me that is more the purpose of firecalc and Monte Carlo.

But I have now convinced myself that modelling investment returns nominally and expenses with inflation is more accurate, though it does not for my purposes (do I have enough money?) provide more information.

I definitely clearly document investment return assumptions.

I have a spreadsheet which models my RMD situation out into the future some 35 years or so.

In it, I chose to set a rate of change on my investments, on my Social Security, on the tax brackets, and on the IRMAA rates and brackets.

Since there were at least three different rates involved, I chose to do it all in nominal terms. I was concerned that using relative real rates might miss some sort of interaction between all those numbers.

My main FIRE spreadsheet is also all in nominal terms, for similar reasons.

 

[SeattleRocks]

Just want to say how cool this forum is and how incredibly helpful. There are some absolute experts on this board that have helped me immensely.

I originally posted this thread to help those investigating FIRE to see what I did and be encouraged or give them something to think about. But I quickly see that there are several things I didn't think through and this board was great to help tune what I put together.

Thanks to all - amazing help!

 

[MichaelB]

Also, and I'm not clear whether the math works this way and don't feel like figuring it out this evening, but the return works on your entire portfolio, while expenses are a yearly amount. Does 4% investment growth with 2% inflation work the same as 10% growth with 8% inflation? At different withdrawal rates?

A higher nominal rate of growth will lead to higher relative taxes on assets that are not price-indexed, such as capital gains. It will also lead to higher local taxes, such as property tax. In some cases it will lower the value of deductions.
To keep the same net real return after tax, a higher nominal rate of return requires a greater outperformance compared with a lower inflation / return.

 

[rodi]

I've got a few homegrown spreadsheets that are similar.
I've also run most of the retirement calculators out there.

I found, as I was getting the confidence to retire, that different calculators uncovered different areas of risk... My three favorite retirement calculators are (in order): firecalc, quicken lifetime planner, and fidelity retirement planner.

Firecalc gives historical stock/bond/inflation cycles... so it uncovers issues if you assume too low of inflation, or too high (or too long) of returns. It allows income changes like social security and pensions coming online. It allows for one time spending hits (kids weddings? trip of a lifetime? buying a dream home?)

Quicken lifetime planner allows for spending for college for kids (a factor for our household because we were late to the parenting game) and the things listed above - but does not do a historical back testing... uses a fixed return/inflation.

Fidelity was the most pessimistic - but you can inflate different things at different rates - for example healthcare can be inflated at a higher rate. But you can only do this if you drill down into the budget, vs using a lump spending amount.

An advantage of a spreadsheet is you can vary things like tax rates... because those can and do change in big ways.

After 5 1/2 years of retirement I'm pretty confident I'm ok - but I still run the various calculators /spreadsheets around 1x year... just to make sure I'm still on track.

 

[Dtail]

I still run about 5 calculators multiple times a year.
Although we are at 100% on each calculator where applicable, our maximum spending runs only around 10% above our actual spending, so SORR is still a real possibility at 2.5 years of retirement.

 

[Spanky]

Pralana looks intersting but we only have Google sheets. Any suggestions other than buy excel?

Try libreoffice: https://www.libreoffice.org/

Free Office software

 

[pb4uski]

For my base retirement planning I used Quicken Lifetime Planner.... it is pretty comprehensive, easy-to-use and intuitive and covers a lot of bases. Just before I decided to retire I supplemented it with every free retirement planner on the planet (or at least so it seemed)... including FIRECalc and all the usual suspects... mainly to overlay a stochastic stress test over QLP since QLP is a deterministic planner. I also had my own home-grown Excel spreadsheet with conservative assumptions. Everything gave me various versions of a green light.... so off I went.

I'm pleased to report that our current nestegg is 129% of what I predicted... and that is after some huge withdrawals for a winter condo paid for in cash and replacing our one-car garage with a two-car garage with bonus loft/"she cave".... of course, market returns have been 187% of what I assumed so that helped big time.... thank you Mr. Market.

These days I'm less concerned with portfolio survivability given where we are and the flexibility in our spending. I'm more focused to optimizing taxes since that is the biggest lever that I can control... and that is more toward survivors and heirs.

 

[Big_Hitter]

how are you modeling medical expenses and taxes in retirement?

 

[pb4uski]

^^^ In my Excel modeling health insurance were separate line items (one for pre-medicare and the other for Medicare) and each had separate and different inflation (5%) from the catch-all of living expenses (3%). However, pre-Medicare health insurance has ended up being much lower than I expected when I retired... about 40% lower. OTOH, it looks like Medicare (includes Part B, Part D and Medigap) will be a lot more than I expected. It all evens out.

I included taxes in living expenses in my Excel model, but in QLP it is a separate input... and the 15% that I used has been on the high side.

FWIW, if you use QLP you could define medical separately as an expense adjustment and give it its own inflation rate.

 

[ImThinkin2019]

Yes, great way to learn and fill the ER time, but I've found Pralana, built on Excel, covered all these requirements and more. Far more granular than firecalc. After using it, I quickly realized that as much as I'd like to build my own Excel spreadsheet to handle my situation, it would be quite an effort to catch up to Pralana's features and I've built many Excel applications in the past. If nothing else, you could check out the free version to validate some of your work.

I ended up buying Pralana (and Excel to run it). It is well worth the purchase price. It models taxes, optimizes social security and Roth conversions, and has other sensitivity analyses built in. All of these functionalities would have taken us hours upon hours to build ourselves. Highly recommend it.

 

[Brett_Cameron]

I built a tax estimator into my spreadsheet to include future changes such as dependent changes, standard dedcution changes, various deductible item changes, etc. I update each year as tax rates and or brackets change. I have the tax forms modelled.

I model the withdrawals from qualified retirement plans and use FIRECALC every few years to see what I can see.

Like someone said, I learned a lot about numbers, which as we all know are HARD! LOL

 

[NotYourAverageJones]

Question: when considering inflation in your projections, do you take into account that not everything inflates at the same rate? Some expenses are growing faster the historical 3.25% average rate ROI (rate of inflation) and way more than the Feds target rate of 2%. One could argue that in order to get an accurate look at the real effect of inflation on future expenses, you have to tweak your inflation rates for each of the recurring/necessary expenses you will maintain in retirement, until death. Does anyone do this or do most just use the historical 3.25% average ROI?

 

[pb4uski]

Mostly the latter since the "basket" includes the higher inflationary items, but not necessarily in the same proportion as in your life. Also, it seems that many times that more granular models are less accurate and the added complexity isn't worth the benefit.

All of that said, in my Excel model I did carve out health insurance and college expenses for separate inflation. As it turns out DS has yet to go to college and my base assumption for health insurance was so far off (too high) that the separatation for inflation was inconsequential.

 

[scrabbler1]

Question: when considering inflation in your projections, do you take into account that not everything inflates at the same rate? Some expenses are growing faster the historical 3.25% average rate ROI (rate of inflation) and way more than the Feds target rate of 2%. One could argue that in order to get an accurate look at the real effect of inflation on future expenses, you have to tweak your inflation rates for each of the recurring/necessary expenses you will maintain in retirement, until death. Does anyone do this or do most just use the historical 3.25% average ROI?

I have separate columns in my big-picture spreadsheet for medical and non-medical expenses so I can assign a separate inflation rate for each one. I use 3% for non-medical and 10% for medical. In my 11 years of ER, Medical has been the most volatile expense for several reasons: changing health plans pre-ACA, an expensive hospital visit, ACA subsidies, big and small, sometimes non-existent. Still, HI premiums have on the whole risen much more quickly than my other expenses which have been fairly flat (so 3% might be a little high).

Income taxes have also been somewhat volatile but only because I have had some income spikes some years. That doesn't worry me in the big picture because I know I will always have the money to pay those added taxes from the added income which was its cause.

 

[SecondCor521]

Question: when considering inflation in your projections, do you take into account that not everything inflates at the same rate? Some expenses are growing faster the historical 3.25% average rate ROI (rate of inflation) and way more than the Feds target rate of 2%. One could argue that in order to get an accurate look at the real effect of inflation on future expenses, you have to tweak your inflation rates for each of the recurring/necessary expenses you will maintain in retirement, until death. Does anyone do this or do most just use the historical 3.25% average ROI?

I use 6% as the education inflation rate and 3% for everything else, including medical.

Historically my actual measured personal inflation rate over the past 14 years has been less than 1% annually.

My policy on assumptions is to use the longest term historical average from what I consider reliable sources.

The education inflation rate (as measured by the College Board's annual report in the number I use for budgeting) has been under 6% every year since I have started my college planning (probably the last 8-10 years).

I couldn't tell you why I think my medical inflation rate is not going to be an issue. I can see why it might, but so far it hasn't. I also have lots of other slack elsewhere. :shrug:

 

[NotYourAverageJones]

Thanks for your input! pb4uski, SecondCor521 & scrabbler1

 

[Brett_Cameron]

Question: when considering inflation in your projections, do you take into account that not everything inflates at the same rate? Some expenses are growing faster the historical 3.25% average rate ROI (rate of inflation) and way more than the Feds target rate of 2%. One could argue that in order to get an accurate look at the real effect of inflation on future expenses, you have to tweak your inflation rates for each of the recurring/necessary expenses you will maintain in retirement, until death. Does anyone do this or do most just use the historical 3.25% average ROI?

Yes, Each expense item is inflated at its own rate, usually a multiple of core inflation or from other best guess.

 

[dtbach]

Isn't this akin to measuring with a micrometer, marking with chalk and cutting with a chainsaw? All these parameters are guessing to a degree.


Are you going to test how accurate this all is a year out, 2 years out, etc.


Just curious.

 

[Brett_Cameron]

Isn't this akin to measuring with a micrometer, marking with chalk and cutting with a chainsaw? All these parameters are guessing to a degree.


Are you going to test how accurate this all is a year out, 2 years out, etc.


Just curious.

Yep, you are correct! It is fun to do and only marginally useful. And no I never try to compare future reality to past guesses.

 

[KCGeezer]

Oh I compare current amount to old projections. Seeing that I’m actually ahead of where I anticipated to be at this point its a cheap thrill. That and I agreed with myself when I was retiring that if it gets ahead/behind of projections by x % then I reconsider our budget.

 

[Montecfo]

Isn't this akin to measuring with a micrometer, marking with chalk and cutting with a chainsaw? All these parameters are guessing to a degree.


Are you going to test how accurate this all is a year out, 2 years out, etc.


Just curious.

This is why I just use an estimated real rate of return and call it a day, at least to this point.

No plans to back test my assumption, but will adjust as I go!

But I have no problem with very granular estimates if they enhance confidence.

 

[tofer]

I've been building my own spreadsheet similar to yours for many years (and I'm not FIRE'd yet). I'm currently on version 32 as I've added variables to the calculation along the way. By far and away, the biggest thing I've learned is that a single forecast based on a constant rate of return is not useful at all. FIRE is not a binary "yes" or "no" based on constants. It is about the "chances of success" given so many variables that you have to guess, the hardest of which being your investment volatility. Therefore, I use an average ROI and a standard deviation for ROI, for which I obtain my guesses using portfoliovisualizer.com. Then, the average and standard deviation generate random ROI values for each future year and I run a Monte Carlo style analysis of 5000 trials to come up with my chance of FIRE success. I'll be comfortable when it reaches about 75%, as I am prepared to adjust future spending when necessary.