| Chapter 13 - Section 10 |
| Estimating Eastman's Cost of Capital |
| 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. |
| Eastman's Cost of Equity |
| 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="&D32&"]". 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 D32 has the entry for the ticker symbol. |
| Stock quote link |
| Stock price: | $ 70.82 |
| Dividend: | $ 1.60 |
| Key statistics link |
| Beta: | 1.29 |
| Shares outstanding: | 148,660,000 |
| Analysts' estimates link |
| 5-year dividend growth: | 7.55% |
| Bond center link: |
| Risk-free rate: | 0.60% |
| 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: | $ 10,528,101,200 |
| Cost of equity |
| Dividend discount model: | 9.98% |
| CAPM: | 9.63% |
| 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 found later in this 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.80% |
| 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. |
| Note that we did not calculate the weighted average industry beta as was suggested in the text. While doing so could be advantageous, we chose not to in this case, although to do so would be fairly easy. |
| Eastman's Cost of Debt |
| To find the cost of debt, we need the current YTM of the company's bonds, the bond price, and the book value of the bonds. The YTM and price are found on the FINRA bond website, while the book value is found in the 10k or 10q. While the SEC website has the most recent electronic filing, the hyperlink below still requires the user to find the most recent report and look up the information. |
| FINRA bond quote |
| SEC EDGAR database |
| Once we have entered the information gathered from these two websites, we can calculate the market value of debt, as well as the weighted average cost of debt. Below, you can see a table with these calculations. |
| Maturity | YTM | Quoted
Price | Book Value | Market Value | Market Value
Weight | Market Values |
| 6/1/17 | 1.561% | 101.342 | $ 998,000,000 | $ 1,011,393,160 | 0.16301 | 0.254% |
| 11/15/18 | 2.405% | 117.783 | $ 169,000,000 | $ 199,053,270 | 0.03208 | 0.077% |
| 11/15/19 | 2.986% | 109.606 | $ 250,000,000 | $ 274,015,000 | 0.04416 | 0.132% |
| 1/15/20 | 2.606% | 100.368 | $ 798,000,000 | $ 800,936,640 | 0.12909 | 0.336% |
| 1/15/21 | 2.878% | 107.526 | $ 250,000,000 | $ 268,815,000 | 0.04333 | 0.125% |
| 8/15/22 | 3.471% | 100.754 | $ 903,000,000 | $ 909,808,620 | 0.14664 | 0.509% |
| 1/15/24 | 4.040% | 124.017 | $ 244,000,000 | $ 302,601,480 | 0.04877 | 0.197% |
| 6/15/24 | 4.462% | 124.277 | $ 54,000,000 | $ 67,109,580 | 0.01082 | 0.048% |
| 3/15/25 | 3.949% | 98.835 | $ 796,000,000 | $ 786,726,600 | 0.12680 | 0.501% |
| 2/1/27 | 4.234% | 130.355 | $ 222,000,000 | $ 289,388,100 | 0.04664 | 0.197% |
| 9/1/42 | 5.070% | 96.055 | $ 497,000,000 | $ 477,393,350 | 0.07694 | 0.390% |
| 10/15/44 | 5.103% | 93.183 | $ 877,000,000 | $ 817,214,910 | 0.13171 | 0.672% |
| Total market value = | $ 6,204,455,710 | Cost of debt = | 3.439% |
| With the following tax rate, the aftertax cost of debt will be: |
| Tax rate: | 35% |
| Aftertax cost of debt: | 2.236% |
| Eastman's WACC |
| Now we have all of the information necessary to calculate the WACC. Below, we have produced a table with the necessary information. |
| Cost of equity: | 9.80% |
| Market value of equity: | $ 10,528,101,200 |
| Aftertax cost of debt: | 2.24% |
| Market value of debt: | $ 6,204,455,710 |
| The market value weight of debt and equity in the capital structure is: |
| Weight of debt: | 0.3708 |
| Weight of equity: | 0.6292 |
| Finally, the weighted average cost of capital is: |
| WACC: | 7.00% |
| Of course, if we wanted, we could create another hyperlink to open the ValuePro estimate of the WACC for the company we are investigating. |
| ValuePro website |
| Notice that the ValuePro link above does not take you directly to the WACC calculations. The reason is that ValuePro uses a different web page naming system than Yahoo! Finance. In this case, concatenation will not work. |
vk4queen
CF11theditionChapter13ExcelMasterstudent.xlsx
