You are an investor with an investment horizon of one year and a certain degree of risk aversion. Your task is to determine the efficient frontier in the case of two risky securities and one risk-free (T-bill) security and select the optimal portfolio depending on your risk-aversion parameter. You need to do your work on excel.

The following steps will help you accomplish this task:

1- Choose

• A well-diversified risky bond B represented by its E(R_B) and STD_B.
• A well-diversified stock fund S represented by its E(R_S) and STD_S.
• A) Choose your own bond and stock and parameters for a company in the real world.
• B) Choose the one-year risk-free rate to be 5%. Choose one of the following: (a) E(R_B) = 9%, SD(B) = 14%, E(R_S) = 14%, SD(S) = 20% (b) E(R_B) = 10%, SD(B) = 15%, E(R_S) = 16%, SD(S) = 22% (c) E(R_B) = 12%, SD(B) = 16%, E(R_S) = 20%, SD(S) = 25%.

2- Choose a correlation coefficient between B and S.

Choose your own correlation corresponding to Step 1 A.

For Step 1 B choose one of the following: (a) Corr(B,S) = 0.20 (b) Corr(B,S) = 0.30 (c) Corr(B,S) = 0.40

3- Make simulations on STD and E(R) of a "complete" portfolio (formed with B and S) by varying the weights allocated on B and S.

4- Construct and graph the opportunity set (feasible set) for B and S from your simulations.

5- Compute the weights of the tangent portfolio (T).

6- Compute the STD_T and E (R_T) of the tangent portfolio (T).

7- Add the T-bill to your portfolio and redo step 3.

8- Repeat step 4 with the T-bill rate.

9- Write a summary of your results

The following equations will help you in your computations:

Var (R_p) = W_B² x Var (B) + W_S² x Var (S) + 2W_B x W_S x (STD_B x STD_S x CORR (B, S))

STD = SQRT (Var)

CORR (B, S) = Correlation coefficient between bond and stock

COV (R_B, R_S) = STD_B x STD_S x CORR (B, S)

• OPTIMAL TANGENT PORTFOLIO (T) WEIGHTS

* For the bond fund B:

Numerator = [E(R_B) R_F] x Var (R_S) - [E(R_S) R_F] x COV (R_B, R_S)

Denominator = [E(R_B) R_F] x Var (R_S) + [E(R_S) R_F] x Var (R_B) [E(R_B) R_F + E(R_S) R_F] x COV (R_B, R_S)

W_B = Numerator/Denominator

* For the stock fund: W_S = 1 - W_B

* The last column of step 7: The Capital Market (Allocation) Line:

E(R_C) = R_F + ([E(R_T) R_F]/STD_T) x STD_C