This is based on the Portfolio Optimization model from our class. Required Return is still 12%. The two following elements need to be added:

Portfolio selection model					Range names used:
					Actual_return	=Model!$B$23
Stock input data					Fractions_to_invest	=Model!$B$15:$D$15
	Stock 1	Stock 2	Stock 3		Portfolio_variance	=Model!$B$25
Mean return	0.14	0.11	0.1		Required_return	=Model!$D$23
StDev of return	0.2	0.15	0.08		Total_invested	=Model!$B$19
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
Fractions to invest	0.500	0.000	0.500
Constraint on investing everything
	Total invested		Required value
	1.00	=	1
Constraint on expected portfolio return
	Actual return		Required return
	0.12	>=	0.12
Portfolio variance	0.0148
Portfolio stdev	0.1217

a) Stock 4 is added to the model with the following information:

				Correlation with other stocks
Stock input data	Mean return	StDev of return	Dividend:	Stock 1	Stock 2	Stock 3	Stock 4
Stock 4	12.0%	10.0%	2.5%	-0.5	-0.8	-0.3	1

b) Stocks distribute the following annual % dividends, which need to be included, as part of the stocks returns:

	Stock 1	Stock 2	Stock 3
Dividend:	1.2%	3.5%	4.0%

Tax rate for capital gain from actual returns is 20% and for dividends is 15%. Note that the dividends must be added to the average return. Then average return formula becomes:

Net Expected Return = (Expected Return)*(1 Capital Gain Tax) + (Total Dividend Return)*(1 Dividend Tax)

Use the Excel template to incorporate the dividends and tax rates into the model, solve it to minimize the portfolio risk (i.e., variance) and answer the following questions:

a) What is the optimal Fractions to Invest for all four stocks?

b) Solve the same model for the Required Returns of 10%, 11%, and 13%. Specifically, make a copy of your spreadsheet and solve the model again by changing the required return. Then, make a summary table showing how optimal portfolio standard deviation changes with respect to the required returns.