Roll Your Own

Portfolio Tools for the Masses

In the September 1996 and January 1997 editions of EF we discussed the limited asset allocation tools available to small investors. To recap,these tools fall into two categories:

1) Mean variance optimizers (MVO), using the Markowitz critical line technique, and

2) Portfolio backtesters, which calculate returns and Standard Deviations of hypothetical portfolios based on historical index data.

As of the January edition, the pickings were extremely limited. There was a slightly awkward Markowitz MVO from Portfolio Software, with its $150 DOS based package. We've since looked at several Windows based shareware MVOs, and they are all amazingly difficult to use, with exceedingly cumbersome data entry procedures.

Mutual Fund Expert, from Steele Systems, is a mutual fund database with rather limited backtesting capabilities, (10-15 year returns, but only 3 year SDs). At about $100, it's not a bad mutual fund database, and the backtesting ability is nice bonus, but we wouldn't buy it as a primary portfolio tool. Morningstar has just added some portfolio testing capability to Principia, but amazingly did not allow for the use of indexes, or allow for periodic rebalancing, which is a mathematically trivial procedure.

Help is on the way. For the first time, there are relatively inexpensive and simple to use spreadsheet based portfolio tools available for small investors.

Wagner Associates MVO

Wagner Associates, a financial consulting firm and neighbor to the Vanguard Group, traditionally caters to folks with five figure software budgets, but has recently produced an Excel based spreadsheet file which will rapidly compute and plot the Markowitz mean variance efficient frontier. Data entry is stone simple and intuitive -- you simply directly enter individual asset returns and SDs, as well as the correlation grid into the spreadsheet cells, hit the "optimize" button, and off you go. It's astonishing that nobody has thought of offering such a package to small investors before. The only disadvantage of the program is that you need Excel 7 to run it. I couldn't get it to load into my Quattro 6, and although it loaded into Quattro 7, it wouldn't optimize. I had to go to my accountant's office to see it do its stuff. Hopefully they will dumb the program down a bit for those of us who are spreadsheet cheapskates. The price will be $99. Of course, you'll have to supply your own input data. Sample correlations can be found in The Intelligent Asset Allocator Appendix, and returns/SDs in Chapter 1. Remember, GIGO.
You can reach Wagner at:

Wagner Associates
40 Lloyd Ave., Suite 200
Malvern, PA 19355
800-345-1252
610-644-3400
fax 610-644-6293
www.wagner.com

Ask for Walt Stromquist, or email him at: walt@pa.wagner.com.




Portfolio Backtesting:

Asset Returns from TAM Asset Management, Spreadsheet File from Yours Truly

You would think that somebody by now would have produced a simple index based portfolio backtester for investors to get their fingers dirty with real assets over long time periods. While I have dozens of spreadsheet files which do so, the index data in them is copyrighted, and since I have a yellow streak a mile wide I've been afraid to put this data online. Bill Sharpe can get permission from Ibbotson and Morgan Stanley to do this, but I can't.

TAM Asset Management, Inc., a money management firm in mellow Marin County, comes to the rescue. They're recently put online annual returns for a range of global assets going back to 1973, as part of an FA type client presentation. They've agreed to make this available to EF's readers, and I've written a spreadsheet template that the data can be entered into.

TAM's site is located at http://www.tamasset.com, and is well worth a visit. Jeff Troutner of Tamasset puts out a dandy monthly bulletin of asset allocation called Asset Class on the site. I don't miss an issue.

The numbers are located at http://www.tamasset.com/tampres.pdf , and will of course need Acrobat for viewing. The data itself is located on page 12 of the 19 page document. The spreadsheet template into which the data is entered is downloadable by clicking here. It is an Excel based spreadsheet, and will need to be unzipped. I suggest printing out the data from the .pdf document, engaging the num lock key if you have a number pad, and use the pad. It should take you about 5-10 minutes to manually load the data into cells B3 .. H26.

When you're done with the data entry simply enter the portfolio compositions in cells D29 .. J29 as decimals (i.e., 50% = 0.5), and the 24 year annualized return/SD will pop out in cells C32 and C33. Make sure that the total portfolio composition is 1.0 in cell C31. You can plot the annual portfolio return data in cells R3 .. R26. Perhaps one of you can sweet talk Jeff into making the data available in .txt form for direct importation into the spreadsheet.

So what are you waiting for? The truth is out there. Go find it!

copyright (c) 1997, William J. Bernstein