How to Calculate WACC in Excel from a 10-K (Step by Step)

WACC is the discount rate in a DCF. It is also the input people copy from a website without checking, which is a problem because a one point change in WACC can move the valuation by more than 15%.

The good news is that almost every input comes from two places. The company's 10-K gives you the capital structure, the interest expense, and the tax rate. The market gives you the share price, the risk-free rate, and beta. This guide walks through one fictional company from filing to finished formula.

The formula

` WACC = E/V × Ke + D/V × Kd × (1 − t) `

Interest is tax deductible, so debt is cheaper than its coupon suggests. That is why only the debt term gets the (1 − t).

Step 1: Pull the inputs from the 10-K

Here is where each number lives in a typical US annual report.

| Input | Where to find it | Example value | |---|---|---| | Shares outstanding | Cover page of the 10-K, or the latest 10-Q cover | 100M | | Total debt | Debt footnote: long-term debt plus current portion | $600M | | Interest expense | Income statement, or the debt footnote | $36M | | Marginal tax rate | Income tax footnote: the rate reconciliation starts at the 21% federal rate, then adds state taxes | 25% | | Fair value of debt | Fair value footnote (usually close to book) | $600M |

One note on shares. The cover page gives the basic count. For equity value you want the diluted count, which adds options and RSUs already granted (options by the treasury stock method). Say the stock comp footnote shows 4M options with a $20 strike and 2M RSUs. At $30 the options add 4 − 4 × 20 / 30 = 1.33M shares, so diluted shares are 103.33M and equity is $3,100M. In this example that moves WACC only from 10.125% to 10.155%, so the rest of the guide keeps 100M to keep the arithmetic clean. The diluted count matters far more later, when you divide equity value by shares to get a price.

Use the marginal rate, not the effective rate at the bottom of the same table. A one-off settlement or a tax credit can push the effective rate far from what the next dollar of interest will actually save.

Two notes on debt. First, use the fair value if the footnote gives one and it differs from book. Second, decide whether operating lease liabilities count as debt. Under ASC 842 they sit on the balance sheet. Either choice is defensible. What matters is doing the same thing in WACC and in the enterprise value bridge.

The market inputs come from outside the filing.

| Input | Source | Example value | |---|---|---| | Share price | Any quote page | $30.00 | | Risk-free rate | 10-year US Treasury yield | 4.25% | | Equity risk premium | Damodaran's published estimate, or your own | 5.0% | | Beta | Regression, or relevered comps (Step 3) | 1.20 | | Size premium | Optional, for small caps | 1.0% |

Step 2: Lay out the Inputs tab

Put every input in its own cell and every calculation in a formula. That way you can change one number and watch the rate move.

| Cell | Label | Entry | |---|---|---| | B3 | Risk-free rate | 4.25% | | B4 | Beta | 1.20 | | B5 | Equity risk premium | 5.00% | | B6 | Size premium | 1.00% | | B7 | Cost of equity | =B3+B4B5+B6 | | B9 | Pre-tax cost of debt | =36/600 | | B10 | Tax rate | 25% | | B11 | After-tax cost of debt | =B9(1-B10) | | B13 | Share price | 30.00 | | B14 | Shares outstanding (M) | 100 | | B15 | Equity value | =B13B14 | | B16 | Debt | 600 | | B17 | Equity weight | =B15/(B15+B16) | | B18 | Debt weight | =1-B17 | | B20 | WACC | =B17B7+B18*B11 |

Step 3: Cost of equity

The model is CAPM with an optional size premium.

` Ke = Rf + β × ERP + size premium Ke = 4.25% + 1.20 × 5.0% + 1.0% = 11.25% `

Beta is the input that deserves the most care. A raw two-year regression on one stock is noisy. The more stable method is to take the median unlevered beta of comparable companies and relever it to your company's capital structure.

` βL = βU × (1 + (1 − t) × D/E) `

Our company has D/E of 600 / 3,000 = 0.20. Say the comps have a median unlevered beta of 1.04. Then:

` βL = 1.04 × (1 + 0.75 × 0.20) = 1.04 × 1.15 = 1.196 `

That rounds to 1.20, which is what the example uses. In Excel with the unlevered beta in B22:

`excel =B22(1+(1-B10)B16/B15) `

Step 4: Cost of debt

There are three ways to estimate Kd, from best to worst.

  1. Yield to maturity on the company's traded bonds. This is what lenders demand today.
  2. A rating-based spread. Take the company's credit rating, look up the typical spread for that rating, and add it to the risk-free rate.
  3. Interest expense divided by average debt. This is what the company pays on old debt, which can be far from today's rate.

The example uses the third method because it is the one you can always compute from the 10-K: $36M / $600M = 6.0%. Debt sat at $600M all year here, so year-end and average debt are the same. If debt changed during the year, divide by the average of the opening and closing balances. If the company issued most of its debt when rates were low, this number will be too low. Check it against method 1 or 2 when you can.

After tax:

` Kd after tax = 6.0% × (1 − 25%) = 4.50% `

Step 5: Weights and the finished rate

` E = $30.00 × 100M = $3,000M D = $600M V = $3,600M

E/V = 83.33% D/V = 16.67%

WACC = 83.33% × 11.25% + 16.67% × 4.50% = 9.375% + 0.750% = 10.125% `

Two sanity checks every WACC should pass. It sits below the cost of equity and above the after-tax cost of debt. And the cost of equity sits above the pre-tax cost of debt, because shareholders are paid after lenders. Here 4.50% < 10.125% < 11.25% and 11.25% > 6.0%, so it passes.

The mistake that moves the answer: book weights

The most common WACC error is using book equity from the balance sheet for E. Book equity is an accounting residual. It has nothing to do with what investors would pay for the shares today.

Say the balance sheet shows $1,200M of shareholders' equity. With book weights:

` E/V = 1,200 / 1,800 = 66.67% D/V = 600 / 1,800 = 33.33%

WACC = 66.67% × 11.25% + 33.33% × 4.50% = 7.50% + 1.50% = 9.00% `

That is 1.125 points lower. Here is what it does to a Gordon growth terminal value with year 5 free cash flow of $120M and 2.5% growth.

| Weights | WACC | Terminal value | |---|---|---| | Market | 10.125% | 120 × 1.025 / 0.07625 = $1,613M | | Book | 9.00% | 120 × 1.025 / 0.065 = $1,892M |

The book weight version is 17.3% higher, and nothing about the business changed. Use market value for equity every time.

How sensitive is WACC to each input

It helps to know which inputs are worth arguing about. With the weights above:

| Change | Effect on Ke | Effect on WACC | |---|---|---| | Beta +0.10 | +0.50 pts | +0.42 pts | | Equity risk premium +1.0 pt | +1.20 pts | +1.00 pt | | Risk-free rate +0.50 pt | +0.50 pts | +0.42 pts | | Pre-tax cost of debt +1.0 pt | none | +0.13 pts | | Drop the size premium | −1.00 pt | −0.83 pts |

Each row holds everything else fixed. In practice a higher risk-free rate also lifts the cost of debt, so the real move is a little larger.

Equity inputs dominate because equity is 83% of the capital structure.

The size premium row deserves a second look. A $3,000M company is mid-cap, and many analysts only add a size premium well below that. Dropping it takes WACC to 9.29% and lifts the same terminal value from $1,613M to $1,811M, a 12.3% jump. Whatever you pick, write down why, because it is a judgment call that moves the answer almost as much as the book weight mistake. For a company with little debt, time spent refining the cost of debt is mostly wasted. Time spent on beta and the equity risk premium is not.

A short checklist

---

Built with AI assistance and formula-checked. For education and planning only, not financial advice.

If you want this built already, the DCF Valuation Model has the full WACC build on its Inputs tab, including CAPM with a size premium, after-tax cost of debt, and market value weights, and a README that shows where to find each input in a 10-K. One known issue is open in that model's exit multiple terminal value (the WACC build is not affected), and the product page explains it and the one-cell fix.