WACC compution using EXCEL formulas only
transformers
WACCforAmazon.com.xlsxAmazon.com computation
| Estimating cost of equity using DGM | |||||
| Estimating Dividend Growth | |||||
| Year | Dividend (from Yahoo) | Growth | 1+g | ||
| Year 1 | |||||
| Year 2 | |||||
| Year 3 | |||||
| Year 4 | Solution Legend | ||||
| Year 5 | Value given or to be downloaded from data website | ||||
| Formula/Calculation/Analysis required using EXCEL FORMULAS only!!! | |||||
| Average | |||||
| Current Price | |||||
| Expected Dividend | |||||
| Growth Estimate | |||||
| Cost of equity | |||||
| Estimating cost of equity using CAPM (SML) | |||||
| Item | Source | Value | |||
| Risk free rate | Treasury Yield Curves | 2.28 | |||
| MRP | Damodaran | 4 | |||
| beta | Yahoo | 1.31 | |||
| ri= rf + βi * (RMkt-rf) | |||||
| ri = 0.0228 + 1.31 * ( 0.04) | |||||
| ri = 0.0752 | |||||
| ri = 7.52% | |||||
| Cost of equity | |||||
| Cost of equity used in WACC can be one of the values calculated, average, or some other number, subjectively adjusted | |||||
| Cost of equity used | |||||
| Estimating Cost of Debt | |||||
| Most recently issued bond data, or bond with longest maturity | |||||
| Source | FINRA-Morningstar | ||||
| Maturity | 30 years | ||||
| Coupon rate | 4.95% | ||||
| Face Value | $100 | ||||
| Price | $98.24 | ||||
| Payments | Semi-annual | ||||
| Number of payments | |||||
| PV | |||||
| Future Value | |||||
| Per period rate | |||||
| Cost of debt (annual) | For estimating the cost of debt, you do not need to calculate the PV. The PV should be the current price and based on that, you needed to calculate the cost of debt. The formula for that is: =rate(Nper, pmt,-PV, FV). | ||||
| Item | Source | Value | |||
| Value of Debt | SEC | 24.047 B | |||
| Market Cap | Yahoo | 481.792 B | |||
| Tax rate | IRS | 35% | You may assume 35% | ||
| WACC |
Amazon.com answer
| Calculated WACC |
| Assets are generally financed by debt and equity. In order to calculate the cost of capital we need to thus consider the weighted average of the cost of both debt and equity. The weighted average cost of capital is calculated using the formula below. |
| Where: |
| E= is the market capitalization (market value of equity) |
| D = is the value of debt |
| rE = is the cost of equity |
| rD = cost of debt |
| Tc = is the corporate tax rate |
| 1. Weights |
| To calculate the weights of debt and equity the market value of equity ( number of shares * price per share) and the current book value of debt was used. |
| i. Weight of equity = E/ (E + D) = |
| ii. Weight of debt = D/ (E + D) = |
| N/B values are in billions of dollars |
| 2. Cost of debt |
| The yield to maturity approach was used to calculate the cost of debt. The yield to maturity rate for a 30 year bond was used as the pretax cost of debt and the adjusted for after tax by multiplying by (1 – T). |
| After tax cost of debt = |
| 3. Cost of equity |
| Amazon does not issue dividends thus the Capital Asset Pricing Model was considered an ideal method of arriving at the cost of equity. The model uses the formula: |
| ri= rf + βi * (RMkt-rf) |
| Where: |
| ri = cost of equity |
| rf = risk free rate |
| βi = Beta |
| (RMkt-rf) = Risk premium |
| ri = |
| 4. WACC |
| WACC = Write a report of at least 3 paragraphs that contains your calculated WACC information. |
| Confidence in the answer and assumptions |
| A discussion of how much confidence you have in your answer. What were the limiting assumptions that you made, if any. |
| Reference: |
| 1. https://finance.yahoo.com/quote/AMZN?p=AMZN |
| 2. https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield |
| 3. http://finra-markets.morningstar.com/BondCenter/Results.jsp |
| https://www.sec.gov/cgibin/viewer?action=view&cik=1018724&accession_number=0001018724-17-000100&xbrl_type=v# |
(
)
C
D
E
T
r
D
E
D
r
D
E
E
WACC
-
+
+
+
=
1
