Portfolio Optimization 3 Stock input data Solver Parameters X IBC NMC NBS 5 Mean return 0.14 0.11 0.1 Set Objective: B$25 6 StDev of return (o) 0.2 0.15 0.08 To: O Max Min O Value Of: 8 Correlations (R) IBC NMC NBS By Changing Variable Cells: 9 IBC 0.6 0.4 $B$15: SD$15 10 NMC 0.6 1 1 0.7 Subject to the Constraints: 11 NBS 0.4 0.7 SBS.19. = SDS.19.. 12 SB$23 > = $D$23 Add 13 Investment decisions Change 14 IBC NMC NBS Delete 15 Fractions to invest (p) 50% 0% 50% 16 Reset All 17 Constraint on fractions 18 Total invested Required value Load/Save 19 100% 100% Make Unconstrained Variables Non-Negative 20 Select a Solving Method: GRG Nonlinear Options 21 Constraint on expected portfolio return Solving Method 22 Actual return Required return Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex 23 0.12 >= 0.12 engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 24 25 Help Close 0.1 Solve 26 p * Std Dev 0.04 27 28 Portfolio variance 0.0148 =SUMPRODUCT(MMULT(B26:D26,B9:D11), B26:D26) 31