S
SquirrelChaser
Guest
So @Marc made me aware of the Dimensional handbook (Matrix Book 2024 Historical Returns Data- United States) which contains annualized compound rates of return for the S&P 500 (among others) over various time periods.
Because I'm focused on the Nasdaq 100 and their handbook doesn't include it, I set this up in a spreadsheet using the opening price for the year taken from Macrotrends or similar online source. I got the equivalent data for the S&P 500, as I wanted to use the S&P as a benchmark and make sure I was comparing like with like.
I used the Excel formula =(BX/BY)^(1/Z)-1 to calculate the annualised returns, where BX is the cell reference for the end value, BY is the cell reference for the starting value, and Z is the period in years. I got this formula via Google.
The problem is, there's a discrepancy between the returns as per Dimensions versus the Excel calculation based on the year open data. For example, the S&P opened 2022 @ 4796.56 and opened 2023 @ 3824.14 which Excel and I believe is a 20.27% drop. But if you look at the Dimensional book, it shows this as an annualised return of -18.1%. Similarly Excel calculates the annualised returns from 2015 to 2020 at 9.62% while Dimensional has them at 11.7%. From 2000 to 2020 Dimensional has them at 6.1% Vs 4.1% for Excel.
Over any significant period of investment, a difference in return of over 2% is obviously far from marginal: over only a 5 year period the difference between an investment at the higher rate would be worth 10% more than the 5 year rate above. At the 20 year rates, the higher would be worth 46% more than the lower.
Clearly this isn't academic to me; 20 years is the period I'll have been paying into my pension at 60, and 50 years is about my maximum investment horizon (I don't expect to be actively participating in many expensive hobbies at the age of 90+). And 2% more or less than my reference figure makes a huge difference.
My base assumption is that my Excel formulae are wrong, but I can't see where. My alternate theory is that the Dimensional calculations are based on something other than the year open, but I don't see how that makes sense either— over 50 years from 1970 to 2020 Dimensional says 10.6% while Excel says 7.37%, and surely any difference in methodology such as that would eliminate 95% of the difference over such a long period?
I'm attaching the spreadsheet where I calculated the 5, 10, and 20 year annualised returns using Excel as well as the Dimensional matrix book.
I'd appreciate if anyone could identify the reason for the divergence between the Dimensional figures and my own.
Thanks.
Because I'm focused on the Nasdaq 100 and their handbook doesn't include it, I set this up in a spreadsheet using the opening price for the year taken from Macrotrends or similar online source. I got the equivalent data for the S&P 500, as I wanted to use the S&P as a benchmark and make sure I was comparing like with like.
I used the Excel formula =(BX/BY)^(1/Z)-1 to calculate the annualised returns, where BX is the cell reference for the end value, BY is the cell reference for the starting value, and Z is the period in years. I got this formula via Google.
The problem is, there's a discrepancy between the returns as per Dimensions versus the Excel calculation based on the year open data. For example, the S&P opened 2022 @ 4796.56 and opened 2023 @ 3824.14 which Excel and I believe is a 20.27% drop. But if you look at the Dimensional book, it shows this as an annualised return of -18.1%. Similarly Excel calculates the annualised returns from 2015 to 2020 at 9.62% while Dimensional has them at 11.7%. From 2000 to 2020 Dimensional has them at 6.1% Vs 4.1% for Excel.
Over any significant period of investment, a difference in return of over 2% is obviously far from marginal: over only a 5 year period the difference between an investment at the higher rate would be worth 10% more than the 5 year rate above. At the 20 year rates, the higher would be worth 46% more than the lower.
Clearly this isn't academic to me; 20 years is the period I'll have been paying into my pension at 60, and 50 years is about my maximum investment horizon (I don't expect to be actively participating in many expensive hobbies at the age of 90+). And 2% more or less than my reference figure makes a huge difference.
My base assumption is that my Excel formulae are wrong, but I can't see where. My alternate theory is that the Dimensional calculations are based on something other than the year open, but I don't see how that makes sense either— over 50 years from 1970 to 2020 Dimensional says 10.6% while Excel says 7.37%, and surely any difference in methodology such as that would eliminate 95% of the difference over such a long period?
I'm attaching the spreadsheet where I calculated the 5, 10, and 20 year annualised returns using Excel as well as the Dimensional matrix book.
I'd appreciate if anyone could identify the reason for the divergence between the Dimensional figures and my own.
Thanks.
Last edited by a moderator: