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.

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

Stock 1

1

0.6

0.4

Stock 2

0.6

1

0.7

Stock 3

0.4

0.7

1

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

				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

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

0.1217