Efficient Portfolios Using Solver PDF

Preview

Summary

Module Notes...

Description

Determination of Mean-Variance Efficient Portfolios Using an Electronic Spreadsheet David A. Carter, William H. Dare, and William B. Elliott College of Business Administration, Oklahoma State University, Stillwater, OK 74078, USA Acknowledgements: We thank Andy Terry and participants at the 2001 Southern Finance Association meeting for comments on this paper. Abstract We present a method to quickly set-up and solve for mean-variance efficient portfolios in a Microsoft Excel spreadsheet using data downloaded from the Internet. As a pedagogical tool, this exercise provides several benefits. First, it stimulates student interest because of the use of ‘real’ data from the Internet, it builds their spreadsheet and general computer skills, and improves their understanding of financial concepts. The method can solve for mean-variance efficient portfolios equivalent to the Markowitz method but requires considerably less effort to formulate in a computer spreadsheet. INTRODUCTION The portfolio model developed by Markowitz [1952] ranks as one of the more important concepts in financial economics. However, the mathematical rigor underlying the Markowitz model can make the theory inaccessible to students. The calculations required in the determination of mean-variance efficient portfolios, of more than two assets, can be tedious and cumbersome. This is particularly true for most undergraduate business students who are not likely to have taken courses in linear algebra or advanced computer programming. For instance, Pace [1996] notes that students are often intimidated by the matrix algebra necessary to create the variance-covariance matrices used in efficient portfolio determination. The power and flexibility of current spreadsheet software offers the possibility of overcoming the gap between the model’s concepts and its mathematical rigor. Stephens [1998] and Kwan [2001] demonstrate the use of the spreadsheet in the determination of efficient portfolios. However, these articles focus on the use of matrix algebra, utilizing the spreadsheet’s matrix functions, to perform the calculations necessary for efficient portfolio determination for large portfolios. 1 The difficulty with this approach is that it still relies on a knowledge of matrix algebra by students. Instead, we argue that by using the Solver feature in an Excel spreadsheet environment, the determination of mean-variance efficient portfolios and the illustration of portfolio theory is easily accomplished without students needing a knowledge of matrix algebra. This method is appropriate for large asset universes and is readily available to finance educators and their students. Coupled with the use of real data from the Internet, this method can provide a powerful in-class demonstration for undergraduate finance students, effectively illustrating the important concepts of the Markowitz model. Further, the methodology is easily adapted to out-of-class assignments. In this paper, we discuss the use of Excel’s Solver as a means of computing mean-variance efficient portfolios and present an example using historical data obtained from the Internet. The pedagogical value for students implementing this method in a Microsoft Excel spreadsheet using Solver is substantial. First, the student’s spreadsheet skills are improved. Secondly, although the optimization routine itself is a “black box,” the student gains a better understanding of the objective function and constraints of the portfolio optimization problem. By observing the individual returns of the efficient portfolio for each observation in the dataset, the student gains a direct appreciation for the distribution of returns of an efficient portfolio compared to a naïve, equally weighted portfolio. Finally, the use of real data from the Internet increases student interest in the exercise. The paper progresses as follows: Section II presents a mathematically direct determination of mean-variance efficient portfolios using historical data. In Section III, downloading data from the Internet and the spreadsheet construction is shown and a detailed example is presented. Section IV concludes.

DETERMINATION OF PORTFOLIO MEAN AND VARIANCE USING HISTORICAL DATA

1

The general use of either the Markowitz or Single-Index model approach to solve for mean-variance efficient portfolios requires calculations on historical asset data. If we assume that we are using historical data, we can rewrite the standard Markowitz equations and arrive at less complex equations that express portfolio return and variance in terms of historical portfolio returns, rather than historical asset returns. To begin, note that the familiar equation for the expected return of a portfolio is nothing more than a simple weighted average of the expected returns of the individual assets contained in the portfolio: N

E ( R p )  w i E ( R i ) ,

(1)

i1

where:

E(Rp) wi E(Ri) N

= Expected return of portfolio p, = Portfolio weight of asset i, = Expected return of asset i, and = Number of assets in universe selected.

Using historical asset returns, we know that the expected return to any asset, i, is the mean return of the asset over time: T

R it . t1 T

E (R i ) 

(2)

In Equation (2), t is an individual time period and T is the total number of periods used in the calculation of the mean. Substitution of Equation (2) into Equation (1) yields: E(Rp) 



N

T

i 1

t 1

 wi 

Rit T

1 N T   wi Rit . T i 1 t1

(3)

In Equation (3) we find that the expected return of a portfolio is the weighted average of the individual asset returns, averaged over time T. The important result from (3) is that the individual asset weights, w i, are constant over the periods. Since the asset weights are constant over time, the portfolio return in any time period t is simply the weighted average of the individual asset returns in that t period: Rpt 

N

w R i

it

.

(4)

i1

Then, through substitution of (4) into (3), we can rewrite Equation (3) in terms of the t-period portfolio returns: E(Rp) 

1 T  R pt . T i 1

(5)

In Equation (5) we find that the expected return of a portfolio that is calculated using historical asset returns (R it) is equivalent to the mean of the historical t-period portfolio returns (R pt), averaged over time. Therefore, to solve for expected portfolio return, all we need are the t-period portfolio returns and not the individual asset returns.

2

The real convenience derived from Equation (5) is that we no longer need the variance-covariance matrix to solve for the portfolio variance. The portfolio variance is simply the variance of the t-period portfolio returns:

1 T R pt  E (R p ) 2 .   T  1 t 1

(6)

In the next section we present a straightforward method to solve for mean-variance efficient portfolios using Equations (5) and (6) in a widely-used computer spreadsheet with data downloaded from the Internet.

EFFICIENT PORTFOLIO DETERMINATION EXAMPLE Stock-Price Data Acquisition from the Internet While the exercise below can be implemented with “canned” or simulated data supplied by the instructor, it is more interesting and relevant to students to obtain current data on well-known stocks from the Internet. For this example, we use Yahoo! Finance as the source of stock-price data. 2 A web browser, such as Netscape Navigator or Microsoft Internet Explorer can be used to access Yahoo! Finance. The URL (e.g., internet address) for Yahoo! Finance is http://finance.yahoo.com/.3 Figure 1 shows the Yahoo! Finance screen as it appears in the browser window. Place Figure 1 about here A great deal of information about U.S. and world financial markets, as well as individual firms is readily available from Yahoo! Finance. 4 Yahoo requires that ticker symbols be entered to get data for individual firms. If the ticker is not known, the symbol lookup feature can be used to search for the desired ticker symbol. 5 In this exercise we will obtain stock-price data for five firms: Apple Computer (AAPL), Anheuser-Busch (BUD), Coca-Cola (KO), Exxon (XOM), and General Motors (GM). If we enter the ticker for Apple Computer (AAPL) and select the Chart format for the report, Yahoo! Finance will return the screen shown in Figure 2. Place Figure 2 about here Select historical quotes to move to the page that allows the downloading of historical stock prices (see Figure 3). In the spaces provided, enter the beginning and ending dates for the stock-price data. Then select the frequency of the data. In this example, we use monthly stock-price data for the period of December 1998 through December 1999. Next click on the Get Historical Data button to obtain the data. Finally, click on Download Spreadsheet Format located at the bottom of the historical data. The data are downloaded in comma-delimited format (csv) that can be opened by a spreadsheet program, such as Microsoft Excel. 6 Place Figure 3 about here Efficient Portfolio Determination Using a Excel Solver Once the stock-price data have been downloaded and merged into a single Excel worksheet, it is necessary to compute monthly returns. The monthly return for each stock is calculated as follows: Ri,t = (Pt / Pt-1)-1.

(7)

For example, the December 1999 return for Apple Computer in “Excel-ese” becomes =(B3/B4)-1. In addition, we also calculate the average monthly return and standard deviation of returns for each stock using the AVERAGE and STDEVP functions. If we make the assumption that the future is like the past, the expected returns for our stocks are the mean returns. So that we can make comparisons later on with efficient portfolios constructed using Solver, we construct returns for an equally-weighted portfolio of the five stocks. Place Figure 4 about here

3

As shown in Figure 4, column L contains monthly returns to an equally-weighted portfolio, while row 20 contains the weights. The return to the portfolio for each time period t is: n

R p,t   wi Ri ,t .

(8)

i 1

To find the return to the portfolio for each time period t, we use the Excel function SUMPRODUCT as follows: =SUMPRODUCT(vector of weights , vector of returns).

(9)

The SUMPRODUCT function returns the sum of the products of the mean (expected) returns and the weights for each of the stocks. We also calculate the average monthly return and standard deviation of returns for the equallyweighted portfolio. The formulas to calculate the returns, mean returns, and standard deviation of returns are shown in Figure 5. Place Figure 5 about here Initially, we will use Solver to find the weights of each of the stocks that will create a global minimum-variance portfolio. This is a constrained optimization problem in which we minimize the following objective function:

1

5 5 5  2  P   w2i i2   wi wj ij , i1 i 1 j 1    ji

(10a)

subject to the constraint: 5

w

i

1 .

(10b)

i 1

Microsoft Excel’s Solver (found under the Tools menu option) provides a means of optimizing an objective function, such as is given in Equation (10a), subject to a set of constraints. Solver uses the Generalized Reduced Gradient (GRG2) nonlinear optimization routine developed by Lasdon, Waren, Jain, and Ratner [1978]. Due to the limitations of this routine, in limited cases Solver may not find an optimal solution, given a particular set of starting values (starting portfolio weights). When Solver is unable to find an optimal solution, a message describing the error will be displayed in the Solver Results dialog box (e.g. ‘Solver could not find a feasible solution’). For the portfolio optimization problem, this error can be resolved by using a different set of starting values (i.e. change the initial portfolio weights). 7 Although it is possible to obtain an Enhanced Solver from Frontline Systems (www.frontsys.com) that will minimize this result, it is not required, as long as students pay close attention to the Solver Results dialog box upon completion of the routine. 8 To modify our Excel worksheet to solve this optimization problem, we first create a row of weights for the minimum-variance portfolio underneath the row of weights used for the equally-weighted portfolio. Initially, we set the weights to 20 percent each. 9 In cell L21, we add a formula to sum the weights [i.e., =SUM(G21:K21)]. The sum of the weights will be used later as a constraint. In column M, we enter the formulas necessary to calculate the returns to our portfolio for each period. We also calculate the expected return and standard deviation of returns. To access the Solver dialog box, we first select the Tools pull-down menu from the Excel menu bar and then click Solver.10 The Solver dialog box will appear as shown in Figure 6. Because we want to minimize the variance of our portfolio, the target cell is the portfolio standard deviation (M18). Click on the Min button so that Solver will minimize the target cell. We enter the cell references for the weights (G21:K21) into the By Changing Cells: box. Solver will seek a solution that minimizes the portfolio standard deviation by changing the weights of the stocks making up the portfolio.

4

Place Figure 6 about here At this point, if we were to click on Solve , Solver would produce a solution in which each of the weights would be zero. This is because we have not entered a constraint, thus the unconstrained solution is a zero standard deviation produced by weights of zero (i.e., no investment in any of the five stocks). We need to add the constraint that the sum of the weights must equal one into the Subject to the Constraints: box. Click on the Add button to add the constraint. The Add Constraint dialog box, shown in Figure 7, will appear. Type L21 into the Cell Reference: box. This is the cell containing the formula summing the weights of each of the stocks in the portfolio. Click the down-arrow button in the center box and select the equal operator. Because we want the sum of the weights constrained to equal one, we enter “1” into the Constraint: box. Finally, click OK to add the constraint to Solver. Place Figure 7 about here Click the Solve button to find the weights producing a minimum-variance portfolio. The resulting solution and a dialog box indicating that Solver found a solution satisfying the constraints is shown in Figure 8. The minimumvariance portfolio has a mean monthly return of 0.92 percent and a standard deviation of returns of 3.57 percent, both considerably lower than those for the equally-weighted portfolio. Note that the weights for Apple Computer and Coca-Cola are negative, indicating short selling of these stocks. 11 Place Figure 8 about here To further illustrate the concept of an efficient portfolio, we create two additional portfolios: 1) an efficient portfolio having the same mean return as the equally-weighted portfolio (Efficient Portfolio #1), and 2) an efficient portfolio having the same standard deviation (risk) as the equally-weighted portfolio (Efficient Portfolio #2). These two portfolios illustrate two alternative interpretations of an efficient portfolio. First, for a given desired return, an efficient portfolio is one with the least variance. Alternatively, for a given level of risk, an efficient portfolio has the greatest return. To setup the worksheet to construct the two additional efficient portfolios, we add two rows (rows 22 and 23) of weights, initially set to 20 percent each. In addition, using the SUMPRODUCT function as before, we create two columns (columns N and O) of monthly portfolio returns. We label column N “Efficient Portfolio #1” and column O “Efficient Portfolio #2”. We also enter the formulas to calculate the expected (mean) return and standard deviation for each of the portfolios. In the case of Efficient Portfolio #1, we use Solver to minimize the portfolio standard deviation (cell N18), constrained so that the sum of the weights equals one and the mean return is equal to that of the equally-weighted portfolio (cell L17). For Efficient Portfolio #2, we use Solver to maximize the portfolio return (cell O17), constrained so that the sum of the weights equals one and the standard deviation of returns is equal to that of the equally-weighted portfolio (cell L18). The resulting Excel worksheet is shown in Figure 9. Efficient Portfolio #1 has the same mean monthly return as the equally-weighted portfolio (2.55 percent) but with a substantially lower standard deviation (3.87 percent versus 6.41 percent). Efficient Portfolio #2 has the same standard deviation as the equally-weighted portfolio (6.41 percent) but has a significantly larger mean monthly return (6.76 percent versus 2.55 percent). Place Figure 9 about here The Efficient Frontier We can use the results for Efficient Portfolios #1 and #2 to illustrate the concept of the efficient frontier. First, in cell G25, we use the Excel CORREL function to calculate the correlation between the returns for the two efficient portfolios [i.e., =CORREL(O4:O15,P4:P15)]. The function returns the value of 0.8330 for the correlation. We create a table to calculate the portfolio expected return and standard deviation for various combinations of the two efficient portfolios. The portfolio expected returns are calculated using the following equation:

E[ RP ]  w #1 E[ R#1 ]  w# 2 E[ R# 2 ] .

(11)

Portfolio standard deviation is calculated using:

5



 P  w#21 #21  w#22 #22  2 w #1w#2  #1,#2 #1 #2



1

2

.

(12)

We graph the relationship between portfolio standard deviation and expected return. For illustrative purposes, we also label the equally-weighted portfolio, the global minimum-variance portfolio, and Efficient Portfolios #1 and #2. Figure 10 shows the resulting graph of the efficient frontier for our set of stocks. Place Figure 10 about here SUMMARY In this paper, we demonstrate the use of Solver to compute the weights of mean-variance efficient portfolios in an Excel spreadsheet environment. Further, we make use of readily-available data from the Internet to make the demonstration more relevant and appealing for students. The benefit of the method presented is that it allows instructors to more actively engage students in portfolio concepts. If the instructor has access to an overhead that allows the projection of the spreadsheet, once the system of equations is in place in the spreadsheet, many conceptual as well as practical points can be shown. For example, an instructor can quickly insert different levels of variance into the model and solve for the returns of mean-variance efficient portfolios, mapping out the efficient frontier. If the data set is constructed with positive and negatively correlated assets, the effects of the correlations can be explored by simple manual manipulation of the portfolio weights. From a practical standpoint, the instructor can talk about the constraints imposed on portfolio managers and how these constraints should be incorporated into the optimization constraints. For the students, these concepts become real rather than just mathematical abstractions presented in a textbook. ENDNOTES Stephens [1998] briefly touches on the use of Excel’s Solver tool to solve for efficient portfolios with constraints against short selling. 2

The stock-price data from Yahoo! are adjusted for splits and dividends.

3

Alternatively, Yahoo! Finance can be accessed by entering Yahoo!’s URL (http://www.yahoo.com/) and then selecting Finance/Quotes from the menu of options.

4

In fairness, we should report that this informat...