My Own Retirement Calculator

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

Quote:

Originally Posted by 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?

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]

Quote:

Originally Posted by 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?

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]

Quote:

Originally Posted by 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?

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]

Quote:

Originally Posted by 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.

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]

Quote:

Originally Posted by 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.

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.

[Dtail]

Quote:

Originally Posted by tophermiller 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.

Do you match up your numbers vs. the free calculators available to you for additional comfort?

[tofer]

Quote:

Originally Posted by Dtail Do you match up your numbers vs. the free calculators available to you for additional comfort?

I do compare to FIRECalc, yes, and the numbers are pretty close. While FIRECalc is based on actual historical returns, mine is based on the actual average and standard deviations of historical periods, but I can pick different periods to see their effect. For example, I can pick 2000-2020 in order to model two early-FIRE recessions for sequence-of-returns risk. Its also based on my actual portfolio. Portfolio visualizer is awesome.

[Dtail]

Quote:

Originally Posted by tophermiller I do compare to FIRECalc, yes, and the numbers are pretty close. While FIRECalc is based on actual historical returns, mine is based on the actual average and standard deviations of historical periods, but I can pick different periods to see their effect. For example, I can pick 2000-2020 in order to model two early-FIRE recessions for sequence-of-returns risk. Its also based on my actual portfolio. Portfolio visualizer is awesome.

Okay.
It sounds like the Flexible Retirement calculator uses some of your concepts, plus you can also match up against Fidelity from the Monte Carlo standpoint.

[ghh101]

I so need these spreadsheets!!!

[davidrn]

Where do I find the spreadsheet?
TIA
Dave

[isisdave]

OpenOffice is a free alternative to Excel. Google Sheets may be able to load and convert an excel spreadsheet; it depends on how complex it is.

I made a similar spreadsheet about 8 years ago and soon converted it to Sheets. It's been pretty accurate with real life. One thing is I can see more clearly the effect of big transactions, like possibly selling the house or one other property. Also, we have big travel budget for the first 6 years of retirement, and can see how that effects later numbers if we reduce, expand, or extend it.

[retiredtuna1]

I've read in this posting that a number of individuals have developed their own calculators. Is anyone willing to share the basic shell of his or her model (minus personal data of course)?

[kgtest]

Quote:

Originally Posted by retiredtuna1 I've read in this posting that a number of individuals have developed their own calculators. Is anyone willing to share the basic shell of his or her model (minus personal data of course)?

This is where I think it's difficult. I've had people ask me to SHARE mine with them...first, mine took literally years to build. I was able to replicate it MYSELF since I wrote the functions within the spreadsheet I am very intimate with the data and workflows and plus its a natural talent of mine to manage data...

But I would be willing to give it a shot... I use Google Sheets... and it all starts with the basics...

The hardest part I've found is consilidating one's assets to a level where SHEETS gets realtime data. And even then, I can't really totally rely on totals until like 8pm CST each day.

All of that I learned in what I call "battle scars" by just trying and doing... I remember the very first day I started my SHEET... I opened a blank one, I called it Budget and I was like ok now what...

Then I thought I want to know the current price of each of my assets. That lead me to basically rolling over and re-allocating which is something this forum was telling me to do everyday.

Once I had all of my assets =GOOGLEFINANCE(TICKER,"price")
example
=GOOGLEFINANCE(AAPL,"price")
to take this one step further... I call the ticker from another cell... so for me it looks like this:

=GOOGLEFINANCE(A28,"price")

THEN!

I have a column that has all of my current shares for each. This part is tricky, so dividends get re-invested, and we are still contributing to 401ks weekly so this number changes...but that is a problem since I don't want to login to like 7 different broker website platforms and dig through to find each of my ticket's curent share price.

I built a screen scraping script that would login to all 7, copy the # of shares, and when needed the currentPrice when Google Sheets couldn't retrieve that data point.

This worked for a while, until MFA became a thing. Now it is a bit harder, but I have since consolidated so really, I only need to login maybe once a quarter and update my # of shares for each asset manually.

So you see, I am getting into maintenance mode already.

Honestly, IM me and maybe we can work something out. I am here to teach.

[cnocmmz]

Did you post the spreadsheet you built for the group to review??