Question

Assume you are professional manager of large stock portfolios and use computer models based on nonlinear programming to guide them. • Investors are concerned about both the expected return and the risk. • One way of formulating their approach is as a nonlinear version of a cost-benefit trade-off problem: • Minimize Risk subject to Expected return 2 Minimum acceptable level . Consider a portfolio with 3 stocks. Risk Joint Risk Expected Pair of Stock (Standard Return per Stock Stocks Deviation Covariance) 1 20 28% 1 and 20.06 2 25% 45% 1 and 3-0.015 3 896 5% 2 and 3-0.010 Question: Please provide excel model and solution for the following questions. a What is the portfolio that will minimize the risk subject to achieving at least an 15% expected return? b. Generate a parameter analysis report using Solver to systematically try all the percentages at 5% intervals from 20% to 40%.

Answer 1

Given:

Decision Variables: Let W1,W2,W3 be the weights of Investment in Stocks 1,2 & 3 respectively

Objective: To Minimize the risk of the Portfolio comprising the three stocks, given by:

Min (Risk) = Min [ ( W1 * 0.28)² + (W2 * 0.45 )² + (W3 * 0.05)² + (2*W1*W2*0.06) + (2*W1*W3* -0.015) + (2*W2 *W3 * -0.01) ]^1/2

Subject to constraints:

Constraint 1: At least 15% expected return of the Portfolio is required:

Expected Portfolio Return = W1 * 0.2 + W2 * 0.25 + W3 * 0.08

0.2 W1 + 0.25 W2 + 0.08 W3 >= 0.15

Constraint 2: Sum of weights of stock in Portfolio equals 1:

W1 + W2 + W3 = 1

Solver Model:

A B C D E F H K L M N o 1 Solver Parameters x Set Objective: SBS6 1 To: O Max O Min O Value Of: 0 By Changing Variable Cells: $B$4,$C$4, SDS4 1 2 3 Stock 1 Stock 2 Stock 3 4 Weights 0.382433 0.141812 0.475755 5 6 Portfolio Riski 0.125718 7 8 Constraint 9 LHS RHS 10 11 0.15 0.15 12 1 Equals 1 13 14 15 16 17 18 Subject to the Constraints: SA$11 >= $C$11 $A$12 = $C$12 SB$4:$D$4 >= 0 Add Change Delete Reset All Load/Save 19 20 21 22 Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options

Formula Sheet:

A B D 1 2 3 Stock 1 Stock 2 Stock 3 0.141811672281049 0.475754865277297 4 Weights 0.382433462441654 5 6 Portfolio Risk ={(B4*0.28)^2+(C4*0.45)^2+(D4*0.05)^2 + (2*34* C4*0.06) + (2*34*D4*-0.015) + (2*24*D4*-0.01))^0.5 7 8 9 Constraint LHS RHS >= 0.15 10 11 =0.2*B4+0.25*C4+0.08*D4 12 =SUM(B4:04) 13 Equals 1

Portfolio Weights: W1 = 0.3824 ; W2 = 0.1418 ; W3 = 0.4758

Thus, stock 1,2 & 3 are in the percentages of 38.24% , 14.18% & 47.58% of the total investment Budget.

Minimum Optimal Risk of Portfolio = 0.1257 = 12.57%

--------------------------------------------------

B)

Parameter Analysis Report:

B C D E F G A 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Book1]Sheet1 3 Report Created: 01-Aug-20 11:16:53 AM 4 5 6 Variable Cells 7 Reduced Gradient 8 Cell 9 Final Name Value Weights Stock 1 0.382433462 Weights Stock 2 0.141811672 Weights Stock 3 0.475754865 $B$4 $C$4 $D$4 0 0 10 11 0 N 13 Constraints 14 15 Name Final Lagrange Value Multiplier 0.15 2.473817885 1 -0.245354758 Cell Expected Return Constraint LHS Weights Constraint LHS 16 17 18 19

The Lagrange Multiplier for the 1st Constraint is 2.47

It implies that for every unit increase / decrease in the RHS constraint, there is 2.47 Unit increase or decrease respectively in the Objective Function.

Thus, for Expected returns of 20%,25%,30%,35%,40%, the following table depicts the returns:

Lagrange Multiplier 2.47 RHS Constraint 0.15 0.2 0.25 Value of Objective Function(Risk) 0.1257 0.2492 0.3727 0.4962 0.6197 0.7432 0.3 0.35 0.4