EXCEL PROJECT AND EXCEL SOLUTION Consider the following stocks, all of which will pay

a liquidating dividend in a year and nothing in the interim:

a$.$ Calculate the expected return of each stock.

b$.$ What is the sign of correlation between the expected return and market

capitalization of the stocks?

PLEASE SHOW SOLUTIONS IN EXCEL

In Problem $20,$ assume the risk$-$free rate is $3\%$ and the market risk premium is $7\%.$

a$.$ What does the CAPM predict the expected return for each stock should be$?$

b$.$ Clearly, the CAPM predictions are not equal to the actual expected returns, so the CAPM

does not hold. You decide to investigate this further. To see what kind of mistakes the CAPM

is making, you decide to regress the actual expected return onto the expected return

predicted by the CAPM. $49$ What is the intercept and slope coefficient of this regression?

The Excel function SLOPE will produce the desired answers

c$.$ What are the residuals of the regression in part $($b$)?$ That is$,$ for each stock compute the

difference between the actual expected return and the best$-$fitting line given by the

intercept and slope coefficient in part $($b$).$

d$.$ What is the sign of the correlation between the residuals you calculated in part $($c$)$ and

market capitalization?

e$.$ What can you conclude from your answers to part $($b$)$ of the previous problem and part

$($d$)$ of this problem about the relation between firm size $($market capitalization$)$ and returns?

$($The results do not depend on the particular numbers in this problem. You are welcome to

verify this for yourself by redoing the problems with another value for the market risk

premium, and by picking the stock betas and market capitalizations randomly.

The Excel command RAND will produce a random number between $0$ and $1.$