Wacc-template.xlsx

WACC Template Prepared by Dheeraj Vaidya, CFA, FRM [email protected]

visit - www.wallstreetmojo.com

WACC Example - Starbucks (by wallstreetmojo.com) WACC Formula = (E/V * Ke) + (D/V) * Kd * (1 – Tax rate) E = Market Value of Equity V = Total market value of equity & debt Ke = Cost of Equity D = Market Value of Debt Kd = Cost of Debt Tax Rate = Corporate Tax Rate STEP 1 – FIND THE MARKET VALUE OF EQUITY Market Value of Equity = Number of shares outstanding x current price. source: Starbucks SEC Filings

As we can see from above, the total number of outstanding shares are 1455.4 million Current Price of Starbucks (as of close of December 13, 2016) = 59.31 Market Value of Equity

86,319.8

= 1455.4 x 59.31 = $86,319.8 million

STEP 2 – FIND THE MARKET VALUE OF DEBT

As of FY2016, book value of Debt is the current portion of long-term debt ($400) + Long Term Debt ($3202.2) = $3602.2 million When we further read about Starbucks debt, we are additionally provided with the following information –

As we note from above, Starbucks provide the fair value of the Debt ($3814 million) as well as book value of debt STEP 3 – FIND THE COST OF EQUITY We use the CAPM model to find the cost of equity. Ke = Rf + (Rm – Rf) x Beta

RISK-FREE RATE Here, I have considered 10 year Treasury Rate as the Risk-free rate. Please note that some analyst also take a 5 year treasury ra

EQUITY RISK PREMIUM (RM – RF) For the United States, Equity Risk Premium is 6.25%.

BETA

Cost of Equity = Ke = Rf + (Rm – Rf) x Beta Ke

7.50%

= 2.47% + 6.25% x 0.805

Cost of Equity = 7.50% STEP 4 – FIND THE COST OF DEBT

Using the interest rate and fair value, we can find the weighted average interest rate of the  Issuance 2016 notes 2018 notes 2021 notes 2021 notes 2022 notes 2023 notes 2026 notes 2045 notes Total Debt

Fair Value

Interest Rate Interest 400 0.88% 3.5 357 2.00% 7.14 511 2.10% 10.731 255 2.10% 5.355 526 2.70% 14.202 839 3.85% 32.3015 509 2.45% 12.4705 417 4.30% 17.931 3814 Total Interest 103.631

EffectiveInterest Rate

2.72%

STEP 5 – FIND THE TAX RATE

For FY2016, Effective tax rate

32.9%

= $1,379.7 / $4,198.6 = 32.9%

STEP 6 – CALCULATE WEIGHTED AVERAGE COST OF CAPITAL (WACC) OF STARBUCKS We have collected all the information that is needed to calculate Weighted Average Cost of Capital. Market Value of Equity = $86,319.8 million Market Value of Debt (Fair Value of Debt) = $3814 million Cost of Equity = 7.50% Cost of Debt = 2.72% Tax rate = 32.9% WACC Formula = E/V * Ke + D/V * Kd * (1 – Tax Rate)

WACC

7.26%

Weighted Average Cost of Capital formula = (86,319.8/90133.8) x 7.50% + (3814/90133.8) x 2.72% x (1-0.329)

o.com)

Debt ($3202.2) = $3602.2 million.

nformation –

book value of debt

lyst also take a 5 year treasury rate as the risk-free rate. Please check with your research analyst before taking a call on this.

age interest rate of the total fair value of Debt ($3,814 million)

72% x (1-0.329)

ing a call on this.