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 checkS&P500 actual values.Open 1/3/1950: 16.66Close 12/31/2012: 1426.1966 years.16.66 growing at 7.32% compounded annually for 66 years = 1427.44Therefore the approximate CAGR was 7.32%$10,000 growing at 7.32% compounded annually for 66 years = $856,805My backtest spreadsheet says:B&H of S&P500 without dividends, no expensesInitial value $10,000, $0/mo additionsFinal value on 1/1/2013 = $836,475The 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,580My backtest spreadsheet says:B&H of S&P500 with dividends,Initial value $10,000, $0/mo additions, 0.09% E/RFinal value on 1/1/2013 = $6,747,890The values are close, only 4% difference.Therefore, the spreadsheet calculations & formulas are correct.The spreadsheet passes the smell test.
Best Of |
Favorites & Replies |
Start a New Board |
My Fool |
BATS data provided in real-time. NYSE, NASDAQ and NYSEMKT data delayed 15 minutes.
Real-Time prices provided by BATS. Market data provided by Interactive Data.
Company fundamental data provided by Morningstar. Earnings Estimates, Analyst Ra