My Own Retirement Calculator

S

SeattleRocks

Recycles dryer sheets
Joined
Mar 18, 2019
Messages
72
I searched the internet and couldn't find a spreadsheet that would fit my needs so I spent all day and built my own.

I wanted to build a spreadsheet that would include:

cost of living (linked to my actual budget spreadsheet which was cool)
emergency padding for each year
current post and pre tax investments
a standard rate of return on investment (I used a conservative 3.5%)
inflation rate (I used 3%)
tax rate for each year
Medicare kicks in at 65 (and reduces my cost of living budget)
SSN kicks in at 67 (used actual SSN projected rates)
IRA RMD kicks in at 72 (used the RMD calculator to get actual %)

I can play with the standard rate of return, inflation rate and cost of living by a click of a button. I also didn't purposely include my house or vacation house in the model nor do I have an investment properties. But I guess I could add it if I wanted in the future.

Ran the spreadsheet from my age today at 56 to age 90 just to see all of the data and it was fascinating. I even built it so I could factor in lower cost of living after age 75 because we all slow down a lot and realistically so does the spending.

I am sure a spreadsheet/calculator exists like this somewhere, but like I said it was fascinating to do that exercise and just see every single datapoint and be able to play with it in a realistic fashion. Allows me to now sleep at night that I have the plan nailed and now realize that I am completely fine on my FIRE and if I hit 90 I am still going to die with a lot of money in the bank.

Curious, what did I miss on the spreadsheet? I *think* FIRECALC does something like this already, but like I said before, I wanted to see the actual year by year data and be able to play with all of the factors to see how the model would change if inflation moved up or my cost of living went up etc.

Just thought I would throw this out to the forum for feedback.

And the greatest thing? Being FIRED I actually have the time to burn all day building this :cool:
 
I'll bet you learned a lot by doing this, and you have a better understanding of your finances and how the various factors affect you.

The downside that I've found, is that sometimes I have errors, and instead of having a whole user community to find them, I'm the only one. Just for example, I had something wrong in my 2019 spreadsheet for taxes, Roth conversions, and staying under the ACA subsidies. Luckily it was a small error (which was why I didn't notice it until comparing estimates to actuals), and I had enough buffer that it didn't cost me the ACA subsidy, but it did cut into that buffer.
 
Well with Firecalc, one is effectively calculating their own tax burden, so some of your features one would need to calculate themselves and then input into Firecalc as part of their spending.
 
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.
 
Last edited:
gbronc said:
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 a many Excel applications in the past. If nothing else, you could check out the free version to validate some of your work.
Click to expand...

I didn't know Pralana was out there. THANK YOU for this. I will dig into it and compare notes. Super excited to see where my model had issues.
 
RunningBum said:
I'll bet you learned a lot by doing this, and you have a better understanding of your finances and how the various factors affect you.

The downside that I've found, is that sometimes I have errors, and instead of having a whole user community to find them, I'm the only one. Just for example, I had something wrong in my 2019 spreadsheet for taxes, Roth conversions, and staying under the ACA subsidies. Luckily it was a small error (which was why I didn't notice it until comparing estimates to actuals), and I had enough buffer that it didn't cost me the ACA subsidy, but it did cut into that buffer.
Click to expand...

I am sure I am going to have a few errors, but I padded it very conservatively to ensure if there were errors, they weren't devastating. This exercise was more about discovery then being accurate down to the penny.

But you are correct, I learned a great deal about my situation, the effects of certain variables and the best of all a peace of mind knowing that my financial model is more then enough to take care of us thru our 90s...
 
SeattleRocks said:
I am sure I am going to have a few errors, but I padded it very conservatively to ensure if there were errors, they weren't devastating. This exercise was more about discovery then being accurate down to the penny.

But you are correct, I learned a great deal about my situation, the effects of certain variables and the best of all a peace of mind knowing that my financial model is more then enough to take care of us thru our 90s...
Click to expand...

My experience was same as yours. Building a model spreadsheet I learned where I might have problems and many more things. Also gave me a good understanding of our assets and financial situation today and down the road. Then I worked through the fidelity tool and found my spreadsheet was pretty close. I have found however that projections don’t always match what happens when life happens.
 
About those variables

At some level, I see value from being able to vary your investment returns and vary cost of living adjustments over time.

But realistically this seems to be a bit of a tail-chasing exercise. Presumably we all expect our investment portfolio to return more than the inflation rate over time. So that seems like a constant to me, not a variable for modelling purposes

That and the fact that we actually have no idea what either figure will be over time. Of course you can assume low investment returns and high price growth and scare yourself to death.

I personally find it much more useful to model tax outcomes and raw cost changes which are driven by changes in circumstances, rather than those driven by general increase in prices. But I recognize there may be some important consideration that I am missing here.
 
SeattleRocks said:
I searched the internet and couldn't find a spreadsheet that would fit my needs so I spent all day and built my own.

I wanted to build a spreadsheet that would include:

cost of living (linked to my actual budget spreadsheet which was cool)
emergency padding for each year
current post and pre tax investments
a standard rate of return on investment (I used a conservative 3.5%)
inflation rate (I used 3%)
tax rate for each year
Medicare kicks in at 65 (and reduces my cost of living budget)
SSN kicks in at 67 (used actual SSN projected rates)
IRA RMD kicks in at 72 (used the RMD calculator to get actual %)

I can play with the standard rate of return, inflation rate and cost of living by a click of a button. I also didn't purposely include my house or vacation house in the model nor do I have an investment properties. But I guess I could add it if I wanted in the future.

Ran the spreadsheet from my age today at 56 to age 90 just to see all of the data and it was fascinating. I even built it so I could factor in lower cost of living after age 75 because we all slow down a lot and realistically so does the spending.

I am sure a spreadsheet/calculator exists like this somewhere, but like I said it was fascinating to do that exercise and just see every single datapoint and be able to play with it in a realistic fashion. Allows me to now sleep at night that I have the plan nailed and now realize that I am completely fine on my FIRE and if I hit 90 I am still going to die with a lot of money in the bank.

Curious, what did I miss on the spreadsheet? I *think* FIRECALC does something like this already, but like I said before, I wanted to see the actual year by year data and be able to play with all of the factors to see how the model would change if inflation moved up or my cost of living went up etc.

Just thought I would throw this out to the forum for feedback.

And the greatest thing? Being FIRED I actually have the time to burn all day building this :cool:
Click to expand...

Any survivors you should plan for?
 
jimbee said:
The price looks too good to be true, but this eBay link for MS Office may be similar to ShokWaveRider's suggestion. $6.99 for MS Office Pro Plus 2019.

https://www.ebay.com/itm/Microsoft-MS-Office-Pro-Plus-2019-GENUINE-Lifetime-Retail-Fast-Delivery/362869419993?hash=item547cb3efd9:g:1eAAAOSwga9eDfnE
Click to expand...

This was interesting. From the eBay link:

eBay Policy States:
"OEM, bundled, or recovery software may be listed only if it is included along with the original hardware, such as the motherboard, hard drive, or computer sold with the OEM software." - Therefore, this sale includes the original broken beyond economical repair computer and motherboard.
Click to expand...
 
I have had a similar type excel spreadsheet for years. I have also compared it's results to various models over time and it's usually in the ball park. What I like about it most is it helps me identify areas of my financial streams that I do not fully understand. Leading me to more research on a particular subject. Every time I learn something new, I check to make sure my spreadsheet is correct and modify as needed.
 
I created from scratch my own retirement planning spreadsheet that I have been using for a few years as it has everything I want and need with everything laid out the way I want it.
 
SeattleRocks said:
cost of living (linked to my actual budget spreadsheet which was cool)
emergency padding for each year
current post and pre tax investments
a standard rate of return on investment (I used a conservative 3.5%)
inflation rate (I used 3%)
tax rate for each year
Medicare kicks in at 65 (and reduces my cost of living budget)
SSN kicks in at 67 (used actual SSN projected rates)
IRA RMD kicks in at 72 (used the RMD calculator to get actual %)

I can play with the standard rate of return, inflation rate and cost of living by a click of a button. I also didn't purposely include my house or vacation house in the model nor do I have an investment properties. But I guess I could add it if I wanted in the future.
./.
Curious, what did I miss on the spreadsheet? I *think* FIRECALC does something like this already, but like I said before, I wanted to see the actual year by year data and be able to play with all of the factors to see how the model would change if inflation moved up or my cost of living went up etc.
Click to expand...
A well crafted spreadsheet does give one a good, detailed view of the factors affecting retirement finances. One thing it doesn’t capture or reflect is asset market volatity. While an average rate of return might be 3.5%, actual returns will be higher some years and lower others. How the portfolio is impacted by withdrawals in down years affects portfolio survival rate. This is very difficult to model on the spreadsheet and one of the important features of FIRECalc.
 
MichaelB said:
A well crafted spreadsheet does give one a good, detailed view of the factors affecting retirement finances. One thing it doesn’t capture or reflect is asset market volatity. While an average rate of return might be 3.5%, actual returns will be higher some years and lower others. How the portfolio is impacted by withdrawals in down years affects portfolio survival rate. This is very difficult to model on the spreadsheet and one of the important features of FIRECalc.
Click to expand...

Totally agree. That's why I run many calculators including FIRECalc. For me it wasn't a perfect science I was after. Transparently, I found it was a really good exercise to go through to just learn and do some modeling and through it I became a lot more educated.

Things like massive downturns, negative interest rates, bond defaults, etc. etc. just aren't predictable.

For me, I am a worrier and my wife says I over think everything and FIRE for me was a complete psychological battle between the heart and mind. My career was my identity yet I know it was killing me with stress. Going through this spreadsheet allowed me to come to peace that we are going to be OK and have enough and now I at least have a plan.

I would encourage all people considering FIRE to go through this exercise and also utilize FIRECalc and other tools to sound up the thesis.
 
SeattleRocks said:
IRA RMD kicks in at 72 (used the RMD calculator to get actual %)

[snip]

Curious, what did I miss on the spreadsheet?
Click to expand...

I'm not sure what you meant by the parenthetical phrase, but your RMD is based on the prior year IRA ending balance and the divisor from the RMD table. Your actual RMD will therefore be dependent on what you predict your IRA balance to be, which will in turn depend on your rate of return assumption.

Since you asked, that's one of the things that you may have not coded correctly in your spreadsheet.

You're probably aware that the IRS may be publishing an updated RMD table sometime in the next few years which will probably have the effect of lowering the RMD amount (by increasing the divisors) to reflect the fact that people are living longer these days.
 
SO for me... my calculator rows are counted by my age all the way up to 100.

Then, the columns (variables) I have are:

87 Columns Wide (this data feeds into a Progress Burndown and I have a seperate spreadsheet that calculates my Asset Allocation, as seperate spreadsheet for my main residence ammortization, a seperate spreadsheet for our budget, a seperate spreadsheet for a running balance of all our accounts, a seperate spreadsheet for SSA records and calculations/accounting, a seperate spreadsheet for Taxes/year and a seperate spreadsheet to track all of our credit card rewards and apr, min spend dates, 0% ending dates etc)

BaseYr -(ie 2020)
Age -My current age
Notes -I leave little notes like the day I hit 1mil, ER date, SWR start date, RMD, SS, kids college, different tax events
BaseYrAmt -(Our Gross Income that year)
IncmGn -(The factor for our raises each year, I assume all future factors are 1 with no raise in future assuming worst case)
AnnualCOL -(The annual Cost of living $ amount based on
IRAGn% -% DW and I's combined IRAs increased/ will increase. (We assume 6% future returns for roth)
RothGn% -% DW and I's combined Roth increased/ will increase. (We assume 6% future returns for roth)
Inflation -Inflation Factor (We assume 1.03 future inflation rate)
COL+Inflation -(The amount we would need NET for Cost of Living after inflation factored in)
IRA$ -Current/future/past combined IRA $ amount
RothIRA$ -Current/future/past combined Roth$ amount
Broker$ -Current/future/past broker Roth$ amount
BckDrRoth$ -Current/future/past Roth$ amount (auto calculates future withdrawal $ amount for me based off some formula's I setup)
Sbj2RMD -the % of our total portfolio that will be subject to RMD over time
InvestableAssets -The amount of money we potentially COULD invest but might not necesarrily
Gift -Any gift$ we might recive currently 0 :)
CurrentAsset -Our combined current assets (IRAs, Roth, Broker)
TotInvstbl -The amount of money I would/could have if I had invested the $ from InvestableAssets
TaxRt -Our current federal tax rate factor
FtrTxRt -Our future fed tax rate factor auto calculated based off other variables
StTx -State tax$
Txbl -Essentially AGI - any tax credits
TaxPd -Amount of federal tax actually paid
AfterRMD -$Total Asset balance after RMD has been taken (in the future we are still accumulating)
Equity - This one is obvious Our current real estate valuations less outstanding loan balance
NetWorth -All wrapped up, includes IRA, Roth and Real Estate...notice how this is sort of off to the right a ways, I don't really care abot NW but it is still important to track.
ActualIRA -$contributions we made to Traditional IRA
Actual401k -$contributions we made to 401k
ActualRoth -$contributions we made to Roth IRA
ActualBrkr -$contributions we made to Broker
Actl529 -$contributions we made to 529
TotActualInv - The sum of all our contributions
Gn+Cntbtn - The $increase/decrease from previous year including the contributions
Chg%PrevYr - The %increase/decrease from previous year
Net$Gn - The $increase/decrease from previous year LESS the contributions
RMD$ - The $ amount of our RMD each year
RMDw.o cnv - The amount of RMD I would have to take had I never converted to Roth (a fun reminder of tax savings)
RMDfctr -The IRS Uniform Lifetime table's factors used to calc RMD
RMD% -%of RMD vs total portfolio value
TaxSv -The $tax I save by doing roth conversions (again fun reminder of double benefit to conversions)
SWR$ -My SWR $amount that I can take each year based on SWR% factor below
SWR% -SWR % factor (currently set at 3.5% from ER age 50 until SS at 70)
AWR% -The ACTUAL withdraw SWR divided into CurrentAsset
529-1 - Our childs 529 $amount
529-1Cnt - 529 Contributions for kid1
529-2 - Our OTHER childs 529 $amount
529-2Cntr - 529 Contributions for kid1
Tuition$ - The $ we expect to pay in tuition each year if kids go out of state
TuitionState - The $ we expect to pay in tuition each year if kids go in state
AnnualSSN62 - The $ I can expect for SS if we file early at 62
SSN67 - The $ I can expect for SS if we file early at FRA
SSN70 - The $ I can expect for SS if we delay until 70
AnnualSSNSpouse62 -The $ DW can expect for SS if we file early at 62
SSN67Spouse -The $ DW can expect for SS if we file early at FRA
SSN70Spouse - The $ DW can expect for SS if we delay until 70
BothSSN70 - Our total SS $ annually
SSNaftrTax - The amount of SS we NET post-tax
EndSalaryPension - Factor needed to calc pension
SalaryIncr% - the % of just MY salary increasing used to calc pension
Pension1Starts - The date my pension check *RAISE begins
IRA-RMD -$ amount I need to pull from IRA after SS to cover my RMD
ExtraPrinciple$ -$ amount of extra principle I put towards loan that year
Property1Aprecdt $Value of main residence after appreciation
Property1Equity $amount of equity I have (appreciation - outstanding loan balance)
ActualAmmrtzn The actual $loan balance each year (to compare against my plan)
Property1Amrtztn16 - Amrt sched for 16 year
Property1Amrtztn18 - Amrt sched for 18 year
Property1Amrtztn20 - Amrt sched for 20 year
Property1Amrtztn30 - Amrt sched for 30 year ( I like to compare interst savings but have a 15yr)
Prop2Bal-18yr
Prop2-PITI-18
Prop2CashFlo
Prop2Prin
Prop2Int
Prop2Equity
Property2Aprectd
ApprecFactor
Property2Rent
RentIncrease -$amount of rent increased that year
Prop2Expns
Prop2Depr%
Property2Depr$
Inhrtnc$ $ of inheritence I might receive
IRMD_Fctr $the IRS factor for inherited IRA
IRMD $amount for the inherited Req Min Distro
IIRA_Tx$ $amount of tax I will pay on that inherited RMD


Did I miss anything?
 
When I was putting together my ER plan back in 2006-2008, I created a spreadsheet which projected my expenses and investment income from my planned ER age (45) only to age 60. It was these years I was most concerned about (i.e. getting to age 60 intact) because I would not yet have access to SS, my frozen company pension, or have unfettered access to what would become my (rollover) IRA. I would be living only from my taxable account.


I split my expenses, calculated in more detail elsewhere, into 2 parts: medical and non-medical. I did this so I could assign separate inflation rates for each. Medical I tried inflation rates as high as 10% while keeping non-medical at 3%.


On the portfolio side, I created columns for each of my 4 main mutual funds: a stock fund, 2 existing bond funds, and a third bond fund I would soon acquire after I cashed out the company stock I had in my 401k upon leaving the company and retiring. Instead of using rates of return, I broke it down into the number of shares I owned or would own and the projected cents per share of dividends the bond funds would generate. I built into each fund the option of reinvesting or taking as cash the dividends and potential cap gain distributions (from the stock fund) as well as reinvesting any excess dividends I didn't end up spending.


Doing all of things on the investment side gave me a good idea of how my portfolio would behave from age 45-60. The spending side also looked good, although this was before the ACA and its subsidies which have greatly changed the figures.


I still use the spreadsheet but it's more to replace projected years with actual data to see how things are shaping up. At this point, only 4 years from 60, I am no longer concerned about getting to age 60 intact. I will.
 
SeattleRocks said:
And the greatest thing? Being FIRED I actually have the time to burn all day building this :cool:
Click to expand...

I did all my planning on company time ;)

I had never heard of FireCalc, so I just invented it as I went. Then when I found this forum, and FireCalc, I plugged in my numbers and it pretty much agreed with my results.
 
When I retired, I had not heard of FireCalc. So we just winged it on our own.

We knew how much we were spending to support ourselves, and my pension exceeds that amount. So we figured that we could afford to retire.

We are accustomed to itemizing our tax filing every year. and I have not paid into Income Taxation since 1983, so we did not consider taxes in our plans.
 
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.
 
You must log in or register to reply here.

Similar threads

E
Est tax calculator?
Replies
8
Views
480
big-papa
big-papa
J
FIRE Day Model After 7.5 years
Replies
7
Views
1K
joesxm3
J
The Cosmic Avenger
Compound interest calculator for LTCI with inflation protection
Replies
5
Views
713
Finance Dave
Finance Dave
K
tax rate estimates
2
Replies
25
Views
2K
kens450r
K
J
QDCG Worksheet vs. Personal Spreadsheet
Replies
5
Views
679
SecondCor521
SecondCor521

Latest posts

Back
Top Bottom