How to Calculate WACC in Excel [Step-by-Step]

When it comes to making informed financial decisions, understanding the Weighted Average Cost of Capital (WACC) is crucial. WACC is a key metric that helps businesses evaluate the cost of their capital structure. In this article, we will guide you through the process of calculating WACC using Excel, ensuring a clear and concise explanation.

Table of Contents

What is WACC?

WACC stands for Weighted Average Cost of Capital. It is a financial metric that represents the average rate of return a company is expected to pay to its investors (both debt and equity) for using their capital to fund the company’s operations and projects. WACC takes into account the cost of both debt and equity, and it is often used as a discount rate in financial valuation and investment analysis.

The WACC formula is expressed as follows:

WACC = (E/V*Ke)+(D/V×(1−T)×Kd)

Where,

E is the market value of equity.
V is the total market value of the firm’s equity and debt.

Ke is the cost of equity.
D is the market value of debt.
T is the corporate tax rate.

Kd is the cost of debt.

In simpler terms, WACC is a blended rate that considers the proportion of debt and equity in a company’s capital structure. It reflects the cost of raising capital from both debt and equity sources. You can use it as a discount rate in discounted cash flow (DCF) analysis and other financial valuation models to evaluate the feasibility of investment projects or acquisitions. A lower WACC generally indicates a lower cost of capital, which may enhance the attractiveness of investment opportunities.

Steps to Calculate WACC in Excel

Delve into the realm of financial analysis as we guide you through the intricate process of calculating the Weighted Average Cost of Capital (WACC) in Excel. This comprehensive procedure breaks down the complexity into user-friendly steps, ensuring a seamless journey through the critical components of WACC determination.

Step 1: Prepare Dataset

I have prepared a dataset to calculate Wacc. This contains financial information for four companies—ABC Inc., XYZ Corp., LMN Ltd., and PQR Co.—including details such as dividends per share, market price per share, growth rate, interest expense, average debt outstanding, corporate tax rate, outstanding shares, short-term debt, long-term debt, and the market price of debt instruments.

Step 2: Calculate the Cost of Equity (Ke)

To calculate the Cost of Equity (Ke), you can use the Gordon Growth Model (Dividend Discount Model). The formula for the Cost of Equity is as follows:

 Cost of Equity, Ke =(Dividends per Share/Market Price per Share) + Growth Rate

To apply this formula follow the steps below:

Insert a new column to store the value.
Write this formula: =B2/C2+D2

After writing the formula, use the fill handle to drag the formula down for the rest of the dataset.

Excel will automatically adjust the cell references for each row. Now, the inserted column will contain the calculated Cost of Equity (Ke) for each company in your dataset.

Step 3: Calculate the Cost of Debt (Kd)

The Cost of Debt (Kd) is the interest rate a company pays on its debt. To calculate Kd in the context of the Weighted Average Cost of Capital (WACC), you can use the following formula:

Kd = Interest Expense/Average Debt Outstanding

Here are the steps to calculate Kd in Excel:

Insert a new column to store the calculated Cost of Debt (Kd).

Write the Formula:=E2/F2
This formula calculates the Cost of Debt (Kd) using the Interest Expense (E2) and Average Debt Outstanding (F2).
After typing the formula in the selected cell, use the Fill Handle to drag the formula down for the rest of the dataset. Excel will automatically adjust the cell references for each row.

Now, you can see the column containing the calculated Cost of Debt (Kd) for each company in your dataset.

Step 4: Find the Market Value of Equity (E)

The market value of equity (E) can be calculated by multiplying the outstanding shares by the market price per share. The formula for the market value of equity is:

E=Outstanding Shares*Market Price per Share

To calculate the Market Value of Equity (E), you can follow these step-by-step procedures in Excel based on the provided dataset:

Insert a new column to store the calculated Market Value of Equity (E).
Type the formula: =H2 * C2
This formula multiplies the Outstanding Shares (H2) by the Market Price per Share (C2) to calculate the Market Value of Equity for each company.
After writing the formula in the selected cell, use the Fill Handle to drag the formula down for the rest of the dataset.

Excel will automatically adjust the cell references for each row. The calculated Market Value of Equity (E) for each company is stored in the inserted column.

Step 5: Find the Market Value of Debt (D)

To find the Market Value of Debt (D) for the Weighted Average Cost of Capital (WACC) calculation in Excel, you can use the following formula:

Market Value of Debt, D=(Cost of Debt*(Outstanding Shares*(1−Corporate Tax Rate)+Short-term Debt+Long-term Debt)*(1−Market Price of Debt Instruments)

Now, go through the steps to find the market value of debt (D):

Insert a new column to store the calculated Market Value of Debt (D).
Write the formula: =C8*(H2*(1-G2)+I2+J2)*(1-K2)
This formula calculates the Market Value of Debt for each company based on the given parameters. It involves the cost of debt (Kd), corporate tax rate (T), outstanding shares (H2), short-term debt (I2), long-term debt (J2), and the market price of debt instruments (K2).

Use the Fill Handle to drag the formula down for the rest of the dataset. Excel will automatically adjust the cell references for each row.

The resulting values will represent the Market Value of Debt for each respective company.

Step 6: Calculate the Total Value of the Firm (V)

The Total Value of the Firm (V) in the context of WACC is the sum of the Market Value of Equity (E) and the Market Value of Debt (D). The formula for calculating the Total Value of the Firm, V is:

Total Value of the Firm, V =Market Value of Equity+  Market Value of Debt

Here’s a step-by-step procedure to calculate the Total Value of the Firm (V) in Excel based on the provided dataset:

Insert a new column to store the calculated Total Value of the Firm (V).

Write the formula in the selected cell: =E+D
This formula adds the Market Value of Equity (E) to the Market Value of Debt (D) to calculate the Total Value of the Firm (V).
Use the Fill Handle to drag the formula down for the rest of the dataset.

Excel will automatically adjust the cell references for each row. The calculated Total Value of the Firm ( V) for each company in the dataset represents the combined market value of both equity (E) and debt (D), providing a crucial component for the Weighted Average Cost of Capital (WACC) calculation.

Step 7: Calculate WACC

The Weighted Average Cost of Capital (WACC) is calculated using the following formula:

WACC= Market Value of Equity/Total Value of the Firm*Cost of Equity+Market Value of Debt/ Total Value of the Firm*(1-Corporate Tax Rate)*Cost of Debt

or,

WACC= E/V*Ke+ D/V*(1−T)*Kd

Where,

E is the Market Value of Equity.
V is the Total Value of the Firm (E+D).

Ke is the Cost of Equity.
D is the Market Value of Debt.
T is the Corporate Tax Rate.

Kd is the Cost of Debt.

In Excel, you can use the following steps to calculate WACC based on the provided dataset:

Insert a new column to store the calculated WACC.

Type the formula: =D8/F8*B8+E8/F8*(1-G2)*C8
This formula calculates the WACC using the Market Value of Equity (E), Market Value of Debt (D), Cost of Equity (Ke), Corporate Tax Rate (T), and Cost of Debt (Kd).
After writing the formula in the selected cell, use the Fill Handle to drag the formula down for the rest of the dataset. Excel will automatically adjust the cell references for each row.

Upon completing these steps, the inserted column contains the calculated Weighted Average Cost of Capital (WACC) for each company in the dataset. This comprehensive metric, derived from the Market Value of Equity (E), Market Value of Debt (D), Cost of Equity (Ke), Corporate Tax Rate (T), and Cost of Debt (Kd), offers valuable insights into the overall cost of capital for effective financial decision-making.

Conclusion

In conclusion, mastering the calculation of WACC in Excel empowers you to assess the cost of capital effectively. This step-by-step guide provides you with the formulas and explanations you need to navigate the complexities of financial analysis. Incorporate WACC into your decision-making process, and you’ll be well-equipped to make informed and strategic choices for your business.

Frequently Asked Questions

Is WACC calculated in percentage?

Yes, the Weighted Average Cost of Capital (WACC) is typically expressed as a percentage. It represents the weighted average of a company’s cost of equity and cost of debt, taking into account the proportions of equity and debt in the company’s capital structure.

What is a good WACC for a company?

A good Weighted Average Cost of Capital (WACC) for a company depends on various factors such as its industry, risk profile, and specific circumstances. Generally, a lower WACC indicates a lower cost of capital and may be considered favorable. However, there is no universally “good” WACC as it varies across industries and company situations.

What is the formula for the cost of equity in Excel?

To calculate the Cost of Equity in Excel, you can use the Gordon Growth Model formula:

Cost of Equity (Ke) = (Dividends per Share / Market Price per Share) + Growth Rate

Simply insert a new column, write the formula, and drag it down for comprehensive calculations.

Rate this post