Question

i must need excel formula

Spreadsheet Exercise: Chapter 8 Jane is considering investing in three different stocks or creating three distinct twostock portfolios. Jane views herself as a rather conservative investor. She is able to obtain historical returns for the three securities for the years 2012 through 2018. The data are given in the following table. Stock C 129 13 Year 2012 2013 2014 2015 2016 2017 2018 Stock A 10% 13% 15% 14% 16% 1496 12 Stock B 10% 11% 8% 12% 10% 15% 15% 10% 1196 9 106 In any of the possible two-stock portfolios, the weight of each stock in the portfolio will be 50%. The three possible portfolio combinations are AR, AC and BC To Do Chapter w P21481217104 t o e SAMSUNG

Answer 1

Excel Formulas are present in the below table

	Stock A	Stock B	Stock C
2012	10.00%	10.00%	12.00%
2013	13.00%	11.00%	14.00%
2014	15.00%	8.00%	10.00%
2015	14.00%	12.00%	11.00%
2016	16.00%	10.00%	9.00%
2017	14.00%	15.00%	9.00%
2018	12.00%	15.00%	10.00%
Expected Return	13.43%	11.57%	10.71%
Standard Deviation	0.019880596	0.026367	0.017995
Coefficient of Variation	0.148046991	0.227866	0.167951
Weighted Average	0.5
	Stock AB	Stock BC	Stock CA
2012	10.00%	11.00%	11.00%
2013	12.00%	12.50%	13.50%
2014	11.50%	9.00%	12.50%
2015	13.00%	11.50%	12.50%
2016	13.00%	9.50%	12.50%
2017	14.50%	12.00%	11.50%
2018	13.50%	12.50%	11.00%
Overall Expected Return	12.50%	11.14%	12.07%
Standard Deviation	0.014719601	0.014058	0.009322
Coefficient of Variation	0.117756812	0.126159	0.077226
Stock A has an expected return of		13.43%	with a standard deviation of			0.019881
Investing in port. AB has return of		12.50%	with a standard deviation of			0.01472
so there is both a	Low	amount of risk and return in the portfolio
We can see that the CV of portfolio is		smaller	than that of stock A alone, so the portfolio AB should be recommended
Stock B has an expected return of		11.57%	with a standard deviation of			0.026367
Investing in port. BC has return of		11.14%	with a standard deviation of			0.014058
so there is both a	low	amount of risk and return in the portfolio
We can see that the CV of portfolio is		smaller	than that of stock B alone, so the portfolio BC should be recommended

	Stock A	Stock B	Stock C
2012	0.1	0.1	0.12
2013	0.13	0.11	0.14
2014	0.15	0.08	0.1
2015	0.14	0.12	0.11
2016	0.16	0.1	0.09
2017	0.14	0.15	0.09
2018	0.12	0.15	0.1
Expected Return	=AVERAGE(B2:B8)	=AVERAGE(C2:C8)	=AVERAGE(D2:D8)
Standard Deviation	=STDEV(B2:B8)	=STDEV(C2:C8)	=STDEV(D2:D8)
Coefficient of Variation	=B11/B9	=C11/C9	=D11/D9
Weighted Average	0.5
	Stock AB	Stock BC	Stock CA
2012	=$B$14*B2+$B$14*C2	=$B$14*C2+$B$14*D2	=$B$14*D2+$B$14*B2
2013	=$B$14*B3+$B$14*C3	=$B$14*C3+$B$14*D3	=$B$14*D3+$B$14*B3
2014	=$B$14*B4+$B$14*C4	=$B$14*C4+$B$14*D4	=$B$14*D4+$B$14*B4
2015	=$B$14*B5+$B$14*C5	=$B$14*C5+$B$14*D5	=$B$14*D5+$B$14*B5
2016	=$B$14*B6+$B$14*C6	=$B$14*C6+$B$14*D6	=$B$14*D6+$B$14*B6
2017	=$B$14*B7+$B$14*C7	=$B$14*C7+$B$14*D7	=$B$14*D7+$B$14*B7
2018	=$B$14*B8+$B$14*C8	=$B$14*C8+$B$14*D8	=$B$14*D8+$B$14*B8
Overall Expected Return	=$B$14*B9+$B$14*C9	=$B$14*C9+$B$14*D9	=$B$14*D9+$B$14*B9
Standard Deviation	=STDEV(B17:B23)	=STDEV(C17:C23)	=STDEV(D17:D23)
Coefficient of Variation	=B26/B24	=C26/C24	=D26/D24
Stock A has an expected return of		=B9	with a standard deviation of			=B11
Investing in port. AB has return of		=B24	with a standard deviation of			=B26
so there is both a	Low	amount of risk and return in the portfolio
We can see that the CV of portfolio is		smaller	than that of stock A alone, so the portfolio AB should be recommended
Stock B has an expected return of		=C9	with a standard deviation of			=C11
Investing in port. BC has return of		=C24	with a standard deviation of			=C26
so there is both a	low	amount of risk and return in the portfolio
We can see that the CV of portfolio is		smaller	than that of stock B alone, so the portfolio BC should be recommended