What's the Formula for Calculating WACC in Excel?

The weighted average cost of capital (WACC) is a financial metric that shows what the total cost of capital is for a firm. Rather than being dictated by a company's management, WACC is determined by external market participants and signals the minimum return that a corporation would take in on an existing asset base. Companies that don't demonstrate an inviting WACC number may lose their funding sources who are likely to deploy their capital elsewhere.

All companies must finance their operations, and this funding either comes from debt, equity, or a combination of the two. Each source has a certain cost associated with it. When analyzing different financing options, calculating the WACC provides the company with its financing cost which is then used to discount the project or business in a valuation model.

WACC Formula

WACC is calculated with the following equation:

WACC: (% Proportion of Equity * Cost of Equity) + (% Proportion of Debt * Cost of Debt * (1 - Tax Rate))

The proportion of equity and proportion of debt are found by dividing the total assets of a company by each respective account. Since all assets are financed via equity or debt, total equity plus total liabilities should equal 100%. This assumes any operating liabilities like accounts payable are excluded.

The cost of equity is the return an investor demands for their holding of shares of the company. This if often distributed as a dividend to ownership from the profits of a company. The cost of debt is the prevailing interest rate charged by a lender. As the company incurs more debt, the rate charged by the lender will likely increase as the company's risk profile will also increase. There is a tax shield impact of interest charged on debt, therefore the cost of debt is reduced by potential tax benefits.

Calculating WACC in Excel

Calculating WACC is a relatively straightforward exercise. As with most financial modeling, the most challenging aspect is obtaining the correct data with which to plug into the model.

1. Obtain appropriate financial information of the company you want to calculate the WACC for. You will need the company's balance sheet, and you need to transfer the company's equity and the company's debt into Excel. You will also need public share information as well as prior year annual report information.

2. Determine the debt-to-equity proportion. Divide each category by the sum of the two categories. To find the equity proportion, divide the total equity by the sum of total equity and total det. Alternatively, divide total debt by the sum of total equity and total debt to find the debt proportion.

3. Determine the cost of equity. The cost of equity is found by dividing the company's dividends per share by the current market value of stock. Then, if applicable, add the growth rate of dividends.

4. Multiply the equity proportion (Step 2) by the cost of equity (Step 3). This it the company's proportional cost of equity.

5. Determine the cost of debt. This is the prevailing interest rate required by lenders in exchange for issuing loans. The interest rate will vary from company to company due to varying levels of risk.

6. Determine the company's tax rate. This can be estimated by looking at prior-year annual reports or identifying the most current tax tables for companies.

7. Multiply the proportion of debt (Step 2) by the cost of debt (Step 5). Then, multiply this product by (1 - tax rate). This is the company's proportional cost of debt.

8. Add Step 4 and Step 7. This is the company-wide weighted cost of capital.

WACC Example

The following illustration exemplifies the data needed to estimate a company's WACC:

Again, much of this information is sourced from external reporting. Account balances are found on the company's balance sheet. The after-tax cost of debt may be sourced from the debt disclosures contained in a company's filings. After setting up your Excel workbook, you can easily calculate future WACC figures by revising any input variable.