Using Excel=s Solver Tool in Portfolio Theory
Excel contains a tool called the Asolver@ that lets you maximize or minimize functions subject togeneral constraints. We will use this tool to compute the global minimum variance portfolio andthe 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 andcovariances. 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 σ = x σ + x σ + x σ + 2 x x σ + 2 x x σ + 2 x x σ2p2121222223231 2121 3

1323

23x1, x 2 , x 3

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 thesolution. Alternatively, we can use the solver to compute the solution numerically (i.e. not usinga formula).
To use the solver to solve for the portfolio weights such that the resulting portfolio variance isminimized 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)
10.03

In the spreadsheet file 483solverex.xls, the value 0.2 is in cell B15, 0.3 is in cell C15, 0.5 is incell 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 toconstraints. As such, you have to set up your spreadsheet such that certain cells contain (1) thefunction 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 thisby 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 thefunction to be minimized. In the cell below the cell labeled VAR(Rp) you would input theformula 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 tominimize 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 willappear and the cell $F$15 will be highlighted in the box labeled Set Target Cell. This is whereyou specify the cell containing the formula you want to minimize. Click in the box to confirm theselection. 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 thevariables 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 optimality1The 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 theoriginal values. Choose Keep Solver Solution and click [OK]. The spreadsheet should now
look like the following:x10.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 thevariance 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 tothe efficient frontier and has slope equal to (µt - rf)/σt. Formally, the optimization problem isµp-rf

maximize σx1 , x 2 , x 3s.t.

pµ p = x 1µ 1 + x 2 µ 2 + x 3 µ 3σ 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 )
2222221/2x1 + 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

x20.200

Portfolio statistics
x30.500

Constraint
1.000

E[Rp]
0.122

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; theconstraint data is in cell E22; E[Rp] 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 E[Rp].

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 thiscell 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 forthe 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 ofthe slope, given in cell I22, subject to the constraint given in cell E22. We follow the sameprocedure 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 fromthe previous example.) We want to maximize the function in the cell so click the radio buttonlabeled 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 theConstraints. 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 toone. In the box labeled Constraint choose = and in the box to the right type 1. Click [OK] toconfirm the constraint. The Solver Parameters dialogue box should reappear. Check to makesure 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 theoriginal values. Choose Keep Solver Solution and click [OK]. The spreadsheet should now
look like the following:
Portfolio weights
x1
0.532

x2
0.153

Portfolio statistics
x3
0.315

Constraint
1.000

E[Rp]
0.159

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 σ2t = 0.339.
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 isminimize σ = x σ + x σ + x σ + 2 x x σ + 2 x x σ + 2 x x σ , s.t.
2p2121222223231 2121 3132323x1, x 2 , x 3x1 + x 2 + x 3 = 1x 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 thesolution 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 isminimized, the portfolio weights sum to 1 and the portfolio expected return achieves a target you
would set up a simple spreadsheet as follows.
targetmu00.01

Portfolio weights
x1x2
0.3
0.3

x3
0.4

Constraint 1 Constraint 2 Var(Rp)10.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 functionin 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 thenhighlight 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 andhighlight 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 theoriginal values. Choose Keep Solver Solution and click [OK]. The spreadsheet should now
look like the following:targetPortfolio weights
mu0x1
x2
0.010
-0.6250.798

x3Constraint 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 theportfolio 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.