|All Industries, General Excel Financial Models|
|DCF Model, Discount Rate, Excel, NPV (Net Present Value), WACC (Weighted Average Cost of Capital)|
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 a 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 overridden, 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.