table of contents
Calculating WACC in Excel used to be quite a difficult task. There is confusion among entrepreneurs, analysts and even professional investors about how they should be calculated. But after reading this article you will find it very easy. This article explains the importance of WACC definition in finance and how to calculate WACC step by step. An example of using the WACC formula in Excel to calculate the WACC is also provided. And finally the interpretation of the WACC method. let's see
What is WAC?
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.
Formula to calculate the WACC
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.
What are the components of the WACC?
The WACC formula essentially consists of 4 elements. Since there are two main sources from which a company can raise capital, the formula includes equity and debt. For the WACC calculation, the example says that the value and cost of debt are added to the value and cost of equity. Before proceeding with the calculation of WACC in Excel, let's familiarize ourselves with the components of WACC. let's see
Equity (We and Ke)
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
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.
Debt (Wd & Kd)
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
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
How to calculate the WACC?
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
- 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.
- 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.
- 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.
- 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.
- Calcular WACC: Finally, substitute the values of We, Ke, Wd, Kd in the WACC formula and you will get the WACC value.
WACC Calculation Example in Excel
This example shows the WACC calculation method in Excel. The company selected for the calculation isApple Inc🇧🇷 Calculating the WACC is very useful because that way you canCalculate the final valueUse ofDCF rating🇧🇷 The data comes frominvestment professor🇧🇷 Below is the WACC calculation with an example in Excel. let's see
Passo 1: Calcular We & Wd
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.
Step 2: Determining the cost of own resources (Ke)
To calculate Ke for the weighted average cost of capital, you need to find the risk-free rate of return, market premium, and beta values. You can easily find the first two values on Google. While for beta, the function [=GOOGLEFINANCE(“NASDAQ:AAPL”,”beta”)] is used. Then use the formula for Ke and you will get the value 0.08378.
Step 3 - Find Cost of Borrowing (Kd) to WACC in Excel
To calculate the cost of borrowing (Kd) for WACC, you need to find the interest rate (IR) and Eff. Tax rate. On slides 2 and 3 the calculation of IR and ETR is given. And that's what happenedThe value of Kd is 0.02101768189.
Step 4: Calculate the WACC in Excel
Finally, paste all the values in this Excel format and use the formula to calculate the WACC in Excel. Then theThe WACC value is 0.0812445208 (8.12%).🇧🇷 If you wish, you can convert it to percentage form.
Importance of the WACC method
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.
The WACC is an important indicator used in making investment decisions. It is the average cost of capital for the company and its competitors. Companies calculate the weighted average cost of capital and compare it to competitors in the same industry. Borrowing costs are a crucial element in calculating the WACC. Therefore, this article describes the correct method to calculate the WACC using an Excel example. You can also use it to compare the cost of capital for two different companies. As a result, the WACC is used more as a forecasting tool than a fixed method for making decisions.
WACC and IRR are different concepts. WACC is the average rate of return you must pay to acquire capital. While the IRR is the rate of return that companies receive for their investments/projects. If IRR > WACC, the company should definitely go ahead with the project.
Why is the WACC used to discount cash flows when valuing a company?
Well, the WACC turns out to be an important element of the DCF model. We try to discount the cash flows to get the firm's value and the stock's value. When a company acquires capital from different sources (stocks, debt), the cost of capital varies. If we start calculating the value of the company using different costs of capital, the process can become very tedious. Therefore, we take the weighted average of all origination costs (equity, debt).