Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Results Graph and Spreadsheet Appear to Not Agree
Old 02-10-2012, 06:23 AM   #1
Confused about dryer sheets
 
Join Date: Sep 2009
Location: Riverdale
Posts: 7
Results Graph and Spreadsheet Appear to Not Agree

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
__________________

__________________
DocLarry is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 02-25-2012, 09:14 PM   #2
Thinks s/he gets paid by the post
Live And Learn's Avatar
 
Join Date: Feb 2012
Location: Tampa Bay Area
Posts: 1,689
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
__________________

__________________
"For the time being no discipline brings joy, but seems grievous and painful; but afterwards it yields a peaceable fruit of righteousness to those who have been trained by it." ~
Hebrews 12:11

ER'd in June 2015 at age 52. Initial WR 3%. 50/40/10 (Equity/Bond/Short Term) AA.
Live And Learn is offline   Reply With Quote
Old 02-25-2012, 09:30 PM   #3
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
REWahoo's Avatar
 
Join Date: Jun 2002
Location: Texas Hill Country
Posts: 42,149
Quote:
Originally Posted by Live And Learn View Post

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.
__________________
Numbers is hard

When I hit 70, it hit back

Retired in 2005 at age 58, no pension
REWahoo is offline   Reply With Quote
Old 02-25-2012, 09:50 PM   #4
Confused about dryer sheets
 
Join Date: Sep 2009
Location: Riverdale
Posts: 7
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
__________________
DocLarry is offline   Reply With Quote
Old 02-25-2012, 09:52 PM   #5
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
REWahoo's Avatar
 
Join Date: Jun 2002
Location: Texas Hill Country
Posts: 42,149
Thanks. I'm sure the FIRECalc staff will be notified of the problem.
__________________
Numbers is hard

When I hit 70, it hit back

Retired in 2005 at age 58, no pension
REWahoo is offline   Reply With Quote
Old 02-25-2012, 11:07 PM   #6
Full time employment: Posting here.
 
Join Date: Jun 2006
Posts: 926
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.
__________________
jclarksnakes is offline   Reply With Quote
Old 02-26-2012, 05:15 AM   #7
Confused about dryer sheets
 
Join Date: Sep 2009
Location: Riverdale
Posts: 7
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
__________________
DocLarry is offline   Reply With Quote
Old 02-26-2012, 07:40 AM   #8
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
REWahoo's Avatar
 
Join Date: Jun 2002
Location: Texas Hill Country
Posts: 42,149
Doc, here is more information that supports your theory. From an old FIRECalc thread:

Quote:
Originally Posted by FUEGO View Post
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:

Quote:
... 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:

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.
__________________
Numbers is hard

When I hit 70, it hit back

Retired in 2005 at age 58, no pension
REWahoo is offline   Reply With Quote
Old 02-26-2012, 11:49 AM   #9
Confused about dryer sheets
 
Join Date: Sep 2009
Location: Riverdale
Posts: 7
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
__________________

__________________
DocLarry is offline   Reply With Quote
Reply

Tags
graph, results, spreadsheet


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


 

 
All times are GMT -6. The time now is 11:27 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.