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!

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.

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.

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

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?

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.

Best regards.

----------------------------------------------------------------

My post to Ozzy:

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.

Best regards.

***************************************************

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.

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.

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

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

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