Nonlinear Optimization

Model

Portfolio selection model
Stock input data
	Stock 1	Stock 2	Stock 3
Mean return	0.14	0.11	0.1
StDev of return	0.2	0.15	0.08
Correlations	Stock 1	Stock 2	Stock 3		Covariances	Stock 1	Stock 2	Stock 3
Stock 1	1	0.6	0.4		Stock 1	0.04	0.018	0.0064
Stock 2	0.6	1	0.7		Stock 2	0.018	0.0225	0.0084
Stock 3	0.4	0.7	1		Stock 3	0.0064	0.0084	0.0064
Investment decisions
	Stock 1	Stock 2	Stock 3
Investment weights	0.500	0.000	0.500
Constraint on investing everything
	Total weights		Required value
	1.00	=	1
Constraint on expected portfolio return
	Mean portfolio return		Required mean return
	0.120	>=	0.120
Portfolio variance	0.0148
Portfolio stdev	0.1217
Range names used:
Investment_weights	=Model!$B$15:$D$15
Mean_portfolio_return	=Model!$B$23
Portfolio_stdev	=Model!$B$26
Portfolio_variance	=Model!$B$25
Required_mean_return	=Model!$D$23
Total_weights	=Model!$B$19

Make sure you understand the covariance formulas above, i.e., how they are calculated from standard deviations and correlations with careful lookups. Then notice how the MMULT function is used to calculate portfolio variance. This single formula (cell B25) replaces a long complex formula that would be required if the MMULT function didn't exist, and the beauty of it is that it works whether there are three potential stocks or hundreds of potential stocks. Finally, make sure you realize that it is the portfolio variance formula that makes this model nonlinear. It includes squares and products of the changing cells, and this rules out linearity. Luckily, though, it satisfies the conditions required to ensure that there are no local minima, i.e., we know that the Solver solution is optimal.

Model_STS

1
$D$23
1
0.1
0.14
0.005
$B$15:$D$15,$B$23,$B$26
Required return

STS_1

Oneway analysis for Solver model in Model worksheet										Sensitivity of Investment_weights_1 to Required return
Required return (cell $D$23) values along side, output cell(s) along top										Data for chart
	Investment_weights_1	Investment_weights_2	Investment_weights_3	Mean_portfolio_return	Portfolio_stdev				1	Investment_weights_1
0.100	0.000 Chris: Solver converged in probability to a global solution.	0.000	1.000	0.100	0.0800					0.0000000373
0.105	0.125 Chris: Solver converged in probability to a global solution.	0.000	0.875	0.105	0.0832					0.1250001043
0.110	0.250 Chris: Solver converged in probability to a global solution.	0.000	0.750	0.110	0.0922					0.2499999851
0.115	0.375 Chris: Solver converged in probability to a global solution.	0.000	0.625	0.115	0.1055					0.3750000522
0.120	0.500 Chris: Solver converged in probability to a global solution.	0.000	0.500	0.120	0.1217					0.4999999404
0.125	0.625 Chris: Solver converged in probability to a global solution.	0.000	0.375	0.125	0.1397					0.625
0.130	0.750 Chris: Solver converged in probability to a global solution.	0.000	0.250	0.130	0.1591					0.7499998808
0.135	0.875 Chris: Solver converged in probability to a global solution.	0.000	0.125	0.135	0.1792					0.8750001341
0.140	1.000 Chris: Solver converged in probability to a global solution.	0.000	0.000	0.140	0.2000					1

Sensitivity of Investment_weights_1 to Required return

0.10000000149011612 0.10500000417232513 0.10999999940395355 0.11500000208616257 0.12000000476837158 0.125 0.12999999523162842 0.13500000536441803 0.14000000059604645 3.7252902845841263E-8 0.12500010430812819 0.24999998509883853 0.37500005215406379 0.49999994039535522 0.62499999999999933 0.74999988079070978 0.8750001341104503 1

Required return ($D$23)

Efficient Frontier

Portfolio_stdev 8.0000000000000307E-2 8.321659014997336E-2 9.2195443215272274E-2 0.10547512177894132 0.1216552423748523 0.1397318861248211 0.15905971831941693 0.1792345052876122 0.2 0.10000000149011613 0.10500000417232515 0.10999999940395355 0.11500000208616257 0.11999999761581423 0.12499999999999997 0.12999999523162842 0.13500000536441803 0.14000000000000001

Standard deviation of portfolio return (risk)

Mean portfolio return

When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.

Once you get this efficient frontier, make sure you understand what it means. The attractive part of the chart for an investor is the upper left: high expected return and low risk. Unfortunately, points above and to the left of the curve are unattainable. On the other hand, the unattractive part of the chart is the lower right: low expected return and high risk. The idea of the efficient frontier is that you don't need to be below and to the right of the curve. By choosing the weights appropriately, you can create portfolios that are on the curve. Which point on the curve is best? There is no correct answer. It depends entirely on how much risk you are willing to incur. Conservative investors will favor the left side; more daring investors will favor the right side.

This is probably the most natural use of SolverTable in the entire book, because it provides exactly the information investors want: the trade-off between expected return and risk. It clearly shows that you can't have your cake and eat it too, i.e., to get a higher expected return, you have to assume more risk.

Topic 7 Chapter 14 Examples (Templates,Data)/Electricity Pricing Big Picture.xlsx