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 Return (Standard per Stock Stocks Deviation) (Covariance) 1 20% 28% 1 and 2 0.06 2 25% 45% 1 and 3-0.015 3 8% 5% 2 and 3-0.010 Showth 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%. Expe Upload Choose a File Question 4 Type here to search 5 pts

Answer 1

(a)

We have to minimize : Risk = (0.285, (0.45 S₂} + (0.05 syy? + 2(0.06) s,S2+ 2(-0.015) s, sz+ 2 (-0.0175253 subjected to (20%) 5, + (25%) Szt (8+)=15% St S₂ + 3 = 100.1 si, S2, S3 20

Viallage Udld vode La From Text Sources Get External Data Connections Query - Lo Recent Sources Get & Transform All - 3 Edit Links Connections Analysis Shee Advanced Columns Dald Valuation Data Tools Sort & Filter Forecast fr =(0.28*B8)^2+0.45*C8)^2+(0.05*D8)^2+2*(0.06*B8*C8-0.015*B8*D8-0.01*C8*D8) A B с D E F G Н. 1 J K L M N o Р 1 Solver Parameters X 2 3 JOINT RISK 0.06 1 EXPECTED RETURN RISK 20% 25% 8% 4 28% 45% 5% 2 -0.015 $C$121 Set Objective: 5 3 -0.01 To: O Max O Min O Value of: 0 6 7 STOCK 1 STOCK 2 STOCK3 14% 48% TOTAL 100% = By Changing Variable Cells: $B$8:$D$8 38% 8 PORTFOLIO 9 100% 10 EXPECTED RETURN 15.000% 15% Subject to the Constraints: $C$10 >= $E$10 $E$8 = $G$8 Add 11 12 13 RISK 0.015805 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

So, portfolio is 38% stock 1

14% stock 2

48% stock 3

(b)

%	STOCK 1	STOCK 2	STOCK 3	RISK
20%	59%	29%	12%	0.061945
25%	0%	100%	0%	0.202494

above 25% we are unable to find feasible solution. it doesn't satisfy all constraints.

.

.

Please give it a thumbs up if this solution helped you. Still any doubt ? Comment box is all yours, I will be happy to help you :)