Simba's backtesting spreadsheet [a Bogleheads community project]
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
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
another bug
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.
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.
rev5f
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
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
Add Munis?
This spreadsheet is a terrific tool!
Would anyone have national muni fund returns for the 19722006 timeframe that could be added to this fantastic tool  for those who do have not room for taxable bonds in their taxadvantaged accounts and so use taxexempt bond funds in taxable accounts?
For instance, Vanguard ShortTerm TaxExempt Fund Investor Shares (VWSTX), IntermediateTerm TaxExempt Fund Investor Shares (VWITX) and LongTerm TaxExempt Fund Investor Shares (VWLTX).
I can only find 1992  2006 data at the Vanguard site, otherwise I'd try to supply the data.
Would anyone have national muni fund returns for the 19722006 timeframe that could be added to this fantastic tool  for those who do have not room for taxable bonds in their taxadvantaged accounts and so use taxexempt bond funds in taxable accounts?
For instance, Vanguard ShortTerm TaxExempt Fund Investor Shares (VWSTX), IntermediateTerm TaxExempt Fund Investor Shares (VWITX) and LongTerm TaxExempt Fund Investor Shares (VWLTX).
I can only find 1992  2006 data at the Vanguard site, otherwise I'd try to supply the data.
Re: Add Munis?
Bob,Bob wrote:This spreadsheet is a terrific tool!
Would anyone have national muni fund returns for the 19722006 timeframe that could be added to this fantastic tool  for those who do have not room for taxable bonds in their taxadvantaged accounts and so use taxexempt bond funds in taxable accounts?
For instance, Vanguard ShortTerm TaxExempt Fund Investor Shares (VWSTX), IntermediateTerm TaxExempt Fund Investor Shares (VWITX) and LongTerm TaxExempt Fund Investor Shares (VWLTX).
I can only find 1992  2006 data at the Vanguard site, otherwise I'd try to supply the data.
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

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
Updated efficient frontier files (Rev 5G2) posted
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, 19722006 Efficient Frontier
Rev 5G2, 19852006 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 addon which comes as part of Microsoft Office.
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, 19722006 Efficient Frontier
Rev 5G2, 19852006 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 addon 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.

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
Installing and using Excel Solver for Efficient Frontiers
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 percentageonly file versions. Please read below for further details.
1.) INSTALL MICROSOFT EXCEL SOLVER ADDIN
Go to Tools > AddIns > select "Solver AddIn"
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.
***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.
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 percentageonly file versions. Please read below for further details.
1.) INSTALL MICROSOFT EXCEL SOLVER ADDIN
Go to Tools > AddIns > select "Solver AddIn"
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.
***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.
Last edited by Eric White on Wed May 23, 2007 1:30 am, edited 5 times in total.

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
No more integers for efficient frontiers!
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 19722006 with percentages
Rev 5G2 19852006 with percentages
I will take a crack at compiling the data tonight and posting results. Gotta figure out how to append pictures correctly...
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 19722006 with percentages
Rev 5G2 19852006 with percentages
I will take a crack at compiling the data tonight and posting results. Gotta figure out how to append pictures correctly...

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
Updated install Excel Solver and efficient frontiers
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 ADDIN
Go to Tools > AddIns > select "Solver AddIn"
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.
***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.
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 ADDIN
Go to Tools > AddIns > select "Solver AddIn"
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.
***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.

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
Efficient frontier results
Hey everybody,
Efficient frontier results for 19722006 and 19852006 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
Efficient frontier results for 19722006 and 19852006 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
Are you using the latest version rev5g?mikenz wrote:Great work.
One thing I found so far  worksheet SP8506 : the formula for CAGR uses Portfolio!$B$38 (which is 35 years) when it should use $J$38 (22 years). The CAGR should be 1112% but calculates to only 7%. The total also has a VLOOKUP with an argument $B$40 which probably should be $J$40
Eric  I downloaded your files but none of them are working correctly. Thanks for your efforts.

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
Using Rev 5G
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
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
 Posts: 2000
 Joined: Mon Feb 19, 2007 10:32 pm
 Location: The desert

 Posts: 54
 Joined: Fri May 18, 2007 10:09 am
Good to hear
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
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
Updated spreadsheet with inflation info
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://filesupload.com/245335/Backtest ... n.xls.html
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://filesupload.com/245335/Backtest ... n.xls.html
Re: Good to hear
Eric,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
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
Re: Updated spreadsheet with inflation info
sleepy, there is an error in your real return values.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://filesupload.com/245335/Backtest ... n.xls.html
regards, gbs
Updated version rev5h
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 19852006.
Cb  corrected the Intl correlation error
Latest version rev5h can be downloaded here
Regards,
Simba
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 19852006.
Cb  corrected the Intl correlation error
Latest version rev5h can be downloaded here
Regards,
Simba
Getting really good
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?
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?
Simba,
I like the addition of comparison graphs on the "Portfolio" tab of Rev5h. But there seems to be something wrong with the graph and table above it for the 19852006 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 19722006 period, but not for 19852006. Bug?
I like the addition of comparison graphs on the "Portfolio" tab of Rev5h. But there seems to be something wrong with the graph and table above it for the 19852006 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 19722006 period, but not for 19852006. Bug?
Re: Getting really good
That's a heck of a good idea!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 way, it wouldn't require people to have extra software other than a browser and they could always be sure of viewing the latest uptodate numbers.
And, collaboration would certainly be easier.
Bob
Re: Getting really good
baldeagle  Thanks for pointing out that error, I am positive I fixed this earlier but anyhow the latest version rev5i can be downloaded hereCyberBob wrote:That's a heck of a good idea!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 way, it wouldn't require people to have extra software other than a browser and they could always be sure of viewing the latest uptodate numbers.
And, collaboration would certainly be easier.
Bob
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
 Posts: 2000
 Joined: Mon Feb 19, 2007 10:32 pm
 Location: The desert
Google SS
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.
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.
Hi Simba,simba wrote:john,johnb wrote:Thanks very much for posting this. I've found it highly useful.
I added ShortTerm Treasuries data to my copy of the spreadsheet, because I've found it to be a fantastic diversifier.
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 19721983 & VMPXX from 19842006.
The ShortTerm Treasury data I posted were for VFISX, the Vanguard ShortTerm Treasury Fund.
Best regards,
John
Hi GBS,gbs wrote:johnb,
what is your source?
gbs
The source is Vanguard ShortTerm 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
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 19722006.
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
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 !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 19722006.
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
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?
No surprise
Is anyone surprised that extremely risky and strange portfolios are the best performing?
Re: No surprise
Well, I was a bit surprised.edge wrote:Is anyone surprised that extremely risky and strange portfolios are the best performing?
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% ShortTerm 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
Yes. I appears as though the returns for VEURX and VPACX (from 19972006) have been swapped for one another.gbs wrote: P.S. Are there errors in the VEURX and VPACX returns?
Here is a link to the spreadsheet I sent Simba with EUR, PAC, and Int LV historical results:
http://gnobility.com/ER/PAC_EUR_IntVal_19722006.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 19972006 (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
Simba:
The spreadsheet is Great!
? Is it okay to put a copy here ?
http://www.gummystuff.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).
The spreadsheet is Great!
? Is it okay to put a copy here ?
http://www.gummystuff.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).
Simba,
I've noticed that the performance of the Coffee House "reference" portfolio from 19722006 is different from that shown by TrevH in Conversation 58280 on Morningstar's Diehard Forum where he shows he shows:
CHP......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 19722006 is different from that shown by TrevH in Conversation 58280 on Morningstar's Diehard Forum where he shows he shows:
CHP......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?
Updated version 5j
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
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
gummy  This coming from you means a lot to me. Please feel free to keep a copy on your website.gummy wrote:Simba:
The spreadsheet is Great!
? Is it okay to put a copy here ?
http://www.gummystuff.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).
Regards,
Simba
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.baldeagle wrote:Simba,
I've noticed that the performance of the Coffee House "reference" portfolio from 19722006 is different from that shown by TrevH in Conversation 58280 on Morningstar's Diehard Forum where he shows he shows:
CHP......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?
Re: No surprise
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).johnb wrote:Well, I was a bit surprised.edge wrote:Is anyone surprised that extremely risky and strange portfolios are the best performing?
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% ShortTerm 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
SCV  10%
EMV  4%
Intl SV  11%
REIT  11%
Comm  4%
VFISX  60%
This topic has an interesting discussion on his allocation
With Simba's permission, I've put a copy (slightly modified) on my website.
It's described here:
http://www.gummystuff.org/Simba.htm
It's currently version version 5i
... but (when I get a minute) I'll modify it to 5j.
It's described here:
http://www.gummystuff.org/Simba.htm
It's currently version version 5i
... but (when I get a minute) I'll modify it to 5j.
 ddb
 Posts: 5509
 Joined: Mon Feb 26, 2007 12:37 pm
 Location: American Gardens Building, West 81st St.
Yup, I agree. One of my favorite asset allocations is something along the lines of 70% TIPs and 30% international smallcompany stocks (better yet, international emerging smallcompany stocks). Huge tracking error relative to any common benchmark (obviously), but it has a very interesting riskreturn profile.Something I've found interesting through backtesting is that combining the *riskiest* stocks with the *safest* bonds produces some really neat results
 DDB
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!!!
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.
updated with gummy's changes
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.
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.