Statistic problem
zq5174
tutorial_for_solver.docFIN 355 Prof. Gershun Page 1
Tutorial for Solver
Portfolio Theory and Management
To define a problem in Solver, you need to follow these essential steps:
Choose a spreadsheet cell to hold the value of each decision variable in your model.
Example 1 : Constructing a Portfolio of Bonds with the Targeted Duration
You need to create a portfolio with the duration equal to 4 years using three bonds. Use 3 bonds described in problem 2.19 from you Bond Homework Set. The total value of the portfolio is $1 million.
Solution
Step 1: Enter prices of all three bonds in one column (say B2 to B4). Enter their durations in the other column (D2 to D4).
Step 2: In the column named "N of bonds" (E2 to E4) enter any 3 numbers (you can start with 1, 2, and 3).
Step 3: Enter the formula for the value of the portfolio (this is cell B7 in this example).
Step 4: Enter formulas for the three weights (cells B10 to B12 in this example).
Step 5: Enter the formula for the sum of weights (cell B13).
Step 6: Enter the formula for the duration of the portfolio (cell E10).
Now start Solver:
Position your cursor in the target cell (the cell where you entered the formula for the duration of the portfolio). Choose Solver from the Tools menu. The main Solver window will open.
- You need the duration equal to 4 years. Choose the option "Equal to: Value of" for the target cell and enter number 4.
- In the window "By changing value of" enter the range of cells, which contain the numbers of bonds (E2 to E4).
- Now enter the constraints:
- Click on "Add" -- the Constraint window will open.
- Enter the address of the cell with the value of the portfolio (B7) in the "Cell reference."
- Enter =.
- Enter 1,000,000 below the word "Constraint" and click on "Add" in the constraint window.
- Repeat steps (i) through (iv) to enter your other constraint (sum of weights equals to 1.
- After you have entered all your constraints, close the Constraint window.
- Click on Solve in the main Solver window (the output is shown in Figure 1 below).
Figure 1: Solver output for Example 1.
Prices
Durations (modified)
N of bonds
Pa
1017.33
Dam
3.44
426.3467
Pb
1053.46
Dbm
2.63
-133.985
Pc
1105.31
Dcm
4.07
640.013
Vpf
1,000,000
weights
wa
0.433735
duration
4
wb
-0.14115
wc
0.707413
Sum
1
Example 2 : Portfolio Optimization
An investor wants to put together a portfolio consisting of up to 5 stocks. What is the best combination of stocks to minimize risk assuming she wants to earn 9% expected return on her portfolio?
The variances are known for each stock, as are the covariances between any pair of stocks. The returns for all stocks are also known.
Variance/Covariance Matrix
Stock 1
Stock 2
Stock 3
Stock 4
Stock 5
Stock 1
2.50%
0.10%
1.00%
-0.50%
1.60%
Stock 2
0.10%
1.10%
-0.10%
1.20%
-0.85%
Stock 3
1.00%
-0.10%
1.20%
0.65%
0.75%
Stock 4
-0.50%
1.20%
0.65%
0.40%
1.00%
Stock 5
1.60%
-0.85%
0.75%
1.00%
2.00%
This Solver model uses the QUADPRODUCT function at cell I14 to compute the portfolio variance. It can be solved for the minimum variance using either the GRG nonlinear solver or the Quadratic Solver.
Solution
1) The variables are the weights of each stock in the resulting portfolio. Enter the initial numbers in 5 weight cells, say B2 to B6.
2) The constraints are very simple.
(i) Enter: weights >= 0 if you want to restrict short sales. If you don’t want to restrict short sales then there is no need to enter restrictions on weights here.
(ii) Sum of weights = 1.
(iii) Then there is a constraint that the portfolio return should be at least a certain target value (9% in this example). Recall that the expected return on a portfolio is just a weighted average of expected returns on individual stocks.
Portfolio Return >= 0.09
3) The objective is to minimize portfolio variance. You can use the QUADPRODUCT function to compute the portfolio variance. If you see #NAME? on your worksheet, you need to open the add-in (DOTPRD32.XLL or DOTPROD.XLL) that provides QUADPRODUCT. You can also calculate the portfolio variance 'manually' without using QUADPRODUCT.
NOTE: In this example all the variances of individual stocks, and the covariances between each pair of stocks are known. You can calculate the variances and covariances from a history of stock returns. You can use CRSP database to download returns on any stock in S&P 500 and more.
Figure 2: Excel output for Example 2
Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Total
Portfolio % 20% 20% 20% 20% 20% 100 %
E xp. Return 7.00% 8.00% 9.50% 6.50% 14.00%
Std. Dev. of the Portfolio Returns 8.22%
Exp. Return on the Portfolio 9.00%
