| Chapter 12 - Section 2 |
| The Cost of Equity |
| Calculating the cost of capital requires inputs for equity and debt. Some of the inputs and assumptions may be different for different companies. For example, we will show how to calculate the WACC for Eastman Chemical, the same company we examined in the textbook. You should note that the WACC will be different because of different dates. |
| Before we begin our calculation of the WACC, we would like to introduce the HYPERLINK function. HYPERLINK will put a link into the Excel spreadsheet that will open a shortcut or jump that opens a document stored on a network server, an intranet, or the internet. By using hyperlinks, you can open objects, documents, pages, and other destinations. When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file that is stored at that location. You are already familiar with using a hyperlink in Excel since the boxes on the opening worksheet that take you directly to a function in the worksheet are hyperlinks. Suppose you want to use HYPERLINK to create a link to a website that will really help you understand Finance. Click on the link below and see what happens. |
| Click here for a link to really help you understand Finance |
| RWJ Excel Tip |
| HYPERLINK is found under the Lookup & Reference functions. The link location is the location of the file you want the function to open, in this case a web page. Note that Excel put the quotes around the link automatically, although there are cases you may want to enter the quotes yourself as we will show later. The Friendly name is the name for the link Excel will display. |
| To calculate the cost of equity, we need a stock quote, the market value of the company, the most recent dividend, as well as the analysts' growth rate projection. While we could enter these as static links, we would like the links to be more robust, that is the link will open the correct page for any stock once we enter the ticker symbol in Excel. Fortunately, many websites, including Yahoo! Finance, have a fairly static link for forms. For example, on Yahoo! Finance, the address for EMN's stock quote is: http://finance.yahoo.com/q?s=emn. For any stock quote, only the last 3 (or 4) letters change, and these last letters represent the ticker symbol. Each webpage on Yahoo! Finance has the same basic syntax, where only the last 3 or 4 letters are different depending on the ticker symbol. What we need to do is create a web link that will concatenate the web link with a cell that has the ticker symbol as an input. The link for the Yahoo! Finance bond center will allow us to find the risk-free rate. |
| Ticker symbol: | EMN |
| Yahoo! Finance stock quote |
| Yahoo! Finance key statistics |
| Yahoo! Finance analyst estimates |
| Yahoo! Finance bond center |
| RWJ Excel Tip |
| Entering the correct link for a more robust web link based on the ticker symbol is slightly trickier. For example, the Link location we used for the analysts estimates web link was "[http://finance.yahoo.com/q/ae?s="&D31&"]". We needed to enter the quotation marks, as well as the brackets, since we are using concatenation later in the input. Entering the quotation marks and ampersands is the standard method of concatenating cells. In this case, cell D31 has the entry for the ticker symbol. |
| Stock quote link |
| Stock price: | $ 65.95 |
| Dividend: | $ 1.66 |
| Key statistics link |
| Beta: | 1.43 |
| Shares outstanding: | 148,610,000 |
| Analysts' estimates link |
| 5-year dividend growth: | 6.85% |
| Bond center link: |
| Risk-free rate: | 0.13% |
| The market risk premium is not publicly available and requires some judgment. We will use the same market risk premium as the textbook: |
| Market risk premium: | 7.00% |
| Now we have enough information to calculate the market value of equity, as well as the required return using both the dividend discount model and CAPM. |
| Market value of equity: | $ 9,800,829,500 |
| Cost of equity |
| Dividend discount model: | 9.54% |
| CAPM: | 10.14% |
| In our calculation in the textbook, both the dividend discount model and CAPM estimates for the cost of equity made economic sense. Since they were both reasonable, we averaged them. We still need to look at the estimates to see if they make economic sense, which makes it somewhat more difficult to enter into a spreadsheet formula. In this case, we will make an assumption: The cost of equity has to be higher than the cost of debt. Given the risk-reward information we discussed earlier, this makes sense. What we will do is set up an equation that averages the two cost of equity estimates only if they are greater than the cost of debt. The calculation for the cost of debt is on the next worksheet, but we will reference it here nonetheless. Below, we have used the IF function to calculate the average of the two estimates under certain conditions. |
| Cost of equity: | 9.84% |
| The equation in the cost of equity cell above tests if the dividend discount model estimate is above the cost of debt. If it is, the next test is to determine if the cost of equity from CAPM is above the cost of debt. If both are true, the result will be the average of the two estimates. If one is not above the cost of debt, the result will be the larger of the two estimates. If both of the cost of equity estimates are below the cost of debt, the equation will return FALSE, which will indicate that neither cost of equity estimate is greater than the YTM on the company's bonds. |
Heybis
Essentials9eChapter12ExcelMasterStudent.2.xlsx
