How to Calculate WACC in Excel, Step by Step Guide

Calculate WACC in Excel

Weighted Average Cost of Capital – WACC Definition

A cost of capital must need to pay against corporate financing recognized weighted average cost of capital. Hence, WACC or Weighted Average Cost of Capital is a sum of all capital sources comprising debt, bonds, equity stocks, and preferred stocks.

Furthermore, it is sum of cost of debt, cost of equity, and preferred stock values of corporation. There are two main sources of raising capital equity and debt.

Cost of Equity (Ke)

The cost of equity is a calculation of common stock to measure the proportion of equity shareholders’ investment in a project. Moreover, the cost of equity is the required rate of return on equity investment in the project.

Cost of Debt (Kd)

The cost of debt is the required rate of return on debt stock investment by the lenders. 

What Weighted Average Cost of Capital Formula

Firstly and most essentially, we need to understand the theoretical formula of WACC which is calculated as follows:

WACC Formula
WACC Formula

Where;

E = Equity market value

D = Debt market value

Re = cost of equity

Rd = cost of debt

t = corporate taxation rate

E / (E+D)= Weightage of equity value

D / (E+D)= Weightage of debt value

How to Calculate WACC in Excel

Now, It will show you how to extract WACC value by using an Excel-based Calculator. For example, LuluLemon has stock value comprised of both debt and equity capital. Thus, the total debt value is $1,000,000 acquired at a rate of 7% and $2,500,000 as equity value at the rate of 4%, while corporate tax rate is 25%.

WACC Calculation in Excel Format

Let’s, divide the above solution into three simple steps to understand it more precise and effectively.

Step 1

So, first is to put all the desire data into the following given format such as total debt value, cost of debt (%), tax rate (%), total equity value, and cost of equity (%).

WACC Format

Step 2

Now, we need to calculate the total value of capital (debt+equity), and weightage of both equity and debt values. Hence, the following image is showing calculation of weightage of both stocks.

WACC Weightage calculation

Step 3

In the last and most importantly, we need to use the WACC formula excel as shown in the following image.

WACC Formula in Excel Sheet
=((E14*E22)+(E8*E20)*(1-E10))

Where,

E14 = Cost of Equity (%)

E22 = Weightage of Equity

E8 = Cost of Debt (%)

E20 = Weightage of Debt

E10 = Tax Rate (%)

WACC Calculation Excel Template

Also, you can download the template of WACC calculator in Excel sheet by clicking on the button or use calculator online.

Add a Comment

Your email address will not be published. Required fields are marked *