The Motley Fool Discussion Boards

Previous Page

Investing/Strategies / Retirement Investing

URL:  http://boards.fool.com/time-to-do-a-back-of-envelope-sanity-check-on-my-30626162.aspx

Subject:  Re: Strategy comparison S&P500 vs. IUL [rev 1] Date:  4/7/2013  6:48 PM
Author:  Rayvt Number:  71782 of 75340

Time to do a back-of-envelope sanity check on my spreadsheet. Those numbers I recently posted seem too good to be true.

S&P500 spreadsheet growth calculations sanity check

S&P500 actual values.
Open 1/3/1950: 16.66
Close 12/31/2012: 1426.19

66 years.
16.66 growing at 7.32% compounded annually for 66 years = 1427.44
Therefore the approximate CAGR was 7.32%

$10,000 growing at 7.32% compounded annually for 66 years = $856,805

My backtest spreadsheet says:
B&H of S&P500 without dividends, no expenses
Initial value $10,000, $0/mo additions
Final value on 1/1/2013 = $836,475
The values are very close, only 2.3% difference.
Therefore, the spreadsheet calculations & formulas are correct.

We know that the historical average dividend yield of the S&P500 is around 3%, sometimes higher, sometimes lower.

Let's make a guess. Let's guess that the average yield from 1950 to 2013 was 3.5%.

Total average return is then 7.32% + 3.5% = 10.82%.

The number often bandied about is that the long-term average total return of the S&P500 is about 10.5% per year, so 10.82% is a reasonable estimate.

$10,000 growing at 10.82% compounded annually for 66 years = $6,470,580


My backtest spreadsheet says:
B&H of S&P500 with dividends,
Initial value $10,000, $0/mo additions, 0.09% E/R
Final value on 1/1/2013 = $6,747,890
The values are close, only 4% difference.
Therefore, the spreadsheet calculations & formulas are correct.

The spreadsheet passes the smell test.
Copyright 1996-2014 trademark and the "Fool" logo is a trademark of The Motley Fool, Inc. Contact Us