Each group should submit their report to me via email, email@example.com, by 5 pm, Wednesday, April 29th. The report consists of two parts. The first part is a word document. It will consist a) an introduction that summarizes the case in a paragraph or two; b) a detailed description of your methodology; c) summarizes your computations and d) conclusion whereby you provide your rationale for the cost of equity of Investor’s.
Although, the case suggests that you use regression to find the beta, it will suffice if you use the formula:
bi = Cov(Ri, Rm)/Var(Rm).
You can use the excel functions for covariance and variance to obtain the beta.
The second part is the excel sheet which you will show the calculations for the case. Make sure you save it as an excel and not a csv file so I can check the formulas that you are using. Here are some helpful hints.
For the case you are being asked to calculate the cost of equity. As shown in class you can calculate the cost of equity using the CAPM or the Dividend Growth Model. Ultimately, you will have to decide which one to use or to take an average. You must provide a rationale for your choice. You will need to download data from Yahoo to obtain price and dividend information.
When calculating the beta, you are being asked to calculate the beta using either daily data for a year and monthly data for 60 months. Hence, to find the beta using the formula above you need to download the price and dividend data for both Investor’s and the S&P. (S&P has no dividends.) The price data obtained from the adjusted closing price columan has already taken into account any stock splits. The dividends have also been adjusted. When you find the beta, you do not have to adjust the measurement to annualize it.
To find the market premium, you should take at least a 50 year average of the difference between the monthly return of the S&P and the three month t-Bill rate. Remember, that when you compute the monthly return of the S&P it is a different periodic measure than the return for the T-Bill that is reported by the Fed. Hence, to find the actual monthly return, divide the T-Bill Rate by 12.
Now when you compute the CAPM, you will need to use the annualize estimate of the T-Bill Rate and the Market Premium to find the cost of equity.
Finally, to find the growth rate of dividends, find the annual growth rate. Most firms keep their quarterly dividend fixed for 4 quarters. So you are looking at the growth rate of the annual dividend.