Roll Your Own

Become Your Own Portfolio Analyst

This piece is intended for those who desire to perform their own portfolio analysis. If you are even marginally computer literate, this is not as difficult as you might think. If you are familiar with spreadsheets, it's downright simple.

MEAN VARIANCE OPTIMIZATION

The classic method of portfolio analysis involves "mean variance optimization" ("MVO"), a complex mathematical technique which calculates optimal portfolio compositions. The input data are the returns and standard deviations for each asset, as well as the correlations among each possible asset pair. For example, a portfolio with 4 possible assets requires 14 bits of data -- 4 returns, 4 SDs, and the 6 unique correlations possible among the 4 assets. A portfolio of 10 possible assets requires 65 individual bits of data -- 10 returns, 10 SDs, and the 45 unique correlations between the 10 assets. Most commercially available optimizers are ridiculously expensive -- at least $500 for the most basic models, not including input data. The one exception is the optimizer available from Portfolio Software. Their DOS based programs are quite clunky, but they do the job for $150 (Actually, a year ago they could be had for $35 -- and we all thought inflation was dead).

One major problem with MVOs is that their return/SD/correlation inputs are not easily come by -- you must be reasonably familiar with this sort of data. A good source of basic return/SD/correlation data can be found in Global Investing by Gary Brinson and Roger Ibbotson. Another major problem with MVO is that it does not take the benefit of portfolio rebalancing into effect, which is a significant shortcoming when dealing with high risk assets such as international small cap, emerging markets, and precious metals stocks.


SPREADSHEET BASED PORTFOLIO BACKTESTING

A simpler and potentially more powerful technique for portfolio analysis involves the construction of spreadsheets which calculate the returns and SDs of different asset mixes. All that is required is a conventional spreadsheet program such as Lotus 123, Quattro Pro, or Microsoft Excel, and historical return data. This method has 3 major advantages over MVO:

1. The input data is simpler and more readily available.
2. The technique automatically includes rebalancing.
3. With some experience you can employ your own custom measures of risk.

The only real difficulties with spreadsheet analysis are that the optimizers resident in the spreadsheets can be a bit balky, and that it is difficult (but not impossible) to vary asset returns.

I've made a sample template for Excel for the 1926 Ibbotson data. (Note: This file is not really a .zip file. Do not bother to try to unzip it. It will run as is in your Excel. You might want to rename or save it as an .xls file.)

Because of fair use restrictions I'm not willing to include the required annual return input data, but this is easily available from Stocks, Bonds, Bills, and Inflation from Ibbotson Associates. You can buy their yearbook for $95, or you can peruse your local library's SBBI annual return page. The annual returns data is entered in blocks C2..BT6. This will be a bit tedious, but should take no more than 30 minutes of your time. Since annual data is used, annual rebalancing is assumed. You can devise a monthly returns spreadsheet if you wish, but this will require entering over 4000 bits of data! To get up and running,

1. Download the apropriate file.

2. Obtain and enter the returns data in blocks C2..BT6.

3. Enter the desired portfolio compositions in blocks B8..B12 as decimals (e.g., 55% = 0.55). Make sure that all of your compositions add up to 1.0 in block B15. The number of study years should be entered in block B28 (70 for the period 1926-95).

4. Read the annual returns in blocks B13..BT13, the wealth index (growth of $1 invested in 1926) in block B16, the annualized total return in block B17, and the SD of the portfolio in block B18.

5. Trailing 3, 5, 10, 20, and 30 year returns may be read in rows 23, 24,25,26, and 27, respectively.

Good luck. Future issues of Efficient Frontier will describe more sophisticated models.

William J. Bernstein
wbern@mail.coos.or.us

copyright (c) 1996, William J. Bernstein