FIRECALC : Spreadsheet edition

[navydavey]

Hey all,

So I've been working on my spreadsheet for the past four years and its really starting to bloom into something nice. However, I would like to have a FIRECALC-like sheet where I can enter variables like rate of return, monthly investments, social security payments, etc and have it yield an age at which I should achieve retirement success (probably defined as a >= $0 remaining balance).

I'll be deployed for a while plus I just like spreadsheets so that is why this would be valuable for me.

Does anyone know if such a thing already exists and/or would someone be willing to contribute to it? Thanks in advance!

 

[ERD50]

Check this thread for a ref to simba's spreadsheet. I have not played with it, so I don't know if it is what you are looking for or not.

http://www.early-retirement.org/for...al-data-back-to-1977-a-78452.html#post1626499

Although I think there is near zero value to 'enter variables like rate of return'. These things vary over time. The historical calculators like FireCALC do a much better job of showing the effect of this. Any 'rate of return' you enter will be wrong.

-ERD50

 

[turbo89]

Check this thread for a ref to simba's spreadsheet. I have not played with it, so I don't know if it is what you are looking for or not.

http://www.early-retirement.org/for...al-data-back-to-1977-a-78452.html#post1626499

Although I think there is near zero value to 'enter variables like rate of return'. These things vary over time. The historical calculators like FireCALC do a much better job of showing the effect of this. Any 'rate of return' you enter will be wrong.

-ERD50

I followed that string for a bit an couldn't find the excel sheet. Is there another link, or do you have it and could share?

 

[ERD50]

I followed that string for a bit an couldn't find the excel sheet. Is there another link, or do you have it and could share?

No, sorry, I just remembered seeing that reference recently - that's all I got.

-ERD50

 

[big-papa]

Start with a Google of "Simba's spreadsheet". That will get you to the bogleheads forum where it's discussed. As for the actual spreadsheet, it's located here:
https://drive.google.com/file/d/0B6rEnGbxebTBOWRfMUpDOC1jbWc/view?pli=1

If you use excel, click on the down arrow at the top center of the page to download.

Big-Papa


Sent from my iPad using Early Retirement Forum

 

[turbo89]

Thanks for sharing. That spreadsheet is much more comprehensive than I expected.

 

[big-papa]

No problem. As I mentioned in the other thread, many of the return series are cobbled together from multiple sources for periods before which the representative mutual funds existed. I also think that the short term bond returns are incorrect and that they used a short term international series instead of a U.S. Govt series. Also tips didn't exist as far back as 1972. A reasonable guess can be made based on regular U.S. bonds and some measure of expected inflation. See the other thread for another source of tips returns besides what's in the spreadsheet - it's fairly easy to replace a return sequence with another.

One thing I like in the spreadsheet is the comparison against fairly well known static portfolios as well as the long term correlations between different asset classes.

As the original author of the spreadsheet has mentioned, this is only a tool - it's not really intended to be used to fine tune a portfolio to the nth degree. As you can see on the tab with well-known portfolios, all roads lead to Dublin - well most roads do, anyway.


Sent from my iPad using Early Retirement Forum

 

[Midpack]

It's most definitely doable, but it looks easier to recreate FIRECALC in spreadsheet form that it actually is IME. Add Soc Sec and federal tax what ifs, with all the dependent variables it gets orders of magnitude more difficult (I've tried that too).

I also played with the bogleheads spreadsheet, but IMO you have to take the (considerable) time to fully understand his/her assumptions to trust the result.

Best of luck...

 

[big-papa]

Yep - I've attempted the recreation as well but it truly is a lot of work. The bogleheads spreadsheet is pretty good and, as mentioned before, I think some of the historic (pre-mutual fund) data isn't quite right. However, it isn't that tough to find other sources for most of those items and do some cutting-and-pasting into the spreadsheet, which is something I've done. It's still a decent framework.

Also remember the bogleheads spreadsheet is really only set up for the accumulation phase.

 

[Sand101]

Hey all,

So I've been working on my spreadsheet for the past four years and its really starting to bloom into something nice. However, I would like to have a FIRECALC-like sheet where I can enter variables like rate of return, monthly investments, social security payments, etc and have it yield an age at which I should achieve retirement success (probably defined as a >= $0 remaining balance).

I'll be deployed for a while plus I just like spreadsheets so that is why this would be valuable for me.

Does anyone know if such a thing already exists and/or would someone be willing to contribute to it? Thanks in advance!

I have worked on and finally have a file that works quite well that does this. I wanted something that would handle a decent sized inherited IRA as I was faced with that (crappy way to gain wealth) and nothing out there really handles it. It is a simple excel file - it explicitly handles SS, taxable accounts, tax deferred accounts, taxes, inherited IRAs, 4 types of withdrawal schemes, inflation assumptions, CPI assumptions, etc. Two output types - an assumed rate of return (deterministic model) and a historical based rate of return. It tends to be a bit more conservative than Firecalc as I think it handles taxes a bit better.

Link here. I preentered it with some numbers to show the outputs.

Graphics give a very nice view of accounts over time as they get consumed. along with SS influxes, expenses, etc. Hopefully useful to some folks out there.

 

[Turn_the_Page]

I did something in Google Sheets a few years ago. I couldn't figure out how to get it to run through all the years (or lost interest before I got there ). I was wanting to compare withdrawal strategies. I ended up with a entry cell for starting year. I just left it at 1929 for all my comparisons. If you can retire then, you can make it just about any time.

The spreadsheet really get complicated when you start adding things like SS, taxes, etc. I really gained a lot of respect for FIRECalc as a result.

 

[marko]

The day 30 years ago when Lotus 123 spreadsheets first came out turned out to be the worst day of my life. I ended up spending most of my young years building sheets re-creating models that already existed; tide charts, water consumption, etc. only to find that "close enough was good enough"

I view FireCalc, i-Orp and all the others as a guide providing a sort of 'windage'; a ballpark number; a direction more than a single, take-it-to-the-bank number. None of the existing calculators claim to deliver a 100% final number.

Respectfully, I hope your work is more within an academic exercise than trying to nail down a more accurate result. IMHO there's just too many variables.

But I could be wrong...carry on if you're having fun.