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.
Similar Products
Other customers were also interested in...
Dynamic Arrays Financial Model
Dynamic Arrays Financial Model generates the three financial statements (profit & loss, balance ... Read more
Mergers and Acquisition (M&A) Financial Model
Merger and Acquisition Model template consists of an excel model which assists the user to assess th... Read more
Leveraged Buyout (LBO) Model
Leveraged Buy Out (LBO) Model presents the business case of the purchase of a company by using a hig... Read more
Operating Company Financial Model
Operating Company Model consists of an Excel model which allows the user to generate forecast financ... Read more
Capital Budgeting Model Excel Template
Capital Budgeting consists of a process that companies use for decision making on investment project... Read more
Sales Commissions Dashboard
This model will allow you to plan your commission structure and monitor the performance of your sale... Read more
Options and Real Options Model Template
Options and Real Options model consists of an Excel model which presents a valuation framework calcu... Read more
Inventory Dashboard Model Template
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Dashboard Actuals PnL Model
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Cash Budget Model
Cash Budget Excel Model generates a simple cash forecast based on a series of assumptions, and compa... Read more
You must log in to submit a review.