Spreadsheet for backtesting (includes TrevH's data)

[simba]

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]

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

[norm]

Simba,

I get nothing when I tried to open the link.

[simba]

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

[Pangloss]

Wow! This is fantastic. I'm sure I'll spend more time than I should running numbers. Thanks very much!

[Murray Boyd]

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]

It works now, thanks.

I don't see any numbers for LB, is it possible to add them?

[ndchamp]

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

[simba]

Looks neat.

I think the EAFE, EM, EAFE/EM numbers don't make sense from 1997 on. EAFE is the same as EAFE/EM.

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]).

Norm - For LCB you can use 500 Idx (VFINX)

Nelson - I updated the spreadsheet and included Wellington & Wellesley

Regards
Simba

[Fclevz]

Are the EAFE returns on the spreadsheet shown with gross dividends or net dividends?

Cheers,
Fred

[gbs]

Gread work Simba!

How about adding the link to a new tread in the Library?

gbs

[alec]

It works now, thanks.

I don't see any numbers for LB, is it possible to add them?

Norm,

Do you mean long term bonds [Treas + Corp]?

- Alec

[TheEternalVortex]

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.

[norm]

It works now, thanks.

I don't see any numbers for LB, is it possible to add them?

Norm,

Do you mean long term bonds [Treas + Corp]?

- Alec

No I meant larged balanced.

[Cb]

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.

OK...scratch LV. Are there any other asset classes for which historical data will prove to be meaningless?

Cb

[BrianTH]

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.

That argument assumes that standard deviation is the only relevant measure of risk, an assumption I would personally not make.

[simba]

Fred - AFAIK the MSCI returns are net returns.

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).

Vanguard's website lists:

Index returns are adjusted for withholding taxes applicable to Luxembourg holding companies.

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.

Bill Bernstein in this article Are Value Stocks Riskier than Growth talks about other risks besides standard deviation.

[Trev H]

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

[Trev H]

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

[Cb]

Trev, what is your source for Commodities returns?

Cb

[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]

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.

That argument assumes that standard deviation is the only relevant measure of risk, an assumption I would personally not make.

IF you assume that SD is a good measure of risk then the argument I gave applies.
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.

[sterjs]

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.

Maybe you want to include raddr's CCF table:
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.

[Trev H]

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

[simba]

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

[Trev H]

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

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]

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

[simba]

I updated the spreadsheet.

You can now
-change % allocations
-Change the number of years

Latest version can be found here


Regards,
Simba

[Trev H]

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

[simba]

Trev,

As per RobertT's pm - He got the returns from IFA Risk & Return Calculator.

Check this post of Robert's.


Regards,
Simba

[investorperson]

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...

[Cb]

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...

Because $10,000 is embedded in the calculation:

=RATE($Y$2,0,-10000,B42)*100

(click cell C42 of the Returns worksheet & look up at the formula bar)

Cb

[simba]

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

[Eric White]

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

[gbs]

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

Eric, I would be very interested to look at your work. Unfortunately I can't help you with how to make it available online.

Regards, gbs

P.S. I sent you a PM with my e-mail if you like to e-mail it to me.

[Eric White]

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

[gbs]

Eric,

I was able to download but I don't know how to use your spreadsheet to compute efficient frontiers.

gbs

[simba]

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

[baldeagle]

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.

[investorperson]

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?

[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.

baldeagle,

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).

[simba]

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?

investorperson,

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]

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.

[investorperson]

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?

[jrj]

This is a very useful project - especially for non-Excel jocks - thanks to Simba and other contributors. Not sure if I'm interpreting Eric White's (wink) correctly, but Int'l LV, SV, and access to Efficient Frontiers would also be great.

[simba]

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.

investorperson,

Thanks for pointing out the error. I verified the links and corrected the spreadsheet. Latest version rev5d can be downloaded here

[johnb]

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

[simba]

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.

john,

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]

If anyone points me in the right direction towards high-yield bonds, I'd be happy to add the data to the sheets. While I currently don't own any high yield bonds, I would like to see for myself how they would have behaved in some sample portfolios.

John

[SoonerSunDevil]

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?

Yes, in my accounting job blue numbers are manually updated, whereas black ones are permanent formulas, or update automatically.

John

[gbs]

johnb,

what is your source?

gbs