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.

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?

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?

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.

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.

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 )

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.

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.

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 )

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.

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

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!

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

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?

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.

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.

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.

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

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?

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?

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.

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?

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.

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.