Question

PLEASE ANSWER THE QUESTION USING EXCEL AND SHOW ALL THE WORK AND FOMRULAS USED IN EXCEL. 5% 33% 6. The following table contains the probability distribution for the returns of three securities over the next year. Expected Returns Scenario Probability -17% .9% 11% 17% 7% 14% 25% 19% 29% 27% 34% -17% 35% 44% -27% a) Determine the expected return and standard deviation of each security. b) Calculate a variance and covariance matrix for these four securities using the given probabilities. c) Calculate a set of five portfolios that make up the capital market line using the Solver. d) Create a chart of the CML including the original securities 17% T-bills 3% 3% 3% 3% 0.03 0.07 0.15 0.50 0.15 0.07 0.03 #2 #3 N4 NS W6 W7 7% 1% 3%

Answer 1

1

Calculation of Expected Return and Standard deviation of each Security

Expected Return = Probability of scenario* Return of Scenario

A

Expected returns

Standard Deviation

Probability

I

II

III

T-bills

I

II

III

T-bills

I

II

III

T-bills

0.03

-0.17

0.05

0.33

0.03

=$C11*D11

=$C11*E11

=$C11*F11

=$C11*G11

=(D11-I$19)*(D11-I$19)*$C11

=(E11-J$19)*(E11-J$19)*$C11

=(F11-K$19)*(F11-K$19)*$C11

=(G11-L$19)*(G11-L$19)*$C11

0.07

-0.09

0.11

0.17

0.03

=$C12*D12

=$C12*E12

=$C12*F12

=$C12*G12

=(D12-I$19)*(D12-I$19)*$C12

=(E12-J$19)*(E12-J$19)*$C12

=(F12-K$19)*(F12-K$19)*$C12

=(G12-L$19)*(G12-L$19)*$C12

0.15

0.07

0.17

0.07

0.03

=$C13*D13

=$C13*E13

=$C13*F13

=$C13*G13

=(D13-I$19)*(D13-I$19)*$C13

=(E13-J$19)*(E13-J$19)*$C13

=(F13-K$19)*(F13-K$19)*$C13

=(G13-L$19)*(G13-L$19)*$C13

0.5

0.14

0.25

0.01

0.03

=$C14*D14

=$C14*E14

=$C14*F14

=$C14*G14

=(D14-I$19)*(D14-I$19)*$C14

=(E14-J$19)*(E14-J$19)*$C14

=(F14-K$19)*(F14-K$19)*$C14

=(G14-L$19)*(G14-L$19)*$C14

0.15

0.19

0.29

-0.09

0.03

=$C15*D15

=$C15*E15

=$C15*F15

=$C15*G15

=(D15-I$19)*(D15-I$19)*$C15

=(E15-J$19)*(E15-J$19)*$C15

=(F15-K$19)*(F15-K$19)*$C15

=(G15-L$19)*(G15-L$19)*$C15

0.07

0.27

0.34

-0.17

0.03

=$C16*D16

=$C16*E16

=$C16*F16

=$C16*G16

=(D16-I$19)*(D16-I$19)*$C16

=(E16-J$19)*(E16-J$19)*$C16

=(F16-K$19)*(F16-K$19)*$C16

=(G16-L$19)*(G16-L$19)*$C16

0.03

0.35

0.44

-0.27

0.03

=$C17*D17

=$C17*E17

=$C17*F17

=$C17*G17

=(D17-I$19)*(D17-I$19)*$C17

=(E17-J$19)*(E17-J$19)*$C17

=(F17-K$19)*(F17-K$19)*$C17

=(G17-L$19)*(G17-L$19)*$C17

Expected Returns

=SUM(I11:I18)

=SUM(J11:J18)

=SUM(K11:K18)

=SUM(L11:L18)

Standard Deviation

=SUM(P11:P17)

=SUM(Q11:Q17)

=SUM(R11:R17)

=SUM(S11:S17)

Calculation of Expected Return and Standard deviation of each Security Expected Return = Probability of scenario* Return of Scenario A Expected returns Standard Deviation Probability I II III T-bills I II III T-bills I II III T-bills 0.03 -17% 5% 33% 3% -0.5% 0.2% 1.0% 0.1% 0.265% 0.109% 0.319% 0.000% 0.07 -9% 11% 17% 3% -0.6% 0.8% 1.2% 0.2% 0.330% 0.119% 0.193% 0.000% 0.15 7% 17% 7% 3% 1.1% 2.6% 1.1% 0.5% 0.049% 0.074% 0.066% 0.000% 0.50 14% 25% 1% 3% 7.0% 12.5% 0.5% 1.5% 0.008% 0.005% 0.002% 0.000% 0.15 19% 29% -9% 3% 2.9% 4.4% -1.4% 0.5% 0.060% 0.037% 0.132% 0.000% 0.07 27% 34% -17% 3% 1.9% 2.4% -1.2% 0.2% 0.143% 0.070% 0.211% 0.000% 0.03 35% 44% -27% 3% 1.1% 1.3% -0.8% 0.1% 0.149% 0.120% 0.225% 0.000% Expected Returns 12.7% 24.0% 0.4% 3.0% Standard Deviation 1.003% 0.533% 1.149% 0.000%