How to Calculate WACC in Excel

The weighted average cost of capital is the blended return a company must earn to satisfy all its capital providers. It weights the cost of equity and the after tax cost of debt by their share of the capital structure. WACC is the discount rate for unlevered free cash flow in a DCF, so getting it right directly drives your valuation.

The WACC formula and its parts

The formula is WACC = E/V * Re + D/V * Rd * (1 - Tax), where E is equity value, D is debt, V is E + D, Re is the cost of equity, Rd is the cost of debt, and Tax is the marginal tax rate.

The cost of equity comes from the capital asset pricing model: Re = Rf + Beta * (Rm - Rf). Rf is the risk free rate, Beta measures the stock's sensitivity to the market, and Rm - Rf is the equity risk premium. The after tax cost of debt is Rd * (1 - Tax) because interest is tax deductible.

Worked example

Assume a risk free rate of 4 percent, a beta of 1.2, an equity risk premium of 5 percent, a pretax cost of debt of 6 percent, a 25 percent tax rate, equity value of 800, and debt of 200.

1. Cost of equity: =0.04+1.2*0.05 returns 10 percent.
2. After tax cost of debt: =0.06*(1-0.25) returns 4.5 percent.
3. Total capital: =800+200 equals 1000, so equity weight is =800/1000 (0.8) and debt weight is =200/1000 (0.2).
4. WACC: =0.8*0.10+0.2*0.045 returns 8.9 percent.

Contributions sum to the WACC: 8.0% + 0.9% = 8.9%.

Setting it up cleanly in Excel

Build WACC as a small block of labeled inputs feeding a single output cell so it can be audited and flexed.

• Use market values for the weights, not book values; market capitalization for equity and market or face value for debt.
• Put Rf, beta, the risk premium, Rd, and the tax rate in separate blue input cells.
• Compute the cost of equity and after tax cost of debt in their own cells before combining them.
• Reference the equity and debt weights from a capital structure block so a change flows through automatically.
• Avoid typing any rate directly into the WACC formula; pull every number from a labeled cell.

Pitfalls

Using book equity instead of market equity is a common error. Book values understate equity for profitable companies, which overweights debt and understates WACC.

Another trap is hardcoding a rate, such as typing 0.04 for the risk free rate inside the cost of equity formula. When the curve moves you forget to update it. Keep every assumption in its own input cell so a single change recalculates the whole stack.