1. Debt Instruments for our Deal

Overview

Dealer Partners plans on a number of different debt types in the proposed deal. In this lesson, we explore the terms and characteristics associated with each type

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:04)

    The goal of this lesson is to learn about the different types of debt that will be used in the transaction.

  2. Understanding the Debt Instruments (01:09)

    In our deal, there are five debt instruments that will be used. For each debt instrument, our model stores the percentage of total debt that will be financed by that instrument, and the value of that debt. 

    The first type of debt is the term loan. The term loan is a low risk loan typically provided by a bank. It allows early repayments, and a percentage of the principal must be repaid each year. The interest rate is linked to LIBOR.

    Second is senior notes. These do not allow early repayment and do not include principal repayments during the loan. As a result, these senior notes have a higher interest rate than the term loan, and include a LIBOR floor, which is a minimum value for LIBOR in the interest rate calculation.

    Third and fourth are subordinated notes and mezzanine. These are both high-risk forms of lending, and carry high interest rates as a result.

    The final debt instrument is the revolver. This instrument is optional, so our model includes a switch cell with a value of 0 or 1 that allows us to decide if the revolver will be used in the loan. To calculate the percentage of debt represented by the revolver, we divide the revolver amount by the total amount of debt in the deal.

  3. Calculating Amounts for Each Instrument (02:54)

    The revolver is optional, so we calculate the amount used in the revolver first. We do this by multiplying the amount of the revolver by our switch cell. This returns the revolver amount if the revolver is used, and zero otherwise.

    To calculate the amount of debt for the other instruments, we subtract the amount used in the revolver from the total debt, then multiply this figure by the percentage of debt accounted for by the relevant instrument.

  4. Understanding the Revolver in Detail (04:05)

    A revolver is similar to a bank overdraft. The company has access to these funds but is not obliged to draw them down. The revolver usually has a low interest rate with a LIBOR floor. If the revolver is not used, the bank charges an “undrawn fee” for making the cash available.

Transcript

In Advanced Leveraged Buyouts Part One, we built a three-statement projection for the company ToolCo, a publicly listed company that the private equity firm Dealer Partners wishes to buy. When we built the full three-statement projection, we completed this on the basis that the transaction had not happened and assuming that the business continued to operate as normal over the next five years. With this phase of the model built, it's now time to include the impact of the transaction on our financial statement projections before we perform our analysis on the attractiveness of that investment, in the second half of this course. That analysis will include profitability analysis, liquidity analysis, and needless to say we perform valuation as well.

By the end of this course, we'll provide Dealer Partners with a comprehensive review of the proposed deal and a decision on whether they should proceed with the transaction. However, let's now return to the model and begin by creating a debt schedule for the transaction. Dealer Partners have informed us that they intend to use up to five different debt instruments as part of the deal. The first of these is the term loan which will contribute 30% of the total. A term loan is typically provided by a bank and represents a lower risk loan on such a deal and this is due to the fact that the principal is typically paid back over the life of the loan, in this case, 10% each year. It's also possible to pay the term loan back early if you wish and there is an interest rate of LIBOR plus 3%. Next up are senior notes which cannot be paid back early and the principal is not paid back during the life of the loan. As a consequence, the senior notes have a higher interest rate of LIBOR plus 4% and a LIBOR floor so that LIBOR can never drop below 1% on this interest rate calculation. Subordinated notes and mezzanine have even more risk and as a result, their interest rates are higher at 9% and 11%.

On the top row, you'll see we have an option for a revolver and we have two assumptions based on the revolver. One is the revolver amount which is 150 million and the second is whether the revolver is used or not and in this cell I'm using the trick of having zero representing no and one representing yes, so this cell can be used in my formulas. To calculate the total percentage of the debt required that the revolver represents, I'll simply take the revolver amount and divide by the total amount of debt.

And now I'll run my calculation, and so, the revolver is simply equal to wherever the revolver is used multiplied by the revolver amount.

Because the revolver is optional, I'm going to adjust the size of the term loan, senior notes, subordinated notes and mezzanine loan based on whether the revolver is used or not.

So, for the term loan, it's going to be the total percentage multiplied by the total debt anchored minus the amount used in the revolver.

And I'll copy down for the remaining cells.

And now I'll sum to get the total amount of debt.

If I decide to use the revolver, then the amount of debt will stay the same and as you can see, the other loan amounts decrease accordingly. I'll now press control Z to return.

The revolver is a little bit like a bank overdraft. If the company needs access to these funds, it can draw them down but it's not under an obligation to do so. The revolver is normally achieved at quite a good interest rate although in this case there's a LIBOR floor included. However, you don't use the revolver, you do need to pay a fee because the bank needs to be compensated for keeping this money available. As a base case, we won't actually use the revolver at the beginning but we may use it in our scenario analysis later on. It's worth remembering that these loan terms are dependent on the debt and equity ratio that Dealer Partners achieves for the deal. Currently, we are assuming 70/30 which can be seen in the transaction funding section. If we adjust this to 50/50 it's likely that the debt holders will give us a better deal because the risk of default is less. With this section of my debt assumptions now completed, we'll create a Sources and Uses table in the next lesson.