Message Font: Serif | Sans-Serif

No. of Recommendations: 3
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

B&H of S&P500 without dividends, no expenses
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

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.

### Announcements

The Retirement Investing Board
This is the board for all discussions related to Investing for and during retirement. To keep the board relevant and Foolish to everyone, please avoid making any posts pertaining to political partisanship. Fool on and Retire on!