Spreadsheet for backtesting (includes TrevH's data)

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

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]

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

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

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.

[simba]

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]

Simba,

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

Bob

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

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


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


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 )

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]

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]

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"


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


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 )

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]

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]

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

[simba]

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]

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

[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

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

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]

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

[gbs]

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

[gbs]

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

[sleepy]

Thanks gbs. I think I found it.

http://files-upload.com/246150/Backtest-Portfolio-returns-rev5f-with_in.xls.html

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

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?

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

[CyberBob]

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

[simba]

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

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

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.

[johnb]

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]

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]

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

[gbs]

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]

Is anyone surprised that extremely risky and strange portfolios are the best performing?

[johnb]

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

[Cb]

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

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

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]

Anyway you guys can add 2-Year Treasuries? 8)

[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

[simba]

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

[simba]

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.

[simba]

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

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

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

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

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

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

[norm]

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

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