60.1K

Verified Solution

Link Copied!

1. Plot in Excel the opportunity set for Portfolios A & B. To do this you will need to calculate the missing information in the table found in the Excel spreadsheet that accompanies the case using weights of portfolio A & B in 10 percentage point increments. To do this you will need to know how to program formulas in Excel using absolute and relative cell references from the data provided. (The table below already exists in the Excel file).

Weight Port A	Weight Port B	Return	Standard Deviation	Sharpe Ratio
0%	100%
10	90
20	80
30	70
40	60
50	50
60	40
70	30
80	20
90	10
100	0

Determine the optimal risky portfolio (e.g. the optimal allocation of A & B) using the concepts from Modern Portfolio Theory and draw in the Capital Allocation Line (CAL). The approximate optimal allocation can be determined using the table in Excel like the one shown above.

2. Find the optimal complete portfolio graphically using the clients indifference curve. Plot an indifference curve on the same graph you just created using the utility function formula from Chapter 6. To make things easier, you can use the same portfolio risk numbers from the table above and then calculate the expected return based on the values of utility (U) and risk aversion coefficient (A) in provided in the Excel spreadsheet. Plot the indifference curve AND the opportunity set of risky assets on the same graph.

Next determine the optimal complete portfolio. You did this graphically above, but you need to calculate the precise composition of the optimal risky portfolio (recall ymaxU) and T-Bills (1-ymaxU).

3. Plot the CAPM regressions of Portfolio A and Portfolio B (separate graphs) in the Excel spreadsheet. The market portfolio is represented by the S&P 500 and the risk-free rate is represented by 90-day T-Bill. Calculate the beta for each portfolio using the following methods:

1. The slope function in Excel

2. The beta formula (co-variance divided by the market variance) is explained in the Modules 6 & 7 Notes; ppt lecture notes; and textbook. Recall the covariance between two assets (A & B) is the volatility of asset A times the volatility of asset B times the correlation between A & B.

Then calculate the alpha for each portfolio A & B using the intercept function in Excel and the CAPM formula solving for alpha. Note the two CAPM regressions are based on monthly returns so the y-intercept (or alpha) is a MONTHLY alpha.

Calculate the annualized alpha using the CAPM formula, beta from above and the annualized returns.

Answer & Explanation Solved by verified expert

See Answer