8.6. Reconsider the portfolio selection example, including its spreadsheet model in Figure 8.13 Q, given in Section 8.2 Q. Note in Table 8.2 @ that Stock 2 has the highest expected return and Stock 3 has by far the lowest. Nevertheless, the changing cells Portfolio (C14:E14) provide an optimal solution that calls for purchasing far more of Stock 3 than of Stock 2. Although purchasing so much of Stock 3 greatly reduces the risk of the portfolio, an aggressive investor may be unwilling to own so much of a stock with such a low expected return. For the sake of such an investor, add a constraint to the model that specifies that the percentage of Stock 3 in the portfolio cannot exceed the amount specified by the investor. Then compare the expected return and risk (standard deviation of the return) of the optimal portfolio with that in Figure 8.13 @ when the upper bound on the percentage of Stock 3 allowed in the portfolio is set at the following values. A D E F G H B Portfolio Selection Problem (Nonlinear Programming) 1 Solver Parameters Set Objective Cell: Variance To: Min By Changing Variable Cells: Portfolio Subject to the Constraints: Expected Return >= MinExpected Return Total = One HundredPercent Stock 1 Stock 2 21% 30% Stock 3 8% Expected Return Risk (Stand. Dev.) 25% 45% 5% Solver Options: Make Variables Nonnegative Solving Method: GRG Nonlinear or Quadratic (Analytic Solver) Joint Risk (Covar.) Stock 1 Stock 2 Stock 1 0.040 Stock 2 Stock 3 Stock 3 -0.005 -0.010 2. 3 4. 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Range Name Cells Covar 12 D9 Covar 13 E9 Covar23 E10 Covariance CI:E11 ExpectedReturn C19 MinExpectedReturn E19 One HundredPercent H14 Portfolio C14:E14 SD1 C6 SD2 D6 SD3 E6 StandDev C23 Stock1 C14 Stock2 D14 Stock3 E14 StockExpectedReturn C4:54 StockStandDev C6:E6 Total F14 Variance C21 Stock 1 Stock 2 40.2% 21.7% Stock 3 38.1% Total 100% Portfolio = 100% Minimum Expected Return 18% Portfolio 18% Expected Return > Risk (Variance) 0.0238 F 13 Total 14 =SUM(Portfolio) Risk (Stand. Dev.) 15.4% B 19 Expected Return =SUMPRODUCT(StockExpectedReturn,Portfolio) 20 21 Risk (Variance) =((SD1*Stock1)^2)+((SD2*Stock2)^2)+((SD3*Stock3)^2)+2*Covar12*Stock1*Stock2+2*Covar13*Stock1*Stock3+2*Covar23*Stock2*Stock3 22 23 Risk (Stand. Dev.) =SQRT(Variance) Referring to Problem 8.6 (Page 325): (a) 20% Under this additional condition, the expected return of the portfolio is % (answer in percentage, one decimal place), and the risk in terms of standard deviation is % (answer in percentage, one decimal place). (b) 0%: Under this additional condition, the expected return of the portfolio is % (answer in percentage, one decimal place), and the risk in terms of standard deviation is % (answer in percentage, one decimal place)