WACC Formula in Excel: Weighted Average Cost of Capital

The WACC formula in Excel combines the cost of a company's equity and the cost of its debt into one rate, weighted by how much of each the company uses. WACC stands for weighted average cost of capital. It is the average return a company must pay the people who fund it. This guide builds it step by step on example figures, checked in Python, and is for finance students, analysts and anyone facing a finance Excel test. It explains the calculation, not what any real company's WACC should be.

What is the WACC formula?#

WACC weights each source of funding by its share of the total and adds up the weighted costs:

WACC = E/V × Re + D/V × Rd × (1 − T)

With these figures: 0.6 × 11% + 0.4 × 6% × 0.75 = 6.6% + 1.8% = 8.4%.

WACC is simply a weighted average: the weights are the shares of funding, and the values being averaged are the costs.

Step 1: Lay out the inputs#

Put each source of funding on its own row, with the tax rate in a separate cell:

Type the costs with the % sign. If you type 11 instead of 11%, Excel stores eleven, not 0.11, and the WACC comes out 100 times too big.

Step 2: Calculate the weights#

Each weight is the source's value divided by the total. In C2:

=B2/SUM($B$2:$B$3)

Fill it down to C3 (Windows: Ctrl+D; Mac: ⌘D). The results are 60% and 40%, and they must add up to 100%.

The $ signs make $B$2:$B$3 an absolute reference, so every row divides by the same total. Without them, C3 would divide by SUM(B3:B4) and show 100%. Press F4 on Windows, or ⌘T on a Mac, to add the dollar signs as you type. The absolute references lesson practises exactly this pattern.

Step 3: Work out the after-tax cost of debt#

Interest is a deductible expense in many tax systems, so debt costs less after tax. In E3:

=D3*(1-$B$5)

The result is 6% × 0.75 = 4.5%. Lock $B$5 so the tax rate stays fixed if you copy the formula. Equity gets no tax adjustment, so E2 is just =D2, or 11%.

Step 4: Combine everything with SUMPRODUCT#

SUMPRODUCT multiplies matching cells in two ranges and adds the results. With the weights in column C:

=SUMPRODUCT(C2:C3,E2:E3)

This calculates 60% × 11% + 40% × 4.5% and returns 0.084, or 8.4% formatted as a percentage.

You can also skip the weight column and divide by the total in the same formula:

=SUMPRODUCT(B2:B3,E2:E3)/SUM(B2:B3)

Same result, 8.4%. The SUMPRODUCT function guide and the article on how SUMPRODUCT works explain the multiply-then-add idea in more detail.

How to estimate the cost of equity with CAPM#

The cost of equity is the return shareholders expect. A common way to estimate it is the capital asset pricing model (CAPM):

Cost of equity = risk-free rate + beta × equity risk premium

Put the three inputs in their own cells, for example H2 (risk-free rate), H3 (beta) and H4 (equity risk premium), and calculate:

=H2+H3*H4

With an example risk-free rate of 4%, a beta of 1.4 and an equity risk premium of 5%, the result is 4% + 1.4 × 5% = 11%. That is where the 11% in the example comes from. Each input is an estimate, and analysts often disagree about them, which is why WACC is usually shown as a range rather than one exact number.

The cost of debt is usually the interest rate the company would pay on new borrowing today, not the rate on an old loan.

Adding preferred stock or more sources#

SUMPRODUCT makes it easy to add rows. Say the company also has 50,000 of preferred stock that costs 8%, and its debt is 350,000:

Extend the ranges to row 4: =SUMPRODUCT(B2:B4,E2:E4)/SUM(B2:B4). The result is 6.6% + 0.4% + 1.575% = 8.575%, about 8.58%. Preferred dividends are usually not tax-deductible, so its cost has no tax adjustment.

Common WACC mistakes in Excel#

These are the errors that most often give a wrong WACC:

1. Using the pre-tax cost of debt. Multiply by (1 − tax rate) first.
2. Weights that don't add up to 100%. Usually a missing $ in the total, so each row divides by a different sum.
3. Book values instead of market values. Use market values where you can; the balance sheet shows historical amounts.
4. Percentages typed as whole numbers. 11 is not 11%.
5. Mixing yearly and monthly rates. WACC is a yearly rate, so discount yearly cash flows with it.

How WACC is used: NPV and IRR#

WACC is often used as the discount rate for a project's future cash flows. Plug it into the NPV function, for example =NPV(8.4%,C2:C6)+C1, where C1 holds the upfront cost as a negative number. A positive NPV means the project earns more than its funding costs. You can also compare it with the IRR of the cash flows: an IRR above the WACC points the same way. Both decisions need judgement beyond the spreadsheet; the numbers here are examples only.

WACC questions are common in finance interviews and tests, along with NPV, IRR and loan payments. Excel skills for finance jobs lists the others, and the finance Excel mock test lets you try them under time pressure.

Practise it#

Try the exercise on this page: one SUMPRODUCT gives the WACC from values and after-tax costs. Then practise absolute references in a live spreadsheet so your weight formulas fill down correctly, and build the department budget practice sheet to get fast at percentages and totals.