How to Calculate WACC Using Excel – Step by Step Guide (2023)

The weighted average cost of capital (WACC) is the average rate at which a company must return sources of cash provided. In the financial sector, the Wacc has great importance.

For example, let's say you want capital to run your business. They decide to borrow money from various sources like investors, banks, shareholders, etc. Now that you've borrowed capital/money from various sources, there are costs (cost of debt, cost of equity) associated with it.

The purpose of the WACC is to determine an average interest rate for repayment to bondholders. The importance of the WACC helps us determine whether to use equity or debt to acquire money. In addition, the WACC is an important element inDCF model.analystsCalculate the weighted average cost of capital after calculating free cash flows.

Now do you know what WACC means? Let's see the formula to calculate the WACC in Excel. Several formulas and functions are used to calculate the WACC in Excel. The WACC formula in Excel is as follows:

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

Here, We - Work Equity (Total Equity)
Ke - Cost of own resources
Wd – value of debt (long-term debt)
Kd - Cost of Debt

To calculate WACC in Excel, you need to calculate all these items separately in an Excel spreadsheet and then add them together. You can learn WACC calculation by example later in this article.

WE are also known asCapital Weight🇧🇷 In simple terms, it is weight or value.capital social🇧🇷 While KE is itcost of capital🇧🇷 This means that when a share is issued, the company pays nothing. It's free for the company. Also, the stock price is constantly going up and down. But shareholders expect a certain return from the company. So that rate of return is the cost of equity, or Ke. Therefore, the cost of capital is the total return a company must generate to maintain an acceptable share price for its investors. Now that you know Ke and Kd, let's see the formula to calculate WE and KE for WACC in Excel.

The formula for calculating WE is;
we =Stock market value or market capitalization
capital total

The formula for calculating KE is:
Ke = [risk free return + market premium] x beta

Here,Risk-free returnsis the risk-free return on investment. While this is not possible, US Treasury bills are risk free. while themarket risk premiumIt is the difference between the expected return on a market portfolio and the risk-free rate.Beta It is a measure of how an individual asset moves on average when the general stock market rises or falls. Each company has a different beta version.

What is it?Indebtedness"🇧🇷 This is the total amount of the company's long-term debt. Therefore, when calculating Wd, one must consider the value of the firm's long-term debt. While Kd o "cost of debt”For the company Unlike Wd, calculating Kd involves a few steps. The formulas for Wd and Kd are shown below. let's see

The formula for Wd:
wd =long-term liabilities
capital total

The formula for Kd:
Kd = Interest Rate x (1 - Effective Tax Rate)

Where, Interest Rate = Interest Expense/Long-Term Debt
Cash Tax Rate = Tax Expenses/Pre-Tax Income (IBT)

You can easily find all the data on the company's balance sheet and profit and loss account. Visit the Investing Guru website for more references. You can easily find them there.IBT any company

Now that you know the different variables used in the WACC, let's see how the WACC is calculated. To calculate WACC in Excel, you need to follow 4 simple steps. let's see

1. Calculations: To calculate We, you must first find the market capitalization and total capital values. You can easily find the market capitalization through Google. While total capital is the sum of market capitalization and long-term debt. for us,Divide market capitalization by total capital🇧🇷 Enter the values ​​described above and you will find the Nodes value.
2. find wd: The next step is to find the Wd value. For Wd, "Divide long-term debt by total equity."🇧🇷 Plug the values ​​into the formula and you get the Wd value.
3. Calculate: To calculate Ke for WACC, you must first "Find risk free return, market premium and beta.🇧🇷 You can find the values ​​for all three on Google (if you're still confused, see the next section of this article). Insert the values ​​into the Ke formula and you will get the Ke value.
4. find Kd: To calculate the cost of borrowing for WACC, you need an interest rate (IR) and Eff. Tax rate (ETR). You can find the IR easily, just divide the interest expense by the long-term debt. Whereas for ETR you have to divide tax expense by pre-tax income. Insert the values ​​into the Kd formula and you will get the Kd value.
5. Calcular WACC: Finally, substitute the values ​​of We, Ke, Wd, Kd in the WACC formula and you will get the WACC value.

To calculate We, you need the company's equity (market capitalization) and long-term debt. Long-term debt is easily found on the company's balance sheet. For market capitalization, use the Excel function in the image above.

The value of us arises0,9636176 ca🇧🇷 To calculate Wd, you must divide debt by total equity. Total equity is the sum of equity and debt. Hence the value of Wd0,036832 ca.

WACC is very useful for investors. It also helps investors analyze the benefits of investing in or acquiring another company. For example, if the company calculates the WACC in Excel and finds that the WACC is lower, that is a good sign. This shows that the company manages to attract investors at a much lower cost. Likewise, if the company has a higher WACC, it means that the company is riskier and it is not advisable to invest in that company.

But in reality, a business needs to raise capital from multiple sources. No source will give you all your money. The same applies to equity. Therefore, you must have a balance between equity and debt. So you need to calculate the cost of debt and equity for the WACC. You have to have a balance of both. This ensures the correct allocation of capital.