What is the formula for calculating weighted average cost of capital (WACC) in Excel?

The weighted average cost of capital (WACC) is a financial metric that shows what the total cost of capital (the interest rate paid on funds used for financing operations) is for a firm.  

All companies need to finance operations, and this funding comes from two sources: debt or equity.  Each source has a cost associated with it.  When analyzing different financing options, whether through debt, equity, or a combination of both, calculating the WACC provides the company with its financing cost. Whatever the WACC rate ends up being, it is then used to discount the project or business in a valuation model.

The WACC takes into account both debt and equity sources of capital and the proportion of total capital each source represents. The weights are simply the ratios of debt and equity to the total amount of capital. As an equation, it would be expressed as:

WACC = wD*rD *(1-t) + wP*rP + wE*rE

where:

• w = the respective weight of debt, preferred stock/equity, and equity in the total capital structure
• t = tax rate
• D = cost of debt
• P = cost of preferred stock/equity
• E = cost of equity

For debt capital, the cost is either the actual interest rate of the bonds, or the interest rate of comparable debt for a similar business. You reduce the cost of debt by (1 - tax rate) because interest payments on debt are tax-deductible, and this tax break lowers the debt's effective cost. 

For equity funds, the cost of capital is more complicated because there is no stated interest rate. For preferred stock, you can calculate the cost as the dividend rate of the shares. Using the Capital Asset Pricing Model (CAPM), you can estimate the cost of equity.

In terms of capital cost, the scale from cheapest to most expensive runs: debt, preferred equity and finally equity.

Calculating WACC is easy. As with most financial modeling, the most challenging part is getting the correct data to plug into the model.

Illustrated below is an example of the data needed to estimate a company's WACC. The after-tax cost of debt is found by looking for debt disclosures in the company filings; the costs should be stated there. The cost of equity is calculated with CAPM, as mentioned above. Total capital is calculated by adding the debt to the market value of the equity.