Learn about a series of capital structure possibilities in Excel (e.g., 10% equity, 20%, equity, 30%, etc.).
- [Instructor] Let's drill down a little further on the issue of WACC by looking at how WACC varies with different debt equity mixes. It's important to understand that debt is expensive. In particular, one of the major factors driving debt ratings are coverage ratios. The coverage ratio is a mathematical concept. It's simply equal to earnings before interest and taxes divided by the interest cost on our debt. We often abbreviate Earnings Before Interest and Taxes as simply EBIT.
The idea is that we have higher levels of debt, well then that leads to greater interest expenses and as a result, we have a lower coverage ratio and subsequently lower debt ratings. Let's see how this plays out in an Excel example. I'm in the 03_03_Begin Excel file and we'd like to understand how changing debt structures will influence our overall WACC. Now, what we see here is that we have a relatively different level of debt depending on what our credit ratings are.
A firm with a Triple A credit rating is using very little debt. A firm with a Single B credit rating is using a lot of debt. That Single B firm, 75% of their capital comes from debt. In particular to be more precise, 75.1% in this example. The Double A firm is using 35.9% of their capital in the form of debt. This Triple A company is only using 5% of their capital in the form of debt. So notice that while Triple A firms have a very cheap cost of debt, they're not actually using much in the way of debt.
That's why their cost is so low. This goes with that old adage about if you need money, a bank won't give you the loan. If you don't need money, the bank is happy to give you the loan, right? Sort of the same way with the bond markets. When you need the money, bond investors are gonna charge you a much higher interest rate than if you don't need the money. Now, if we go through and look at the level of debt that the firm is using based on their coverage ratio, that is EBIT divided by interest rates, what we find is that Triple A firm is using 9.5% of their capital in the form of debt.
90.5% is in equity. Well, we can now go through and we can calculate our WACC based on the book value of debt, that's row 13, and based on the market value of debt, that's row 14. So we can compute our WACC based on market weights and book weights. When we're computing based on a book weight, we'll be using row 13. When we're computing using market weights, we'll be using row 14.
In both cases, we'll be using row 15 for our equity levels. So our WACC is gonna be based on the level of debt being used. In this case, row 14 our 9.5% debt is the level of debt that we're using. Our after-tax cost of debt is what we're gonna multiply by that 9.5% so it's C14 times C7 and that gives us the contribution of debt to our overall WACC.
Similarly, we're gonna multiply C15, which is our equity weight, by the cost of equity which is row three, in this case C3. When we get done, our WACC for equity is 9.67. I'm gonna make this easier to read by changing this to two decimal places instead of 16. To do that, I'm gonna hit Control and then the number one and I'm gonna change my percentage value from 16 decimal places to two.
We now see that based on our market weights of capital, our WACC is 9.67%. We can make a similar calculation using the book value. Here our level of debt that we're using is 5% of capital, that's cell C13, and we're gonna multiply that by our cost of debt, C7, and that is the contribution to our WACC from debt. We're gonna make a similar calculation using one minus C13 to represent our equity weights multiplied by the cost of equity C8.
When we do that, we get 9.94%. So notice, we get a slightly different WACC depending on whether we use the market value or the book value for our capital. I can now drag these cells across to compute my WACC for each of the following debt levels and notice that my cells have unformatted themselves so I'm going to reformat them to be two decimal places by holding down Control + One and then changing those decimal places to two.
What do we observe when we look at this? Well, notice that before when we used a simple 50-50 mix, our cost of debt, our WACC, went down consistently. Now once we understand that a Triple A company uses relatively little in the way of debt, what we see is that lower-level firms can actually have a lower cost of capital than a higher-rated firm.
These lower-grade firms are taking a little bit of risk by having more debt, but their cost of capital will be lower than a more highly-rated firm. The key then is to understand that picking the correct capital structure, picking the correct debt equity mix is a very important decision that the firm needs to make. How much risk do you wanna have in the form of debt versus what do you want your cost of capital to be? As you should now see, determining optimal WACC is a little more complicated than you might have thought at first.
While debt is typically cheaper than equity, it gets expensive as you use more and more of it.
- Capital budgeting in Excel
- Using Excel in project selection
- Analyzing capital structure using Excel
- Estimating weighted average cost of capital (WACC)
- Applying Excel to operational budgeting
- Cash flow budgeting
- Stress test tools for budgets