introduction - university of notre damepgao/finc40670_final/markowitziim.pdf · 2007-05-08 · note...

Note on Using Excel to Compute Optimal Risky Portfolios

- 1 -

Candie Chang, Hong Kong University of Science and Technology

Andrew Kaplin, Kellogg Graduate School of Management, NU

Introduction This document shows how to,

(1) Compute the expected return and standard deviation of a portfolio of N given risky assets;

(2) Construct the portfolio that gives the highest rate of return for a given standard deviation;

(3) Construct the portfolio that has the lowest standard deviation for a given expected return;

(4) Construct the tangent portfolio that has the the steepest capital allocation line CAL when combined

with the risk-free rate of asset;

(5) Compute the optimal combination of the tangent portfolio and the risk-free asset for a risk averse

investor;

using MarkowitzII.xls ( 2000, Ravi Jagannathan and Andrew Kaplin)

You will require the following information for using MarkowitzII.xls.

a. Expected returns, standard deviations, and the correlation matrix for the N risky asset returns;

b. The risk-free rate of return;

c. The risk aversion coefficient, A, of the investor that relates the certainty equivalent rate of return of

a risky asset to its expected return and standard deviation 1:

Certainty Equivalent Rate of Return = (Expected Return) - .005×A×(Variance of Returns).

You have the expected returns, standard deviations, and a correlation matrix for IBM, COLEX, ATX, HPX,

IRWINX, and BORYLAND. The risk-free rate of return is 1.4% and the aversion coefficient is 25.

1 See Bodie, Zvi, Alex Kane and Alan J. Marcus, Investments (Homewood, Illinois: Irwin, Fourth Edition 1999)


- 2 -

Section 1: Entering the inputs

1.1. Open MarkowitzII.xls

1.2. Enable Macros.

1.3. Input 6 (=N) in the cell C3 for the Number of Securities and press the Enter key !!!!.

1.4. Click the button Construct Tables.

1.3 1.4


- 3 -

1.5. Input the means, the standard deviations and the correlation matrix as follows.

1.6. Input the names of the securities in Row B as follows.

1.7. Click the button Fill in Names to fill the names for the correlation matrix.

1.6

1.7


- 4 -

The following window will appear.

1.8. Input 0.014 in the box Risk Free Rate.

1.9. Input 25 in the box Risk Aversion Coefficient and press the Enter key !!!!.

1.9 1.8


- 5 -

Section 2: Calculating the Expected Return and Standard Deviation for an Arbitrary Portfolio

Input the portfolio weights (fractions) of the investment on each of the N risky assets in row C to

obtain

(1) The expected return and standard deviation of a portfolio of N given risky assets.

Input

Output


- 6 -

Section 3: Calculating Optimal Portfolios

3A. The highest rate of return for a given level of standard deviation 3A.1. Click Tools on the menu bar and choose Solver …

3A.2. In the Solver Parameters window, choose "maximize" to maximize the portfolio’s expected return:

Set Target Cell: $D$19 the cell that contains the portfolio’s expected return Equal To: ! Max

By Changing Cells: $C$8:$C$13 the cells that contain the weights for the 6 assets.

Suppose that the maximum risk level you would like to take is 8.5%.


- 7 -

3A.3. You have to add the following constraints.2

Constraint 1: $C$14 the cell recording the sum of the weights (=sum($C$8:$C$13)) equals (=) 1.

Press the button Add to add the second constraint.

Constraint 2: $D$20 the cell recording the portfolio’s standard deviation is no more than (<=) 0.085.

Press the button OK to return to the Solver Parameters window.

3A.4. The following window will appear. Press the button Solve to obtain the desired optimal portfolio.

2 You can add other constraints such as “the weight of each asset is at least 5%” and “all the weights are non-negative”.


- 8 -

3A.5. To keep the results, press the button OK.

The optimal portfolio is given in $C$8:$C$13 and the corresponding expected return in $D$19.

Note that the weight for ATX is practically zero, 6.7989E-05 = .000067989.


- 9 -

3B. The lowest standard deviation for a given rate of expected return 3B.1. Click Tools on the menu bar and choose Solver …

3B.2. In the Solver Parameters window, you ask the solver to minimize the portfolio’s standard deviation:

Set Target Cell: $D$20 the cell recording the portfolio’s expected return

Equal To: ! Min

By Changing Cells: $C$8:$C$13 the cells recording the weights or the fractions of the 6 assets.


- 10 -

Suppose that you want the portfolio’s expected return to be at least 20%.

3B.3. You have to tell the Solver this by adding constraints (do not forget to delete constraints that

may be there already if you are not going to need them).


Press the button Add to add the second constraint.

Constraint 2: $D$19 the cell recording the portfolio’s expected return is at least (=>) 0.2.


3B.4. The following window will appear. Press the button Solve to obtain the desired optimal portfolio.


- 11 -

3B.5. To keep the results, press the button OK.

The optimal portfolio is given in $C$8:$C$13 and the corresponding standard deviation in $D$20.


- 12 -

3C. The highest reward-to-variability portfolio &

the optimal portfolio for a given level of risk aversion.

3C.1. Click Tools on the menu bar and choose Solver …

3C.2. In the Solver Parameters window, you choose "maximize" to find the portfolio that has the steepest

capital allocation line CAL -- i.e., maximize the reward-to-variability ratio.

Set Target Cell: $C$25 the cell recording the slope of CAL

Equal To: ! Max

By Changing Cells: $C$8:$C$13 the cells containing the weights for the 6 assets.


- 13 -

3C.3. You have to provide the Solver with the constraints by adding constraints (do not forget to delete

constraints that may be there already if you are not going to need them).


Press the button Add to add another parameter.

Constraint 2: $C$23 the cell recording the risk-free rate equals (=) 0.014.

Press the button Add to add the third constraint (another parameter).

Constraint 3: $C$23 the cell recording the risk aversion coefficient equals (=) 25.



- 14 -

3C.4. The following window will appear. Press the button Solve to obtain the desired optimal portfolio.

3C.5. To keep the results, press the button OK.


- 15 -

The optimal risky portfolio is given in $C$8:$C$13 and the corresponding expected return and standard

deviation in $D$19 and $D$20 whereas the optimal weight on the tangency portfolio is given in $I$25 for

the risk aversion coefficient of 25.


- 16 -

3C.6. You can also study how the optimal weight for the risky assets and the slope of CAL change with the

risk-free rate or the risk aversion coefficient. For instance, increase the risk-free rate to 0.05, the

optimal weight on the risky assets recording in the cell $I$25 immediately decreases from 1.92 to

1.38 and the slope of CAL recording in the cell $C$25 drops from 2.48 to 1.78.

introduction - university of notre damepgao/finc40670_final/markowitziim.pdf · 2007-05-08 · note...

Documents