Spreadsheet for backtesting (includes TrevH's data)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

Postby baldeagle » Mon May 21, 2007 6:19 pm

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
baldeagle
 
Posts: 61
Joined: Sun Mar 04, 2007 7:23 pm
Location: Portland, OR

another bug

Postby edge » Tue May 22, 2007 7:35 am

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.
edge
 
Posts: 1820
Joined: Mon Feb 19, 2007 8:44 pm
Location: Great Falls VA

Postby jms969 » Tue May 22, 2007 9:16 am

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.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 6:56 pm
Location: South Central Wisconsin

rev5f

Postby simba » Tue May 22, 2007 11:16 am

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

Add Munis?

Postby Bob » Tue May 22, 2007 12:57 pm

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
 
Posts: 96
Joined: Mon Feb 26, 2007 6:15 pm

Re: Add Munis?

Postby simba » Tue May 22, 2007 2:23 pm

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

Postby Bob » Tue May 22, 2007 2:29 pm

Simba,

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

Bob
Bob
 
Posts: 96
Joined: Mon Feb 26, 2007 6:15 pm

Updated efficient frontier files (Rev 5G2) posted

Postby Eric White » Wed May 23, 2007 12:48 am

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 1:55 am, edited 2 times in total.
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 11:09 am

Installing and using Excel Solver for Efficient Frontiers

Postby Eric White » Wed May 23, 2007 1:26 am

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.
***[i]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 2:30 am, edited 5 times in total.
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 11:09 am

No more integers for efficient frontiers!

Postby Eric White » Wed May 23, 2007 1:54 am

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 11:09 am

Updated install Excel Solver and efficient frontiers

Postby Eric White » Wed May 23, 2007 2:27 am

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.
***[i]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.
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 11:09 am

Postby mikenz » Wed May 23, 2007 3:49 am

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
mikenz
 
Posts: 780
Joined: Sat Mar 10, 2007 9:33 pm

Efficient frontier results

Postby Eric White » Wed May 23, 2007 3:57 am

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
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 11:09 am

Postby simba » Wed May 23, 2007 10:26 am

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

Using Rev 5G

Postby Eric White » Wed May 23, 2007 1:55 pm

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
Eric White
 
Posts: 54
Joined: Fri May 18, 2007 11:09 am

Postby SoonerSunDevil » Wed May 23, 2007 2:40 pm

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
SoonerSunDevil
 
Posts: 2001
Joined: Mon Feb 19, 2007 11:32 pm
Location: The desert

Postby gbs » Wed May 23, 2007 3:07 pm

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
User avatar
gbs
Wiki Admin
 
Posts: 558
Joined: Tue Feb 20, 2007 1:41 pm

Good to hear

Postby Eric White » Wed May 23, 2007 7:41 pm

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 White
 
Posts: 54
Joined: Fri May 18, 2007 11:09 am

Updated spreadsheet with inflation info

Postby sleepy » Wed May 23, 2007 9:32 pm

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-Portfolio-returns-rev5f-with_in.xls.html
sleepy
 
Posts: 5
Joined: Wed May 23, 2007 9:23 pm

Re: Good to hear

Postby gbs » Wed May 23, 2007 11:28 pm

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: 558
Joined: Tue Feb 20, 2007 1:41 pm

Re: Updated spreadsheet with inflation info

Postby gbs » Wed May 23, 2007 11:29 pm

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-Portfolio-returns-rev5f-with_in.xls.html


sleepy, there is an error in your real return values.

regards, gbs
User avatar
gbs
Wiki Admin
 
Posts: 558
Joined: Tue Feb 20, 2007 1:41 pm

Postby sleepy » Thu May 24, 2007 8:12 am

sleepy
 
Posts: 5
Joined: Wed May 23, 2007 9:23 pm

Updated version rev5h

Postby simba » Thu May 24, 2007 10:14 am

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

Getting really good

Postby edge » Thu May 24, 2007 11:46 pm

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=pWspEm902eoWIksdqxL4Bfg

Edit: Wouldn't a log scale work better for the graphs?
edge
 
Posts: 1820
Joined: Mon Feb 19, 2007 8:44 pm
Location: Great Falls VA

Postby baldeagle » Fri May 25, 2007 1:33 am

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?
baldeagle
 
Posts: 61
Joined: Sun Mar 04, 2007 7:23 pm
Location: Portland, OR

Re: Getting really good

Postby CyberBob » Fri May 25, 2007 10:20 am

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
CyberBob
 
Posts: 2874
Joined: Tue Feb 20, 2007 3:53 pm

Re: Getting really good

Postby simba » Fri May 25, 2007 11:47 am

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
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby SoonerSunDevil » Fri May 25, 2007 1:29 pm

Simba,

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

John
User avatar
SoonerSunDevil
 
Posts: 2001
Joined: Mon Feb 19, 2007 11:32 pm
Location: The desert

Google SS

Postby edge » Fri May 25, 2007 1:32 pm

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=pWspEm902eoWIksdqxL4Bfg&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.
edge
 
Posts: 1820
Joined: Mon Feb 19, 2007 8:44 pm
Location: Great Falls VA

Postby johnb » Sat May 26, 2007 12:09 pm

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 1:11 pm

Postby johnb » Sat May 26, 2007 12:13 pm

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 1:11 pm

Postby johnb » Sat May 26, 2007 4:32 pm

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
johnb
 
Posts: 282
Joined: Sat Mar 17, 2007 1:11 pm

Postby gbs » Sat May 26, 2007 5:12 pm

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?
User avatar
gbs
Wiki Admin
 
Posts: 558
Joined: Tue Feb 20, 2007 1:41 pm

No surprise

Postby edge » Sat May 26, 2007 6:17 pm

Is anyone surprised that extremely risky and strange portfolios are the best performing?
edge
 
Posts: 1820
Joined: Mon Feb 19, 2007 8:44 pm
Location: Great Falls VA

Re: No surprise

Postby johnb » Sat May 26, 2007 7:09 pm

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
johnb
 
Posts: 282
Joined: Sat Mar 17, 2007 1:11 pm

Postby Cb » Sat May 26, 2007 7:18 pm

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
Cb
 
Posts: 267
Joined: Wed Feb 21, 2007 1:39 am

Postby gummy » Sun May 27, 2007 3:33 am

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).
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 3:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby baldeagle » Mon May 28, 2007 1:32 am

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?
baldeagle
 
Posts: 61
Joined: Sun Mar 04, 2007 7:23 pm
Location: Portland, OR

Postby sterjs » Tue May 29, 2007 3:31 am

Anyway you guys can add 2-Year Treasuries? 8)
sterjs
 
Posts: 281
Joined: Sun Mar 25, 2007 3:22 am

Updated version 5j

Postby simba » Tue May 29, 2007 10:10 am

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
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby simba » Tue May 29, 2007 10:15 am

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
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby simba » Tue May 29, 2007 10:26 am

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
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Re: No surprise

Postby simba » Tue May 29, 2007 10:36 am

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
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby gummy » Tue May 29, 2007 11:04 am

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
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 3:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby ddb » Tue May 29, 2007 11:48 am

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
User avatar
ddb
 
Posts: 5479
Joined: Mon Feb 26, 2007 1:37 pm
Location: American Gardens Building, West 81st St.

Postby norm » Tue May 29, 2007 12:12 pm

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
norm
 
Posts: 594
Joined: Mon Feb 19, 2007 8:10 pm

Postby gummy » Tue May 29, 2007 12:21 pm

After I entered the % of my allocations nothing appeared on the graphs. Am I missing a step?
Do your allocations add to 100%?
User avatar
gummy
 
Posts: 340
Joined: Mon Mar 12, 2007 3:34 pm
Location: Burlington, Ontari-ari-ari-O

Postby jms969 » Tue May 29, 2007 12:38 pm

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.
jms969
 
Posts: 156
Joined: Tue Feb 20, 2007 6:56 pm
Location: South Central Wisconsin

Postby norm » Tue May 29, 2007 1:02 pm

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!.
norm
 
Posts: 594
Joined: Mon Feb 19, 2007 8:10 pm

updated with gummy's changes

Postby simba » Tue May 29, 2007 2:12 pm

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 6:16 pm, edited 2 times in total.
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

PreviousNext

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: Google [Bot], Karamatsu and 41 guests