Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Barry Barnitz
Wiki Admin
Posts: 3353
Joined: Mon Feb 19, 2007 9:42 pm
Contact:

Asset Class Returns:

Post by Barry Barnitz »

We have asset class returns for the fiat currency era (1970-2008) for gold and other asset classes on the wiki:

Asset Class Returns

Gold Bullion Prices 1970 - 2008

regards,
Additional administrative tasks: Financial Page bogleheads.org. blog; finiki the Canadian wiki; The Bogle Center for Financial Literacy site; La Guía Bogleheads® España site.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by grayfox »

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
DP
Posts: 660
Joined: Thu Apr 17, 2008 5:19 pm

Post by DP »

Hi,
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
User avatar
Leif
Posts: 3698
Joined: Wed Sep 19, 2007 4:15 pm

Post by Leif »

Not sure about your CPI-U 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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

Leif Eriksen wrote:Not sure about your CPI-U 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 Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba
User avatar
Leif
Posts: 3698
Joined: Wed Sep 19, 2007 4:15 pm

Post by Leif »

simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U 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 Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba
I guess over time Dec-Dec versus Avg-Avg balances out.
User avatar
DaleMaley
Posts: 1592
Joined: Thu Mar 01, 2007 7:04 pm
Location: Fairbury, Illinois
Contact:

Post by DaleMaley »

many thanks Simba !!

:D
Most investors, both institutional and individual, will find that the best way to own common stocks is through an index fund that charges minimal fees. – Warren Buffett
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

Some corrections

Post by Paul Douglas Boyer »

Simba, in your spreadsheet, here are some corrections:

Cell SP-85-08!C35 should read "=RATE(Portfolio!$K...
Cell SP-85-08!C36 should read "=RATE(Portfolio!$K...
Column P in SP-85-08 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 ... cw&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.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

Re: Some corrections

Post by MachineGhost »

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 SP-85-08!C35 should read "=RATE(Portfolio!$K...
Cell SP-85-08!C36 should read "=RATE(Portfolio!$K...
Column P in SP-85-08 incorrectly points to 72 data.
[/u]
Last edited by MachineGhost on Sat Feb 14, 2009 11:35 am, edited 2 times in total.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Re: Some corrections

Post by grayfox »

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.
grok87
Posts: 10512
Joined: Tue Feb 27, 2007 8:00 pm

Post by grok87 »

simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U 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 Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U 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,
RIP Mr. Bogle.
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

Re: Some corrections

Post by Paul Douglas Boyer »

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.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

Re: Some corrections

Post by MachineGhost »

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.
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 11:58 am, edited 2 times in total.
MachineGhost
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

Post by MachineGhost »

I second this.
grok87 wrote:
simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U 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 Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U 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,
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by grayfox »

grok87 wrote:
simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U 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 Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U 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
Posts: 108
Joined: Sat Feb 14, 2009 1:46 am

Post by MachineGhost »

But annual data isn't an average, it is ( 12/31/xx divided by 12/31/xx-1 ) -1 or YTD as of 12/31/xx.
grayfox wrote:
grok87 wrote:
simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U 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 Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U 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.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Post by grayfox »

MachineGhost wrote:But annual data isn't an average, it is ( 12/31/xx divided by 12/31/xx-1 ) -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 (P1-P0+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.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Re: Some corrections

Post by simba »

Paul Douglas Boyer wrote:Simba, in your spreadsheet, here are some corrections:

Cell SP-85-08!C35 should read "=RATE(Portfolio!$K...
Cell SP-85-08!C36 should read "=RATE(Portfolio!$K...
Column P in SP-85-08 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 ... cw&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.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

grok87/grayfox/MachineGhost,

Earlier my thoughts on this was we use goods/services throughout the year so I used the avg-avg prices to calculate inflation.

But as you have mentioned, the industry and Govt standard is to use Dec-Dec 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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Re: Some corrections

Post by simba »

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
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

What Correlates With CPI?

Post by Paul Douglas Boyer »

What asset classes correlate closest with inflation (CPI)?

I took the CPI numbers and added them to the correlation matrix (1972-2008) in the spreadsheet, did a sort, and here is how it turns out:
(1 means perfect correlation, 0 means perfect non-correlation. 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    S-TIPS  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 T-Bills, Gold, and shorting the Long-Term 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?
grok87
Posts: 10512
Joined: Tue Feb 27, 2007 8:00 pm

Post by grok87 »

simba wrote:grok87/grayfox/MachineGhost,

Earlier my thoughts on this was we use goods/services throughout the year so I used the avg-avg prices to calculate inflation.

But as you have mentioned, the industry and Govt standard is to use Dec-Dec 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,
RIP Mr. Bogle.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

Re: What Correlates With CPI?

Post by grayfox »

Paul Douglas Boyer wrote:What asset classes correlate closest with inflation (CPI)?
Paul--that 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.
Anagoge
Posts: 5
Joined: Thu Feb 26, 2009 11:59 am

Post by Anagoge »

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!
User avatar
Cb
Posts: 275
Joined: Tue Feb 20, 2007 11:39 pm

Post by Cb »

Image
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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 month-month CPI instead of avg-avg. 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
User avatar
Rager1
Posts: 999
Joined: Fri Jun 01, 2007 12:03 pm

Post by Rager1 »

Congratulations on the new baby girl! Also, thank you for the latest version of this fabulous spreadsheet.

Ed
blackball
Posts: 107
Joined: Thu May 01, 2008 9:36 pm

Post by blackball »

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 month-month CPI instead of avg-avg. 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.
AboveBeyond
Posts: 26
Joined: Tue May 27, 2008 10:58 am

Post by AboveBeyond »

Appreciate the backtest update Simba! :P
oyz79
Posts: 3
Joined: Thu Apr 02, 2009 8:09 pm

Awesome sheet! Other asset classes?

Post by oyz79 »

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
oldDogNewTrick
Posts: 1
Joined: Sat Apr 04, 2009 12:55 pm

OpenOffice 3.01 Problem?

Post by oldDogNewTrick »

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 x-axis labels. Is this possibly a version incompatibility?

Regards,
OldDog
rwwoods
Posts: 1312
Joined: Thu Jun 07, 2007 7:54 pm
Location: The Villages, Florida

Post by rwwoods »

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 72-07 sheet.

Returns 72-08 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
User avatar
Chas
Posts: 846
Joined: Sat Mar 24, 2007 8:41 pm
Location: America

Post by Chas »

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,
Chas | | The course of true love never did run smooth. Shakespeare
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Updated version rev8d

Post by simba »

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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Updated version rev8e

Post by simba »

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
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Post by stratton »

Simba,

Thanks for putting in the work on this. Enjoy the newborn!

Paul
User avatar
Chas
Posts: 846
Joined: Sat Mar 24, 2007 8:41 pm
Location: America

Post by Chas »

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,
Chas | | The course of true love never did run smooth. Shakespeare
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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) :D

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.
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Post by stratton »

simba wrote:Feedback is very much appreciated (especially if its the +ve kind) :D
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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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
palffy
Posts: 6
Joined: Sun Mar 29, 2009 8:20 pm

Post by palffy »

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...
goolsby
Posts: 2
Joined: Mon Aug 24, 2009 9:48 am

Market Neutral

Post by goolsby »

All --

Can somebody add a market neutral or hedge fund allocation to the spreadsheet?
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

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
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Re: Market Neutral

Post by simba »

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 1972-2008
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 1972-2008 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 1985-2008 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 1985-2008 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
goolsby
Posts: 2
Joined: Mon Aug 24, 2009 9:48 am

Tech Support

Post by goolsby »

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.
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Re: Tech Support

Post by stratton »

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
Chuck T
Posts: 1062
Joined: Wed Sep 03, 2008 3:23 pm
Location: Lowcountry of South Carolina

Post by Chuck T »

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
Chuck | Past Performance Is Just That - bob | For info on the SC LowCountry & Savannah GA Area Bogleheads contact me at chucktanner46@gmail.com
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

Return vs. Risk charts

Post by Paul Douglas Boyer »

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/Bac ... -rev8i.xls

And here is what the charts looks like:

Image

I look forward to an updated version of the spreadsheet with 2009 returns. If you need any help compiling it, let me know.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

Paul - Thanks for your suggestion.

I have added this to my spreadsheet.

I'll release the next version on or around 1/15 when the CPI data is released. I am also waiting for some other returns to be posted.

Best Regards,
Simba
User avatar
Paul Douglas Boyer
Posts: 130
Joined: Wed Mar 07, 2007 2:19 pm
Location: Leesburg, VA

Post by Paul Douglas Boyer »

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 Non-Vanguard returns.
And I flipped a couple of 2008 to 2009 switches, but not sure if I got them all.

http://MadMoneyMachine.com/podcasts/Bac ... -rev8j.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.
Post Reply