[As of 27-Jan-2016, support for this spreadsheet is maintained by siamond in this wiki article: Simba's backtesting spreadsheet (includes download instructions)
[22-Jan-2017: Modified title, was "Spreadsheet for backtesting (includes TrevH's data)" --admin LadyGeek]
The backtesting tool is a spreadsheet to backtest portfolio returns from 1972 to present [1985 to present if you want to include certain Sector/Tax-Exempt funds that started after 1985] of various Vanguard Mutual Funds. This tool is customizable and you can add the returns for any mutual fund.
This tool originated from TrevH's data that was listed in numerous topics on both the forums.
The spreadsheet calculates various metrics such as CAGR/Std Dev/Sharpe Ratio/Sortino Ratio. The tools also provides rolling returns [both nominal and real] for 3,5,10 and 15 years. It lets you compare upto 5 different portfolios [both 1972-present and 1985-present].
Please see the README for more instructions/revision history. The data sources can be found in the Data_Sources worksheet.
Hope you find the tool useful. Do keep in mind that the past returns do not guarantee future results and use this tool only for learning purposes.
Download Location:
You can download the latest version of Excel Spreadsheet [rev11a] or the LibreOffice version [rev11a] [see above, admin LadyGeek]
DISCLAIMER: Please use this spreadsheet only for learning purposes and as a need for diversification. Do not change your Asset Allocation purely based on historical returns.
Past returns are not indicative of future returns.
I'd like to thank TrevH and all the other diehards/bogleheads who continue to assist folks like me on a daily basis.
Regards,
Simba
Disclaimer/Caution/Caveat Emptor: Use these only for Entertainment purposes. Please do not change your asset allocation purely based on historical performance. This was a fun project and please treat it such.
Audit/Request log:
1 - Chas - Fix Sharpe Ratio to reflect the T-Bill Avg for the period backtesting (Currently it uses the avg for the entire 1972-2008 or 1985-2008 time period) - Fixed in rev8e
2 - acr123 - Fix the formula for 2008 returns in Returns_85_08 - Fixed in rev8e
3 - Chas - Fix correlations for CHP and SBFS SP-72-08 and SP-85-08 worksheets Fixed in rev8f
4 - simba - Fix formula for Total-Unrebalanced in compare portfolio section Fixed in rev8f
5 - MrMatt2532 - Sortino Ratio formula needs to be updated Fixed in rev8f
6 - acr123 - Sortino Ratio is incorrect in Returns_72_08 and Returns_85_08 (Missing the *100 in part of the formula) Fixed in rev8g
Edit: Corrected the link
Edit: Added data for Balanced Funds VWELX (wellington) & VWINX (Wellesley). Also corrected the data from EAFE.
Edit: Updated the spreadsheet with link to rev4.
Edit: rev4a - Changed the CAGR formula to reflect the change in initial investment. Also fixed a flaw in the forumula to calculate total.
Edit: rev5a - Improved the usability. The only changes permitted are in the worksheet portfolio.
Edit: rev5b - Changed the background color of cells that can be changed
Edit: rev5c - User inputs starting/ending year rather than # of years.
Edit: rev5d - corrected the columns/data in Returns_85_06 to match Data_85_06.
Edit: rev5f - corrected some typos and some other formula/data. Included chart.
Edit: rev5g - added CHP portfolio and Spreadsheet now compares the growth of your portfolio with CHP
Edit: rev5h - spreadsheet now includes both nominal/real returns. Also included High Yield Corp.
Edit: rev5i - corrected the formula for the CHP in 85-06 returns.
Edit: rev5j - Added ST (2 year) treasury. Corrected the returns for Pacific/Europe funds
Edit: rev5k - Added gummy's changes regarding probability estimates (charts/macro)
Edit: rev5l - Incorrect formula for ST treasury when I added gummy's changes
Edit: rev5m - Added Sortino Ratio
Edit : rev6a - Compare 5 different portfolios. SS now returns rebalanced and un-rebalanced total/returns etc. Chart shows the selected period.
Edit: rev6b - corrected the formula for real returns in portfolio comparisons.
Edit: rev6c - Changed the Intl value data returns
Edit: rev6d - Added Mid-cap Growth & Mid-Cap Value Index.
Edit: rev6e - Added VG Extended Mkt Index
Edit: rev6j - Added drawdown plots
Edit: rev6g - Added rev6g for OO
Edit: rev7a - Added 2007 returns
Edit: rev7c - Updated the CPI-U, BRSIX and PCRIX returns.
Edit: rev7d - Corrected the errors that were caused by adding 2007 returns
Edit: rev7e - Added a new parameter MAR(Minimum Acceptable Return) for Sortino Ratios; Change the method for calculating the un-rebalanced portfolios; Deduct ER for synthetic/index returns (modifiable)
Edit: rev7f - Fixed the Returns so ER is deducted for the comparisons as well.
Edit: rev7h - Added 2008 returns and some minor updates
Edit: rev8c - Added Gold returns, Now using month-month CPI instead of avg-avg. New worksheet for comparing Lazy Portfolios.
Edit: rev8d - bug fixing ; updated sources ; updated references to non-existing 07 worksheet
Edit: rev8e - Fixed formula for 2008 returns in Returns_85_09 and Fixed Sharpe Ratio to reflect the T-Bill Avg for the time period being tested.
Edit: rev8f - Fixed Correlations issues; Updated Sortino Ratio formula ; Added the ability to change the time period for comparing portfolios ; Added the ability to change the time period for comparing Lazy Portfolios.
Edit: rev8g - Fixed the formula for sortino in Returns_72_08 and Returns_85_08
See the Revision History in the spreadsheet for complete list of changes
Simba's backtesting spreadsheet [a Bogleheads community project]
Simba's backtesting spreadsheet [a Bogleheads community project]
Last edited by LadyGeek on Wed Jan 27, 2016 6:42 pm, edited 64 times in total.
Norm - link corrected
Norm,
The link I posted earlier seems to work fine in Firefox but not in IE. It should be okay now.
Try it now.
Regards,
Simba
The link I posted earlier seems to work fine in Firefox but not in IE. It should be okay now.
Try it now.
Regards,
Simba
Thank you!
Wow! This is fantastic. I'm sure I'll spend more time than I should running numbers. Thanks very much!
-
Murray Boyd
- Posts: 794
- Joined: Mon Feb 19, 2007 5:00 pm
Great Spreadsheet
I would like to see the yearly numbers for the Vanguard Wellington Fund, for comparison purposes.
It would make interesting viewing when compared against DIY portfolios.
Nelson
It would make interesting viewing when compared against DIY portfolios.
Nelson
Updated Spreadsheet (rev2)
Murray - Thanks for pointing the error. Trev was using Vanguard Total Intl Stock so I corrected the values for EAFE (with Vanguard Developed MKt Idx [VDMIX]).Murray Boyd wrote:Looks neat.
I think the EAFE, EM, EAFE/EM numbers don't make sense from 1997 on. EAFE is the same as EAFE/EM.
Norm - For LCB you can use 500 Idx (VFINX)
Nelson - I updated the spreadsheet and included Wellington & Wellesley
Regards
Simba
EAFE Dividends
Are the EAFE returns on the spreadsheet shown with gross dividends or net dividends?
Cheers,
Fred
Cheers,
Fred
-
TheEternalVortex
- Posts: 2583
- Joined: Tue Feb 27, 2007 8:17 pm
- Location: San Jose, CA
OK...scratch LV. Are there any other asset classes for which historical data will prove to be meaningless?TheEternalVortex wrote:I don't know if this data is very useful. For example, in that time period LV outperformed US market with a lower standard deviation. It's unlikely that will continue as otherwise there is no sense in investing the US market.
Cb :lol:
That argument assumes that standard deviation is the only relevant measure of risk, an assumption I would personally not make.TheEternalVortex wrote:I don't know if this data is very useful. For example, in that time period LV outperformed US market with a lower standard deviation. It's unlikely that will continue as otherwise there is no sense in investing the US market.
Fred - AFAIK the MSCI returns are net returns.
Vanguard's website lists:The MSCI EAFE Index values are calculated using net returns. Net returns approximate the minimum possible dividend reinvestment (the dividend is reinvested after deduction of withholding tax, applying the highest rate applicable to non-resident individuals (Luxembourg holding companies) who do not benefit from double taxation treaties).
Index returns are adjusted for withholding taxes applicable to Luxembourg holding companies.
Bill Bernstein in this article Are Value Stocks Riskier than Growth talks about other risks besides standard deviation.TheEternalVortex wrote:I don't know if this data is very useful. For example, in that time period LV outperformed US market with a lower standard deviation. It's unlikely that will continue as otherwise there is no sense in investing the US market.
Hey Guys...
If you want to go back a couple more years...
The data set below is the same... but the years 1970-1971 are included.
REIT is in this set... but for the first two years there are no returns... just N/A included.
This set includes another asset class or two... I think Mid Caps and Commodities, perhaps Long Term Gov Bonds.
The EAFED column is EAFE Developed Only... The last few years of it included actual returns for Vanguards fund.
The EAFE85 EM15 column
Uses Vanguards Total International for as far back as the history exist... then uses EAFE/EM at 85/15 ratio back to 1988 (as far back as EM returns go)... then past that it is EAFE only.
Enjoy !
Trev H
YEAR,MKT-TSM,LCV,500 Idx,LCG,MCB,SCV,SCB,SCG,D10,REIT,EAFED,EAFE85,COMM,LTGB,5 Yr T,ITB,TBILL
YEAR,VTSMX,VIVAX,VFINX,VIGRX,VIMSX,VISVX,NAESX,VISGX,BRSIX,VGSIX,VDMIX,EM15,PCRIX,VUSTX,VFITX,VBMFX,VMPXX
1970,0.00,10.40,3.90,-5.00,-2.10,0.30,-17.40,-18.60,-17.80,N/A,-11.70,-11.70,4.90,12.10,5.20,12.10,6.50
1971,16.20,13.10,14.30,24.00,21.10,14.40,16.50,23.50,18.50,N/A,29.60,29.60,-6.10,12.30,5.20,13.20,4.40
1972,16.90,19.00,19.00,21.50,9.10,7.00,4.40,3.70,-0.20,8.00,36.30,36.30,30.60,5.70,5.20,5.70,3.80
1973,-18.10,-3.60,-14.70,-21.60,-26.50,-26.00,-30.90,-39.10,-41.70,-15.50,-14.90,-14.90,102.10,-1.10,4.60,3.40,6.90
1974,-27.20,-23.10,-26.50,-29.20,-25.20,-18.20,-19.90,-33.40,-28.90,-21.40,-23.20,-23.20,12.80,4.40,5.70,7.00,8.00
1975,38.70,57.20,37.20,34.20,57.40,54.50,52.80,63.20,72.40,19.30,35.40,35.40,-11.20,9.20,7.80,8.30,5.80
1976,26.70,44.00,23.90,17.80,40.40,53.60,57.40,43.50,55.70,47.60,2.50,2.50,1.30,16.80,12.90,11.70,5.10
1977,-4.20,1.40,-7.20,-9.70,4.00,21.80,25.40,20.30,23.20,22.40,18.10,18.10,1.30,-0.70,1.40,3.00,5.10
1978,7.50,3.30,6.60,6.90,10.90,21.80,23.50,18.60,28.40,10.30,32.60,32.60,23.00,-1.20,3.50,2.20,7.20
1979,23.00,20.50,18.60,23.90,32.50,35.40,43.10,50.80,42.20,35.90,4.80,4.80,57.50,-1.20,4.10,6.60,10.40
1980,32.70,24.40,32.50,39.60,32.50,25.40,38.60,52.30,30.50,24.40,22.60,22.60,-3.80,-4.00,3.90,6.60,11.30
1981,-3.70,1.30,-4.90,-11.30,2.40,14.90,2.00,-9.20,7.80,6.00,-1.00,-1.00,-26.50,1.90,9.40,10.80,14.70
1982,20.80,20.00,21.50,20.50,23.30,28.50,24.90,21.00,28.00,21.60,-0.90,-0.90,-3.50,40.40,29.10,25.40,10.50
1983,22.00,28.30,22.60,16.00,23.80,38.60,29.10,20.10,37.30,30.60,24.60,24.60,17.00,0.70,7.40,8.20,8.80
1984,4.50,10.10,6.30,-1.00,1.40,2.30,-7.30,-15.80,-19.50,20.90,7.90,7.90,-9.40,15.50,14.00,14.30,9.80
1985,32.20,31.50,31.70,32.90,32.00,31.00,31.10,31.00,25.70,19.10,56.70,56.70,3.30,31.00,20.30,18.00,7.70
1986,16.10,20.00,18.70,15.40,18.20,7.40,5.70,3.60,0.30,19.20,69.90,69.90,-4.90,24.50,15.10,13.10,6.20
1987,1.70,0.50,5.30,5.30,0.20,-7.10,-8.80,-10.50,-14.90,-3.60,24.90,24.90,23.00,-2.70,2.90,3.60,5.50
1988,18.00,23.20,16.60,11.30,19.80,29.50,24.90,20.40,20.80,13.50,28.60,30.37,16.70,9.70,6.10,6.40,6.40
1989,28.90,25.20,31.70,35.90,26.30,12.40,16.20,20.20,5.50,8.80,10.80,18.93,1.90,18.10,13.30,12.70,8.40
1990,-6.00,-8.10,-3.10,-0.30,-11.50,-21.80,-19.50,-17.40,-31.30,-15.40,-23.20,-21.31,4.70,6.20,9.70,9.60,7.80
1991,34.70,24.60,30.50,41.20,41.50,41.70,46.10,51.20,48.30,35.70,12.50,19.61,-1.60,19.30,15.30,14.10,5.60
1992,9.80,13.80,7.42,5.00,16.40,29.10,18.20,7.80,33.60,14.60,-11.80,-8.32,-1.40,7.40,7.78,7.14,3.53
1993,10.62,18.35,9.89,1.53,14.30,23.80,18.70,13.40,25.40,19.70,32.90,39.19,-2.50,16.79,11.43,9.68,2.86
1994,-0.17,-0.73,1.18,2.89,-2.10,-1.50,-0.51,-2.40,-2.90,3.20,8.10,5.79,14.00,-7.03,-4.33,-2.66,3.81
1995,35.79,36.94,37.45,38.06,34.40,25.80,28.74,31.00,30.10,15.30,11.60,9.94,11.10,30.11,20.44,18.18,5.49
1996,20.96,21.86,22.88,23.74,19.00,21.40,18.12,11.30,16.80,35.30,6.40,7.81,21.70,-1.25,1.92,3.58,5.09
1997,30.99,29.77,33.19,36.34,29.00,31.80,24.59,12.90,21.50,18.77,2.10,-0.77,4.40,13.90,8.96,9.44,5.12
1998,23.26,14.64,28.62,42.21,10.10,-6.50,-2.61,1.20,-1.81,-16.32,20.30,15.60,-27.60,13.05,10.61,8.58,5.00
1999,23.81,12.57,21.07,28.76,15.32,3.35,23.13,19.80,31.49,-4.04,27.30,29.92,7.20,-8.66,-3.52,-0.76,4.55
2000,-10.57,6.08,-9.06,-22.21,18.10,21.88,-2.67,1.59,0.67,26.35,-14.00,-15.61,42.40,19.72,14.03,11.39,5.80
2001,-10.97,-11.88,-12.02,-12.93,-0.50,13.70,3.10,-0.78,23.98,12.35,-22.04,-20.15,-31.70,4.31,7.55,8.43,3.99
2002,-20.96,-20.91,-22.15,-23.68,-14.61,-14.20,-20.02,-15.41,4.90,3.75,-15.70,-15.08,27.20,16.67,14.15,8.26,1.51
2003,31.35,32.25,28.50,25.92,34.14,37.19,45.63,42.88,79.43,35.65,38.61,40.34,29.80,2.68,2.37,3.97,0.82
2004,12.52,15.29,10.74,7.20,20.35,23.55,19.90,16.06,20.12,30.76,20.25,20.84,16.40,7.12,3.40,4.24,1.00
2005,5.98,7.09,4.77,5.09,13.93,6.07,7.36,8.64,4.08,11.89,13.34,15.57,20.50,6.61,2.32,2.40,2.77
2006,15.51,22.15,15.64,9.01,13.60,19.24,15.64,11.94,11.48,35.07,26.18,26.64,-3.00,1.74,3.14,4.27,4.55
The data set below is the same... but the years 1970-1971 are included.
REIT is in this set... but for the first two years there are no returns... just N/A included.
This set includes another asset class or two... I think Mid Caps and Commodities, perhaps Long Term Gov Bonds.
The EAFED column is EAFE Developed Only... The last few years of it included actual returns for Vanguards fund.
The EAFE85 EM15 column
Uses Vanguards Total International for as far back as the history exist... then uses EAFE/EM at 85/15 ratio back to 1988 (as far back as EM returns go)... then past that it is EAFE only.
Enjoy !
Trev H
YEAR,MKT-TSM,LCV,500 Idx,LCG,MCB,SCV,SCB,SCG,D10,REIT,EAFED,EAFE85,COMM,LTGB,5 Yr T,ITB,TBILL
YEAR,VTSMX,VIVAX,VFINX,VIGRX,VIMSX,VISVX,NAESX,VISGX,BRSIX,VGSIX,VDMIX,EM15,PCRIX,VUSTX,VFITX,VBMFX,VMPXX
1970,0.00,10.40,3.90,-5.00,-2.10,0.30,-17.40,-18.60,-17.80,N/A,-11.70,-11.70,4.90,12.10,5.20,12.10,6.50
1971,16.20,13.10,14.30,24.00,21.10,14.40,16.50,23.50,18.50,N/A,29.60,29.60,-6.10,12.30,5.20,13.20,4.40
1972,16.90,19.00,19.00,21.50,9.10,7.00,4.40,3.70,-0.20,8.00,36.30,36.30,30.60,5.70,5.20,5.70,3.80
1973,-18.10,-3.60,-14.70,-21.60,-26.50,-26.00,-30.90,-39.10,-41.70,-15.50,-14.90,-14.90,102.10,-1.10,4.60,3.40,6.90
1974,-27.20,-23.10,-26.50,-29.20,-25.20,-18.20,-19.90,-33.40,-28.90,-21.40,-23.20,-23.20,12.80,4.40,5.70,7.00,8.00
1975,38.70,57.20,37.20,34.20,57.40,54.50,52.80,63.20,72.40,19.30,35.40,35.40,-11.20,9.20,7.80,8.30,5.80
1976,26.70,44.00,23.90,17.80,40.40,53.60,57.40,43.50,55.70,47.60,2.50,2.50,1.30,16.80,12.90,11.70,5.10
1977,-4.20,1.40,-7.20,-9.70,4.00,21.80,25.40,20.30,23.20,22.40,18.10,18.10,1.30,-0.70,1.40,3.00,5.10
1978,7.50,3.30,6.60,6.90,10.90,21.80,23.50,18.60,28.40,10.30,32.60,32.60,23.00,-1.20,3.50,2.20,7.20
1979,23.00,20.50,18.60,23.90,32.50,35.40,43.10,50.80,42.20,35.90,4.80,4.80,57.50,-1.20,4.10,6.60,10.40
1980,32.70,24.40,32.50,39.60,32.50,25.40,38.60,52.30,30.50,24.40,22.60,22.60,-3.80,-4.00,3.90,6.60,11.30
1981,-3.70,1.30,-4.90,-11.30,2.40,14.90,2.00,-9.20,7.80,6.00,-1.00,-1.00,-26.50,1.90,9.40,10.80,14.70
1982,20.80,20.00,21.50,20.50,23.30,28.50,24.90,21.00,28.00,21.60,-0.90,-0.90,-3.50,40.40,29.10,25.40,10.50
1983,22.00,28.30,22.60,16.00,23.80,38.60,29.10,20.10,37.30,30.60,24.60,24.60,17.00,0.70,7.40,8.20,8.80
1984,4.50,10.10,6.30,-1.00,1.40,2.30,-7.30,-15.80,-19.50,20.90,7.90,7.90,-9.40,15.50,14.00,14.30,9.80
1985,32.20,31.50,31.70,32.90,32.00,31.00,31.10,31.00,25.70,19.10,56.70,56.70,3.30,31.00,20.30,18.00,7.70
1986,16.10,20.00,18.70,15.40,18.20,7.40,5.70,3.60,0.30,19.20,69.90,69.90,-4.90,24.50,15.10,13.10,6.20
1987,1.70,0.50,5.30,5.30,0.20,-7.10,-8.80,-10.50,-14.90,-3.60,24.90,24.90,23.00,-2.70,2.90,3.60,5.50
1988,18.00,23.20,16.60,11.30,19.80,29.50,24.90,20.40,20.80,13.50,28.60,30.37,16.70,9.70,6.10,6.40,6.40
1989,28.90,25.20,31.70,35.90,26.30,12.40,16.20,20.20,5.50,8.80,10.80,18.93,1.90,18.10,13.30,12.70,8.40
1990,-6.00,-8.10,-3.10,-0.30,-11.50,-21.80,-19.50,-17.40,-31.30,-15.40,-23.20,-21.31,4.70,6.20,9.70,9.60,7.80
1991,34.70,24.60,30.50,41.20,41.50,41.70,46.10,51.20,48.30,35.70,12.50,19.61,-1.60,19.30,15.30,14.10,5.60
1992,9.80,13.80,7.42,5.00,16.40,29.10,18.20,7.80,33.60,14.60,-11.80,-8.32,-1.40,7.40,7.78,7.14,3.53
1993,10.62,18.35,9.89,1.53,14.30,23.80,18.70,13.40,25.40,19.70,32.90,39.19,-2.50,16.79,11.43,9.68,2.86
1994,-0.17,-0.73,1.18,2.89,-2.10,-1.50,-0.51,-2.40,-2.90,3.20,8.10,5.79,14.00,-7.03,-4.33,-2.66,3.81
1995,35.79,36.94,37.45,38.06,34.40,25.80,28.74,31.00,30.10,15.30,11.60,9.94,11.10,30.11,20.44,18.18,5.49
1996,20.96,21.86,22.88,23.74,19.00,21.40,18.12,11.30,16.80,35.30,6.40,7.81,21.70,-1.25,1.92,3.58,5.09
1997,30.99,29.77,33.19,36.34,29.00,31.80,24.59,12.90,21.50,18.77,2.10,-0.77,4.40,13.90,8.96,9.44,5.12
1998,23.26,14.64,28.62,42.21,10.10,-6.50,-2.61,1.20,-1.81,-16.32,20.30,15.60,-27.60,13.05,10.61,8.58,5.00
1999,23.81,12.57,21.07,28.76,15.32,3.35,23.13,19.80,31.49,-4.04,27.30,29.92,7.20,-8.66,-3.52,-0.76,4.55
2000,-10.57,6.08,-9.06,-22.21,18.10,21.88,-2.67,1.59,0.67,26.35,-14.00,-15.61,42.40,19.72,14.03,11.39,5.80
2001,-10.97,-11.88,-12.02,-12.93,-0.50,13.70,3.10,-0.78,23.98,12.35,-22.04,-20.15,-31.70,4.31,7.55,8.43,3.99
2002,-20.96,-20.91,-22.15,-23.68,-14.61,-14.20,-20.02,-15.41,4.90,3.75,-15.70,-15.08,27.20,16.67,14.15,8.26,1.51
2003,31.35,32.25,28.50,25.92,34.14,37.19,45.63,42.88,79.43,35.65,38.61,40.34,29.80,2.68,2.37,3.97,0.82
2004,12.52,15.29,10.74,7.20,20.35,23.55,19.90,16.06,20.12,30.76,20.25,20.84,16.40,7.12,3.40,4.24,1.00
2005,5.98,7.09,4.77,5.09,13.93,6.07,7.36,8.64,4.08,11.89,13.34,15.57,20.50,6.61,2.32,2.40,2.77
2006,15.51,22.15,15.64,9.01,13.60,19.24,15.64,11.94,11.48,35.07,26.18,26.64,-3.00,1.74,3.14,4.27,4.55
Last edited by Trev H on Mon May 14, 2007 6:33 pm, edited 1 time in total.
Oh Yeah...
Just in case you think it is a data entry error...
In 1970 The US Cap Weighted Market CRSP D1-10 had a return of 0.00.
And you might also notice that the 500 Index.... outperformed the Market for the span of 1972-2006 and 1970-2006.
That does not make sense to me... but that is what the data shows.
My 500 Index Returns are from the fpanet article 1970-1992 and then from Vanguards Fund thru to 2006.
The TSM returns are CRSP D1-10 from 1970-1992 then from Vanguards TSM fund 1993-2006.
A good part of the early data 1970 thru 1990's was taken from the fpanet article. Table 5 in the Link below.
http://www.fpanet.org/journal/articles/ ... 6-art7.cfm
Let me know if you find any problems with the data.
Trev H
In 1970 The US Cap Weighted Market CRSP D1-10 had a return of 0.00.
And you might also notice that the 500 Index.... outperformed the Market for the span of 1972-2006 and 1970-2006.
That does not make sense to me... but that is what the data shows.
My 500 Index Returns are from the fpanet article 1970-1992 and then from Vanguards Fund thru to 2006.
The TSM returns are CRSP D1-10 from 1970-1992 then from Vanguards TSM fund 1993-2006.
A good part of the early data 1970 thru 1990's was taken from the fpanet article. Table 5 in the Link below.
http://www.fpanet.org/journal/articles/ ... 6-art7.cfm
Let me know if you find any problems with the data.
Trev H
fpanet...
.
CB....See Below.
====
Annual Return Data Sources:
I have compiled a rather extensive collection of returns from several asset classes.
The source for this collection includes data from the sites below:
http://www.tamasset.com/other/AC2705.xls
http://www.fpanet.org/journal/articles/ ... 6-art7.cfm
https://flagship.vanguard.com/VGApp/hnw/FundsByType
In order to compile a data set that is as realistic as possible I use the actual yearly total return from Vanguard Index Funds for as far back as I can easily get access to the historical return data on the Vanguard Website. 15 Years History is the max that Vanguard has available under the funds performance link.
For US MicroCaps and Commodities I used the Bridgeway BRSIX and Pimco PCRIX Funds for actual returns to the extent of the funds history.
Below list the Abbreviations (column heading in my Excel Sheet) and a summary of the data sources by year range.
MKT = US Cap Weighted Market:
=============================
CRSP Market Decile 1-10 1972-1992
Vanguards Total Stock Market Index Fund 1993-2006
LCB = US Large Blend:
=====================
S&P 500: Standard & Poors 1972-1991
Vanguards 500 Index Fund 1992-2006
LCV = US Large Value:
=====================
Fama and French 1972–1978
Russell 1000 Value Index 1979–1992
Vanguards Value Index Fund 1993-2006
LCG = US Large Growth:
======================
Fama and French 1972–1978
Russell 1000 Growth Index 1979–1992
Vanguards Growth Index Fund 1993-2006
MCB = US Mid Blend:
===================
CRSP Decile 3-5 1972-1978
Russell Mid Cap Index 1979-1998
Vanguards Mid Cap Index Fund 1999-2006
SCB = US Small Blend:
=====================
Ibbotson 1972–1978
Russell 2000 Index 1979-1991
Vanguards Small Cap Index Fund 1992-2006
SCV = US Small Value:
=====================
Ibbotson 1972-1978
Russell 2000 Value Index 1979-1998
Vanguards Small Cap Value Index Fund 1999-2006
SCG = US Small Growth:
======================
Ibbotson 1972–1978
Russell 2000 Growth Index 1979-1998
Vanguards Small Cap Growth Index Fund 1999-2006
MICRO = US MicroCap Blend:
==========================
CRSP Decile 10 1972-1997
BRSIX Bridgeway Ultra Small Market 1998-2006
REIT = Real Estate:
===================
Nat. Assn. of Real Estate Inv Trusts 1972-1996
Vanguards REIT Index Fund – 1997-2006
INTLD = International Developed:
================================
MSCI EAFE Index 1972-2000
Vanguards Developed Market Index Fund 2001-2006
INTLT - Total International:
============================
MSCI EAFE Index 1972-1987 (Developed Only)
85% EAFE Index, 15% Emerging Markets Index 1988-1996
Vanguards Total International Index Fund 1997-2006
COMM – Commodities / Natural Resources:
======================================
Standard & Poor's Commodity Index 1972-2002
Pimco PCRIX 2003-2006
5YT – InterTerm Treasury:
=========================
Tamasset Spreadsheet 1972-1991
Vanguards InterTerm Treasury Fund 1992-2006
LTGB = Long Term Government Bonds:
==================================
Tamasset Spreadsheet 1972-1991
Vanguards Long Term Treasury 1992-2006
ITB = Intermediate Term Bonds:
==============================
Ibbotson 1972
Lehman Brothers 1973-1991
Vanguards Total Bond Index Fund 1992-2006
STIPS = Syn TIPS:
=================
Synthetic TIPS 1972-2000
Vanguard Inflation Protected Securities Fund 2001-2006
Risk Free Benchmark for Sharpe:
===============================
T-Bills 1972-1991
Vanguard Treasury Money Market Fund 1992-2006
Note on “Total International” – this is an attempt to get a more realistic picture of returns on international investments considering both developed and emerging markets by using Vanguards Total International Fund for real returns since inception 1997 and then including Developed/Emerging Markets at a 85%/15% ratio from 1988 to 1996 to simulate the ratio in Vanguards Total International Fund (which holds near 15% Emerging Markets). Although this category is called Total International, I do not have a source for Emerging Markets returns prior to 1988 so Emerging Market Data is not considered for the first 16 years of this data set.
=====
Where most of the above shows 1972-2006..... you can change that to 1970-2006 in what I just posted above.
The commodities are listed in that fpanet article as natural resources... and if you look at the end of that fpanet document... they give their data sources... and for Commodities it was S&P Commodity Index.
Where the returns for Mid Cap Blend were not available from the russell index data in that fpanet article... I used CRSP D3-5. The most recent returns are from VIMSX.
Microcaps D10 + BRSIX.
I try to use actual fund returns where available to make it more realistic.
Trev H
CB....See Below.
====
Annual Return Data Sources:
I have compiled a rather extensive collection of returns from several asset classes.
The source for this collection includes data from the sites below:
http://www.tamasset.com/other/AC2705.xls
http://www.fpanet.org/journal/articles/ ... 6-art7.cfm
https://flagship.vanguard.com/VGApp/hnw/FundsByType
In order to compile a data set that is as realistic as possible I use the actual yearly total return from Vanguard Index Funds for as far back as I can easily get access to the historical return data on the Vanguard Website. 15 Years History is the max that Vanguard has available under the funds performance link.
For US MicroCaps and Commodities I used the Bridgeway BRSIX and Pimco PCRIX Funds for actual returns to the extent of the funds history.
Below list the Abbreviations (column heading in my Excel Sheet) and a summary of the data sources by year range.
MKT = US Cap Weighted Market:
=============================
CRSP Market Decile 1-10 1972-1992
Vanguards Total Stock Market Index Fund 1993-2006
LCB = US Large Blend:
=====================
S&P 500: Standard & Poors 1972-1991
Vanguards 500 Index Fund 1992-2006
LCV = US Large Value:
=====================
Fama and French 1972–1978
Russell 1000 Value Index 1979–1992
Vanguards Value Index Fund 1993-2006
LCG = US Large Growth:
======================
Fama and French 1972–1978
Russell 1000 Growth Index 1979–1992
Vanguards Growth Index Fund 1993-2006
MCB = US Mid Blend:
===================
CRSP Decile 3-5 1972-1978
Russell Mid Cap Index 1979-1998
Vanguards Mid Cap Index Fund 1999-2006
SCB = US Small Blend:
=====================
Ibbotson 1972–1978
Russell 2000 Index 1979-1991
Vanguards Small Cap Index Fund 1992-2006
SCV = US Small Value:
=====================
Ibbotson 1972-1978
Russell 2000 Value Index 1979-1998
Vanguards Small Cap Value Index Fund 1999-2006
SCG = US Small Growth:
======================
Ibbotson 1972–1978
Russell 2000 Growth Index 1979-1998
Vanguards Small Cap Growth Index Fund 1999-2006
MICRO = US MicroCap Blend:
==========================
CRSP Decile 10 1972-1997
BRSIX Bridgeway Ultra Small Market 1998-2006
REIT = Real Estate:
===================
Nat. Assn. of Real Estate Inv Trusts 1972-1996
Vanguards REIT Index Fund – 1997-2006
INTLD = International Developed:
================================
MSCI EAFE Index 1972-2000
Vanguards Developed Market Index Fund 2001-2006
INTLT - Total International:
============================
MSCI EAFE Index 1972-1987 (Developed Only)
85% EAFE Index, 15% Emerging Markets Index 1988-1996
Vanguards Total International Index Fund 1997-2006
COMM – Commodities / Natural Resources:
======================================
Standard & Poor's Commodity Index 1972-2002
Pimco PCRIX 2003-2006
5YT – InterTerm Treasury:
=========================
Tamasset Spreadsheet 1972-1991
Vanguards InterTerm Treasury Fund 1992-2006
LTGB = Long Term Government Bonds:
==================================
Tamasset Spreadsheet 1972-1991
Vanguards Long Term Treasury 1992-2006
ITB = Intermediate Term Bonds:
==============================
Ibbotson 1972
Lehman Brothers 1973-1991
Vanguards Total Bond Index Fund 1992-2006
STIPS = Syn TIPS:
=================
Synthetic TIPS 1972-2000
Vanguard Inflation Protected Securities Fund 2001-2006
Risk Free Benchmark for Sharpe:
===============================
T-Bills 1972-1991
Vanguard Treasury Money Market Fund 1992-2006
Note on “Total International” – this is an attempt to get a more realistic picture of returns on international investments considering both developed and emerging markets by using Vanguards Total International Fund for real returns since inception 1997 and then including Developed/Emerging Markets at a 85%/15% ratio from 1988 to 1996 to simulate the ratio in Vanguards Total International Fund (which holds near 15% Emerging Markets). Although this category is called Total International, I do not have a source for Emerging Markets returns prior to 1988 so Emerging Market Data is not considered for the first 16 years of this data set.
=====
Where most of the above shows 1972-2006..... you can change that to 1970-2006 in what I just posted above.
The commodities are listed in that fpanet article as natural resources... and if you look at the end of that fpanet document... they give their data sources... and for Commodities it was S&P Commodity Index.
Where the returns for Mid Cap Blend were not available from the russell index data in that fpanet article... I used CRSP D3-5. The most recent returns are from VIMSX.
Microcaps D10 + BRSIX.
I try to use actual fund returns where available to make it more realistic.
Trev H
-
TheEternalVortex
- Posts: 2583
- Joined: Tue Feb 27, 2007 8:17 pm
- Location: San Jose, CA
IF you assume that SD is a good measure of risk then the argument I gave applies.BrianTH wrote:That argument assumes that standard deviation is the only relevant measure of risk, an assumption I would personally not make.TheEternalVortex wrote:I don't know if this data is very useful. For example, in that time period LV outperformed US market with a lower standard deviation. It's unlikely that will continue as otherwise there is no sense in investing the US market.
IF you assume that SD is not a good measure of risk then the data is still not useful because the only measure of risk it gives is SD.
Re: fpanet...
Maybe you want to include raddr's CCF table:Trev H wrote: The commodities are listed in that fpanet article as natural resources... and if you look at the end of that fpanet document... they give their data sources... and for Commodities it was S&P Commodity Index.
http://raddr-pages.com/research/CommodityFutures.htm
Raddr uses equal-weighted commodities index (1961-2003). I'd bet those will be more similar to DJAIG (which PCRIX and DJP are based on) than the S&P index. I think you might have to add T-Bills to those returns to get a CCF Index as opposed to just CF Index.
Raddr...
Raddr
Commodities
3.90 1970
6.20
16.20
33.30
26.40
13.60
-6.20
-4.60
1.20
11.70
10.70
-7.30
-13.00
-8.60
0.90
3.80
-1.40
5.50
7.00
17.50
19.40
11.30
2.50
-4.80
1.90
7.30
15.40
8.60
-6.70
-6.20
3.70
7.20
7.80
6.00 2003
I put them beside the S&P Commodities in a spreadsheet and did a comparison.
The Stats...
1970-2003
S&P Commodities
106,873.77 = 10K Growth
24.88 = StDev
7.22 = CAGR
-0.2332 = Correlation to 500 Idx
Raddr Commodities
54,811.47 = 10K Growth
10.15 = StDev
5.13 = CAGR
-0.1946 = Correlation to 500 Idx
It would be nice to have a good source of Data for Each to finish it up thru 2006.
I added PCRIX to the S&P commodities... but PCRIX is a large part TIPS.
The S&P and Raddr data is quite different... but both do show similar correlations to US Large Blend.
Trev H
Commodities
3.90 1970
6.20
16.20
33.30
26.40
13.60
-6.20
-4.60
1.20
11.70
10.70
-7.30
-13.00
-8.60
0.90
3.80
-1.40
5.50
7.00
17.50
19.40
11.30
2.50
-4.80
1.90
7.30
15.40
8.60
-6.70
-6.20
3.70
7.20
7.80
6.00 2003
I put them beside the S&P Commodities in a spreadsheet and did a comparison.
The Stats...
1970-2003
S&P Commodities
106,873.77 = 10K Growth
24.88 = StDev
7.22 = CAGR
-0.2332 = Correlation to 500 Idx
Raddr Commodities
54,811.47 = 10K Growth
10.15 = StDev
5.13 = CAGR
-0.1946 = Correlation to 500 Idx
It would be nice to have a good source of Data for Each to finish it up thru 2006.
I added PCRIX to the S&P commodities... but PCRIX is a large part TIPS.
The S&P and Raddr data is quite different... but both do show similar correlations to US Large Blend.
Trev H
VFINX returns since inception
Trev,
Vanguard 500 Index returns since inception can be found here
Year,VFINX
1976,5.3
1977,-7.8
1978,5.9
1979,18
1980,31.9
1981,-5.2
1982,20.9
1983,21.3
1984,6.21
1985,31.23
1986,18.06
1987,4.71
1988,16.22
1989,31.36
1990,-3.32
1991,30.22
1992,7.42
1993,9.89
1994,1.18
1995,37.45
1996,22.88
1997,33.19
1998,28.62
1999,21.07
2000,-9.06
2001,-12.02
2002,-22.15
2003,28.5
2004,10.74
2005,4.77
2006,15.64
Vanguard 500 Index returns since inception can be found here
Year,VFINX
1976,5.3
1977,-7.8
1978,5.9
1979,18
1980,31.9
1981,-5.2
1982,20.9
1983,21.3
1984,6.21
1985,31.23
1986,18.06
1987,4.71
1988,16.22
1989,31.36
1990,-3.32
1991,30.22
1992,7.42
1993,9.89
1994,1.18
1995,37.45
1996,22.88
1997,33.19
1998,28.62
1999,21.07
2000,-9.06
2001,-12.02
2002,-22.15
2003,28.5
2004,10.74
2005,4.77
2006,15.64
Hey Guys...
Cb emailed me that I had pulled the commodities returns from a table that was showing 3 year annualized returns... not annual returns.
I looked thru that Raddr document again and found my way to the annual returns. Listing them below.
Please double check me on this.
1970-1990 that Raddr document uses Chase Physical Commodities Index
Then 1991-present uses DJ-AIG.... I also found my way to a table that list the Total Return for DJ-AIG returns and listed them below.
Chase Physical Commodity Index Return Characteristics
Total Return
============
11.65 1970
11.98
39.43
69.14
04.84
05.85
-4.81
03.79
27.11
35.00
06.86
-21.78
04.58
12.98
-2.77
13.77
-5.45
18.65
19.24
28.46
25.87
-3.67
04.05
-1.35
09.21
17.09
40.29
-3.07
-29.45
29.63 1999
Perhaps we could put together a good Annual Return for Commodities from these two sources ?
Let me know what you think.
I have not had time this morning to do a comparison between this data and what the fpanet article listed for S&P Commodities index returns.
Will do that when I get a chance.
Trev H
Cb emailed me that I had pulled the commodities returns from a table that was showing 3 year annualized returns... not annual returns.
I looked thru that Raddr document again and found my way to the annual returns. Listing them below.
Please double check me on this.
1970-1990 that Raddr document uses Chase Physical Commodities Index
Then 1991-present uses DJ-AIG.... I also found my way to a table that list the Total Return for DJ-AIG returns and listed them below.
Chase Physical Commodity Index Return Characteristics
Total Return
============
11.65 1970
11.98
39.43
69.14
04.84
05.85
-4.81
03.79
27.11
35.00
06.86
-21.78
04.58
12.98
-2.77
13.77
-5.45
18.65
19.24
28.46
25.87
-3.67
04.05
-1.35
09.21
17.09
40.29
-3.07
-29.45
29.63 1999
Code: Select all
Dow Jones AIG Yearly Returns
Year Annual Total Return: Annual Excess Return:
====================================================
Year Index Value Return (%) Index Value Return (%)
2007 332.246 5.80 173.215 4.03
2006 314.023 2.07 166.509 -2.71
2005 307.650 21.36 171.149 17.54
2004 253.495 9.15 145.604 7.64
2003 232.249 23.93 135.269 22.66
2002 187.401 25.91 110.276 23.86
2001 148.843 -19.51 89.033 -22.32
2000 184.917 31.84 114.613 24.21
1999 140.257 24.35 92.273 18.60
1998 112.796 -27.03 77.803 -30.52
1997 154.579 -3.39 111.984 -8.24
1996 160.001 23.17 122.044 16.99
1995 129.908 15.21 104.323 8.93
1994 112.755 16.61 95.772 11.73
1993 96.694 -1.07 85.716 -4.04
1992 97.736 3.70 89.324 0.13
1991 94.245 -5.75 89.210 -10.79
1990 100.00 100.00
Perhaps we could put together a good Annual Return for Commodities from these two sources ?
Let me know what you think.
I have not had time this morning to do a comparison between this data and what the fpanet article listed for S&P Commodities index returns.
Will do that when I get a chance.
Trev H
-
Random Musings
- Posts: 7102
- Joined: Thu Feb 22, 2007 3:24 pm
- Location: Pennsylvania
TrevH,
Thanks for the data set.
Would it be possible to add available asset classes back to inception points (when available)? From one of my old spreadsheets, I believe the synthetic S&P 500, Ibbotson Small Cap, Intermediate Bond, T-Bills, one-yr notes, inflation, HY LT corporate are available from 1926. International is trickier, but globalfinddata had some stuff back to 1926 (without dividends?).
RM
Thanks for the data set.
Would it be possible to add available asset classes back to inception points (when available)? From one of my old spreadsheets, I believe the synthetic S&P 500, Ibbotson Small Cap, Intermediate Bond, T-Bills, one-yr notes, inflation, HY LT corporate are available from 1926. International is trickier, but globalfinddata had some stuff back to 1926 (without dividends?).
RM
Updated backtesting Spreadsheet - Rev4
I updated the spreadsheet.
You can now
-change % allocations
-Change the number of years
Latest version can be found here
Regards,
Simba
You can now
-change % allocations
-Change the number of years
Latest version can be found here
Regards,
Simba
Hey Simba...
Great Work.
I would really like to see/have the best possible source for Internatinal LV and Small or Small Value Returns to add to my collection (and your sheet).
A little while back (on the old board) Eric/enels posted the details below.
====
Here are the annual returns starting in 2006 and going back to 1975 for Int'l Small and Int'l LV:
25.62 32.96
19.54 15.07
33.08 30.58
60.87 69.06
-3.70 -14.01
-17.81 -15.44
-11.38 3.95
33.13 32.83
9.15 22.97
-16.85 .30
4.40 10.21
2.66 10.27
15.91 16.15
34.39 46.87
-20.58 -9.86
5.83 9.75
-17.94 -21.63
30.77 18.16
25.94 38.60
40.67 35.02
59.54 65.37
67.50 55.11
11.62 9.01
36.12 29.35
.04 -2.20
.10 8.99
35.46 18.75
-.78 5.99
65.53 42.67
74.08 29.99
11.46 3.33
49.86 38.99
======
I looked around on the French site and found lots of details on International but I could not find (or figure out) how to take the data there and come up with what Eric posted above.
I bet Robert T... could help us out with getting some good returns for ILV (HmL) and possibly IS... SmB.
I think that is what Eric included above... but he did not say which was which so I'm not sure which was IS and which was ILV.
Perhaps Robert or Eric or someone that can figure out that from the French Data can verify or give us some Value and Small returns on the International Side.
Hope So !
Trev H
Great Work.
I would really like to see/have the best possible source for Internatinal LV and Small or Small Value Returns to add to my collection (and your sheet).
A little while back (on the old board) Eric/enels posted the details below.
====
Here are the annual returns starting in 2006 and going back to 1975 for Int'l Small and Int'l LV:
25.62 32.96
19.54 15.07
33.08 30.58
60.87 69.06
-3.70 -14.01
-17.81 -15.44
-11.38 3.95
33.13 32.83
9.15 22.97
-16.85 .30
4.40 10.21
2.66 10.27
15.91 16.15
34.39 46.87
-20.58 -9.86
5.83 9.75
-17.94 -21.63
30.77 18.16
25.94 38.60
40.67 35.02
59.54 65.37
67.50 55.11
11.62 9.01
36.12 29.35
.04 -2.20
.10 8.99
35.46 18.75
-.78 5.99
65.53 42.67
74.08 29.99
11.46 3.33
49.86 38.99
======
I looked around on the French site and found lots of details on International but I could not find (or figure out) how to take the data there and come up with what Eric posted above.
I bet Robert T... could help us out with getting some good returns for ILV (HmL) and possibly IS... SmB.
I think that is what Eric included above... but he did not say which was which so I'm not sure which was IS and which was ILV.
Perhaps Robert or Eric or someone that can figure out that from the French Data can verify or give us some Value and Small returns on the International Side.
Hope So !
Trev H
Intl LV/SV returns
Trev,
As per RobertT's pm - He got the returns from IFA Risk & Return Calculator.
Check this post of Robert's.
Regards,
Simba
As per RobertT's pm - He got the returns from IFA Risk & Return Calculator.
Check this post of Robert's.
Regards,
Simba
-
investorperson
- Posts: 35
- Joined: Wed Feb 28, 2007 12:31 am
Question on Initial $10K Investment Cell X5 Returns Sheet
Thanks so much Simba for putting this together!
This might be a stupid question (I'm learning here) -
Why does the CAGR change if I change my initial $10,000 investment (Cell X5 in the Returns sheet). Shouldn't it stay the same no matter how much your initial investment is?
I'm trying to tell myself, well, if in 1972 I started with $##,###, how much would I have today using this allocation, or that allocation, etc...
This might be a stupid question (I'm learning here) -
Why does the CAGR change if I change my initial $10,000 investment (Cell X5 in the Returns sheet). Shouldn't it stay the same no matter how much your initial investment is?
I'm trying to tell myself, well, if in 1972 I started with $##,###, how much would I have today using this allocation, or that allocation, etc...
Re: Question on Initial $10K Investment Cell X5 Returns Shee
Because $10,000 is embedded in the calculation:investorperson wrote:Thanks so much Simba for putting this together!
This might be a stupid question (I'm learning here) -
Why does the CAGR change if I change my initial $10,000 investment (Cell X5 in the Returns sheet). Shouldn't it stay the same no matter how much your initial investment is?
I'm trying to tell myself, well, if in 1972 I started with $##,###, how much would I have today using this allocation, or that allocation, etc...
=RATE($Y$2,0,-10000,B42)*100
(click cell C42 of the Returns worksheet & look up at the formula bar)
Cb
Thanks Cb
Investorperson - There's no such thing as a stupid question. Your question helped make the spreadsheet better.
I've uploaded rev4a that can be downloaded here
It now calculates the CAGR based on the initial investment (cell X4) instead of a fixed 10000.
There was another flaw in the formula for calculating the totals, that was fixed as well.
Regards,
Simba
I've uploaded rev4a that can be downloaded here
It now calculates the CAGR based on the initial investment (cell X4) instead of a fixed 10000.
There was another flaw in the formula for calculating the totals, that was fixed as well.
Regards,
Simba
-
Eric White
- Posts: 54
- Joined: Fri May 18, 2007 10:09 am
How to post updated spreadsheet for efficient frontiers
Simba & TrevH:
I was interested in using the spreadsheet to determine efficient frontiers based on risk levels last night. There are a few minor structural changes I had to make to enable it, but it works great now!
There were several very interesting results. Of course, these are static frontiers based on this relatively short horizon and thus have limited implications. However, I would still like to discuss these results in this thread.
Let me know if you would like me to post it. I'm not sure what process you used, Simba, but I would appreciate it if you could let me know.
Thanks!
-Eric White
I was interested in using the spreadsheet to determine efficient frontiers based on risk levels last night. There are a few minor structural changes I had to make to enable it, but it works great now!
There were several very interesting results. Of course, these are static frontiers based on this relatively short horizon and thus have limited implications. However, I would still like to discuss these results in this thread.
Let me know if you would like me to post it. I'm not sure what process you used, Simba, but I would appreciate it if you could let me know.
Thanks!
-Eric White
Re: How to post updated spreadsheet for efficient frontiers
Eric, I would be very interested to look at your work. Unfortunately I can't help you with how to make it available online.Eric White wrote:Simba & TrevH:
Let me know if you would like me to post it. I'm not sure what process you used, Simba, but I would appreciate it if you could let me know.
Thanks!
-Eric White
Regards, gbs
P.S. I sent you a PM with my e-mail if you like to e-mail it to me.
-
Eric White
- Posts: 54
- Joined: Fri May 18, 2007 10:09 am
Excel Solver version for efficient frontier calculations
GBS and/or Simba:
I created a Google group so that I can post files.
As this is my first attempt, try to download the file HERE and confirm if the download worked.
If so, I may try to incorporate Simba's Rev 5 changes so that we are working off the most recent common file.
Rev 6 will incorporate Int'l LV & Int'l SV
I created a Google group so that I can post files.
As this is my first attempt, try to download the file HERE and confirm if the download worked.
If so, I may try to incorporate Simba's Rev 5 changes so that we are working off the most recent common file.
Rev 6 will incorporate Int'l LV & Int'l SV
Updated backtesting Spreadsheet - Rev5a
Eric,
I downloaded your spreadsheet as well but couldn't figure out what you did.
Cb - provided the data for Pacific/Europe & Intl Value from MSCI barra website. Alec had earlier provided the Vanguard returns.
The new spreadsheet now includes 2 returns (1972-2006 & 1985-2006). Certain funds are only included in the 1985-2006 returns.
Most of the worksheets are locked (no password).
One can change the following in the worksheet Portfolio
1 - % allocation of funds
2 - Current Year (Could be 1972-2006 or 1985-2006)
3 - Number of Years you want to backtest (1-35 or 1-22)
4 - Initial Investment
Updated Spreadsheet - rev5a can be downloaded here
Regards,
Simba
I downloaded your spreadsheet as well but couldn't figure out what you did.
Cb - provided the data for Pacific/Europe & Intl Value from MSCI barra website. Alec had earlier provided the Vanguard returns.
The new spreadsheet now includes 2 returns (1972-2006 & 1985-2006). Certain funds are only included in the 1985-2006 returns.
Most of the worksheets are locked (no password).
One can change the following in the worksheet Portfolio
1 - % allocation of funds
2 - Current Year (Could be 1972-2006 or 1985-2006)
3 - Number of Years you want to backtest (1-35 or 1-22)
4 - Initial Investment
Updated Spreadsheet - rev5a can be downloaded here
Regards,
Simba
Simba,
Your spreadsheets are great! Thank you!
Maybe you can help me with a problem I found. I've just done a run with Rev-5a, compared it to a run with Rev-2, and found different results. I used the "Returns_72_06" and "Returns" tabs to enter allocations and review results.
I traced one possible cause for the difference to a variation in results for LCV. Although the data tables in each Rev shows identical data from 72 through 06, identical asset allocations in Rev-2 and Rev-5a does not result in identical returns for the LCV class. Is that a bug?
Also, returns data for some of the other asset classes disagree. Do you recommend not using Rev-2 and instead using Rev-5a?
Thanks again.
Your spreadsheets are great! Thank you!
Maybe you can help me with a problem I found. I've just done a run with Rev-5a, compared it to a run with Rev-2, and found different results. I used the "Returns_72_06" and "Returns" tabs to enter allocations and review results.
I traced one possible cause for the difference to a variation in results for LCV. Although the data tables in each Rev shows identical data from 72 through 06, identical asset allocations in Rev-2 and Rev-5a does not result in identical returns for the LCV class. Is that a bug?
Also, returns data for some of the other asset classes disagree. Do you recommend not using Rev-2 and instead using Rev-5a?
Thanks again.
-
investorperson
- Posts: 35
- Joined: Wed Feb 28, 2007 12:31 am
Yes, this is great Simba. You're the best.
Suggestion
- For data that we can change, make the cells blue . For formulas/data we shouldn't change, leave it black. This is standard practice in the finance world I believe. (I'm not in finance.) That way, users know, "OK, I can change blue."
Question
- What do the bold numbers mean in the Data sheets?
Suggestion
- For data that we can change, make the cells blue . For formulas/data we shouldn't change, leave it black. This is standard practice in the finance world I believe. (I'm not in finance.) That way, users know, "OK, I can change blue."
Question
- What do the bold numbers mean in the Data sheets?
baldeagle,baldeagle wrote:Simba,
Your spreadsheets are great! Thank you!
Maybe you can help me with a problem I found. I've just done a run with Rev-5a, compared it to a run with Rev-2, and found different results. I used the "Returns_72_06" and "Returns" tabs to enter allocations and review results.
I traced one possible cause for the difference to a variation in results for LCV. Although the data tables in each Rev shows identical data from 72 through 06, identical asset allocations in Rev-2 and Rev-5a does not result in identical returns for the LCV class. Is that a bug?
Also, returns data for some of the other asset classes disagree. Do you recommend not using Rev-2 and instead using Rev-5a?
Thanks again.
Thanks for the compliment. I think you did find an error in rev2. I went back and checked and there's was an error in formula for calculating returns for LCV. Once I corrected the issue I got similar numbers for rev2 and rev5a/rev5b.
I would recommend using the latest version (currently rev5b).
investorperson,investorperson wrote:Yes, this is great Simba. You're the best.
Suggestion
- For data that we can change, make the cells blue . For formulas/data we shouldn't change, leave it black. This is standard practice in the finance world I believe. (I'm not in finance.) That way, users know, "OK, I can change blue."
Question
- What do the bold numbers mean in the Data sheets?
Thanks for the suggestion which I think is great and makes it easy to identify which cells can be modified. One can download rev5b Here.
The bold in Data sheets indicate (in most cases) that the actual Vanguard fund returns were included since that year.
Regards,
Simba
-
investorperson
- Posts: 35
- Joined: Wed Feb 28, 2007 12:31 am
Simba,
I may have caught an error.
Returns_85_06 Columns Z and AA (Energy, Health) feed off of Data_85_06 Columns Z and AA. However these data are for S-Tips and Wellington, not Energy, Health.
If I'm right in thinking that this is an error,
I believe it's the Energy, Health, P.Metals columns that don't match between Returns_85_06 and Data_85_06 that are causing things to shift.
Yes, No, Maybe So?
One of these days I have to learn all those formulas you use in Excel.
I may have caught an error.
Returns_85_06 Columns Z and AA (Energy, Health) feed off of Data_85_06 Columns Z and AA. However these data are for S-Tips and Wellington, not Energy, Health.
If I'm right in thinking that this is an error,
I believe it's the Energy, Health, P.Metals columns that don't match between Returns_85_06 and Data_85_06 that are causing things to shift.
Yes, No, Maybe So?
One of these days I have to learn all those formulas you use in Excel.
-
investorperson
- Posts: 35
- Joined: Wed Feb 28, 2007 12:31 am
Also, Readme file example needs to be updated:
For example
To calculate the returns from 1981-1990
Readme Says:
change cell AB2 to 10 in Returns_72_06
change cell AC2 to 1990 in Returns_72_06
The spreadsheet would return the porfolio return over the period 1981-1990.
With Revision 5b one no longer updates the Sheet Returns_72_06.
This is like updating a software product isn't it?
For example
To calculate the returns from 1981-1990
Readme Says:
change cell AB2 to 10 in Returns_72_06
change cell AC2 to 1990 in Returns_72_06
The spreadsheet would return the porfolio return over the period 1981-1990.
With Revision 5b one no longer updates the Sheet Returns_72_06.
This is like updating a software product isn't it?
investorperson,investorperson wrote:Simba,
I may have caught an error.
Returns_85_06 Columns Z and AA (Energy, Health) feed off of Data_85_06 Columns Z and AA. However these data are for S-Tips and Wellington, not Energy, Health.
If I'm right in thinking that this is an error,
I believe it's the Energy, Health, P.Metals columns that don't match between Returns_85_06 and Data_85_06 that are causing things to shift.
Yes, No, Maybe So?
One of these days I have to learn all those formulas you use in Excel.
Thanks for pointing out the error. I verified the links and corrected the spreadsheet. Latest version rev5d can be downloaded here
Thanks very much for posting this. I've found it highly useful.
I added Short-Term Treasurys data to my copy of the spreadsheet, because I've found it to be a fantastic diversifier. The 1972-2006 returns (if anyone else would like to paste them into their spreadsheet) are as follows:
3.9
6.1
9.1
7.9
8.9
3.7
5.5
10.4
14.1
18.9
19.5
8.6
12.8
13.2
11.9
6
5.9
8.7
8.9
10.7
6.75
6.41
-0.58
12.11
4.39
6.39
7.22
2.07
9.18
8.61
7.61
1.99
1.36
1.8
4.32
I added Short-Term Treasurys data to my copy of the spreadsheet, because I've found it to be a fantastic diversifier. The 1972-2006 returns (if anyone else would like to paste them into their spreadsheet) are as follows:
3.9
6.1
9.1
7.9
8.9
3.7
5.5
10.4
14.1
18.9
19.5
8.6
12.8
13.2
11.9
6
5.9
8.7
8.9
10.7
6.75
6.41
-0.58
12.11
4.39
6.39
7.22
2.07
9.18
8.61
7.61
1.99
1.36
1.8
4.32
Last edited by johnb on Sat May 26, 2007 6:13 pm, edited 1 time in total.
john,johnb wrote:Thanks very much for posting this. I've found it highly useful.
I added Short-Term Treasuries data to my copy of the spreadsheet, because I've found it to be a fantastic diversifier.
The Treasury Bill returns are already included in the spreadsheet. The corresponding Vanguard Fund VMPXX was started in 1983 hence its returns were included since 1984. Spreadsheet includes Tbills from 1972-1983 & VMPXX from 1984-2006.
-
SoonerSunDevil
- Posts: 2000
- Joined: Mon Feb 19, 2007 9:32 pm
- Location: The desert
-
SoonerSunDevil
- Posts: 2000
- Joined: Mon Feb 19, 2007 9:32 pm
- Location: The desert
Yes, in my accounting job blue numbers are manually updated, whereas black ones are permanent formulas, or update automatically.investorperson wrote:Yes, this is great Simba. You're the best.
Suggestion
- For data that we can change, make the cells blue . For formulas/data we shouldn't change, leave it black. This is standard practice in the finance world I believe. (I'm not in finance.) That way, users know, "OK, I can change blue."
Question
- What do the bold numbers mean in the Data sheets?
John