Attached is an Excel model that utilizes the Markowitz Portfolio Theory to beat the S&P 100® with a diversified subset of stocks taken directly from the S&P 100®. Ninety-six stocks were optimized simultaneously using Solver (i.e., Frontline System’s Solver that is bundled with Excel), with an objective function of minimizing the portfolio’s CV. The model was first optimized for stock returns between ten to five years ago, and solver selected 12 stocks for the optimized portfolio. The model was then tested against stock returns between five and zero years ago to see how it performed against the S&P 100® Index. During this test period of time, the market returned an annual 0.42%, whereas the optimized stock portfolio of 12 stocks returned an annual 12.04%. To further refine and test this approach, the model should be characterized against different periods of returns (i.e., bear/bear, bear/bull. bull/bull, and bull/bear markets) and against other indices to further diversify the portfolio, and to optimize the model around macroeconomic cycles.

What follows is a detailed description of the model, please download the model here, but note that this is a 5MB model: **Application of Markowitz Portfolio Theory**

The first worksheet in this workbook labeled *Index* lists the current companies that comprise the S&P 100®. Because my optimization and testing range ten years, four of the current companies had to be excluded since they have been added to the index within the last ten years, and these companies are Google Inc. (GOOG), Mastercard Inc. (MA), NYSE Euronet (NYX) and Phillip Morris International (PM).

The next worksheet, Equity Optimizer, is where the ten to five year old stock data is optimized with Solver to obtain a minimum portfolio CV. The objective function is thus cell C16, with Solver varying the weights of the individual stocks in the portfolio in cells D3 through CU3, with an arbitrary 1,000 stock portfolio being an added constraint. Other constraints imposed on this model included whole stock purchases, and only long positions on stock by limiting the stocks to only positive purchases (i.e., a negative stock weight in the optimized portfolio would represent a short). To optimize the CV, the portfolio’s standard deviation must be calculated, and this was accomplished using the following equation that can be found in cell C15:

=MMULT(D12:CU12,MMULT(D20:CU115,TRANSPOSE(D12:CU12)))

This equation takes the stock weights multiplied by their individual standard deviations (i.e., cells D12:CU12), transposes this from their horizontal position to a vertical vector orientation, then multiplies this vector by the correlation matrix in cells D20:CU115, with this resulting matrix then being multiplied again by the horizontal vector D12:CU12. The resulting matrix is then the Markowitz Portfolio Theory equation. Since this is the variance for the portfolio, the square root of this equation is then taken in cell C15 to obtain the standard deviation of the portfolio. The correlation matrix was built using the Correl function in cell D18, and using the named ranges such as AAPL in this example. The correlation matrix was copied over with static values since it is not dependent on the portfolio’s weighting, and to speed-up the model.

This optimized portfolio is then applied to the returns for the next five years for the individual stocks (i.e., Yahoo Finance data that is adjusted for dividends and splits), and this is done using the approximated equations in worksheet *Future Stats *and by the rigorous method of calculating each weekly return for the portfolio in the worksheet *Future Stats Detailed*. As can be seen, the returns are obviously the same between these two methods since they blend linearly, but the standard deviations vary slightly (i.e., 24% for the approximated method versus 22.6% for the absolute method), with this being due to the approximate beginning/ending stock average used in the approximate model (i.e., cells D9 through P9), versus the perfect weighting of the absolute model. For purposes of optimization and speed of the model solving, the approximate method is probably accurate enough.

The optimized portfolio is then compared against the S&P 100® in cells C269 through F278 of the worksheet *Future Stats Detailed*, and for CV, Treynor, Sharpe, Alpha, Beta, and the Information Ratio the portfolio outperformed the index. The portfolio still correlates well with the index as seen by a correlation coefficient of 0.88. And the chart *Returns* shows the performance of the portfolio over the index.

The chart* Diversification* shows the portfolio’s standard deviation as each stock is added to the portfolio, with the fully diversified S&P 100® being the datapoint to the far right. This curve fits the classic textbook curves that show a single randomly selected stock having a standard deviation of around 35%, with this then being reduced to around 16% by adding randomly selected stocks to the portfolio, and the typical stock correlation of 0.6 reducing the stand-alone company specific risk. Depending on the study or textbook, the number of stocks required for full diversification ranges somewhere between 20 and 35 stocks, so the portfolio obtained in the attached model should probably be supplemented with additional stocks from other indices as well as international stocks. At 12 stocks though, with a standard deviation of 22.6%, it is almost equal to the S&P 100 which had a standard deviation of 21.1%.

You may be asking yourself if this can be real or not, a 12.04% portfolio return compared to a S&P 100® Index return of only 0.42%, but consider this fact – an index is not an optimized set of stocks, it is just the top 100 stocks chosen on biased parameters set by the exchange. Quoted straight from S&P themselves, “The S&P 100, a subset of the S&P 500, is comprised of 100 leading U.S. stocks with exchange listed options. Constituents of the S&P 100 are selected for sector balance and represent over 60% of the market capitalization of the S&P 500 and almost 45% of the market capitalization of the U.S. equity markets.” This says nothing with regards to risk and return, or an optimized portfolio, and thus the attached optimization model can produce a portfolio of stocks from the S&P100® that beats the index. This fact even holds more weight when one considers that only one third of managed funds beat index funds, and here with this simple model we were able to beat an index fund.

©2011 Ben Etzkorn