FUND Selected: Tangerine Equity Growth Portfolio

In this part of the project you are asked to empirically test CAPM on your Mutual Fund and the seven 7 stocks. You need to learn how to do a regression on excel. You have to rely on the S&P/TSX index and the Risk_Free rates.

1. Use the one month Canadian Tbills as your proxy for Risk free. N.B.: you need to obtain historical monthly prices (levels) for Tbills and S&P/TSX and calculate their 60 Months historical returns. (0.5 pt)

2. Calculate and compare the Sharpe ratio of the MF you have selected in Part I and S&P500. What can you conclude? (0.5pt)

3. Estimate CAPM (using regression on excel) for your MF? a. What is your Beta? (0.25t) b. What is your alpha? Is it significant? (0.5pt) c. How much Market Risk is there in the total risk of the equal portfolio? (0.5pt) d. What can you conclude? (0.25pt)

4. Estimate CAPM for the 7 stocks that form your portfolio. a. Relying only on the 7 stocks estimated betas, the estimated variance of the residuals, and the Market Variance, the Market expect return, RF, re-estimate your covariance matrix. (2.5pt) b. Relying on this new covariance matrix construct the new efficient frontier (no short sales). (1.5pt)

5. Suppose your friend suggested you add stock ABC to your portfolio of 7 stocks. What is the minimum expected return you require on stock ABC given that its standard deviation is 23% and its beta is 0.75. (0.5pt)

I would like to know how to do this on Excel please I don't really care about the exact answer would just like to know the format and procedure thank you!