Overview

 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.

Reviews Add a review
No reviews yet

More From Big4WallStreet

Browse our top rated business templates. See All
Dynamic Arrays in Excel
187
4
Dynamic Arrays (“DA”) allow you to work with multiple values at the same time. Dynamic Arrays are only available in…
Self Storage Financial Model (Buy Upgrade Operate Exit)
341
0
Considering investing in a self-storage facility? This might be an easier way for entry-level real estate investors to get into…
Self Storage Financial Model (Construction, Operations & Exit)
551
1
 Use our Financial Model to plan the Self-Storage construction, and subsequent rental operations for several years before exiting your investment.…
Fast Food Chain Financial Model
572
0
The financial model presents the business case of a startup fast-food chain. The model accommodates up to 20 outlets with…
Start Up Nursery School Financial Model
659
0
 Start Up Nursery School Financial Model presents the case of an investment in a nursery school and its operations. The…
Time Value of Money, Bond & Stock Valuation
746
1
 Time Value of Money, Bond and Stock Valuation Excel Model template presents various financial tools which allow you to discount…
See All