Spreadsheet for backtesting (includes TrevH's data)
 Barry Barnitz
 Wiki Admin
 Posts: 2870
 Joined: Mon Feb 19, 2007 10:42 pm
 Contact:
Asset Class Returns:
We have asset class returns for the fiat currency era (19702008) for gold and other asset classes on the wiki:
Asset Class Returns
Gold Bullion Prices 1970  2008
regards,
Asset Class Returns
Gold Bullion Prices 1970  2008
regards,
Thanks. I didn't realize how easy it was to add new assets. Incredible work simba.
I notice there is some differences in gold prices from various data sources and therefore calculated gold returns.
Finfacts
http://www.finfacts.ie/Private/curency/ ... tprice.htm
Only Gold
http://www.onlygold.com/TutorialPages/p ... 0yrsfs.htm
I notice there is some differences in gold prices from various data sources and therefore calculated gold returns.
Finfacts
http://www.finfacts.ie/Private/curency/ ... tprice.htm
Only Gold
http://www.onlygold.com/TutorialPages/p ... 0yrsfs.htm
Hi,
A Google Docs version of the spreadsheet was made that includes gold. See this prior post in this thread: Paul Douglas Boyer Posted: Fri Feb 06, 2009 9:23 pm. I think a google account is required.
Don
What happened to gold? I thought gold was added to the spreadsheet so that Harry Browne permanent portfolio could be compared.
A Google Docs version of the spreadsheet was made that includes gold. See this prior post in this thread: Paul Douglas Boyer Posted: Fri Feb 06, 2009 9:23 pm. I think a google account is required.
Don
Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
Leif Eriksen wrote:Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
I use the AvgAvg for the CPIU numbers.
CPIU for 2008= (Annual Avg for 2008  Annual Avg for 2007)/(Annual Avg for 2007)
CPIU for 2008 = (215.303  207.342) / 207.342 = 0.03839 = 3.84%
Best Regards,
Simba
simba wrote:Leif Eriksen wrote:Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
I use the AvgAvg for the CPIU numbers.
CPIU for 2008= (Annual Avg for 2008  Annual Avg for 2007)/(Annual Avg for 2007)
CPIU for 2008 = (215.303  207.342) / 207.342 = 0.03839 = 3.84%
Best Regards,
Simba
I guess over time DecDec versus AvgAvg balances out.
 Paul Douglas Boyer
 Posts: 130
 Joined: Wed Mar 07, 2007 3:19 pm
 Location: Leesburg, VA
Some corrections
Simba, in your spreadsheet, here are some corrections:
Cell SP8508!C35 should read "=RATE(Portfolio!$K...
Cell SP8508!C36 should read "=RATE(Portfolio!$K...
Column P in SP8508 incorrectly points to 72 data.
Also,
I have the values for gold in the Google version of the spreadsheet at:
https://spreadsheets.google.com/ccc?key=pOjc3ot10vgs0emlDJZKcw&newcopy
I added a returns vs risk chart for 1972  2008
and compared against Harry Browne, Coffeehouse, Scott Burns Four Square, and all of the IFA Index Portfolios there.
Cell SP8508!C35 should read "=RATE(Portfolio!$K...
Cell SP8508!C36 should read "=RATE(Portfolio!$K...
Column P in SP8508 incorrectly points to 72 data.
Also,
I have the values for gold in the Google version of the spreadsheet at:
https://spreadsheets.google.com/ccc?key=pOjc3ot10vgs0emlDJZKcw&newcopy
I added a returns vs risk chart for 1972  2008
and compared against Harry Browne, Coffeehouse, Scott Burns Four Square, and all of the IFA Index Portfolios there.

 Posts: 56
 Joined: Sat Feb 14, 2009 2:46 am
Re: Some corrections
There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is newer than the YOI, the ER will not be deducted!
Paul Douglas Boyer wrote:Simba, in your spreadsheet, here are some corrections:
Cell SP8508!C35 should read "=RATE(Portfolio!$K...
Cell SP8508!C36 should read "=RATE(Portfolio!$K...
Column P in SP8508 incorrectly points to 72 data.
[/u]
Last edited by MachineGhost on Sat Feb 14, 2009 12:35 pm, edited 2 times in total.
Re: Some corrections
MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!
It looks like MachineGhost is right. The ER is not deducted.
I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.
simba wrote:Leif Eriksen wrote:Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
I use the AvgAvg for the CPIU numbers.
CPIU for 2008= (Annual Avg for 2008  Annual Avg for 2007)/(Annual Avg for 2007)
CPIU for 2008 = (215.303  207.342) / 207.342 = 0.03839 = 3.84%
Best Regards,
Simba
Hi Simba,
Here's a vote for using the more traditional definition of inflation i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was 37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was 37.1%, etc.
just my 2 cents,
cheers,
grok, CFA
 Paul Douglas Boyer
 Posts: 130
 Joined: Wed Mar 07, 2007 3:19 pm
 Location: Leesburg, VA
Re: Some corrections
grayfox wrote:MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!
It looks like MachineGhost is right. The ER is not deducted.
I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.
I have updated the Google version of the spreadsheet.

 Posts: 56
 Joined: Sat Feb 14, 2009 2:46 am
Re: Some corrections
I don't know if its semantics on yours or my part or whatever, but what I originally meant was that the ER is not being deducted from funds after they are started. i.e. if inception is 2004 for GLD, than the ER will not be deducted for 2005, 2006, 2007, and 2008, but will be deducted for 2004 and earlier years.
Although as you infer, the ER should be deducted for all years.
Maybe the implication was that the ER was automatically deducted from mutual funds total return as opposed to index composites, but that is not the case. Fees are a separate accounting on the yearly statement that is paid out the investor's own pocket, not the fund's. I am not sure about ETF's.
Although as you infer, the ER should be deducted for all years.
Maybe the implication was that the ER was automatically deducted from mutual funds total return as opposed to index composites, but that is not the case. Fees are a separate accounting on the yearly statement that is paid out the investor's own pocket, not the fund's. I am not sure about ETF's.
grayfox wrote:MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!
It looks like MachineGhost is right. The ER is not deducted.
I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.
Last edited by MachineGhost on Sat Feb 14, 2009 12:58 pm, edited 2 times in total.

 Posts: 56
 Joined: Sat Feb 14, 2009 2:46 am
I second this.
grok87 wrote:simba wrote:Leif Eriksen wrote:Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
I use the AvgAvg for the CPIU numbers.
CPIU for 2008= (Annual Avg for 2008  Annual Avg for 2007)/(Annual Avg for 2007)
CPIU for 2008 = (215.303  207.342) / 207.342 = 0.03839 = 3.84%
Best Regards,
Simba
Hi Simba,
Here's a vote for using the more traditional definition of inflation i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was 37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was 37.1%, etc.
just my 2 cents,
cheers,
grok87 wrote:simba wrote:Leif Eriksen wrote:Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
I use the AvgAvg for the CPIU numbers.
CPIU for 2008= (Annual Avg for 2008  Annual Avg for 2007)/(Annual Avg for 2007)
CPIU for 2008 = (215.303  207.342) / 207.342 = 0.03839 = 3.84%
Best Regards,
Simba
Hi Simba,
Here's a vote for using the more traditional definition of inflation i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was 37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was 37.1%, etc.
just my 2 cents,
cheers,
First of all there should be consistency. If average annual return series is used then average annual inflation series should be used.
But aside from that I believe it is mathematically more correct to use average numbers for a period. For instance if you have monthly data you would use monthly average rather than picking a one day like the last day of the month. Otherwise you get biases in the data caused by aliasing. In more simple terms, the average for a month is more representative of that month that a particular day.
So for annual data the series to use should be annual average.

 Posts: 56
 Joined: Sat Feb 14, 2009 2:46 am
But annual data isn't an average, it is ( 12/31/xx divided by 12/31/xx1 ) 1 or YTD as of 12/31/xx.
grayfox wrote:grok87 wrote:simba wrote:Leif Eriksen wrote:Not sure about your CPIU numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link
ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
I use the AvgAvg for the CPIU numbers.
CPIU for 2008= (Annual Avg for 2008  Annual Avg for 2007)/(Annual Avg for 2007)
CPIU for 2008 = (215.303  207.342) / 207.342 = 0.03839 = 3.84%
Best Regards,
Simba
Hi Simba,
Here's a vote for using the more traditional definition of inflation i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was 37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was 37.1%, etc.
just my 2 cents,
cheers,
First of all there should be consistency. If average annual return series is used then average annual inflation series should be used.
But aside from that I believe it is mathematically more correct to use average numbers for a period. For instance if you have monthly data you would use monthly average rather than picking a one day like the last day of the month. Otherwise you get biases in the data caused by aliasing. In more simple terms, the average for a month is more representative of that month that a particular day.
So for annual data the series to use should be annual average.
MachineGhost wrote:But annual data isn't an average, it is ( 12/31/xx divided by 12/31/xx1 ) 1 or YTD as of 12/31/xx.
How do you know that? The spreadsheet data only has a number for each annual return. It doesn't calculate it from prices within the spreadsheet. You would have to go look at the data source and see how the return was calculated.
For stocks supposed you calculated the annual return as (P1P0+D0)/P0
The Ps could be the price on 12/31 or 7/1 or the average yearly price. It depends on the data source.
Now a typically person wants to know the return he got from one date to another like 12/31/07 to 12/31/08 so he would use the closing price on the last day of the year. But mathematically it results in biases in the statistics because one random day is not representative of the whole year. Pick a different day and you'll get different results.
On the other hand this spreadsheet is more about getting accurate statistics for different portfolios over various periods. So it probably is better to use annual averages rather than picking an arbitrary day.
Re: Some corrections
Paul Douglas Boyer wrote:Simba, in your spreadsheet, here are some corrections:
Cell SP8508!C35 should read "=RATE(Portfolio!$K...
Cell SP8508!C36 should read "=RATE(Portfolio!$K...
Column P in SP8508 incorrectly points to 72 data.
Also,
I have the values for gold in the Google version of the spreadsheet at:
https://spreadsheets.google.com/ccc?key=pOjc3ot10vgs0emlDJZKcw&newcopy
I added a returns vs risk chart for 1972  2008
and compared against Harry Browne, Coffeehouse, Scott Burns Four Square, and all of the IFA Index Portfolios there.
Thanks Paul  The next version has the corrections. I have added Gold as well.
grok87/grayfox/MachineGhost,
Earlier my thoughts on this was we use goods/services throughout the year so I used the avgavg prices to calculate inflation.
But as you have mentioned, the industry and Govt standard is to use DecDec prices to report inflation.
I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.
Best Regards,
Simba
Earlier my thoughts on this was we use goods/services throughout the year so I used the avgavg prices to calculate inflation.
But as you have mentioned, the industry and Govt standard is to use DecDec prices to report inflation.
I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.
Best Regards,
Simba
Re: Some corrections
grayfox wrote:MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!
It looks like MachineGhost is right. The ER is not deducted.
I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.
If the returns are for a mutual fund then the ER is already deducted. I deduct the ER if the returns are for the benchmark or not true fund returns.
Honestly this was not easy since the ER was not the same since 1972/1985. But the spreadsheet allows the user to change the ER and the fund returns are automatically updated based on the ER and YOI.
Best Regards,
Simba
 Paul Douglas Boyer
 Posts: 130
 Joined: Wed Mar 07, 2007 3:19 pm
 Location: Leesburg, VA
What Correlates With CPI?
What asset classes correlate closest with inflation (CPI)?
I took the CPI numbers and added them to the correlation matrix (19722008) in the spreadsheet, did a sort, and here is how it turns out:
(1 means perfect correlation, 0 means perfect noncorrelation. Note that a negative correlation means the investment goes down when inflation goes up, right? So bet against it in times of inflation. )
Is the take away here that if one enters a long period of higher inflation that the asset classes of TBills, Gold, and shorting the LongTerm bond would work best? Or might stocks still outperform even though they are not as closely correlated?
Or what else can we take away from these correlation stats as pertains to investment performance?
I took the CPI numbers and added them to the correlation matrix (19722008) in the spreadsheet, did a sort, and here is how it turns out:
(1 means perfect correlation, 0 means perfect noncorrelation. Note that a negative correlation means the investment goes down when inflation goes up, right? So bet against it in times of inflation. )
Code: Select all
Asset Class Ticker Correlation
TBILL VMPXX 0.63
GOLD GOLD 0.52
LTGB VUSTX 0.40
ST Trsry VFISX 0.28
Commodities PCRIX 0.25
5 Yr T VFITX 0.24
Wellesley VWINX 0.21
Wellington VWELX 0.16
SCG VISGX 0.13
Total Bond VBMFX 0.12
Small Cap NAESX 0.11
EAFED VDMIX 0.10
Europe VEURX 0.10
Intl Value VTRIX 0.10
EAFE85/EM15 EAFE/EM 0.09
500 Idx VFINX 0.09
LCG VIGRX 0.07
Total Market US VTSMX 0.06
Simulated TIPS STIPS 0.06
Pacific VPACX 0.06
EM VEIEX 0.06
LCV VIVAX 0.04
SCV VISVX 0.04
REIT VGSIX 0.01
Windsor VWNDX 0.01
Mid Cap VIMSX 0.01
Micro Cap BRSIX 0.00
Is the take away here that if one enters a long period of higher inflation that the asset classes of TBills, Gold, and shorting the LongTerm bond would work best? Or might stocks still outperform even though they are not as closely correlated?
Or what else can we take away from these correlation stats as pertains to investment performance?
simba wrote:grok87/grayfox/MachineGhost,
Earlier my thoughts on this was we use goods/services throughout the year so I used the avgavg prices to calculate inflation.
But as you have mentioned, the industry and Govt standard is to use DecDec prices to report inflation.
I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.
Best Regards,
Simba
Great Simba thanks!
cheers,
grok, CFA
Re: What Correlates With CPI?
Paul Douglas Boyer wrote:What asset classes correlate closest with inflation (CPI)?
Paulthat is an interesting question, but this is the wrong thread to discuss it. This thread is really about updates to the backtest spreadsheet, fixing errors, etc.
I would suggest starting a new thread and posting those results. Make it a poll. It is worthy of discussion.
simba wrote:I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.
Simba, did you have any luck completing your updates? I'm particularly interested in investigating the addition of a small amount of gold to a portfolio over time. Thanks!
I've updated the spreadsheet and for those interested, You can download the Excel Spreadsheet [rev8c] and/or OpenOffice version [rev8c]
Changes include  Added Gold returns, Now using monthmonth CPI instead of avgavg. New worksheet for comparing Lazy Portfolios.
Sorry for the delay but have been concentrating on the big rocks in my Life (DW gave birth to a precious little girl)
Best Regards
Simba
Changes include  Added Gold returns, Now using monthmonth CPI instead of avgavg. New worksheet for comparing Lazy Portfolios.
Sorry for the delay but have been concentrating on the big rocks in my Life (DW gave birth to a precious little girl)
Best Regards
Simba
simba wrote:I've updated the spreadsheet and for those interested, You can download the Excel Spreadsheet [rev8c] and/or OpenOffice version [rev8c]
Changes include  Added Gold returns, Now using monthmonth CPI instead of avgavg. New worksheet for comparing Lazy Portfolios.
Sorry for the delay but have been concentrating on the big rocks in my Life (DW gave birth to a precious little girl)
Best Regards
Simba
Thanks for the update!
BTW, where did you get the returns for Gold? I don't see it in the data sources worksheet.
Awesome sheet! Other asset classes?
Awesome work on the spreadsheet. Is the data available for the following asset classes:
Corporate Bond (investment grade)
International Bond
International Small Cap Value and/or International Small Cap
Corporate Bond (investment grade)
International Bond
International Small Cap Value and/or International Small Cap

 Posts: 1
 Joined: Sat Apr 04, 2009 12:55 pm
OpenOffice 3.01 Problem?
Thanks very much for this tool. Very helpful!
I seem to be having one minor problem with the .ods version using OpenOffice 3.0.1... the 3 pairs of graphs between lines 69 and 145 of the Portfolio worksheet contain no data or xaxis labels. Is this possibly a version incompatibility?
Regards,
OldDog
I seem to be having one minor problem with the .ods version using OpenOffice 3.0.1... the 3 pairs of graphs between lines 69 and 145 of the Portfolio worksheet contain no data or xaxis labels. Is this possibly a version incompatibility?
Regards,
OldDog
Simba, please correct a couple of errors in your excellent spreadsheet.
Portfolio Sheet: The CHP captions in the left two graphs reference the older Returns 7207 sheet.
Returns 7208 sheet: Cell AN43 is blank and needs to be filled in.
Portfolio Sheet: The CHP captions in the left two graphs reference the older Returns 7207 sheet.
Returns 7208 sheet: Cell AN43 is blank and needs to be filled in.
"I'm not so much concerned about the return on my money as the return of my money"  Will Rogers
Hi Simba,
I have been using your latest version 8c. I know how burdensome upgrading and debugging a major enterprise like your spreadsheet can be. I have noticed various bugs and problems and I have dusted off my rusty 'ol Excel skills and just started corrections myself. However, it would be good to keep the application together so I wanted to ask if you would like some help like maybe we could coauthor or if you are weary with the project I would be interested in picking up the load for you. At any rate if you just wanted to use my help I would have to start a log book and all. My offer is all just in the spirit of fun of course. Let me know.
Thanks,
I have been using your latest version 8c. I know how burdensome upgrading and debugging a major enterprise like your spreadsheet can be. I have noticed various bugs and problems and I have dusted off my rusty 'ol Excel skills and just started corrections myself. However, it would be good to keep the application together so I wanted to ask if you would like some help like maybe we could coauthor or if you are weary with the project I would be interested in picking up the load for you. At any rate if you just wanted to use my help I would have to start a log book and all. My offer is all just in the spirit of fun of course. Let me know.
Thanks,
Chas

 The course of true love never did run smooth. Shakespeare
Updated version rev8d
An updated version rev8d is now available for those interested.
You can download the Excel Spreadsheet [rev8d] and OpenOffice version [rev8d]
This version includes some bug fixing ; updated sources
MrMatt2532  I am still looking into the Sortino ratio issue you PM'd me about. Although to be consistent I am using the MAR across the board in the rev8d spreadsheet.
Best Regards,
Simba
You can download the Excel Spreadsheet [rev8d] and OpenOffice version [rev8d]
This version includes some bug fixing ; updated sources
MrMatt2532  I am still looking into the Sortino ratio issue you PM'd me about. Although to be consistent I am using the MAR across the board in the rev8d spreadsheet.
Best Regards,
Simba
Updated version rev8e
An updated version rev8e is now available for those interested.
You can download the Excel Spreadsheet [rev8e] and OpenOffice version [rev8e]
This version fixes the Sharpe Ratio formula to calculate the average of TBills for the time period being tested. It also fixes the returns for 2008 in Returns_85_08 worksheet.
Best Regards,
Simba
You can download the Excel Spreadsheet [rev8e] and OpenOffice version [rev8e]
This version fixes the Sharpe Ratio formula to calculate the average of TBills for the time period being tested. It also fixes the returns for 2008 in Returns_85_08 worksheet.
Best Regards,
Simba
Simba,
I don't know how I overlooked this for so long, but there is a mismatch of the two arrays in the correlation formulas for the calculation of the CHP and Scot Burns portfolios for both the U.S. and Int'l Market. The correlations as listed on the portfolio sheet of versions 8c, 8d, and 8e are grossly in error.
Sorry to be the bearer of bad news, for I know how frustrating it is to put out a new release and have a bug crop up the very next day or so. Still, I can't even begin to say how valuable your work has been to me.
Thanks,
I don't know how I overlooked this for so long, but there is a mismatch of the two arrays in the correlation formulas for the calculation of the CHP and Scot Burns portfolios for both the U.S. and Int'l Market. The correlations as listed on the portfolio sheet of versions 8c, 8d, and 8e are grossly in error.
Sorry to be the bearer of bad news, for I know how frustrating it is to put out a new release and have a bug crop up the very next day or so. Still, I can't even begin to say how valuable your work has been to me.
Thanks,
Chas     The course of true love never did run smooth. Shakespeare
An updated version rev8f is now available for those interested.
You can download the Excel Spreadsheet [rev8f] or the OpenOffice version [rev8f]
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. See the Revision History in the spreadsheet for complete list of changes
I am happy to inform that a user now has the ability to change the time period when comparing the portfolios in the Portfolio worksheet. Implemented the same feature for comparing the Lazy Portfolios.
Thanks to MrMatt2532 who informed me about the incorrect Sortino Ratio formula.
Chas  Hopefully you won't find any correlation issues in the new version
Feedback is very much appreciated (especially if its the +ve kind)
Edit: Sortino Ratio is incorrect in Returns_72_08 and Returns_85_08 (Missing the *100 in part of the formula) but the formula is correct in the rest of the spreadsheet. I fixed this prior to uploading the 8f version but it looks like I uploaded the incorrect version. To avoid confusion, I'll upload rev8g in couple of days.
Best Regards,
Simba
You can download the Excel Spreadsheet [rev8f] or the OpenOffice version [rev8f]
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. See the Revision History in the spreadsheet for complete list of changes
I am happy to inform that a user now has the ability to change the time period when comparing the portfolios in the Portfolio worksheet. Implemented the same feature for comparing the Lazy Portfolios.
Thanks to MrMatt2532 who informed me about the incorrect Sortino Ratio formula.
Chas  Hopefully you won't find any correlation issues in the new version
Feedback is very much appreciated (especially if its the +ve kind)
Edit: Sortino Ratio is incorrect in Returns_72_08 and Returns_85_08 (Missing the *100 in part of the formula) but the formula is correct in the rest of the spreadsheet. I fixed this prior to uploading the 8f version but it looks like I uploaded the incorrect version. To avoid confusion, I'll upload rev8g in couple of days.
Best Regards,
Simba
Last edited by simba on Tue Jul 28, 2009 8:30 am, edited 1 time in total.
simba wrote:Feedback is very much appreciated (especially if its the +ve kind)
Thanks for adding more blank entries. I have lots of oddball data sets for various things and its easier to play with them if there is more slots.
I'm tempted to go to the barra.com site and create sets in Euros, Yen and GBP sometime for some research.
Paul
An updated version rev8h is now available for those interested.
See the Revision history in the README worksheet for a list of changes.
You can download the Excel Spreadsheet [rev8h] or the OpenOffice version [rev8h]
A special thanks to Al(acr123) and Matt (MrMatt2532).
Cheers,
Simba
See the Revision history in the README worksheet for a list of changes.
You can download the Excel Spreadsheet [rev8h] or the OpenOffice version [rev8h]
A special thanks to Al(acr123) and Matt (MrMatt2532).
Cheers,
Simba
Market Neutral
All 
Can somebody add a market neutral or hedge fund allocation to the spreadsheet?
Can somebody add a market neutral or hedge fund allocation to the spreadsheet?
palffy wrote:simba, first of all, thanks for all of the work you're doing, spreadsheet looks great! do you mind making sure that the BRSIX data for 2008 is correct? It may be 39.49 instead of the 44.61 currently listed...
I checked the returns for BRSIX. Yahoo and M* have the returns of 39.49 but the fund's website has the 2008 returns for BRSIX at 41.74. This matches the returns in their SEC filing.
I'll correct it to 41.74 in the next version of the spreadsheet.
Cheers,
Simba
Re: Market Neutral
goolsby wrote:All 
Can somebody add a market neutral or hedge fund allocation to the spreadsheet?
Welcome to the forum. You can add any fund to your local copy. You only need to have the returns of the fund you are trying to add. As you can see from the comments in the topic, it's pretty straight forward to add your own funds.
For Example, if you need to add the returns for Dodge and Cox's stock fund.
If you have the mutual fund returns from 19722008
Go to the Data_72_08 worksheet
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns from 19722008 starting at cell AC8.
If you are using a hybrid of fund returns and synthetic or index returns,
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns starting at cell AC65.
This ensures that the ER is deducted from the synthetic/index returns.
For 19852008 returns
Go to the Data_85_08 worksheet
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns from 19852008 starting at cell AP8.
If you are using a hybrid of fund returns and synthetic or index returns,
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns starting at cell AP55.
Now you can go the Portfolio tab of the spreadsheet and add allocation to the new fund you just added.
Hope that helps
Cheers,
Simba
Tech Support
Simba,
Thanks. Not sure how to get the data into the spreadsheet for some of the different hedge fund indices (managed futures, equity market neutral, global macro, etc) since they are relatively new and data hard to find. Thought someone out there might have already built that into spreadsheet and willing to share.
Let me know if you can help.
Thanks. Not sure how to get the data into the spreadsheet for some of the different hedge fund indices (managed futures, equity market neutral, global macro, etc) since they are relatively new and data hard to find. Thought someone out there might have already built that into spreadsheet and willing to share.
Let me know if you can help.
Re: Tech Support
goolsby wrote:Simba,
Thanks. Not sure how to get the data into the spreadsheet for some of the different hedge fund indices (managed futures, equity market neutral, global macro, etc) since they are relatively new and data hard to find. Thought someone out there might have already built that into spreadsheet and willing to share.
Let me know if you can help.
Vanguard's market neutral fund goes back 10 years because they bought it from Laudus Rosenberg. Go to the funds web page and dig into the performance tab page.
Forget the hedge fund indexes. They are full of survivorship and backfill bias that Yale's David Swensen estimates is ~7.5% a year.
Rydex used to have a brochure on their web site for the S&P managed futures index they followed that had data back to 1985. The problem is its backtested and their fund tracks about 4% below the index.
Paul
Simba
Thank you for all the work you have done to develop and share this wonderful tool. It is great and I use it frequently. Chuck
Thank you for all the work you have done to develop and share this wonderful tool. It is great and I use it frequently. Chuck
Chuck
 Past Performance Is Just That  bob
 For info on the SC LowCountry & Savannah GA Area Bogleheads contact me at chucktanner46@gmail.com
 Paul Douglas Boyer
 Posts: 130
 Joined: Wed Mar 07, 2007 3:19 pm
 Location: Leesburg, VA
Return vs. Risk charts
Simba,
For your consideration, I am linking to a slight enhancement to your spreadsheet which simply adds two charts, one for each time span, that plot the return vs. risk for the 5 portfolios under comparison. Take a look and include them in your official version if you wish. I hope you will, because this helps to quickly visualize a comparison of the portfolios on an equal basis.
Here is the spreadsheet:
http://MadMoneyMachine.com/podcasts/BacktestPortfolioreturnsrev8i.xls
And here is what the charts looks like:
I look forward to an updated version of the spreadsheet with 2009 returns. If you need any help compiling it, let me know.
For your consideration, I am linking to a slight enhancement to your spreadsheet which simply adds two charts, one for each time span, that plot the return vs. risk for the 5 portfolios under comparison. Take a look and include them in your official version if you wish. I hope you will, because this helps to quickly visualize a comparison of the portfolios on an equal basis.
Here is the spreadsheet:
http://MadMoneyMachine.com/podcasts/BacktestPortfolioreturnsrev8i.xls
And here is what the charts looks like:
I look forward to an updated version of the spreadsheet with 2009 returns. If you need any help compiling it, let me know.
 Paul Douglas Boyer
 Posts: 130
 Joined: Wed Mar 07, 2007 3:19 pm
 Location: Leesburg, VA
simba wrote:I have added this to my spreadsheet.
I have already found an error: I used Average instead of CAGR in the charts.
Here is a link to a fixed version. I have also gone through Vanguard's site to add in their 2009 returns. If you build on this spreadsheet, you may wish to double check the Vanguard returns and especially the NonVanguard returns.
And I flipped a couple of 2008 to 2009 switches, but not sure if I got them all.
http://MadMoneyMachine.com/podcasts/BacktestPortfolioreturnsrev8j.xls
I put a chart in the Lazy_Portfolios_85 tab, feel free to delete it since it does not include all of the portfolios in the table.