Problem Set

Question 2

(a)  According to the regression model for its return, we can determine the equity beta  using the “=SLOPE” formula in excel. Then, we verify the result by using formula. Here, in excel, we can easily get the results of  by entering the formula “=COVARIANCE.S” and we can also calculate the  using the variance function “=VAR.S”.[pic 1][pic 2][pic 3][pic 4][pic 5]

The following data is the related results copied from excel:

[pic 6]

At the end, we test our result by calculating the difference between  calculating from regression model and  calculating from the formula. From the table we can obviously find that the differences are 0 which means the result from the regression model is correct.[pic 7][pic 8][pic 9]

(b) After calculating the average monthly return for each firm, and getting the  by following the regression model, we get a scatter plot showing the relationship:  as showing below.[pic 10][pic 11][pic 12][pic 13]

[pic 14]

From the formula of CAPM, we can predict that the relationship between  and  should be linear, specifically, the slope should be equal to  and the interception with vertical axis should be equal to. However, the results on the scatter plot are not exactly same as our prediction. We can see that the trend line of scatter plot is same as our prediction which shows the linear relationship. However, there are differences from each scatter point to the trend line. These can be explained as , which is the risk involved in each company, so is not constant and can be different with different companies.[pic 15][pic 16][pic 17][pic 18][pic 19][pic 20][pic 21]