• Originally published: 12/07/2019 09:02
Last version published: 19/07/2019 07:26
Publication number: ELQ-87982-2
View all versions & Certificate

# Weighted Average Cost of Capital (WACC) Excel Model

Weighted Average Cost of Capital (or WACC) is an excel tool that calculates the discount rate of a company.

Description
Weighted Average Cost of Capital (or WACC) is an excel tool that calculates the discount rate of a company, which effectively is the weighted mix of the cost of debt and the cost of equity of a company.

The color coding of the model is the usual one. Inputs from the user are always depicted with a yellow fill, and calculations are depicted with white fill and black characters, finally restricted cells are in light grey fill.

So a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet. You have to enable macros in order to be able to get updated flags when selecting country.

We start with the tab "Main Inputs" where we have to select the country (the map and flag will be updated automatically), this input feeds the tax rate and the Moody's rating in column C. Then you have to choose the cost of debt method (Method 1, 2 or 3) (this will be explained later in the video). Afterwards you have to choose the company's sector, which will feed the unlevered beta. The Debt to Equity is fed directly from the "Leverage" tab, The relevered beta is calculated from the values above that is the unlevered beta, the taxes and the debt to equity.

Take note that the following values tax, country risk premium, cost of debt, unlevered beta, debt to equity and relevered beta can be overriden, that is the value entered in column H in the yellow cell will replace the current calculations based on column C.

In the cost of debt tab, you will be able to choose among three different methods: the current interest rate of the company, the interest rate based on the interest coverage ratio, and the interest rate based on the risk free rate, country risk premium and corporate bond spread. You will also have two options for the risk free rate, namely the 10 Year German Bonds and the 10 Year US T. Bonds.

In the cost of equity tab, the risk free rate, beta, and country risk premium are linked with the main inputs tab. The equity market risk premium has to be selected by the user in column D and as well as the various additional premiums (marketability, size, minority shareholder, owner management reliance).

In the leverage tab, the user needs to fill the target debt percentage over the total capital.

The last five tabs don't need any amendments on a consistent basis only when changes occur. The only amendments needed are in the BETA tab, the sector unlevered BETA when it changes, in the tabs "Country Risk Premium" & "Coverage Ratio" the regression coefficients, in the Moody's tab the country ratings and finally the corporate income tax per country in case it is different.

The way to proceed with the worksheet is to fill the yellow tabs first starting from leverage, then cost of debt, cost of equity and finally the main inputs. In the main inputs you can override any values in column H. Finally you will be able to see the resulting Weighted Average Cost of Capital in the "WACC" tab.

This Best Practice includes
1 Excel and 2 PDFs

Discuss

See all

## Any questions on Weighted Average Cost Of Capital (WACC) Excel Model?

The user community and author are here to help. Go ahead!