In this lesson, you’ll learn how to calculate the cash flow available for debt repayment, determine whether or not a Revolver draw is required, and calculate the mandatory debt principal repayments for each tranche of debt in the schedule.
Table of Contents:
 2:25: Step 1: Gather Historical Data
 3:41: Step 2: Calculate Cash Flow Available for Debt Repayment and Subtotal Before Revolver
 7:39: Step 3: Link in the Mandatory Debt Repayments
 13:46: Step 4: Check the Formulas
 15:43: Recap and Summary
For more tutorials on Excel & Financial Modeling Fundamentals, click here.
Transcript: More Advanced Leveraged Buyout (LBO) Model: Debt Schedule, Part 1: Mandatory Repayments
Welcome to the next lesson in our more advanced LBO modeling module. In this lesson we’re going to start going into the debt schedule, and we’ll cover the entire debt schedule over the course of the next three or four lessons, because some parts it makes sense to address independently, and it makes more sense to divide everything here into several lessons as opposed to having one very long lesson to cover everything.
So we’re going to start by looking at one of the topics here, the sources of funds to repay debt, and also the mandatory debt repayments. Then in the next lesson after this, we’ll get into the optional debt repayments, because the formulas here are a bit more complicated. Then we’ll turn our attention to the interest expense, and the interest rates on different types of debt after that.
[00:52]
And then we’ll turn our attention to linking everything on the financial statements, and to linking in the interest expense from the debt schedule to our income statement, and also filling out some of these acquisition effects such as the depreciation and amortization on writeups that we have not yet included here.
So let’s get started by turning our attention to the lesson outline here once again. Overall, this part of the process is pretty straightforward, and most of the formulas here are fairly simple. Here we have to calculate how much debt we’re required to repay each year, and figure out if we need to borrow anything extra to repay that debt. Now to do that, first we need to get some historical data on the company’s existing debt balance, if they maintain it. Then we also need to see what type of cash flow is available for debt repayment in a given year. And we need to determine what the company’s total sources of funds might be that they can use to repay debt – factoring in the cash flow and any additional borrowing.
[02:01]
Then we’ll calculate the actual mandatory debt repayments. These are what the company’s required to pay, so we’ll check those. And then at the end we will go through, and actually check the formulas and try a few different cases. It’s tough to do a comprehensive check at this stage, because we need to link more of the statements. But we can at least check to see that a few simple cases are working.
For the historical data, we already have everything we need for the actual debt repayments on new debt, such as the term loans, the senior notes, the subordinated notes, and so on. But what we do not have, or did not have before this anyway, are the repayments, and the interest expense on existing debt. If you look over here, I’ve already entered all of these numbers. These are coming from page 42 of the company’s 10K just prior to the deal. And if you look at what they’ve done here, they have the principal repayments on the revolving credit facility, as well as the interest obligations.
[03:00]
So I’ve just taken these numbers, and for the interest obligations, the $5,997 here, I’ve just used for the Year 1 payment. And then the $11,039, I’ve just divided by two and assumed that in years 2 and 3. And I’m assuming that the revolver comes due in Year 3 here, in this model. Typically for something like a revolver, since there is no mandatory repayment required on the principle, it all comes due at once at some point in the future. So I’ve already entered these numbers, and you can read my footnotes for more on those. But that’s really about it for that section. So there’s nothing too complicated in this case.
Next up, we’re going to calculate the cash flow available for debt repayment, and the subtotal before the revolver. Now for the cash flow available for debt repayment, this line item right here on the cash flow statement, this is pretty easy, because all we do is simply go up, and take our cash flow from operations, and then our cash flow from investing activities, and then our cash flow from financing activities, and the effective FX rate effects.
[04:07]
And that is the total amount of cash flow that we generate, that we can actually use for debt repayment. Now of course, this specific number is going to change over time. It’s going to change, because we haven’t factored in the acquisition effects, we haven’t factored in the new interest on debt, and a number of other things. But this is just our starting scenario for our baseline version of this model as we go through and create it right now.
So then, moving down, the next step here is to look at the sources of funds, and to figure out exactly how much we have available to repay debt with, and if we need to borrow something extra, because we currently do not have enough to use for the mandatory repayments. I’ve set up a grouping here, so that we can see everything a little bit better.
[04:56]
So, let’s think about these formulas. I’m actually also going to set up a frame here, so that you can see the year numbers a little bit better. For the beginning cash balance, for this one, we prefer to link to the balance sheet numbers, and specifically we want to link to the posttransaction number for 2014. And then for everything after this, we want to link to the relevant number on the balance sheet in the preceding year. So I’m going to contradict myself, and just undo that frame briefly, and undo the grouping, just so we can go up, and actually get this.
So let’s go to the balance sheet, and get the beginning cash number, which is really the ending cash number from right after the transaction took place, and we can copy this across. And then for the minimum cash balance over here, remember, this is a named cell all the way up at the top. So I’m going to say negative Min_Cash, and that just links up to our assumption up here, the minimum cash balance.
[06:00]
So I’ll copy this across. And then for the cash flow available for debt repayment, so for this one, we can just go up to our cash flow statement, and link it in from right there. And then we can add this up. Copy this across. You can see how right know our ending cash goes up to a very high level, because we are not yet reflecting the repayments of debt. And those of course are going to reduce our cash balance here. So we’ll complete that in the last step of the process, but for now, yes it is a very high number.
Now for the revolver borrowing required, the way to think about this is that if a company has to repay so much debt that it cannot do so with its cash flow right here, this subtotal before the revolver, then it will have to borrow something extra simply to meet these minimum required repayments.
[06:57]
To set this up, we can say max between zero and then mandatory repayment total, minus the subtotal before the revolver. And in this case it’s zero going all the way across, because we don’t have anything entered for the mandatory repayments yet. But just to show you an example of how this works, let’s say that we entered $100 here, well, now we need to borrow something on the revolver, because we clearly do not have enough cash flow to repay $100 of debt. We only have enough to repay $63.5 of debt. So let’s add up our total sources of funds, the subtotal before the revolver and then revolver borrow required, and copy this across. So we have that. And that’s really it for this step of the process.
So let’s go up, and take a look at our outline once again. We’ve calculated the cash flow available for debt repayment, and the subtotal before the revolver. And now we have to link in our mandatory debt repayments here.
[07:55]
Most of this is pretty simple and straightforward, and we’ve been over so many debt schedules by now in the course, that I’m not going to spend too much time explaining the logic here. But let’s just go down, and link in the formulas for this part.
For the existing debt, and everything else down here, the existing debt is the only one that’s going to be different really. Everything else is going to follow pretty much the same pattern, which is we want to look at the set annual repayment, and compare it to the remaining balance at this point. And if the remaining balance at this point is smaller, we just repay that entire balance; otherwise we repay the set annual amount.
So the existing debt is different, because here, we already have our set repayments all coming due in Year 3. So we have these already, we don’t have to compare anything, these have already been laid out by the terms of this debt facility. So you might think that we can just go up, and say okay we’ll link into these directly, and we’ll just copy those across.
[09:00]
But you’d be wrong, because remember in the base case scenario here, we actually repay this existing debt, we refinance it, so this goes away. One easy way to check for this condition, is to say, one minus refinanced debt, times that M460. So if you think about the way this works, if refinanced debt is set to one this entire term is going to become zero, and this entire term will also become zero. So we don’t have any existing debt to repay.
On the other hand, if refinanced debt is set to zero, so in other words we’re assuming existing debt, this entire term will become one, and it’ll just be one times this number, which of course is just this number, the $361.5 in Year 3. So I’ll just change that, and so we have that in place now.
[09:53]
Of course, we’re actually not quite done here, and you’ve probably already realized the other thing that we’ve overlooked here, which is that, yes, we have a set repayment schedule for this existing debt. But what happens if we repay some of it early, because going down to our optional debt repayments, potentially if we have enough cash flow available, we could repay some of this existing debt earlier on. So you have to factor that in as well. And so the best way to set up this formula, it’s not relevant 99.9% of the time, but the best way to set this up, is to say that this is our set annual repayment, and then to take the minimum between this, and our beginning debt balance for existing debt.
So let’s actually undo the frame here, so I can move up, and show you what this looks like. I’ll undo the grouping as well. So I’ll say the minimum between this, and let’s go up to our balance sheet. And we want to retrieve the posttransaction debt balance right here, which is zero, and in most cases it’s going to be refinanced, which is why it’s not that relevant. But to make it slightly more flexible, we think it’s a good idea to generally include something like this.
[11:05]
And I’ll just change the order of the terms slightly, so we have that. And to illustrate the problem with this very quickly, let’s say that I set this to zero. So the problem for example, is that if we somehow end up in a situation where we’ve repaid some of this existing debt, let’s say we have repaid it down to $250 by Year 2. Well, in Year 3, it would not really make sense to repay the entire $361.5. It would only make sense to repay the $250. And if we left out that term in the formula, let’s say we left out the min between that and the set annual repayment, then we’d be repaying the $361.5, which is incorrect. So that’s why we like to include that type of term.
[12:00]
Now in the base case here, we do refinance debt, so I just press Ctrl + Z a few times to undo all of that. But that just illustrates the problem, and why we like to include terms such as that one.
Now for the rest of these, it’s easier, because we don’t have to take into account the possibility of refinancing versus assuming existing debt. It’s just simply new debt that’s already there. So say the minimum, starting with the revolver between our set annual repayment on that revolver, and then the prior year’s debt balance. Let’s go up to our debt schedule to get the first part of this.
So I’m going to take the starting amount for the revolver, times the principle repayment amount. I also want to anchor the column parts of both of these, so the F part, and then the K part. The reason for that is that we do want these to move down. We want the row part to move down as we copy this down. But we do not want the column to shift over. So we always want to be linking to that in the area at the top.
[13:03]
And then for the prior year’s balance for the revolver, let’s go up, and we want to take the revolver right from here, the posttransaction column. So we have that. Again, to make the terms a little more consistent, I’m just going to move the prior year’s debt balance to the beginning. And now what we can do actually is just copy it around everything here, because of the way we’ve smartly set up the formulas. So you can see how this type of setup saves us a lot of time over the simplified treatment earlier, because now, since everything here follows directly on all the statements, and all the schedules, we can just copy and paste around formulas like this, and everything will work correctly.
Now in this case, there is not really too much to test, because we sort of have these set annual repayments. But the problem is that we’re not even linking this to our debt on the balance sheet yet. So these are simply blank in future periods.
[14:03]
We’re not yet linking it on the cash flow statements, so we’re not really saying what the impact of this is. However, what we do know is that the term loan A, there is a 10% annual repayment each year. And then for term loan B, if you go up, and look at the assumptions, there’s a 2% principal repayment. So we could go in, and do a quick check of those numbers. But in this case, we’d say that it is sort of pointless, just because too little here is actually filled out.
For the rest of these, as expected none of them actually have mandatory repayments. The revolver typically has no mandatory repayment. It’s simply if you have the cash flow available, yes, you should repay it first type of basis. One thing we could test very quickly is, to see if we correctly handled the case where revolver borrowing is required here. To show you an example, let’s go up, and say that we actually assume existing debt.
[15:01]
So let’s go up and change “refinance target company’s debt” to no. And then let’s see if that correctly handles the case down here. So it looks like the answer is yes, because look at this, subtotal before revolver is $193 in the third year. We have $383.9, because we have to repay that revolver in Year 3. So we have to borrow something on our new revolver to do that, and it seems to be working correctly, at least so far here. So that’s a bit of a check that you can do. But again, it’s hard to check too much until the schedule is completely done. But we do like to insert those types of checks along the way, just to make sure that we’re going in the right direction here.
So that’s pretty much it for this lesson. Let’s do a quick recap and summary. We started by gathering some historical data on the existing debt principle repayments and interest expense. Then we looked at the cash flow available for debt repayment and the subtotal before the revolver.
[15:59]
To do that we summed up many of the items on the cash flow statement. We factored in the minimum cash balance, and then the cash flow available for debt repayment, and our beginning cash balance. And if we don’t have enough to make the mandatory debt repayments, then we borrow extra. Then we linked in our mandatory debt repayments. And for the existing debt, we made sure to only include it if the existing debt is assumed instead of refinanced.
For the others we had a simpler formula, where just always comparing the annual repayment amount, to the beginning amount of debt, and we repay whichever one is smaller. And then finally we did a bit of a check of our formulas at the end. That’s it for this part of the process. Coming up next we’re going to look at the optional debt repayments, and see how the formula for that gets somewhat more complicated and involved. And go through that; get to the bottom of our debt schedule here. And then proceed to the interest expense, and linking everything on the rest of the financial statements.
Download Videos
Files & Resources
Excel Files
How to Download These Files:

In Firefox, Chrome, and Internet Explorer:
Right click the link and click Save Link As...

In Safari:
Right click the link and click Download Linked File As...