Question 1 (20 points): This question is about calculating the cost of equity using CAPM. Display GetFormula for required cells. 1) Calculate monthly returns (assume discrete compounding) for Ford Motors and S&P 500. (4 points 2) Draw a line chart including both return series you calculated in question (1). The x-axis should be the dates. (1 point) 3) Use excel built-in statistical function to compute variance of S&P 500 return and covariance of and covariance you just computed. (4 points) 4) Use the Excel built-in financial function to calculate beta for Ford Motors. (3 points) 5) Compute the annualized expected market return, E(r_m). If the annual risk-free rate, r_f, is 2.15% (as given in cell M29), calculate the cost of equity for Ford Motors using CAM (4 points) 6) Compare the cost of equity for Ford Motors with the expected annual market return. Which one is higher? Why? What is the relationship between the cost of equity for Ford Motors and its beta? Answer this part in the given text box. (4 points) Ford Returns S&P500 Returns on Date GetFormula Motors on Ford GetFormula Close S&P 500 Adj Close Motors GetFormula 12/1/2015 2043.9399 11/2/2015 2080.4099 10/1/2015 2079.3601 9/1/2015 1920.0300 8/3/2015 1972.1801 7/1/2015 2103.8401 6/1/2015 2063.1101 5/1/2015 2107.3899 4/1/2015 2085.5100 3/2/2015 2067.8899 2/2/2015 2104.5000 1/2/2015 1994.9900 12/1/2014 2058.8999 11/3/2014 2067.5601 10/1/2014 2018.0500 9/2/2014 1972.2900 8/1/2014 2003.3700 7/1/2014 1930.6700 6/2/2014 1960.2300 5/1/2014 1923.5699 4/1/2014 1883.9500 3/3/2014 1872.3400 Variance of SP500: Covariance of SP500 and Ford Motor Risk free rate: Beta: (Formula) Beta: (Excel build-in Function) Average annual return of market (SE Cost of Equity (CAPM): 13.335378 0.0175 (B29-B30)/B30 13.562524 0.0167 (E29-E30)/E30 0.0005 (B30-B31)/B31 14.016817 -0.0324 (E30-E31)/E31 0.0830 (B31-B32)/B32 12.7138470.1025 (E31-E32)/E32 0.0264 (B32-B33)/B33 12.99492-0.0216 (E32-E33)/E33 0.0626 (B33-B34)/B34 13.894352 0.0647 (E33-E34)/E34 0.0197 (B34-B35)/B35 13.920753 -0.0019 (E34-E35)/E35 0.0210 (B35-B36)/B36 14.069142 -0.0105 (E35-E36)/E36 0.0105 (B36-B37)/B37 14.653425 -0.0399 (E36-E37)/E37 0.0085 (B37-B38)/B38 14.8289420.0118 (E37-E38)/E38 0.0174 (B38-B39)/B39 15.012697 -0.0122 (E38-E39)/E39 0.0549 (B39-B40)/B40 13.515102 0.1108 (E39-E40)/E40 0.0310 (B40-B41)/B41 14.097083 0.0413 (E40-E41)/E41 0.0042 (B41-B42)/B42 14.306265 0.0146 (E41-E42)/E42 0.0245 (B42-B43)/B43 12.8147030.1164 (E42-E43)/E43 0.0232 (B43-B44)/B44 13.332602 -0.0388 (E43-E44)/E44 0.0155 (B44-B45)/B45 15.69427-0.1505 (E44-E45)/E45 0.0377 (B45-B46)/B46 15.342858 0.0229 (E45-E46)/E46 0.0151 (B46-B47)/B47 15.430614 0.0057 (E46-E47)/E47 0.0191 (B47-B48)/B48 14.71770.0487 (E47-E48)/E48 0.0210 (B48-B49)/B49 14.455011 0.0180 (E48-E49)/E49 0.0062 (B49-B50)/B50 13.854465 0.0433 (E49-E50)/E50 2.1500% 0.0215
