Using Execel Solver
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
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