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. . Stock Risk Joint Risk Expected Pair of (Standard per Stock Return Stocks Deviation) (Covariance) 20% 28% 1 and 2 0.06 1 2 25% 45% 1 and 3-0.015 3 8% 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

Answer:-

Given that:-

(a)

we have to

minimize ;Risk$=(0.28S_{1})^{2}+(0.45S_{2})^{2}+(0.05S_{3})^{2}+2(0.06)S_{1}S_{2}+2(-0.015)S_{1}{3}+2(-0.01)S_{2}{3}$

Subjected to-

$(20\%)S_{1}+(25\%)S_{2}+(8\%)S_{3}\geq 15\%$

S1 + S2 + S3 = 100%

$S_{1},S_{2},S_{3}\geq 0$

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% stock1

14% stock 2

48% stock3

(b)

%	STOCK1	STOCK2	STOCK3	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.