Zapped wrote:Now I've just got the blank graphs I mentioned...
I'm looking at it right now and it appears that the data-range for those two graphs is incorrect and is showing the data for the graphs to be located at README$A$1.
I'll look at it later to see exactly what's up, but to just get the graphs working was an easy fix. PM me an email address and I'll shoot you a .ods file with working graphs.
Does that happen often, that rebalancing hurts a portfolio?
Rebalancing replaces good performers with poorer performers
... so (maybe) it does happen often.
P.S.
I've had lots of email asking how to run my spreadsheets with (for example) google software.
I ain't found out how ... yet.
P.S.2
Once upon a time I had started a macro that did Monte Carlo, but have since removed it.
Hence the warning 'bout macros (on earlier versions of Simba's spreadsheet).
First, thanks so much for the excellent spreadsheet. It's a brilliant piece of work.
Pardon me if this has already been covered, but I'd like to ask a question about a possible error in the Sortino ratio calculation in rev6e. I noticed that if I input a Coffeehouse Portfolio on the Portfolio worksheet, I end up with a Sortino ratio of 1.90 (Portfolio Growth-Nominal) vs. 2.90 calculated in the SP-85-06 sheet. I checked the Sortino ratio formula (SP-85-06 worksheet cell F29) and found that it references the worksheet Data_72_06 rather than referencing worksheet Data_85_06. I tried correcting the sheet reference (and the cell range referenced in the formula) but ended up with a #VALUE error.
Any thoughts on a simple fix? Thanks once again for taking the time to make this tool available to us all!
kaesler wrote:Is enough historical data on VINEX (Vanguard Ingternational Explorer) available so that it could be added to this terrific spreadsheet?
VINEX was started in 1996 so it would be difficult to add it to the spreadsheet.
However you are welcome to play around with the SS by adding it yourself.
The latest version of SS (rev 6g) that can be downloaded here makes it easier to add additional funds (you may have to change the Start year to include funds that were started in the recent years).
FYI - I had compiled the historical returns for Vanguard funds that I posted in this Thread
You can download the historical returns of Vanguard funds here.
I also compiled the returns for Dodge & Cox. You can download them here.
First, thanks so much for the excellent spreadsheet. It's a brilliant piece of work.
Pardon me if this has already been covered, but I'd like to ask a question about a possible error in the Sortino ratio calculation in rev6e. I noticed that if I input a Coffeehouse Portfolio on the Portfolio worksheet, I end up with a Sortino ratio of 1.90 (Portfolio Growth-Nominal) vs. 2.90 calculated in the SP-85-06 sheet. I checked the Sortino ratio formula (SP-85-06 worksheet cell F29) and found that it references the worksheet Data_72_06 rather than referencing worksheet Data_85_06. I tried correcting the sheet reference (and the cell range referenced in the formula) but ended up with a #VALUE error.
Any thoughts on a simple fix? Thanks once again for taking the time to make this tool available to us all!
Cheers!
Mike
First post!
Mike - Welcome to the forum. Thanks for the compliment.
I've corrected the sortino ratio and made a few other changes. The latest version rev6g can be downloaded here.
After confirming with gummy, I've removed the Ito's calculation from the SS for now.
Something happened to the 35 year run for CHP on the Portfolio tab in rev-6g. The table still shows the $481k ending value, but the graph below it tops out around $100K. Graph looks just like the run to its right for 22 years.
Something happened to the 35 year run for CHP on the Portfolio tab in rev-6g. The table still shows the $481k ending value, but the graph below it tops out around $100K. Graph looks just like the run to its right for 22 years.
The graph shows the real value for CHP while showing the nominal values for rebalanced & un-rebalanced. Cyberbob pointed me to that error and this will be corrected in the next revision.
Thanks for all of the work on these spreadsheets. They are very interesting and fun to test different portfolios with.
I was wondering what your thoughts are on the validity of using a 30 year period to backtest a portfolio strategy. Is this really enough time to base asset allocation decision on?
I was wondering what your thoughts are on the validity of using a 30 year period to backtest a portfolio strategy. Is this really enough time to base asset allocation decision on?
With respect to the bond portion of the portfolio, no. IMO, best way to treat bonds (and TIPS) are to use current yields (or fixed component rate). In the case of TIPS, the future inflation number is still a guess. So for traditional bonds, you still have the unknown of price volatility, and for TIPS the unknown of inflation volatility (that's where MC simulation can come in handy).
For the equity portion, this model provides food for thought on the importance of diversifying in various asset classes - but don't use it as the holy grail - just understand that it is beneficial to diversify in various asset classes (and that historically pushing out the efficient frontier with the nth asset class may or may not work in the future), be tax-efficient, rebalance and make sure your portfolio risk matches your need to take risk.
This spreadsheet is a very good tool - but to solely determine portfolio asset allocation on this model was probably not the intention of the developers of this spreadsheet (since there are other factors involved).
That being said, I still like the overall body of work - and it's fun to play with. However, I leave you with this thought - if we had today's knowledge (and computers) in the early 1940's, what sort of conclusions would we come up with using this type of model then?
mwgr5 wrote:Thanks for all of the work on these spreadsheets. They are very interesting and fun to test different portfolios with.
I was wondering what your thoughts are on the validity of using a 30 year period to backtest a portfolio strategy. Is this really enough time to base asset allocation decision on?
My whole intent for developing this tool was to use it as a learning tool. I DO NOT intend to change my allocation based on data mining or historical returns. As RM and others have said, historical returns are well historical. There is no guarantee that we'll get those returns.
I wouldn't base my allocation purely based on returns/valuations etc. You have to take into consideration individual circumstances/goals/risks etc and remember "There is no such thing as a perfect portfolio, but only the one that works for you and helps you meet your goals".
Jack/Larry/Rick and Bill B agree that the next 20-30 years will not result in the same kind of returns that we saw in the previous 20 years.
Rick has posted the valuations for the next 30 years based on his research here.
MCB VIMSX
COMM PCRIX
Wellington VWELX
Wellesley VWINX
Energy VGENX
Health VGHCX
P.Metals VGPMX
Windsor VWNDX
Windsor II VWNFX
V LT TE VWLTX
MidCap Growth VMGIX
MidCap Val VMVIX
V Ext Mkt VEXMX
Both Windsor and Windsor II are LV funds - Would it be okay to use the valuations for LV here?
Wellington (60/40) - Wellesley (40/60) are balanced funds. Would it be okay to use the valuations for LC and Inter Bond for these?
Hi
I downloaded your spreadsheet OK. I also entered values OK but the software did not respond to the input percentages. Does it automatically update on data entry or do you have to initiate the update after data entry and if so how is it done . ? You did a fantastic job and I am anxious to try it out if I can get to work. Thanks for your help. aurel
avstan@cableone.net wrote:Hi
I downloaded your spreadsheet OK. I also entered values OK but the software did not respond to the input percentages. Does it automatically update on data entry or do you have to initiate the update after data entry and if so how is it done . ? You did a fantastic job and I am anxious to try it out if I can get to work. Thanks for your help. aurel
Aurel - Welcome to the forum. Once you enter your allocation % (as long as it equals 100%), you don't need to do anything else.
Take a look at Gummy's site to see if you entered the values correctly [rev6j does not have Ito's probability stuff but you should still be able to check if you are doing anything wrong].
Cyberbob helped me out me with a workaround to get the charts to show up in OO. If you are using OO, please download rev6g for OO .
FWIW, I created a synthetic MSCI 1750 Value Index(the index that VISVX/VBR follow). I used MSCI's data from 1996-2006 and from 1972-1995 I used ( 1.0 * Market + .40 * SmB + .80 * HmL - .18 ) = return as per Robert T's regression and the Kenneth French Annual Factor Returns.
Edit: I think there is something fishy with the result--MSCI SV is superior to FF SV using that methodology.
If I were to chose between those two I chose the second. Still that does not seem right as the difference is the opposite of what I expected what gives?
One other thing that I tried and was not able to do was to come up with an un-rebalanced combination of VPACX and VEURX that will have the VDMIX risk/return.
1972-2006
VDMIX CAGR 11.31% STDEV 21.91%
Can someone find the matching combination and if not why not?
If I were to chose between those two I chose the second. Still that does not seem right as the difference is the opposite of what I expected what gives?
Two reasons:
1. CRSP Decile 9 was a dog from 1972-2006. D6-8 outperformed D9-10 over that period with lower variance:
D6-8
CAGR: 13.48%
SD: 22.36%
D9-10
CAGR: 12.92%
SD: 26.65%
From a portfolio perspective, D9-10 weren't quite that bad because they had lower correlations with both U.S. Market and International Market.
The first portfolio is overexposed to D9-10 relative to the 2nd.
2. 0.8 HmL for MSCI might not be a reasonable approximation for 1972-2006. That was the factor regression for 1996-2006 only.
An IFA.com pamphlet showed HmL loading in the .4 range for FF SV, S&P 600 Barra Value and Russell 2000 Value from 1979-2003, whereas all of those were .65-.8 range from 96-06.
I'm loathe to use their data(their Emerging Markets construction is completely bogus, for example), but it's the only source I know of for this asset class.
Can someone briefly explain to me the differences in how Nominal and Real Growth are calculated in this spreadsheet model? Appreciate it.
Rich
Nominal would be the value of your Asset Allocation at the time specified in the spreadsheet.
Real Value specifies the value of the dollar amount in today’s time (accounting for inflation).
Hi,
Thanks for the reply. This is what I thought, but it seems that when I compare a test portfolio with the Coffeehouse portfolio, that there is barely a difference between the Coffeehouse nominal and real returns while there is a significant one with the tested portfolio? Am I misreading something? Thanks again for the help.
Vig Oren wrote:What good is backtesting if most likely it will not repeat? :roll:
Backtesting is useful for measuring results in the context of risk, plus it's useful for discovering the correlation of asset classes under different market conditions. These are things that do tend to repeat btw.
I love to see Backtesting spreadsheets and hope that they would really help my portfolio's performance, especially now while I am retired and depend on withdrawals for a living.
BUT
If Backtesting has any merit in it why was I criticized by Ozark542 on M* discussion board in APR 2003? See it here:
Ozark542 = Ozzy
Vig = Viggy
Ozzy's post:
Predicting the past
Actually, Viggy, I'm wide awake. Or at least I was until I tried reading the stuff at Dr. Sortino's web site.
Here's the deal, Viggy. If you feel you can improve your portfolio's asset allocation by running the portfolio through various computer programs, measuring and grading various risk/reward relationships, feel free. It's okay with me. Honest. For myself, I'm not interested.
I'm also not interested in running reams of data through a computer program in order to discover how much I can withdraw yearly from my portfolio and never go broke.
Without having studied it, I'm willing to assume the Risk Grades deal is similar to the well known Efficient Frontier concept: Invest in a mix of assets that will give the best return for the least risk.
Wonderful. The problem in execution is this; both these approaches would seem to be limited to looking at PAST risk/return relationships, in order to predict FUTURE such relationships.
This approach hasn't worked very well and it never will.
There's lots of stuff we can learn by studying the past. One thing we can't learn, though, is how much the future will resemble the past.
There really is an Efficient Frontier. There really is a withdrawal rate that will allow my wife and I to spend all our money during our life times, but never go broke.
But Viggy? These things are unknown and unknowable, going forward. Such things are only knowable looking backward.
Given that such things are only knowable looking backward, academics with more letters after their names than I have money in the bank, have spent unconscionable amounts of time goobering through the past. They thus invented Modern Portfolio Theory---Beta, Alpha, R-Squared, and the crowning achievement, Sharpe Ratio. These accomplishments were celebrated and awards were given. Yes.
And then...a funny thing happened on the way to the bank. These numbers turned out to have little or no predictive value, regarding returns. And since they couldn't predict returns, they also failed to predict risk/return ratios.
Joining in the fun, M* invented their first Star Rating system, a system that graded...yep...risk- adjusted, past performance.
I wish I had 10 bucks for every post I've read where the poster said, essentially, "I have a balanced portfolio, made up entirely of 4 and 5 star funds." Too late, these jokers discovered what M* eventually discovered; past risk-adjusted performance doesn't predict future risk-adjusted performance.
I don't want to discover the Sharpe Ratio of my portfolio, Vig. I don't want to discover its Beta. I don't want to discover its Risk Grade. I have absolutely no confidence that adjusting the portfolio so that these numbers become more favorable will improve future risk/reward.
If others do want to do that, that's okay with me. I seriously doubt, though, that many successful mutual fund managers select securities in that manner. If any do, or if any money managers set their asset allocations in that manner, I'd be interested in their long-term results---results over periods of, say, 10 years, or more.
In short, Vig, computers are wondrous tools, but that's all they are. Every computer on Earth, all linked up and working 24/7, from now on, won't tell me my survivable withdrawal rate. Neither will they tell me what asset allocation would give me the best risk/reward ratio.
In my opinion, these things can't be calculated. We have to forge ahead without knowing these things. Deal with it.
Thanks Ozzy for the reply. You still need to explain why you go at all for mutual funds in which managers "waste" days and nights on Computerized Optimization, Indexing, Sortino's returns, paying huge amounts (your money!) to Mr. William Sharpe's company, etc.
Humor me a minute here, Vig. List all the mutual funds in which I invest, and tell me how many managers of those funds employ the techniques you describe, in selecting individual stocks and bonds for their portfolios. Also list all my managers who pay huge amounts to Bill Sharpe or similar worthies.
You might also list for us 5 money managers who use the techniques you talk about. Don't forget to supply their total returns over the last 1, 3, 5, and 10 years.
Meanwhile, I'm busy calculating the Reynold's Number for my portfolio. Assuming it to still be liquid, of course.
p.s. the remark about the Reynold's Number was that Ozzy as an ex Jumbo jet pilot never bothered checking the the plane's turbo engines which the technicians had to do on his behalf by using computers.
Returns_85_06 Columns Z and AA (Energy, Health) feed off of Data_85_06 Columns Z and AA. However these data are for S-Tips and Wellington, not Energy, Health.
If I'm right in thinking that this is an error,
I believe it's the Energy, Health, P.Metals columns that don't match between Returns_85_06 and Data_85_06 that are causing things to shift.
Yes, No, Maybe So?
One of these days I have to learn all those formulas you use in Excel.
investorperson,
Thanks for pointing out the error. I verified the links and corrected the spreadsheet. Latest version rev5d can be downloaded here
I tried clicking on the blue "here" and got a message that the page does not exist. What am I doing incorrectly?
Gatorman
Thanks for all the work that went into putting it together
Most investors, both institutional and individual, will find that the best way to own common stocks is through an index fund that charges minimal fees. – Warren Buffett
This spreadsheet is a great work. Many thanks to simba for creating and maintaining it.
The most useful thing for me is the chart showing Max Drawdown. That is the first thing I look at. My main concern is preservation of principle so I always want to know how much my portfolio can drop. I will get a lot of use out of this. It will help answer many questions that pop up.
There should be a permanent link to this thread so it is easy to find.
I'm doing some portfolio analysis today......and decided to plot selected asset classes return versus risk......using the 1972-2006 data in the rev6j.xls version of the spreadsheet posted above. Later I will add some portfolio mixes to compare them to the asset class returns.
Investments I selected were:
And of course.....past performance is no guarantee of future results.
:lol:
Most investors, both institutional and individual, will find that the best way to own common stocks is through an index fund that charges minimal fees. – Warren Buffett
I recently downloaded the most recent version of Trev's awesome spreadsheet. Fantastic job!
I'd like to make a suggestion. I think the "Portfolio Growth" charts should be set to use a logarithmic scale for the y-axis. Otherwise, the linear scale exaggerates the returns of recent years.
The spreadsheet looks like a terrific tool. Unfortunately, when I put my numbers in, I get nothing but #VALUE! errors at the bottom, in rows 57-58 (Portfolio Growth - Nominal and Portfolio Growth - Real).
iad wrote:The spreadsheet looks like a terrific tool. Unfortunately, when I put my numbers in, I get nothing but #VALUE! errors at the bottom, in rows 57-58 (Portfolio Growth - Nominal and Portfolio Growth - Real).
Does anyone have any idea what the problem is?
Are you sure the test portfolio you entered adds up to 100%?
Bad things happen if you are too low or too high.
There are two validation checkpoints built into the spreadsheet (Row 36/Row 49 in Rev 6j, depending upon whether you are using data back to 1972 or 1985). Make sure this checkpoint is exactly 100%.
The problem with my getting #VALUE! errors doesn't stem from the columns not adding up to 100%. They DO add up to 100% exactly.
What's weird is that the "Compare 5 Portfolios" parts of the spreadsheet work fine for both 1972-2006 and 1985-2006 scenarios. It's just the top sections that fail in the results rows when I put my own values in.
iad wrote:The problem with my getting #VALUE! errors doesn't stem from the columns not adding up to 100%. They DO add up to 100% exactly.
What's weird is that the "Compare 5 Portfolios" parts of the spreadsheet work fine for both 1972-2006 and 1985-2006 scenarios. It's just the top sections that fail in the results rows when I put my own values in.
Go through and clear the cells with a <backspace> and make sure they are truly empty. I have funky stuff happen if you get a space or other spurious white space chars such as tabs.
I know most of the regular readers know that the idea behind the spreadsheet is not for optimization or to change asset allocation based on backtested results. For the newcomers to the site, this was a fun project and please treat it as such.
I've updated the SS with the 2007 returns (Yet to update data for BRSIX, PCRIX and the Inflation data[CPI-U])
I have removed the charts/graphs from the OO version (for now). If anyone knows how to convert the Excel macros to be compatible with OO, I would really appreciate it.
Thanks and Best Regards,
Simba
Edit: My apologies, the file I uploaded was an older version.
Last edited by simba on Thu Jan 10, 2008 10:14 pm, edited 1 time in total.