Solution to Portfolio Optimization: Parametric Approach[1]

Consider a portfolio composed of three stocks. Let the random variable si be the annual return on stock i, with expected value μi, variance , and standard deviation .

The correlation between si and sj, for any pair of stocks i and j, is ρij.

The covariance between si and sj, for any pair of stocks i and j, is σiσjρij.

The means are: μ1 = 0.14, μ2 = 0.11, μ3 = 0.10.

The variances are: = 0.20, = 0.08, = 0.18.

The correlations are: ρ12. = 0.8, ρ13. = 0.7, and ρ23. = 0.9.

(a)Determine the minimum-variance portfolio that attains an expected annual return of at least 0.12, with no shorting of stocks allowed.

Managerial Formulation

Decision Variables

We need to allocate 100% of the available funds to the three stocks.

Objective

We want to minimize risk.

Constraints

The expected return on the portfolio must be at least 12% (or 0.12).

All of the money must be invested.

No shorting is allowed.

Mathematical Formulation

Decision Variables

Define xi to be the proportion of the portfolio allocated to stock i.

The decision variables are three proportions: x1, x2, and x3.

The expected return on the portfolio is:

The variance of the return on the portfolio is:

The standard deviation of the return on the portfolio is:

Objective

Minimize Z =

Constraints

(1)

(2)

For all i, (3)

Solution Methodology

Here’s the spreadsheet model:

Notes:

The decision variables are in B14:D14.

Cell D18 keeps track of constraint (1).

Cell B14 keeps track of constraint (2).

We can comply with constraint (3) by selecting “assume nonnegative” in the Solver Options box.

The range H8:J10 uses the HLOOKUP Excel function to calculate the covariances.

Cell B20 uses two of Excel’s matrix functions, MMULT and TRANSPOSE, to calculate the portfolio variance. If you use these functions, you will want to learn more about working with “arrays” in Excel, an advanced topic beyond the scope of this course. In this case, you need to know not to type in the “curly brackets”; instead, type in the rest of the function and then Ctrl+Shift+Enter. The curly brackets will appear automatically. You will get a #VALUE! Error message if you do this wrong.

Here is the optimized spreadsheet:

Conclusions

The least risky way to invest these three stocks while having an expected return of at least 12% is to invest 33.3% in Stock 1 and 66.7% in Stock 2. This portfolio will have an expected return of 12% and a standard deviation of return of 32.1%.

(b)Construct a trade-off curve between the chosen portfolio's expected return and risk (standard deviation), with risk on the horizontal axis. In finance terminology this trade-off curve is called the efficient frontier.

We need to solve the model repeatedly for different levels of required return; a perfect situation for SolverTable.

Here’s the SolverTable output:

Here’s a picture of the efficient frontier, as well as the three individual stocks. Note that there is no way to get a portfolio that is less risky than Stock 2, nor is it possible to get a portfolio with a higher expected return than Stock 1.

(c)Determine the minimum-variance portfolio that attains an expected annual return of at least 0.12, with shorting of stocks allowed.

All we need to do here is remove the nonnegativity constraint and re-run SolverTable.

We are able to achieve a lower risk than Stock 2 (or a higher return than Stock 1) by shorting in various combinations (see the SolverTable output on the next page).

B60.23501Prof. Juran

[1]Based on 7-49 (p. 393) and 9-24 (p. 471) in Practical Management Science (2nd ed., Winston and Albright, 2001 Duxbury Press). Solution by David Juran, 2002.