Please Note: Blog posts are not selected, edited or screened by Seeking Alpha editors.

Excel Spreadsheets For Portfolio Optimization

Summary
 

  • Mean-Variance optimization can be profitably applied to portfolio management.
  • An Excel spreadsheet for optimization of portfolios with three assets is freely available from the author for noncommercial use.
  • Four optimization scenarios are applied to a portfolio made up of MDY, QQQ and SHY.

The mean-variance optimization algorithm has been widely used for portfolio management, ever since its publication in 1952 by Harry Markowitz. I have used it extensively in many articles published at seekingalpha. For almost all articles, I received numerous inquires about the availability of software tools for its implementation. Regularly, I provided links to articles on the web to those who asked. There are many excellent articles describing the algorithm, as well as articles describing tools for its implementing in Excel or in many high level computer languages.

Since there are so many resources readily available on the internet, what motivates me to write another article on this subject? My desire is to help the many readers who keep asking me about an easy tool to use.

Here is the list of securities used to build portfolios:

  • SPDR S&P Mid-Cap 400 ETF Trust (NYSEARCA:MDY)
  • Power Shares QQQ Trust (NASDAQ:QQQ)
  • I-Shares 1-3 Year Treasury Bond (NYSEARCA:SHY)

Basic information about the funds was extracted from Yahoo Finance and is shown in table 1.

Table 1.

Symbol

Inception Date

Net Assets

Yield%

Category

MDY

5/4/1995

17.04B

1.08%

Mid-Cap Blend

QQQ

3/31/1999

45B

1.01%

Technology Large-Cap

SHY

7/22/2002

9.17B

0.42%

Short Term Treasury Bond

The data for the study were downloaded from Yahoo Finance on the Historical Prices menu for MDY, QQQ, SHY. We use the daily price data adjusted for dividend payments.

SHY was created on 7/22/2002. Since we need 65 trading days for estimating market parameters, we were able to simulate our optimal allocation strategy starting with October 2002. We performed an analysis of the difference in performance of the portfolios over a 153 months period.

The portfolio is managed as dictated by a variance-return optimization algorithm developed on the Modern Portfolio Theory (Markowitz). The allocation is rebalanced monthly at market closing of the first trading day of the month. The optimization algorithm seeks to maximize the return under a constraint on the portfolio risk determined as the standard deviation of daily returns.

In table 2 we list the total return, the compound average growth rate (CAGR%), the maximum drawdown (maxDD%), the annual volatility (VOL%), the Sharpe ratio and the Sortino ratio of the portfolios. We simulated the performance of the portfolio under three targets of the volatility of the returns: minimum, mid and maximum.

Table 2. Performance of the portfolio from October 2002 to June 2015

 

TotRet%

CAGR%

maxDD%

VOL%

Sharpe

Sortino

MIN_VAR

34.48

2.37

-2.12

1.43

1.65

2.13

MID_VOL

176.81

8.38

-10.96

8.9

0.94

1.19

MAX_RET

291.33

11.38

-31.99

16.7

0.68

0.85

SPY

195.49

8.94

-55.19

19.23

0.46

0.57

In figure 1 we show the equity curves for the portfolio with the three targets of the volatility.

Figure 1. Equity curves for the volatility enhanced portfolio adaptively optimized with a low, mid, and high volatility constraint.

Source: This chart is based on calculations using the adjusted daily closing share prices of securities.

The mean-variance optimization algorithm can easily be implemented in Excel. Wayne A. Thorp published a couple of articles in AAII Computerized Investing magazine. In developing my spreadsheets, I followed his instructions. I implemented four different strategies, each on a different excel sheet.

My spreadsheet is extremely easy to use, basically in two steps. First step, the user enters by copy and paste or any other means the adjusted closing prices of the stocks. Second step, the user launches the "Excel Solver", found under the Data tab. Change the value of some parameters if you wish and click the "Solve" button in the solver's dialog window. The algorithm modifies the weightings of the assets to achieve the optimization requested.

Following is the list of the four variants, each programmed in a separate sheet:

  1. MAX_RET: maximize return without any constraint on variance
  2. MIN_VAR: minimize variance
  3. TGT_RET: minimize variance for a target return
  4. TGT_VOL: maximize return for a target volatility

In table 3 we present the allocations obtained for July 2015. For those calculations we used the price data for 65 trading days starting on March 31, ending on July 1.

Table 3

 

QQQ

MDY

SHY

ANNUALIZED_ VOL

ANNUALIZED_RET

MIN_VAR

0%

2%

98%

0.81%

0.11%

TGT_VOL=8%

63%

0%

37%

8%

5.78%

TGT_RET=8%

87%

0%

13%

11.18%

8%

MAX_RET

100%

0%

0%

12.79%

9.12%

The annualized volatility varies from a low of 0.81% when investing 98% in SHY and 2% in MDY to a high of 12.79% when investing 100% in QQQ. For the same extreme allocations, the annualized return varies from a low of 0.11% to a high of 9.12%.

Conclusion

The mean-variance optimization algorithm allows a lot of flexibility is managing portfolios to match various investor preferences. The algorithm can easily be executed in an Excel spreadsheet. The Excel spreadsheet can be freely obtained for noncommercial use from the author via email.

Disclosure: I am/we are long QQQ.