Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
baldeagle
Posts: 61
Joined: Sun Mar 04, 2007 5:23 pm
Location: Portland, OR

Post by baldeagle »

Simba,

I am just loving your spreadsheet!

I think there is a bug in the entry space for "Windsor" in the "Portfolio" tab. Any entry =/> 1 raises an error. It appears that entry cell is not formated as a percent (like all the others). When I unlocked the spreadsheet and formated it as a percent, all was well.

BE
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

another bug

Post by edge »

I like the SS as well, its great. I found a bug in the rev5d (not sure if this is the latest) with the calculation of international EAFE correlation in the 1985 portfolio.

Cell G29 should be =(PEARSON(AE5:AE26,Data_85_06!$L$5:$L$26))

it was using AF instead of AE. Probably cut/paste error. Anyway, it was giving me negative correlation to EAFE which seemed odd so I corrected it.
jms969
Posts: 156
Joined: Tue Feb 20, 2007 4:56 pm
Location: South Central Wisconsin

Post by jms969 »

Wow this SS is fantastic. By the time we get to a "stable release" it will be a very nice tool to add to the tool box for AA calcs.

THANKS!!!
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

rev5f

Post by simba »

baldeagle - thanks for pointing out the windsor error. It has been corrected.

edge - I did find the correlation bug for EAFE and was corrected in rev5e.

jms - Hopefully we'll get there sooner than later ;) But I am glad my little project is turning out to be useful.

Latest version rev5f can be downloaded here
Bob
Posts: 213
Joined: Mon Feb 26, 2007 4:15 pm

Add Munis?

Post by Bob »

This spreadsheet is a terrific tool!

Would anyone have national muni fund returns for the 1972-2006 timeframe that could be added to this fantastic tool -- for those who do have not room for taxable bonds in their tax-advantaged accounts and so use tax-exempt bond funds in taxable accounts?

For instance, Vanguard Short-Term Tax-Exempt Fund Investor Shares (VWSTX), Intermediate-Term Tax-Exempt Fund Investor Shares (VWITX) and Long-Term Tax-Exempt Fund Investor Shares (VWLTX).

I can only find 1992 - 2006 data at the Vanguard site, otherwise I'd try to supply the data.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Re: Add Munis?

Post by simba »

Bob wrote:This spreadsheet is a terrific tool!

Would anyone have national muni fund returns for the 1972-2006 timeframe that could be added to this fantastic tool -- for those who do have not room for taxable bonds in their tax-advantaged accounts and so use tax-exempt bond funds in taxable accounts?

For instance, Vanguard Short-Term Tax-Exempt Fund Investor Shares (VWSTX), Intermediate-Term Tax-Exempt Fund Investor Shares (VWITX) and Long-Term Tax-Exempt Fund Investor Shares (VWLTX).

I can only find 1992 - 2006 data at the Vanguard site, otherwise I'd try to supply the data.
Bob,

I had earlier compiled the returns for all the Vanguard funds since inception Here.

As you can see in that thread, there are about 16 funds whose year does not end on 12/31. I do however have the returns for all the funds from 84 onwards. If you want feel free to download the returns and test it out. I'll try to add the munis in the next update.

Regards,
Simba
Bob
Posts: 213
Joined: Mon Feb 26, 2007 4:15 pm

Post by Bob »

Simba,

Missed that. Thank you very much.
Love this project.

Bob
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Updated efficient frontier files (Rev 5G2) posted

Post by Eric White »

Simba,

Sorry for the delay. I ran out of time the other night to fully address all the questions.

Here are updates I made to your rev 5g files from your Yahoo Group. I did not want to duplicate work again, as this has taken me ~3 hours of testing this evening. Please use these files moving forward.

Given that we now have two portolio options, I had to split the Excel Solver inputs into two different files, as Excel Solver does not support different inputs in the same file.

Rev 5G2, 1972-2006 Efficient Frontier
Rev 5G2, 1985-2006 Efficient Frontier

***EDIT: These files have been simplified with percentages only per a later response. Please read below for further details.

I will start another response explaining how to install and use Excel Solver, which is a standard add-on which comes as part of Microsoft Office.
Last edited by Eric White on Wed May 23, 2007 12:55 am, edited 2 times in total.
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Installing and using Excel Solver for Efficient Frontiers

Post by Eric White »

Hey everybody,

I apologize for not having time the other night to post a brief tutorial on how to use Excel Solver to find asset allocation efficient frontiers.

***EDIT: This tutorial has been updated for the simplified percentage-only file versions. Please read below for further details.

1.) INSTALL MICROSOFT EXCEL SOLVER ADD-IN

Go to Tools --> Add-Ins --> select "Solver Add-In"
Image

2.) DOWNLOAD AND OPEN POSTED "EFFICIENT FRONTIER" VERSIONS OF FILES.

***NOTE: Target cell, changing cells, and constraints are dependent on the time horizon and asset allocation options you would like to run. Please use the appropriate file as required.

3.) ON PORTFOLIO WORKSHEET, UPDATE INPUTS

-Allocation (int) - input your preferred allocation; efficient frontier search will begin from this allocation (can reduce run time if it's closer to feasibility)
-initial investment - $10k currently
-max ratio for allocation (int) - 10:1 max ratio currently
-max risk (std dev) - 11 currently (similar to couch potato portfolio)

4.) ON PORTFOLIO WORKSHEET, OPEN EXCEL SOLVER

Go to Tools --> Solver --> Solve
Image

DEFINITIONS:
Target Cell - Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. This cell must contain a formula.
***Target Cell currently set to returns via "Your Portfolio Results"

Equal to - Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. If you want a specific value, type it in the box.
***Target Cell currently set to MAXIMIZE return via "Your Portfolio Results"

By Changing Cells -
Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target. The adjustable cells must be related directly or indirectly to the target cell.
***Changing cells set to Allocation (int). Percentage allocations prevent simulation runs due to error messages. I was forced to remove the ERROR message built into the calculations when it deviated from 100% due to the Newtonian search function and the way it seeks to meet the objective.

Subject to the Constraints - Lists the current restrictions on the problem.
***Constraints include:
max ratio for allocation (int) - the largest asset class or subasset class is allowed to be X times that of the smallest. The smaller this number is, the faster your run will be and the likelier it will be to find a viable solution.

max risk (std dev) - this is the largest risk you are willing to accept. Excel Solver will find the asset allocation efficient frontier that maximizes return while meeting your risk requirement. As you reduce the standard deviation that you will accept, this will drive a more conservative asset allocation or allocation with lower correlations.

integers - inputs are required to be integers in order to speed up the Solver Newtonian search iterations.

postive - inputs are required to be greater than 0. All asset allocations must be long; no shorts allowed. (No academics with leveraged risk free rates allowed :wink: )

5.) REVIEW RESULTS. REPEAT STEPS 3 & 4 AS REQUIRED TO BUILD YOUR EFFICIENT FRONTIER ASSET ALLOCATIONS ACROSS DIFFERING RISK LEVELS.


I think that's all. Please post comments and I'll try to answer them ASAP.

I look forward to discussing the results with other Diehards. Obviously, efficient frontiers are static and are only as good as the input data. However, they may give us some directional indications which we should discuss.
Last edited by Eric White on Wed May 23, 2007 1:30 am, edited 5 times in total.
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

No more integers for efficient frontiers!

Post by Eric White »

Hey everybody,

I just found a way to remove the integer requirements for the Solver files. After removing all the Error statements built in, I have successfully driven both files without the integers. It also sped up the calculations significantly.

So here are the new files with percentages!
Rev 5G2 1972-2006 with percentages
Rev 5G2 1985-2006 with percentages

I will take a crack at compiling the data tonight and posting results. Gotta figure out how to append pictures correctly...
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Updated install Excel Solver and efficient frontiers

Post by Eric White »

Hey everybody,

Here are the updated instructions to install and use Excel Solver for efficient frontier calculations. These have been updated with the simplified percentage only inputs.

1.) INSTALL MICROSOFT EXCEL SOLVER ADD-IN

Go to Tools --> Add-Ins --> select "Solver Add-In"
Image

2.) DOWNLOAD AND OPEN POSTED "EFFICIENT FRONTIER" VERSIONS OF FILES.

***NOTE: Target cell, changing cells, and constraints are dependent on the time horizon and asset allocation options you would like to run. Please use the appropriate file as required.

3.) ON PORTFOLIO WORKSHEET, UPDATE INPUTS

-initial investment - $10k currently
-max risk (std dev) - 11 currently (similar to couch potato portfolio)

4.) ON PORTFOLIO WORKSHEET, OPEN EXCEL SOLVER

Go to Tools --> Solver --> Solve
Image

DEFINITIONS:
Target Cell - Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. This cell must contain a formula.
***Target Cell currently set to returns via "Your Portfolio Results"

Equal to - Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. If you want a specific value, type it in the box.
***Target Cell currently set to MAXIMIZE return via "Your Portfolio Results"

By Changing Cells -
Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target. The adjustable cells must be related directly or indirectly to the target cell.
***Changing cells set to Allocation (%).

Subject to the Constraints - Lists the current restrictions on the problem.
***Constraints include:
max risk (std dev) - this is the largest risk you are willing to accept. Excel Solver will find the asset allocation efficient frontier that maximizes return while meeting your risk requirement. As you reduce the standard deviation that you will accept, this will drive a more conservative asset allocation or allocation with lower correlations.

postive - inputs are required to be greater than 0. All asset allocations must be long; no shorts allowed. (No academics with leveraged risk free rates allowed :wink: )

5.) REVIEW RESULTS. REPEAT STEPS 3 & 4 AS REQUIRED TO BUILD YOUR EFFICIENT FRONTIER ASSET ALLOCATIONS ACROSS DIFFERING RISK LEVELS.


I think that's all. Please post comments and I'll try to answer them ASAP.

I look forward to discussing the results with other Diehards. Obviously, efficient frontiers are static and are only as good as the input data. However, they may give us some directional indications which we should discuss.
mikenz
Posts: 780
Joined: Sat Mar 10, 2007 7:33 pm

Post by mikenz »

Great work.

One thing I found so far - worksheet SP-85-06 : the formula for CAGR uses Portfolio!$B$38 (which is 35 years) when it should use $J$38 (22 years). The CAGR should be 11-12% but calculates to only 7%. The total also has a VLOOKUP with an argument $B$40 which probably should be $J$40
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Efficient frontier results

Post by Eric White »

Hey everybody,

Efficient frontier results for 1972-2006 and 1985-2006 are in the following attachment:
Efficient frontier results, Rev 5G3

I'd be interested in Diehards reactions to the "efficient" allocations. Some were counterintuitive to me (reliance on Emerging Markets over EAFE; extremely low Large Blend / Large Value / Mid Blend allocations; use of Small Value over Microcap; etc.).

Of course, hindsight is 20/20 and efficient frontiers are never static. Next step is to run standard portfolios against the EF and see how everything stacks up!

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

Post by simba »

mikenz wrote:Great work.

One thing I found so far - worksheet SP-85-06 : the formula for CAGR uses Portfolio!$B$38 (which is 35 years) when it should use $J$38 (22 years). The CAGR should be 11-12% but calculates to only 7%. The total also has a VLOOKUP with an argument $B$40 which probably should be $J$40
Are you using the latest version rev5g?

Eric - I downloaded your files but none of them are working correctly. Thanks for your efforts.
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Using Rev 5G

Post by Eric White »

Simba,

Using Rev 5G.

Can you be more specific as to what's not working? I plan on fixing them tonight per the previous comment, if that's the problem. I don't know if that was a problem in 5G or one of my mods.

-Eric
User avatar
SoonerSunDevil
Posts: 2000
Joined: Mon Feb 19, 2007 9:32 pm
Location: The desert

Post by SoonerSunDevil »

Simba,

I have added Vanguard's Corporate High-Yield Fund (VWEHX) to the spreadsheet. How do I go about getting this information to you so that others may use this if they please?

Thanks,

John
User avatar
gbs
Wiki Admin
Posts: 557
Joined: Tue Feb 20, 2007 11:41 am

Post by gbs »

Hi Eric,

I downloaded the 72-06 spreadsheet, installed solver and on my machine it does compute the allocation for a given set of constraints and STDEV.

Good job!

Regards, GBS
Eric White
Posts: 54
Joined: Fri May 18, 2007 10:09 am

Good to hear

Post by Eric White »

GBS,

That's really good to hear. I need to work on the problem area that was called out last night, but I don't know if that was introduced by my changes or was part of the original Rev5G.

Are you as surprised with some of the results as I am?

Cheers,
Eric
sleepy
Posts: 5
Joined: Wed May 23, 2007 8:23 pm

Updated spreadsheet with inflation info

Post by sleepy »

Simba,

Your spreadsheet is fantastic. I found it personally useful to incorporate inflationary data into the spreadsheet so that I could look at returns in terms of real losses and gains.

Here is a link to rev5f with inflation data. I hope you or someone else can benefit from it.

http://files-upload.com/245335/Backtest ... n.xls.html
User avatar
gbs
Wiki Admin
Posts: 557
Joined: Tue Feb 20, 2007 11:41 am

Re: Good to hear

Post by gbs »

Eric White wrote:GBS,

That's really good to hear. I need to work on the problem area that was called out last night, but I don't know if that was introduced by my changes or was part of the original Rev5G.

Are you as surprised with some of the results as I am?

Cheers,
Eric
Eric,

I played with MVO's before and not surprised. Basically you can guess what the winning portfolio look's like without one. Look for combinations of assets with high Sharpe ratios and similar returns.

gbs
User avatar
gbs
Wiki Admin
Posts: 557
Joined: Tue Feb 20, 2007 11:41 am

Re: Updated spreadsheet with inflation info

Post by gbs »

sleepy wrote:Simba,

Your spreadsheet is fantastic. I found it personally useful to incorporate inflationary data into the spreadsheet so that I could look at returns in terms of real losses and gains.

Here is a link to rev5f with inflation data. I hope you or someone else can benefit from it.

http://files-upload.com/245335/Backtest ... n.xls.html
sleepy, there is an error in your real return values.

regards, gbs
sleepy
Posts: 5
Joined: Wed May 23, 2007 8:23 pm

Post by sleepy »

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

Updated version rev5h

Post by simba »

sleepy - thanks for the effort.

I've updated the spreadsheet to include both nominal and real returns.

OUJohn - I had returns for High Yield Corp from 1984 so I included it in the 1985-2006.

Cb - corrected the Intl correlation error

Latest version rev5h can be downloaded here

Regards,
Simba
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

Getting really good

Post by edge »

I wonder if there is a better way to host the spreadsheet. Does anyone else think it would be a better idea to use the google online spreadsheet for this sort of thing?

It may make it easier to collaborate using an online tool. I tried uploading it and it worked ok except the graphs did not show up.

http://spreadsheets.google.com/pub?key= ... ksdqxL4Bfg

Edit: Wouldn't a log scale work better for the graphs?
baldeagle
Posts: 61
Joined: Sun Mar 04, 2007 5:23 pm
Location: Portland, OR

Post by baldeagle »

Simba,

I like the addition of comparison graphs on the "Portfolio" tab of Rev-5h. But there seems to be something wrong with the graph and table above it for the 1985-2006 period.

My test -- I set my portfolio equal to the CHP and expected identical CAGR and Std. Dev. and for the graphs to overlap perfectly. But none of that happened. It does happen for the 1972-2006 period, but not for 1985-2006. Bug?
User avatar
CyberBob
Posts: 3387
Joined: Tue Feb 20, 2007 1:53 pm

Re: Getting really good

Post by CyberBob »

edge wrote:I wonder if there is a better way to host the spreadsheet. Does anyone else think it would be a better idea to use the google online spreadsheet for this sort of thing?
That's a heck of a good idea!
That way, it wouldn't require people to have extra software other than a browser and they could always be sure of viewing the latest up-to-date numbers.
And, collaboration would certainly be easier.

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

Re: Getting really good

Post by simba »

CyberBob wrote:
edge wrote:I wonder if there is a better way to host the spreadsheet. Does anyone else think it would be a better idea to use the google online spreadsheet for this sort of thing?
That's a heck of a good idea!
That way, it wouldn't require people to have extra software other than a browser and they could always be sure of viewing the latest up-to-date numbers.
And, collaboration would certainly be easier.

Bob
baldeagle - Thanks for pointing out that error, I am positive I fixed this earlier but anyhow the latest version rev5i can be downloaded here

edge/CyberBob - Normally that would be an excellent idea but in this case since the SS would be read only it would be of no use unless you allow write access to the world. I am not aware of any way to do this (where you give write access to everyone in google docs).

Secondly, even if this was possible (where anyone can write/modify this doc) - this poses another issue of preventing someone from messing up the spreadsheet.
I haven't used google docs extensively so maybe there are ways to overcome both the issues that I may not be aware of.

Regards,
Simba
User avatar
SoonerSunDevil
Posts: 2000
Joined: Mon Feb 19, 2007 9:32 pm
Location: The desert

Post by SoonerSunDevil »

Simba,

Thanks for adding the High Yield Corporate Fund to the sheet. Again, I appreciate you taking the time to assemble the sheet.

John
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

Google SS

Post by edge »

Simba,

Google documents/spreadsheets have permission settings. So you add the users who you want to have write/read permission.

If you have a google account I can add you. Send a PM.

Also, I forgot to mention that there are several viable ways to use the Google Spreadsheet tool. Here is a scheme that could work well:

Only SS maintainers have write access so that they can update the core functionality/data. Other users have read access and they can export the spreadsheet to xls or whatever format (it supports a bunch). Or they can copy it into a personal Google Spreadsheet for playing.

It has a nice chat feature when multiple maintainers are editing the spreadsheet so you don't clobber each other's work.

For a more concrete example, I have added the SS as a Google SS and now I have published it to the web as XLS (earlier it was published as HTML). In this scenario the SS maintainers would edit the SS as a Google SS and then it would be publised as an XLS or comma separated or whatever for people to use at home.

http://spreadsheets.google.com/pub?key= ... output=xls

Does this satisfy your earlier concerns?

Finally, I don't think it is a good idea to give write access to the world but this is also possible. I *think* it can be done by checking the "Anyone can view this document" checkbox in the "Share" tabbed window.
johnb
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Post by johnb »

simba wrote:
johnb wrote:Thanks very much for posting this. I've found it highly useful.

I added Short-Term Treasuries data to my copy of the spreadsheet, because I've found it to be a fantastic diversifier.
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.
Hi Simba,

The Short-Term Treasury data I posted were for VFISX, the Vanguard Short-Term Treasury Fund.

Best regards,
John
johnb
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Post by johnb »

gbs wrote:johnb,

what is your source?

gbs
Hi GBS,

The source is Vanguard Short-Term Treasury Fund as far back as I could find it, and before that from http://www.ifa.com/images/12steps/enlar ... annual.htm (It corresponds with the 2F fund.)

Best regards,
John
johnb
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Post by johnb »

I guess I'm a little confused by backtesting now. A portfolio of 50% EM, 38% SV, and 12% REITs (and no bonds) has an average annual growth rate of 17.65% with a Sharpe ratio of .66 when backtested 1972-2006.

When you look at growth rate and Sharpe ratio together, there's nothing that can beat that. However, that's a ridiculous portfolio. Right?

John
User avatar
gbs
Wiki Admin
Posts: 557
Joined: Tue Feb 20, 2007 11:41 am

Post by gbs »

johnb wrote:I guess I'm a little confused by backtesting now. A portfolio of 50% EM, 38% SV, and 12% REITs (and no bonds) has an average annual growth rate of 17.65% with a Sharpe ratio of .66 when backtested 1972-2006.

When you look at growth rate and Sharpe ratio together, there's nothing that can beat that. However, that's a ridiculous portfolio. Right?

John
I can beat that :) !

40 SV, 54 EM, 6 PCRIX 17.73 same stdev. Which allocation is more ridiculous ?

gbs

P.S. Are there errors in the VEURX and VPACX returns?
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

No surprise

Post by edge »

Is anyone surprised that extremely risky and strange portfolios are the best performing?
johnb
Posts: 282
Joined: Sat Mar 17, 2007 12:11 pm

Re: No surprise

Post by johnb »

edge wrote:Is anyone surprised that extremely risky and strange portfolios are the best performing?
Well, I was a bit surprised. :?

Something I've found interesting through backtesting is that combining the *riskiest* stocks with the *safest* bonds produces some really neat results. For example:

- 60% Short-Term Treasurys (the VFISX info I posted on page 1)
- 20% EM
- 10% SV
- 10% REITs

...yields 12.10% average growth, an 8.15 standard deviation, and a Sharpe ratio of .78. (The worst year that portfolio would have had was in 1974, with a loss of only 3.56%!)

By comparison, the Coffeehouse portfolio had an 11.71% return, a 10.45 standard deviation and a .59 Sharpe ratio.

John
User avatar
Cb
Posts: 275
Joined: Tue Feb 20, 2007 11:39 pm

Post by Cb »

gbs wrote: P.S. Are there errors in the VEURX and VPACX returns?
Yes. I appears as though the returns for VEURX and VPACX (from 1997-2006) have been swapped for one another.

Here is a link to the spreadsheet I sent Simba with EUR, PAC, and Int LV historical results:

http://gnobility.com/ER/PAC_EUR_Int-Val_1972-2006.xls

For example, here are the VPACX historical returns from Yahoo (halfway down the page):

http://finance.yahoo.com/q/pm?s=VPACX

In that spreadsheet I pasted in MSCI gross returns for Europe, Pacific, and International Value from the MSCI/Barra data website. Beside each I added the corresponding Vanguard index funds from their inception. In keeping with TrevH's practice I suggested that Simba use the MSCI data from 1972 until the first year of results where in for the corresponding Vanguard fund (1997).

The Europe & Pacific funds track the MSCI data nicely from 1997-2006 (considering their ER's), but the managed VTRIX fund & the MSCI "Value" data was a poorer match. I suggested that Simba consider using use the IntLV data that another poster had compiled instead of the MSCI + VTRIX dataset.

Cb
User avatar
gummy
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by gummy »

Simba:
The spreadsheet is Great!

? Is it okay to put a copy here ?
http://www.gummy-stuff.org/Excel/Backte ... eturns.xls

If you object, I can remove it.
It's rev5i, but I can put later versions at the above location (if they become available).
baldeagle
Posts: 61
Joined: Sun Mar 04, 2007 5:23 pm
Location: Portland, OR

Post by baldeagle »

Simba,

I've noticed that the performance of the Coffee House "reference" portfolio from 1972-2006 is different from that shown by TrevH in Conversation 58280 on Morningstar's Diehard Forum where he shows he shows:

C-H-P......560,471.31...12.19...11.66....0.58

If I understand correctly, you are using the same data as he. I did some random spot checking which seemed to confirm that premise. So...why do you think there is there a difference?
sterjs
Posts: 300
Joined: Sun Mar 25, 2007 2:22 am

Post by sterjs »

Anyway you guys can add 2-Year Treasuries? 8)
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Updated version 5j

Post by simba »

johnb/sterjis,

I uploaded the latest version 5j which includes the 2 year ST treasury fund (VFISX).

Cb - I corrected the returns for Pacific/Europe funds as well.

Latest version rev5j can be found here
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

gummy wrote:Simba:
The spreadsheet is Great!

? Is it okay to put a copy here ?
http://www.gummy-stuff.org/Excel/Backte ... eturns.xls

If you object, I can remove it.
It's rev5i, but I can put later versions at the above location (if they become available).
gummy - This coming from you means a lot to me. Please feel free to keep a copy on your website.

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

Post by simba »

baldeagle wrote:Simba,

I've noticed that the performance of the Coffee House "reference" portfolio from 1972-2006 is different from that shown by TrevH in Conversation 58280 on Morningstar's Diehard Forum where he shows he shows:

C-H-P......560,471.31...12.19...11.66....0.58

If I understand correctly, you are using the same data as he. I did some random spot checking which seemed to confirm that premise. So...why do you think there is there a difference?
We started out with the same data but I added lot more funds and also changed the returns for Large Blend to include VG 500 Idx from its inception. Ditto for some other funds. The total you are referring to is the modified CHP (It includes 40% IT treasury). The Std CHP portfolio includes 40% Total Bond.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Re: No surprise

Post by simba »

johnb wrote:
edge wrote:Is anyone surprised that extremely risky and strange portfolios are the best performing?
Well, I was a bit surprised. :?

Something I've found interesting through backtesting is that combining the *riskiest* stocks with the *safest* bonds produces some really neat results. For example:

- 60% Short-Term Treasurys (the VFISX info I posted on page 1)
- 20% EM
- 10% SV
- 10% REITs

...yields 12.10% average growth, an 8.15 standard deviation, and a Sharpe ratio of .78. (The worst year that portfolio would have had was in 1974, with a loss of only 3.56%!)

By comparison, the Coffeehouse portfolio had an 11.71% return, a 10.45 standard deviation and a .59 Sharpe ratio.

John
That's very close to Larry's portfolio. As per this thread - Larry's allocation is: (This is not the exact allocation but I am guessing some of the components here).
SCV - 10%
EMV - 4%
Intl SV - 11%
REIT - 11%
Comm - 4%
VFISX - 60%

This topic has an interesting discussion on his allocation
User avatar
gummy
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by gummy »

With Simba's permission, I've put a copy (slightly modified) on my website.
It's described here:
http://www.gummy-stuff.org/Simba.htm

It's currently version version 5i
... but (when I get a minute) I'll modify it to 5j. :D
User avatar
ddb
Posts: 5511
Joined: Mon Feb 26, 2007 11:37 am
Location: American Gardens Building, West 81st St.

Post by ddb »

Something I've found interesting through backtesting is that combining the *riskiest* stocks with the *safest* bonds produces some really neat results
Yup, I agree. One of my favorite asset allocations is something along the lines of 70% TIPs and 30% international small-company stocks (better yet, international emerging small-company stocks). Huge tracking error relative to any common benchmark (obviously), but it has a very interesting risk-return profile.

- DDB
norm
Posts: 594
Joined: Mon Feb 19, 2007 6:10 pm

Post by norm »

I seem to be having a problem. After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step?

Norm
User avatar
gummy
Posts: 340
Joined: Mon Mar 12, 2007 2:34 pm
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by gummy »

After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step?
Do your allocations add to 100%?
jms969
Posts: 156
Joined: Tue Feb 20, 2007 4:56 pm
Location: South Central Wisconsin

Post by jms969 »

Simba,

It might be nice to plagiarize some of Gummy's work and add the Sortino ratio as well.... Fully realizing that you did not intend for this to turn into your life's work.

What would really be nice (as mentioned earlier) would be to put this in the public domain so to speak and operate it as an open source collaborative effort i.e., firefox and open office.

You could keep control of what was actually released but a lot of very bright spreadsheet jockeys and financial thinkers could really add to their hearts content...

IMHO,

JMS

EDIT: Gummy you have done some amazing work!!!
I want to die peacefully in my sleep like my grandfather, not screaming like the passengers in his car.
norm
Posts: 594
Joined: Mon Feb 19, 2007 6:10 pm

Post by norm »

gummy wrote:
After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step?
Do your allocations add to 100%?
Yes they do. In addition on Row 45 under Total it shows Error and under CAGR it shows #VALUE!.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

updated with gummy's changes

Post by simba »

jms - I updated the spreadsheet with gummy's changes.

Latest version rev5l can be downloaded here

Honestly I can't say I understand everything he did there. I am still reading about Ito's calculus and sortino ratio.
Last edited by simba on Tue May 29, 2007 5:16 pm, edited 2 times in total.
Post Reply