Results Graph and Spreadsheet Appear to Not Agree

[DocLarry]

If you run FIRECalc with the default numbers ($750k portfolio, $30k/year withdrawal for 30 years) the results page says, "The lowest and highest portfolio balance throughout your retirement was $-300,739 to $4,259,606, with an average of $1,323,668"

If you open the Excel results spreadsheet for the same case and calculate minimum, maximum and average with the Excel internal functions you get -$1,387,963, $9,436,637 and $1,495,078.

Shouldn't these two sets of numbers be the same or have I missed something?

Thanks!
Doc Larry

 

[Live And Learn]

DocLarry, I found the same thing and its a little unsettling. Firecalc has given me results that make FIRE extremely possible in the next few years but the discrepancies in the spreadsheet version have me scratching my head

ETA: could it be because the spreadsheet version is only good for a 30 year longevity and I'm using 40 years

 

[REWahoo]

ETA: could it be because the spreadsheet version is only good for a 30 year longevity and I'm using 40 years

I think that's the reason. You can check by doing a run using 30 years to verify that the numbers do match.

 

[DocLarry]

Folks,

The example that I gave in the original post in this thread was for 30 years, and that is where I originally saw the discrepancy. So I do not think that is the explanation.

DocLarry

 

[REWahoo]

Thanks. I'm sure the FIRECalc staff will be notified of the problem.

 

[jclarksnakes]

I think I know how Firecalc works? It looks at the last approximately 150 years of financial trends and runs numbers you input through scenarios starting each year in that 150 year history to see what the results would have been and then converts them to present day dollars (sort of) and reports them to you in dollar number estimates and percentage probability of not depleting your portfolio numbers. If you input for the scenarios to run for 30 years the results will be very different than if you input for 40 years. I am not sure how Excel works. If it runs the same numbers the same way through the same 150 year histories the numbers would be pretty close or even exactly the same. I am thinking Excel probably uses a different set of historical information and may run numbers through their information differently and so would be expected to produce different numbers. To me those numbers ranges Doc Larry posted do not look so dissimilar. Both sets of numbers are only estimates.

 

[DocLarry]

Folks,

I believe that the explanation for the discrepancy is that the Excel spreadsheet results DO NOT INCLUDE INFLATION even though the text says that they do. A quick test case that supports this conclusion is given below, and I invite everyone to sharpen their pencils and check my reasoning.

The test case is for an initial $1,000,000 portfolio with no spending, no additions or subtractions, and no portfolio growth, but with a constant 3% inflation rate.

Inputs

Spending = 0
Initial Portfolio = 1,000,000
Years = 30
Other Spending/Income = 0
Market Growth = 0
Fixed Income = 0
Inflation = 3%

The FIRECalc result for this case is a single graph with a 30-year endpoint of $411,987. This is exactly what is expected for constant 3% inflation*.

Now here is the real zinger!! If you open the Excel spreadsheet results you will see that the portfolio value for every year in each of the 91 cycles is $1,000,000, which is exactly what you will get for this test case if the spreadsheet does not include the effects of inflation.

I am going to pursue this further by correcting the spreadsheet for inflation in the general case and then checking the results against FIRECalc. I will post further results.

Best,
DocLarry

Note:
* The inflation factor for 3% for 30 years = (1.03)^30 = 2.427262471. And
$1,000,000/2.427262471 = $411,987, which is exactly the FIRECalc result

 

[REWahoo]

Doc, here is more information that supports your theory. From an old FIRECalc thread:

Curious about the detailed spreadsheets on the Firecalc results page.

Specifically, the spreadsheets linked to where it says:

"Open an (unformatted) Excel spreadsheet showing the inflation-adjusted end-of-year portfolio balances for every year in each of the cycles tested by FIRECalc. Open a spreadsheet showing the year by year inputs, data, and formulas for the cycle beginning in 1960"

The first spreadsheet sounds like it is in inflation adjusted (real) dollars, but comparing those results with the second spreadsheet, it appears that the first spreadsheet presents nominal dollars, not adjusted for inflation. Well, I guess they are adjusted up for inflation through the calcs.

Is there any way the results can be clarified on the text of the results page to prevent misunderstandings of real or nominal terms?

Dory36, the creator of FIRECalc who is now retired from the forum, wrote the following in response:

...[FONT=verdana, sans-serif] at first blush, I'd say his assumption is correct. The second spreadsheet shows raw data - unadjusted values, inflation factors, and formulas in many cells. It is intended to let someone see what is being done and change anything they want and make their own "personal firecalc" as a spreadsheet, where FC has done the hard work of building the initial spreadsheet, populating data and formulas, etc.

The notes on rows 35-37:

[/FONT][FONT=Verdana, sans-serif]The numbers above are results calculated by FIRECalc. The numbers below are generated by formulas in each cell, and should match the numbers above. Examine each formula to determine how results are obtained.[/FONT]

 

[DocLarry]

Folks,

I stick to my observation that the first spreadsheet is not inflation corrected and that the statement on the results page that the first spreadsheet shows, "...the inflation-adjusted end-of-year portfolio balances for every year in each of the cycles." is not correct as is demonstrated by the test case described in my earlier post.

I plan to post later today or tomorrow a spreadsheet that correctly accounts for inflation.

Best,
DocLarry