Here is the fix for Firecalc Excel spreadsheet error

[PontGuy]

I updated my projections this morning and decided to figure out the Excel Spreadsheets that you can call up from the Firecalc Results page. The second Firecalc Excel spreadsheet has an error that needs to be fixed. This is the spreadsheet that shows the year by year inputs, data, and formulas. The second table in the spreadsheet is supposed to match the one above, but it is incorrect if Social Security or Pension/Off-Chart spending is entered into the Firecalc calculator.

The problem is with the formula in column J "Starting portfolio". It currently takes the "Ending Portfolio" from the previous year and subtracts inflation adjusted annual "Withdrawals", "Social Security" and "Pension/Off-Chart Spending" income. But SS and Pension income should be added since it is money coming in, not going out. And Off Chart Spending income is already loaded as a negative, so it should be added as well. (Complicating things is that the columns marked "Withdrawal Change" actually reflect the Pension/Off-Chart Spending income entered into the calculator.) Anyway, here is the fix that makes everything work correctly:

Change J40 to: =IF(I40<>"",$J$1+I40,$J$1-C40+SUM(D40:H40))

And starting with the next line in column J change to: =IF(I41<>"",T40+I41,T40-C41+SUM(D41:H41)) and so on down column J
​

Doing this makes the tables match, and then the rest of the formulas make a lot more sense.

With the time I spent resolving this I figured out a couple of other things that might help others make sense of the numbers. The first Excel file shows annual balances before inflation adjustment to today's dollars. So these figures align with those in the second Excel file column T "Ending Portfolio" for a given input year. However the Firecalc graph shows the year-by-year portfolio balances after inflation adjustment to today's dollars. So these match the figures in the second Excel file column U "Infl Adj End".

Moderators, please correct me if I got any of this wrong.... but 99.9% pretty sure I got it right.

 

[Retireatee1]

I don't see an Excel download offered on the FIRECalc 3.0 website results page. What am I missing?

 

[USGrant1962]

If you go to the "Investigate" tab, there is a checkbox in the "Display results of the retirement plan" paragraph. Then on your results there will be links to get the spreadsheet.

 

[erjourney]

I just found the same error today. My question is this:

Is this *just* an error in the excel spreadsheet (i.e. It only matters if you want to see the details.... which I would like)? Or is this an error that actually exists in calculating the final results in FIRECalc?

The reason I ask is that I don't know if I should trust the final results of FIRECalc or not.

Thanks

 

[erjourney]

I used to be on here years ago when I first retired but, as it has been some years, I no longer remember my login so I had to create a new one.

I used to use a calculator that I believe someone here created that was terrific and showed the excel outputs and all the formulas that went into it (although it didn't have the graphs and all the new bells and whistles, but it helped to let me know that I was in the ballpark). Perhaps that was one of the first versions of what would ultimately become FIRECalc

Thanks for the feedback on the respectability and reputation of FIRECalc!

 

[W2R]

I used to be on here years ago when I first retired but, as it has been some years, I no longer remember my login so I had to create a new one.

Oh that's easy! Just tell us your username and we can reset the password. I see that you are not using the same computer here as any other username did, though, and none of the other identifying information that we have matches AFAIK. I wonder if it was this forum or some other forum.

I used to use a calculator that I believe someone here created that was terrific and showed the excel outputs and all the formulas that went into it (although it didn't have the graphs and all the new bells and whistles, but it helped to let me know that I was in the ballpark). Perhaps that was one of the first versions of what would ultimately become FIRECalc

Nope! FIRECalc came first, and the forum was created around it.

The first post on this new Early-Retirement.org website was in June of 2002. Dory had retired (early) and had moved aboard his boat, a 36’ Grand Banks Trawler named Dory by the previous owner, to live his dream of life afloat. This initial post was inauspicious and lacked fanfare, but it marked the launch of a free forum to discuss not only FIRECalc, but also investing, early retirement, the joys of not working and (almost) any other topic under the sun.

Our history is freely available in a public thread here, and the above is an exerpt from it. Here's the link to that thread.

https://www.early-retirement.org/forums/f32/the-history-of-early-retirement-org-53657.html

Thanks for the feedback on the respectability and reputation of FIRECalc!

You're welcome, and please feel free to participate in our forum.

 

[REWahoo]

I just found the same error today. My question is this:

Is this *just* an error in the excel spreadsheet (i.e. It only matters if you want to see the details.... which I would like)?

^ This.

 

[erjourney]

One other question, I plan on reducing my expenses by $20,000 when I turn 65. I have entered this in "Off Chart Spending". Should I enter this as a positive or a negative number? (I am not using the Bernicke model nor the fixed percentage of my remaining portfolio, rather I'm using the Constant Spending Power.)

Thanks!

 

[Janet H]

Thanks for posting about this - we'll have a look.

 

[REWahoo]

One other question, I plan on reducing my expenses by $20,000 when I turn 65. I have entered this in "Off Chart Spending". Should I enter this as a positive or a negative number? (I am not using the Bernicke model nor the fixed percentage of my remaining portfolio, rather I'm using the Constant Spending Power.)

Thanks!

Sorry, didn't see this buried in the Excel spreadsheet error thread. If you start a new thread under FIRECalc support you'll be more likely to get a prompt response to your questions.

To your question: On the Other Income/Spending tab note the "Pension Income" option also says "(or off chart spending reduction)". Check that button, input $20K (as a positive number) the year your turn 65, un-check "Inflation adj?" and that should do it.

 

[PontGuy]

Excel file error has been fixed

I updated my projections this morning and decided to figure out the Excel Spreadsheets that you can call up from the Firecalc Results page. The second Firecalc Excel spreadsheet has an error that needs to be fixed. This is the spreadsheet that shows the year by year inputs, data, and formulas. The second table in the spreadsheet is supposed to match the one above, but it is incorrect if Social Security or Pension/Off-Chart spending is entered into the Firecalc calculator.

Just a quick update- the moderators followed up (thanks!!!) and the error in the Excel spreadsheet has been fixed.

Also a mention, this error never affected the output from Firecalc in any way. The Excel spreadsheets are for those that want to play around and see how the calculations are done.