The instuction is attached. Actually the solution is attached too. But I want the calculation in excel. You can take the solution as reference and calculate it in excel. I need it in an hour or two.

USC - MARSHALL SCHOOL OF BUSINESS FBE 441 Investments David Solomon - Spring 2015 Homework Assignment #3 Solutions 1. If CAPM is valid, which of the following situations are possible? Explain. Consider each situation independently. Hint: recall that the CAPM has implications for expected returns, correlations, covariances and variances. a) Portfolio A B Expected Beta Return 20 1.4 25 1.2 CAPM tells us that assets with higher betas should have higher expected returns. In this example, portfolio B has a lower beta, but a higher expected return. Contradiction. b) Portfolio Expected Standard Return Deviation A 30 35 B 45 25 Since part of the total asset's risk can be diversified away, higher standard deviation does not necessarily imply higher expected returns. Therefore, there is no contradiction with CAPM here. c) Portfolio Expected Standard Return Deviation Risk-free 10 0 Market 18 24 A 16 12 CAPM implies that the market portfolio has the highest Sharpe ratio Expected Return - Riskfree rate SR . In this example, Standard Deviation SR(Market)=(18-10)/24=0.33, while SR(A)=(16-10)/12=0.5, i.e. SR(Market) 'Paste Values'. The easiest way to select only the portfolio returns is to add a number in a new column that signifies the high/low momentum portfolios. So next to each cell where you've put a return from the high momentum portfolio, put a '2', and next to each low momentum return, put a '1' (call this variable 'MomLevel'. Now, select all the data and sort first on 'MomLevel', then on 'Datemonth'. This should now give you the portfolio returns month by month, first for the low momentum and then the high momentum. In each case, subtract the risk free rate ('RF') from the portfolio returns. Calculations At last, you have your portfolios sorted on high and low momentum. 1. For the high and low momentum portfolios, calculate the average returns, the standard deviation of returns, and the Sharpe Ratio. 2. In addition, calculate the average returns, standard deviation of returns and the Sharpe Ratio for the excess market returns (MktRf), as well as the SMB and HML portfolios. How do the high and low momentum portfolios compare with these factor portolios? Which would you prefer? 3. Run a CAPM regression for the high momentum and low momentum portfolios. To do this, select 'Data Analysis' and then select the 'Regression' option. The CAPM regression is (e.g. for high momentum stocks): Ret(High Mom) - Rf = a + b*MktRf + e As a result, the range of Y variable is the excess returns for the high momentum portfolio. The range of the X variable is the returns over the same period for 'MktRf' i.e. the excess market returns. Output the results to new worksheets. What are the alphas (intercepts) of the two portfolios? Are they statistically different from zero? 4. Run a Fama French 3 Factor Regression for the high and low momentum portfolios. The procedure is similar to the CAPM regression, but the X variables now include 'MktRf', 'SMB' and 'HML', so select the range of all three variables for the 'X Variable' choice in the regression dialog box. What are the 3 Factor Alphas for the long and short portfolios? Are they statistically different from zero? 5. Calculate a difference portfolio, which buys all high momentum stocks and shorts all low momentum stocks. To do this, for each month take the high momentum portfolio and subtract the low momentum portfolio. Run a CAPM regression and a Fama French 3 Factor Regression for the difference portfolio. Does this difference portfolio earn abnormal returns under each model? 6. Finally, for the long and short portfolios, run a 4 Factor regression, using the Fama French 3 Factor variables ('MktRf', 'SMB', 'HML') as well as a momentum factor ('UMD'). What are the intercepts for each portfolio relative to this model? 7. Adding in a momentum factor in #6 should control for the effect of momentum on returns. Given the results in #6, do you think these stocks look representative of the momentum effect in general? If not, how do you think I might have chosen the list of 30 stocks