1 / 52

Estimating WACC and Target Weights in Valuation Spreadsheet

Learn how to estimate the weighted average cost of capital (WACC) and target weights using the Corporate Valuation Spreadsheet. This chapter provides step-by-step instructions and explains the concept of WACC, target weights, and inputs needed for calculation.

audreylujan
Download Presentation

Estimating WACC and Target Weights in Valuation Spreadsheet

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter 11 Estimating the Weighted Average Cost of Capital DES Chapter 11

  2. Using the Corporate Valuation Spreadsheet • Look at the file: Home Depot (for Ch 9-11, WACC, default inputs).xls. • This file will be called Home Depot.xls for short. DES Chapter 11

  3. Steps to estimate value using the Corporate Valuation Spreadsheet The valuation spreadsheet has seven interrelated worksheets The valuation spreadsheet has seven interrelated worksheets, each of which performs an essential function: (1) Proj & Val (2) Inputs (3) WACC (4) Hist Analys (5) Condensed (6) Comprehensive (7) Actual DES Chapter 11

  4. DES Chapter 11

  5. The WACC Sheet • The WACC worksheet has cells for the inputs needed to calculate the WACC. • Following is an explanation of how to find reasonable inputs. • See DES Chapter 11 for details. DES Chapter 11

  6. WACC Concept The WACC is the return required by all of the firm’s investors. • To calculate WACC, we need to know the return required by each class of investors, and the percentage of the firm that is financed by each class. (Continued) DES Chapter 11

  7. WACC Concept (continued) Focus is on four classes of investors: • Long-term debtholders • Preferred stockholders • Short-term debtholders • Common stockholders DES Chapter 11

  8. Target Weights Weights for the WACC • wLTD = weight, or percentage of firm that will be financed with long-term debt. • wSTD = weight, or percentage of firm that will be financed with short-term debt. • wPs = weight, or percentage of firm that will be financed with preferred stock. • wS = weight, or percentage of firm that will be financed with common equity. (Continued) DES Chapter 11

  9. Target Weights (continued) These percentages should be based on: • target market values of equity and debt, not book values of equity and debt • target percentages that the firm expects to have in the future, not necessarily the weights it has now. DES Chapter 11

  10. Target Weights (continued) Common Stock Value (Row 12) • always use an estimate of the current market value of equity, also known as market capitalization, or just market cap • enter the current price per share (row 10) and the current number of shares (row 11) • the default value for the number of shares is the number reported for the end of the last fiscal year (Comprehensive worksheet) (Continued) DES Chapter 11

  11. Target Weights (continued) Long-term Debt Value (Row 15) • if you know the actual total market value of long-term debt, enter it in the WACC worksheet • market data for long-term debt is often not available • It is ok to use the value of long-term debt shown on its financial statements as an estimate of the market value of its long-term debt (Continued) DES Chapter 11

  12. Target Weights (continued) Short-term Debt Value (Row 18) • if you know the actual total market value of short-term debt, enter it in the WACC worksheet • short-term debt often has a floating interest rate, so its market value is close to its book value • the market value of the debt is close to its maturity value because the time until maturity is relatively short. (Continued) DES Chapter 11

  13. Target Weights (continued) Preferred Stock Value (Row 21) • the market value of the preferred stock is found by multiplying the number of preferred shares by the price per share • If market value is not readily available, it is ok to accept the default value in the WACC worksheet (the book value of preferred stock as reported on the Condensed balanced sheets) (Continued) DES Chapter 11

  14. Estimating Weights in the Spreadsheet The total market value of Home Depot is: Total market value = LTD + STD + Preferred stock + Common equity = $1,321 + $0 + $0 + $71,528 = $72,849 million (next slide) DES Chapter 11

  15. WACC worksheet - Home Depot: DES Chapter 11

  16. Estimating Weights in the Spreadsheet (continued) Are current percentages reasonable as estimates of long-term targets? • Consider the firm’s debt load relative to industry averages. • Consider the circumstances (e.g., startup vs. a mature firm). • Consider firm’s own statements about its intentions. DES Chapter 11

  17. Estimating Weights in the Spreadsheet (continued) • Row 24: target percent to be financed with long-term debt. In the absence of other information, choose the current value shown in Row 23. • Row 29: target percent to be financed with preferred stock. In the absence of other information, choose the current value shown in Row 28. • Row 33: target percent to be financed with short-term debt. In the absence of other information, choose the current value shown in Row 32. (next slide) DES Chapter 11

  18. DES Chapter 11

  19. Estimating Weights in the Spreadsheet (continued) Target Weight for Common Equity • The target weights have already been estimated for the other components. • The weights must sum to 100%, so the target percentage financed with common stock is: wS = 1.0 – (wLTD + wSTD + wPS) DES Chapter 11

  20. Cost of Long-Term Debt The rate at which a company could issue new long-term debt. • rLTD = before tax cost of debt. (Continued) DES Chapter 11

  21. Cost of Long-Term Debt (Continued) Methods: • calculate the yield on existing debt using the techniques discussed in Chapter 4 (or obtain from a published source, as we describe later) • proxy using yields on other companies’ debt with similar features, including risk and maturity • interest payments are deductible for tax purposes, so the after-tax cost is: • (1-T) rLTD (Continued) DES Chapter 11

  22. Cost of Long-Term Debt (Continued) Yield on Existing Long-Term Debt • If a company has publicly-traded debt, go to: www.bondsonline.com (You will have to register, but it’s free.) • On the left side of the page is a “BondSearch/Quote Center”. • Select “Corporate Bonds” • For “Issue” enter company name. (Continued) DES Chapter 11

  23. Cost of Long-Term Debt (Continued) Here’s an example for Procter & Gamble, obtained on October 6, 2003: Source: http://www.bondsonline.com/. (Continued) DES Chapter 11

  24. Cost of Long-Term Debt (Continued) Cost of Long-Term Debt Using Similar Bonds • An alternative approach if a company doesn’t have long-term publicly traded debt is to proxy using the interest rate on a bond of similar risk. (Continued) DES Chapter 11

  25. Cost of Long-Term Debt (Continued) “Similar” Risk is Defined by Ratings • Companies pay rating agencies (Moody’s, S&P, etc.) to rate their bonds so that investors will know how risky they are. • Different agencies use slightly different codes, but the more A’s in a rating (including more capital A’s vs. lower-case A’s, and the more + signs), the better it is. (Continued) DES Chapter 11

  26. Cost of Long-Term Debt (Continued) • Add the average spread for bonds of similar risk and maturity to the T-bond yield of the desired maturity to estimate the interest rate on an identically rated bond. • Bond yield = T-bond yield + Spread. (Continued) DES Chapter 11

  27. Cost of Long-Term Debt (Continued) • WACC worksheet, Row 43: input the company’s bond rating. • Get this from www.bondsonline.com or www.moodys.com . (You may have to register, but it’s free.) (Continued) DES Chapter 11

  28. Cost of Long-Term Debt (Continued) • WACC worksheet, Row 44: input the bond spread that corresponds to the bond rating. The spread is the extra return required by bondholders above the rate on a government bond. • Start by going to www.bondsonline.com (Continued) DES Chapter 11

  29. Cost of Long-Term Debt (Continued) • At Bondsonline, look at right side, look under section called Corporate/Agency Bonds, then pick Industrial Spreads (if you have a regular company). • An example is shown (for October 6, 2003) on the next slide: (Continued) DES Chapter 11

  30. Source: http://www.bondsonline.com/. (Continued) DES Chapter 11

  31. Cost of Long-Term Debt (Continued) • Pick the spread that is shown for a long-term (10 to 30 years) bond. • The spread is shown in basis points, so be sure to enter it as a percentage • E.g.,for a Aa3 10 year bond, 67 basis points = 0.67 percentage point, or .0067. (Continued) DES Chapter 11

  32. Cost of Long-Term Debt (Continued) • Row 45: Input tax rate, based on your knowledge of company or historical rates. • U.S.: normally 34% to 39%. (next slide) DES Chapter 11

  33. WACC worksheet - Home Depot’s cost of LTD: DES Chapter 11

  34. Cost of Short-Term Debt • rSTD = before tax cost of debt. • This is the rate at which a company can borrow short-term debt (less than a year until it must be repaid). (Continued) DES Chapter 11

  35. Cost of Short-Term Debt (continued) • WACC worksheet, Row 57: input the prime rate (the rate banks charge their best customers). Go to: www.bloomberg.com/markets/rates/index.htmlThen select country. • U.S.: the prime is shown. • Other countries: the “reference rate” may be different, e.g., LIBOR, or a short-term government bond rate. (Continued) DES Chapter 11

  36. Cost of Short-Term Debt (continued) • WACC worksheet, Row 58: • Input an adjustment to the prime rate. • If your company is very strong, then enter 0%. If your company is a little risky, enter 0.5% to 1.0%. • Enter a bigger number if you company is even riskier. 2% is about the biggest number to enter, since banks won’t loan to anyone riskier. (next slide) DES Chapter 11

  37. WACC worksheet - Home Depot’s cost of STD: DES Chapter 11

  38. Cost of Preferred Stock This is the rate at which a company could issue new preferred stock. • rPs= cost of preferred stock. (Continued) DES Chapter 11

  39. Cost of Preferred Stock (continued) • WACC worksheet, Row 51: Input the yield on the company’s preferred stock. • Get a quote from finance.yahoo.com. The yield is shown.(The symbol is usually the company’s symbol, followed by a “_pa”. For example, Duke Power’s common stock symbol is DUK, its preferred (class A) is DUK_pa.) • WACC worksheet, Row 52: If yield info is not available, enter the coupon rate on outstanding preferred stock (Recall that Home Depot had no preferred stock) (next slide) DES Chapter 11

  40. WACC worksheet - Home Depot’s cost of preferred: DES Chapter 11

  41. Return Required by Stockholders: The Capital Asset Pricing Model (CAPM) rs = rRF + Beta (RPM) • rs: return required by stockholders. • rRF: the risk free rate. • Beta: measure of stock’s risk. • RPM: market risk premium (Continued) DES Chapter 11

  42. Return Required by Stockholders: CAPM (continued) The Risk-Free Rate • rRF is the yield on a long-term (10 to 30 years) government bond. (Continued) DES Chapter 11

  43. Return Required by Stockholders: CAPM (continued) Beta • Beta measures how much risk a stock contributes to a portfolio. • Beta measures the volatility of the stock relative to the volatility of the stock market. The average beta is equal to 1. • Higher beta stocks should get higher returns. (Continued) DES Chapter 11

  44. Return Required by Stockholders: CAPM (continued) The Market Risk Premium (RPM) • How much extra return above a Treasury bond do investors expect from stocks? • RPM = Expected market return over and beyond the risk-free rate. • In the U.S., the market risk premium has been around 5.0% to 6.5% during the last decade. (Continued) DES Chapter 11

  45. Return Required by Stockholders: CAPM (continued) The Market Risk Premium (cont’d.) • When the market is high, the premium is on the low end of the range. • When the market is low, the premium is on the high end. (Continued) DES Chapter 11

  46. Return Required by Stockholders: CAPM (continued) • WACC worksheet, Row 36: input the beta from the Yahoo Profile. • WACC worksheet, Row 37: input the long-term (10 to 30 year) government bond rate. Go to: www.bloomberg.com/markets/rates/index.htmlThen select country. (Continued) DES Chapter 11

  47. Return Required by Stockholders: CAPM (continued) • WACC worksheet, Row 38: input the market risk premium. This is the extra expected return that it takes to entice an investor to invest in the risky stock market instead of a long-term government bond. • U.S.: between 4.5% and 6.5%. If the stock market is currently high, pick a value closer to 4.5%. If the market is low, pick a value closer to 6.5%. (Continued) DES Chapter 11

  48. Return Required by Stockholders: CAPM (continued) WACC worksheet, Row 40 - calculation of rs for Home Depot: rs = rRF + Beta(RPM) rs = 3.32% + 1.38(5%) rs = 10.2% (next slide) DES Chapter 11

  49. WACC worksheet - Home Depot’s cost of equity: DES Chapter 11

  50. Calculating the WACC • WACC worksheet, Row 64 - Putting the Pieces Together: • WACC = wsrs • + wLTD (1 – T)rLTD • + wSTD(1 – T)rSTD • + wPSrPS DES Chapter 11

More Related