How to Calculate WACC in Excel (with Easy Steps)

Overview

The Formula

E = Equity Value of the Company

V = Total Value of Debt and Equity of a Company.

D = Total Debt of a Company.

Tc = Tax Rate.

Re = Cost of Equity.

Rd = Cost of Debt.

We can also present it as:

Weightage is the ratio of Equity and Debt concerning the summation of Equity and Debt.

Components of WACC

WACC has four essential parameters or components. Without any of them, calculations are impossible.

Market Valuation of Equity

The market value of the Equity is the total price of outstanding shares of a particular company.

Cost of Debt

This is the price that the company must pay for Debt (bonds or loans). The Cost of Debt is a very good indicator of a company’s risk factor. Riskier companies have a higher Cost of Debt compared to other companies.

This is calculated by the following formula:

Cost of Debt = Interest rate x (1 – Tax rate)

Market Valuation of Debt

Estimation of a company’s total Debt is troublesome as debt is rarely public. It can be calculated from the listed bond price or from the bank statements.

Cost of Equity

Cost of Equity is the rate of return of company issued stocks or shares as expected by the shareholder.

When a share is issued, the company doesn’t pay any money for the stock. Instead, it sells a small chunk of the company share, and the share is bought by the shareholders.
As the performance of the company fluctuates, so do the stock prices.
This is the price the company must pay in the long run in order to generate investment. This cost is described as the Cost of Equity. It is presented as the formula below:

Cost of Equity = Risk Free Rate + Beta * (Market Return Rate – Risk Free Rate)

Step-by-Step Procedure to Calculate WACC in Excel

Step 1: Prepare the Dataset

To calculate the WACC, we need to calculate some parameters first. Components are Cost of Equity, Equity Evaluation, Cost of Debt, Debt Valuation, etc.

Cost of Equity, for example, requires information like the Rate of Risk-Free, Beta, and Market return, while the cost of Debt requires information like Rate, Tax Rate, and Credit Spread.

Equity and Debt requirements vary wildly from company to company. Equity represents the total amount of money that the company would have to return if it decided to liquidate assets. The calculation may involve shares of different types, retained earnings, etc. In this case, we presented only the share quantity and the price per share.

Step 2: Estimate Cost of Equity

Select the relevant cell (C8 in this example) and enter the following formula:

=C5+C6*(C7-C5)

Press Enter.

Step 3: Calculate Market Valuation of Equity

Select the relevant cell (F7) and enter the following formula:

=F5*F6

Press Enter.

Step 4: Estimate Cost of Debt

Select the relevant cell (C14) and enter the following formula:

=(C11+C13)*(1-C12)

Press Enter.

Step 5: Calculate the Market Valuation of Debt

Select the relevant cell (F13) and enter the following formula:

=F11*F12

Press Enter.

Step 6: Estimate Gross Capital

Select the relevant cell (F15) and enter the following formula:

=F7+F13

Press Enter.

Step 7: Calculate WACC (Weighted Average Cost of Capital)

Select the relevant cell (F17) and enter the following formula:

=C8*(F7/F15)+C14*(F13/F15)*(1-C12)

Press Enter.

Step 8: Interpret Outcome

In the example shown above, the WACC is 31.42%. While we didn’t add it, suppose the expected return is 15%. That means the business is losing money at a 16.42% rate (31.42%-15%). That makes this venture more volatile for investment.
On the other hand, if the expected return was 35%, the business is generating wealth at a rate of 3.58% (35%-31.42%). This investment is safer.

Things to Remember

The calculations seem pretty straightforward when all the numbers are entered into a worksheet, but the reality is that determining parameters like Equity and Debt is difficult.
WACC also assumes that the investment in the company, or the capital, will remain the same throughout the year, but this isn’t actually possible in most cases.

Download Practice Workbook

Download this practice workbook below.

Calculate WACC.xlsx

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!