Using 1 x 3? 13 + 2 x

Using 1 x 3? 13 + 2 x

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 ER 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 = 1We 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.

3 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 Subject to the Constraints. Click the Add button to add constraints. A new dialogue box will appear labeled Add Constraint.

Click in the box labeled Cell Reference and then highlight the cell E15 containing the constraint formula. In the box labeled Constraint choose = and in the box to the right type 1. Click OK to confirm the constraint.

The Solver Parameters dialogue box should reappear. Check to make sure everything is set up correctly and then click Solve.The solver will crank away and if you notice you will see the values in the spreadsheet change as the solver iterates.

When it finds a solution the dialogue box named Solver Results will appear. You should see the message: Solver found a solution. All constraints and optimality The solver is an add-in and it may not be listed on the tools menu. If it is not on the tools menu then you need to add it. To do this select Tools/Add-Ins and check the box next to Solver Add-in.

If the Solver Add-in is not listed then you must install it from Excel installation disk. conditions are satisfied. You will be given the option of keeping the solution or to reset the original values. Choose Keep Solver Solution and click OK. The spreadsheet should now look like the following: x1 0. 310 x2 0. 196 x3 0.

495 Constraint VAR(Rp) 1. 000 0. 011 The global minimum variance portfolio weights are x1 = 0. 310, x2 = 0. 196 and x3 = 0. 495 and the variance of the portfolio is 0. 011.

(This is indeed the solution – I checked using the matrix algebra formula). Pretty cool, huh?Using the Solver to find the tangency portfolio Recall, to find the tangency portfolio you need to find the capital allocation line (CAL) that has the highest (Sharpe=s) slope. This line has vertical intercept at the risk-free rate, rf, is tangent to the efficient frontier and has slope equal to (µt – rf)/? t. Formally, the optimization problem is maximize x1 , x 2 , x 3 2 2 2 2 2 2 s. t. ?p µ p = x 1µ 1 + x 2 µ 2 + x 3 µ 3 1/2 µp-rf ? p = ( 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 ) x1 + x 2 + x 3 = 1 You can set up the Lagrangian for this problem and try to solve it but it will be a giant mess.Trust me.

This problem is easy to solve, however, using the Solver. To use the solver you would set up the following spreadsheet (see 483solverex. xls): Portfolio weights x1 0. 300 x2 0. 200 x3 0. 500 Constraint 1. 000 ERp 0.

122 Portfolio statistics VAR(Rp) 0. 011 SD(Rp) 0. 104 SLOPE(Rp) 0.

022 In the file 483solverex. xls, the portfolio weights data are in the cells B22, C22 and D22; the constraint data is in cell E22; ERp data is in cell F22; VAR(Rp) data is in cell G22; SD(Rp) data is in cell H22; and SLOPE(Rp) data is in cell I22.In the above spreadsheet, the function to be maximized is the slope of the CAL for the portfolio and the variables to be changed are the portfolio weights.

As in the previous example, I have put in an initial guess for the portfolio weights (notice that this guess is different from the guess I used in the previous example ). The constraint is that the portfolio weights sum to one. The formula for the slope depends on the excess expected return on the portfolio and on the portfolio standard deviation. I compute the expected return on the portfolio in the cell with label ERp.

The formula in this cell is =B22*ER_1+C22*ER_2+D22*ER_3 The variance of the portfolio is calculated in the cell with label VAR(Rp) and the formula in this cell is =B22^2*VAR_1 + C22^2*VAR_2 + D22^2*VAR_3 + 2*B22*C22*COV_12 + 2*B22*D22*COV_13 + 2*C22*D22*COV_23 The standard deviation of the portfolio is given by the formula SQRT(G22) and the formula for the slope of the portfolio is =(F22-R_F)/H22 where R_F refers to the cell containing the value of the risk-free rate. You want the solver to change the values in the cells B22, C22 and D22 to maximize the value of the slope, given in cell I22, subject to the constraint given in cell E22.We follow the same procedure as in the previous example. Place the cursor in the cell you want to maximize (I22). Now, from the menu bar click Tools and choose the option Solver. A dialogue box named Solver Parameters will appear and the cell $I$22 will be highlighted in the box labeled Set Target Cell.

This is where you specify the cell containing the formula you want to maximize. Click in the box to confirm the selection. (If necessary, click Reset All to clear the results from the previous example.

) We want to maximize the function in the cell so click the radio button labeled Max.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 B22.. D22 containing the portfolio weights. Next click in the box labeled Subject to the Constraints. Click the Add button to add constraints. A new dialogue box will appear labeled Add Constraint. Click in the box labeled Cell Reference and then highlight the cell E22 containing the constraint formula.

This sets up the constraint that the portfolio weights sum to one. In the box labeled Constraint choose = and in the box to the right type 1.Click OK to confirm the constraint. The Solver Parameters dialogue box should reappear. Check to make sure everything is set up correctly and then click Solve. The solver will crank away and if you notice you will see the values in the spreadsheet change as the solver iterates.

When it finds a solution the dialogue box named Solver Results will appear. You should see the message: Solver found a solution. All constraints and optimality conditions are satisfied. You will be given the option of keeping the solution or to reset the original values. Choose Keep Solver Solution and click OK.

The spreadsheet should now look like the following: Portfolio weights x1 0. 532 x2 0. 153 x3 0. 315 Constraint 1. 000 ERp 0. 159 Portfolio statistics VAR(Rp) 0. 115 SD(Rp) 0.

339 SLOPE(Rp) 0. 116 The weights in the tangency portfolio are x1 = 0. 532, x2 = 0. 153 and x3 = 0. 315. The expected return on the tangency portfolio is µt = 0. 159 and the standard deviation is ? 2 = 0.

339. t Using the Solver to find efficient portfolios In this last example, we will use the solver to find an efficient portfolio. Recall, an efficient portfolio is one that minimizes portfolio variance subject to achieving a target return.Formally, the optimization problem is minimize ? x1, x 2 , x 3 2 p 2 2 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 , s. t. x1 + x 2 + x 3 = 1 x 1µ 1 + x 2 µ 2 + x 3 µ 3 = µ p,0 where µp,0 denotes the target expected return.

We can set up the Lagrangian for this problem and use it to solve for x1, x2, x3, ? 1 and ? 2. This will give us five linear equations in five unknowns and we can use matrix algebra to find the solution analytically. Alternatively, we can use the solver to compute the solution numerically.To use the solver to solve for the portfolio weights such that the resulting portfolio variance is minimized, the portfolio weights sum to 1 and the portfolio expected return achieves a target you would set up a simple spreadsheet as follows. target mu0 0. 01 Portfolio weights x1 x2 0.

3 0. 3 x3 0. 4 Constraint 1 Constraint 2 Var(Rp) 1 0. 1309 0. 128 In the spreadsheet file 483solverex. xls, the target expected return value 0.

01 is in cell A29. The portfolio weights are in cells B29, C29 and D29. The constraint that the portfolio weights sum to 1 is in E29 and the formula given there is = B29+C29+D29.The constraint to compute the portfolio expected return is in F29 and the formula is =B29*ER_1+C29*ER_2+D29*ER_3 The objective function to be minimized, the formula for computing portfolio variance, is in cell G29 and the formula is =B29^2*VAR_1 + C29^2*VAR_2 + D29^2*VAR_3 + 2*B29*C29*COV_12 + 2*B29*D29*COV_13 + 2*C29*D29*COV_23 You now want to set up the solver to change the values in the cells B29, C29 and D29 to minimize the portfolio variance given in cell G29 subject to the constraints given in cells E29 and F29. To do this you would place the cursor in the cell you want to minimize (G29).Now, from the menu bar click Tools and choose the option Solver. A dialogue box named Solver Parameters will appear and the cell $G$29 will be highlighted in the box labeled Set Target Cell (if it is not then make it so).

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 B29..

D29 containing the portfolio weights. Next click in the box labeled Subject to the Constraints. Click the Add button to add constraints.

A new dialogue box will appear labeled Add Constraint. Click in the box labeled Cell Reference and then highlight the cell E29 containing the first constraint formula. In the box labeled Constraint choose = and in the box to the right type 1.

Click OK to confirm the constraint. Click the Add button again to add the second constraint. Click in the box labeled Cell Reference and highlight the cell F29 containing the second constraint formula.In the box labeled Constraint choose = and in the box to the right type the cell reference, A29, containing the target return.

The Solver Parameters dialogue box should reappear. Check to make sure everything is set up correctly and then click Solve. The solver will crank away and if you notice you will see the values in the spreadsheet change as the solver iterates. When it finds a solution the dialogue box named Solver Results will appear. You should see the message: Solver found a solution.

All constraints and optimality conditions are satisfied.You will be given the option of keeping the solution or to reset the original values. Choose Keep Solver Solution and click OK. The spreadsheet should now look like the following: target Portfolio weights mu0 x1 x2 0. 010 -0. 625 0.

798 x3 Constraint 1 Constraint 2 Var(Rp) 0. 827 1. 000 0. 010 0.

266 The efficient portfolio has weights x1 = -0. 625, x2 = 0. 798 and x3 = 0. 827, the variance of the portfolio is 0. 266 and the expected return is 0. 01. To get another efficient portfolio you would follow the same process using a different target return.