How To Calculate The WACC Using Excel – Step By Step Guide

Table of Contents

Introduction

Earlier, calculating WACC in excel was kind of a tough job. There is confusion among business owners, analysts, and even professional investors on how to calculate it. But, after reading this article, you will find it pretty easy. This article will explain the meaning of Wacc definition in finance and how to calculate WACC step-by-step. Also, for calculating WACC, an example is also given on how to use the WACC formula in excel. And lastly, interpretation of the WACC method. Let’s check it out.

What Is WACC?

An Image showing the WACC
Source : Screenshot by IIFPIA Editor

Weighted Average Cost of Capital (WACC) is the average rate at which a company has to repay the sources for the money it provided. In finance, Wacc is of great importance.

For example, assume you want capital to operate your business. You decide to borrow money from different sources like investors, banks, shareholders, etc. Now that you have borrowed capital/ money from different sources, there is a cost (Cost of debt, cost of equity) involved in it.

The purpose of WACC is to find an average rate for repaying the security holders. The WACC importance helps us to find out whether we should use equity or Debt for acquiring money. Besides that, WACC forms an important element in the DCF model. Analysts calculate the weighted average cost of capital after calculating free cash flows. 

Formula To Calculate WACC

Now that you know about what WACC stands for? let’s look at the formula for calculating WACC in excel. For calculating WACC in excel, different formulas and functions get used. The WACC formula in excel is as follows,

WACC = (We x Ke) + (Wd x Kd)

Here, We – Working equity (Total Equity)
           Ke – Cost of equity
          Wd – Value of debt (Long term debt)
           Kd – Cost of Debt

For calculating the WACC in excel, you need to calculate all these items separately in an excel sheet and then club them together. You can learn calculating WACC in the example later on in this article.

What Are The Components Of WACC

The WACC formula is made up of basically 4 elements. Since there are two main sources from where a company can get capital, the formula contains equity and debt. For calculating WACC, example says that value and cost of equity get added to value and cost of debt. Before actually proceeding to the calculation of WACC in excel, let’s understand the components of WACC. Let’s check it out.

Equity (We & Ke)

WE are also known as Weight of equity. In simple words, it is the weight or worth of equity. Whereas KE is the Cost of equity. It means when a share gets issued, the company doesn’t pay anything. It is free for the company. Also, the price of shares keeps on moving up and down. But, the shareholders expect a certain rate of return from the company. Thus, this rate of return is the cost of equity or Ke. The cost of equity is thus the total return that a company must generate in order to maintain a share price that is acceptable to its investors. Now that you know about Ke & Kd, let’s look at the formula for calculating WE and KE for WACC in excel.

The formula for calculating WE is;
We = Market value of equity or market cap
                            Total capital

The formula for calculating KE is;
Ke = [ Risk free return + Market premium] x Beta

Here, Risk-free return is the rate of return of an investment with zero risks. Although, this is not possible yet U.S treasury bills are risk-free. Whereas, the Market risk premium is the difference between the expected return on a market portfolio and the risk-free rate. Beta is a measure of how an individual asset moves on average when the overall stock market increases or decreases. Every company has a different Beta.

Debt (Wd & Kd)

Wd is the “Weight of debt”. That’s the total amount of the long-term debt of the company. So while calculating the Wd, you need to take the value of the long-term debt of the company. Whereas Kd is the “Cost of debt” to the company. Unlike Wd, the calculation of Kd involves some steps. Below, are the formulas of Wd and Kd. Let’s check it out.

The formula for Wd:
Wd = Long term debt
             Total capital

The formula for Kd:
Kd = Interest rate x (1 – Eff. Tax rate)

Where, Interest rate = Interest expense/ Long term debt
              Eff. tax rate = Tax expense/ Income before tax (IBT)

You can easily find all the data in the balance sheet and income statement of the company. For more references, you can visit Investing guru site. There, you will easily find the IBT of any company.

How To Calculate The WACC?

Now that you know about the different variables used in WACC, let’s look at how to calculate the WACC. For compute the WACC in excel, you need to follow 4 easy steps. Let’s check it out.

  1. Calculate We: For calculating We, you need to first find the values of Market cap and Total capital. You can easily find the Market cap from google. Whereas Total capital is the Sum of market cap and long-term debt. For We, Divide Market Cap by Total Capital. Insert the values described above and you will find the value of We.
  2. Find Wd: The next step includes, finding the value of Wd. For Wd, “Divide Long-term debt by Total capital”. Insert the values in the formula and you’ll get the value of Wd.
  3. Calculate Ke: For calculating Ke for WACC, you need to first “Find Risk-free return, market premium, and Beta”. You can find the values of all three from Google (If it is still confusing, check the next section of this article). Insert the values in the Ke formula and there is the value of Ke.
  4. Find Kd: In order to calculate the cost of debt for WACC, you must have an Interest rate (IR) and Eff. tax rate (ETR). You can easily find IR, just divide the Interest Expense by Long term debt. Whereas for ETR, you need to divide tax expense by Income before tax. Insert the values in the Kd formula and you’ll get the value of Kd.
  5. Calculate WACC: Lastly, put the values of We, Ke, Wd, Kd in the WACC formula and you’ll get the value of WACC.

Example Of Calculating WACC In Excel

This example shows the method for calculating WACC in excel. The company chosen for calculation is Apple Inc. Calculating WACC is very useful because you can then calculate the terminal value using the DCF valuation. The data is taken from Investing guru. Below is the calculation of WACC with example in excel. Let’s check it out.

Step 1: Calculate We & Wd

An Image showing how to calculate WE & Wd using Wacc formula in excel
Source : Screenshot by IIFPIA Editor

For calculating We, you need to have Equity (market cap) and the long-term debt of the company. Long-term debt can be easily found in the balance sheet of the company. Whereas for Market cap, use the Excel function in the image above.

The value Of We comes to be 0.9636176 approx. To calculate Wd, you need to divide Debt by Total capital. Total capital is the sum of both equity and debt. Therefore, the value of Wd is 0.036832 approx.

Step 2: Finding Cost of Equity (Ke)

An Image showing how to calculate Ke using WACC formula in excel
Source : Screenshot by IIFPIA Editor

To calculate Ke for a Weighted average cost of capital, you need to find the values of Risk-free return, market premium, and beta. You can easily find the first two values from Google. Whereas for Beta use the function [=GOOGLEFINANCE(“NASDAQ:AAPL”,”beta”)]. Then, use the formula for Ke, and you’ll get the value of 0.08378. 

Step 3: Finding Cost Of Debt (Kd) For WACC In Excel

In order to calculate the cost of debt (Kd) for WACC, you need to find the Interest rate (IR) and Eff. tax rate. In Slides 2 & 3, the calculation of IR and ETR is given. Thus, the Value of Kd comes out to be 0.02101768189.

Step 4: Calculating WACC In Excel

An Image showing how to calculate WACC with an example in Excel
Source : Screenshot by IIFPIA Editor

Finally, insert all the values in this excel format and use the formula for calculating WACC in excel. So, the Value Of WACC is 0.0812445208 (8.12%). If you want you can convert it into percentage form. 

Importance of WACC Method

WACC is very useful for investors. It also helps the investors to analyze the benefits if they invest or acquire another company. For example, If the company is calculating WACC in excel and finds that the WACC is lower, it is a good sign. It indicates the company is able to attract investors at a much lower cost. Similarly, if the company has a higher WACC it tells that the company is riskier and it is not wise to invest in such a company.

But, in reality, a company has to collect capital from different sources. No one source will give you all your money. Similarly goes for equity also. Therefore, you need to have a balance of both equity and debt. Thus, you need to calculate the cost of debt and equity for WACC. You need to have a balance of both. This will ensure the proper allocation of capital.

Conclusion

The WACC is an important number that is used in capital investment decisions. It is the average cost of capital for the company and its competitors. The companies calculate the weighted average cost of capital and compare it with competitors in the same industry. The cost of debt is a very crucial element for calculating WACC. Thus, this article discusses the proper method for calculating the WACC with an example in excel. You can also use it to compare the cost of capital for two different companies. As result, WACC is used as a prediction tool rather than a fixed method for decision making.

FAQs

WACC and IRR are both different concepts. WACC is the average rate of return you need to pay back for acquiring capital. While, IRR is the rate of return company gets on their investment/projects. If the IRR> WACC then, the company should definitely go ahead with the project. 

Well, WACC proves to be an important element of DCF model. We try to discount the cash flows so that we arrive to the company’s value and vale of stock. A company when it acquires capital from different sources(Equity, Debt), there is different cost of capital for each. If we start calculating company’s value through different cost of capital, the process might become too lengthy. Therefore, we take the weighted average of all the cost of the sources (Equity, Debt).