Using Execel Solver

Topics: Optimization, Modern portfolio theory, Maxima and minima Pages: 7 (2737 words) Published: November 1, 2011
Using Excel=s Solver Tool in Portfolio Theory
Excel contains a tool called the Asolver@ that lets you maximize or minimize functions subject to general constraints. We will use this tool to compute the global minimum variance portfolio and the tangency portfolio for the three-firm example (see the spreadsheet 3firm.xls). The spreadsheet for this tutorial is called solverex.xls. The data for this example are given in the following table Stock 1 2 3 E[R] 0.229 0.138 0.052 VAR(R) 0.924 0.862 0.528 COV(I,J) 0.063 -0.582 -0.359 PAIR(I,J) (1,2) (1,3) (2,3)

For convenience, I have named the cells containing the expected returns, variances and covariances. See the 483solverex.xls spreadsheet. Using the Solver to find the global minimum variance portfolio Here we want to find the global minimum variance portfolio. That is, the portfolio of stocks 1, 2 and 3 that has the smallest variance regardless of expected return. In general, we want to solve the problem

minimize σ
x1, x 2 , x 3

2 p

2 2 2 2 = x 1σ 1 + x 2σ 2 + x 3σ 3 + 2 x 1 x 2σ 12 + 2 x 1 x 3σ 13 + 2 x 2 x 3σ 23 2 2

s. t. x 1 + x 2 + x 3 = 1

We can set up the Lagrangian for this problem and use it to solve for x1, x2, x3 and λ. This will give us four linear equations in four unknowns and we can use matrix algebra to find the solution. Alternatively, we can use the solver to compute the solution numerically (i.e. not using a formula). To use the solver to solve for the portfolio weights such that the resulting portfolio variance is minimized and the portfolio weights sum to 1 you would set up a simple spreadsheet as follows: Portfolio weights x1 0.2 x2 0.3 x3 0.5 Constraint VAR(Rp) 1 0.03

In the spreadsheet file 483solverex.xls, the value 0.2 is in cell B15, 0.3 is in cell C15, 0.5 is in cell D15, 1 is in E15 and 0.03 is in F15. These initial values are chosen arbitrarily such that they sum to 1.

The solver works by maximizing or minimizing a function of a set of given values subject to constraints. As such, you have to set up your spreadsheet such that certain cells contain (1) the function to be maximized or minimized; (2) the variables to be changed that define the function; (3) the constraints of the problem. In the above spreadsheet, the function to be minimized is the portfolio variance and you do this by changing the portfolio weights. The portfolio weights are given in the cells below the cells labeled x1, x2 and x3. I have put in an initial guess for the portfolio weights: x1 = 0.2, x2 = 0.3 and x3 = 5. You need to do this to get the solver algorithm going. The portfolio weights must sum to one and you need to set up a cell to enforce this restriction. In the cell under the cell labeled Constraint I entered the formula =B15 + C15 + D15 which simply sums the portfolio weights. Finally, you have to create a cell that contains the function to be minimized. In the cell below the cell labeled VAR(Rp) you would input the formula for the variance of a portfolio =B15^2*VAR_1 + C15^2*VAR_2 + D15^2*VAR_3 + 2*B15*C15*COV_12 + 2*B15*D15*COV_13 + 2*C15*D15*COV_23 You now want to set up the solver to change the values in the cells B15, C15 and D15 to minimize the portfolio variance given in cell F15 subject to the constraint given in cell E15. To do this you would place the cursor in the cell you want to minimize (F15). Now, from the menu bar click Tools and choose the option Solver1. A dialogue box named Solver Parameters will appear and the cell \$F\$15 will be highlighted in the box labeled Set Target Cell. This is where you specify the cell containing the formula you want to minimize. Click in the box to confirm the selection. We want to minimize the function in the cell so click the radio button labeled Min. Next, click in the box by changing cells. This is where you specify the cells that contain the variables that will change to minimize the function. Highlight the cell range B15..D15 containing the portfolio weights. Next click in the box labeled...