LBO Model Debt Schedule – Mandatory Repayments

In this lesson, we’ll calculate the annual mandatory repayment for each tranche of debt, and show you how to draw on the revolver when Yahoo’s cash flow is not sufficient to repay its required debt obligations.

Debt Schedules – Mandatory Repayments Transcript

Okay, now we are going to move into the next part of our debt schedules here, which will help us to figure out what the mandatory repayments on each tranche of debt are, and how much of Yahoo’s cash flow it’s going to be devoting, to actually paying off the principal of each tranche of debt, here.

So, to start with this formula is going to be very similar to some of the other one’s we’ve seen elsewhere. And, the way we calculate this is going to be quite similar to what we’ve seen, for example, in the advanced merger model; scrolling over to the right here; under the acquisition effects on the income statement, specifically (i.e. those sections on annual amortization for intangible asset write-up, for instance).

And the basics are going to be the same, which is to figure out the amortization in each year we simply look at what has been amortized, so far. And then the remaining balance, we either take the remaining balance if that’s lower than the specified amount, or we go with the specified amount.


So, if we’re supposed to amortize 20% each year, and we’ve already amortized 90%, then this year we’ll only amortize 10%, because that’s what makes sense (i.e. it is the lesser of the two). We don’t want to be amortizing 20%, because then we pay off more than the original balance of the loan which will result in a negative debt balance on our balance sheet because we ‘over-amortized’.

Likewise, if we’ve amortized 20% each year, now we have 80% amortized. Then the final year we simply amortize 20%. So, it’s a pretty simple concept. The formula can get a little messy, so I will spend some time going into the details on that, but that’s the basic idea for what we want to do, here.

Now, for the initial year for each of these the concept is pretty simple. We simply want to take the yearly amortization percentage, right here. We’re going to skip the Term Loan A recap, because that doesn’t apply here, and then just paste this all the way down.

And this is just handling the initial case, because if you think about it, even if we have 100% amortization in one year, as long as we have this here, and as long as we have proper checks afterward, it’s fine to just link this in directly. It just means that the entire loan has been paid off. Now, for the formula, so this is the part that it gets a little tricky.


So, we’re going to start with an IF statement, and the first thing we want to check is whether or not our debt has matured in this particular year. So, the maturity for Term Loan A is 2012, so I am going to link to that, and anchor the column, right here. Then I am going to go up to the top and link to the year, and anchor the row in that case. So, we have that. And so if this is true, what we want to do is simply take one minus the sum of everything that has been amortized, so far.

And if you think about this formula for a minute, this works even if for example we’re in a situation where 80% has been amortized and we only have 20% left. Well, if our debt has come to maturity then we need to pay off the remaining balance, no matter how much has been amortized, so far.

So, this works regardless of which case we’re in, and we’re insuring that we never go below 0.0% for our annual debt amortization or anything weird like that here, because we’re subtracting the total so far from 100%, basically. So, that’s an explanation of why this case is the way it is.


I’m going to anchor the L here, at least the column part of the L433, right here. So, that’s that case. So, this is the case where our debt is now mature, and we need to pay back everything to the lenders. For the other case, where it is not we need to look at the minimum between the yearly amortization percentage over here, which again, I’m going to anchor the column here to make sure that F doesn’t shift around.

So, take the minimum of that, and then once again everything that has been amortized, so far. So, we’re basically taking one minus everything that has been amortized. So, the remaining amount that has yet to be amortized so far. Making sure I get the appropriate number of parentheses, here.


And so, once again basically what this formula is saying; I know it may look a little intimidating if you’re looking at it; but basically in the first case, we’re saying if our debt is now mature we have to pay back everything. Then the amount we have to pay back is one minus the total that we’ve already paid back.

So, if 60% has already been amortized, this is going to be 40%. That means we owe 40% of the loan back, in a particular year. Otherwise, if we have not yet reached maturity then we are going to either take our yearly amortization percentage, right over here, so in this case the 20%. And if that is the lower number in this case then we’re going to use that.

Otherwise if for example, if we only have 10% left to amortize then we’re going to make sure that we get that 10% instead, because we don’t want to have a situation where we have 10% left, and we’re still pulling from this 20% yearly amortization, right here (which would result in the ‘over-amortization’ mentioned previously).

We want it to work correctly, so that’s why we need to have this formula. Again, I know it may not be completely intuitive just looking at it, but if you break this apart, and think through each part of the formula and what it’s actually saying here, I think it will start to make sense after time.


But, that is basically all we’re doing here. All we’re saying is, if we’ve hit maturity pay back the remainder that we haven’t yet paid off. If it is not the maturity year, then pay back either the yearly amount that we have to pay each year, 20%, and 10%., or if we’re in a case where we only have 1% left, then let’s just pay back that 1%, assuming that it’s smaller than the yearly amortization percentage rate, here. So, that’s how that formula works. Keep in mind we are using the MIN function in the formula here as well, so it will take the lesser of the two so as to prevent out model from ‘over-amortizing’ the debt and us resulting in a negative debt balance on our balance sheet.

And, we’re going to take this and actually apply it to pretty much everything else on here. So, you see the way it works for Term Loan A, right here. Interestingly enough, we are also going to use this for the other types of debt.

And you see how it works for Term Loan B, here. We have 10% amortization each year, but it matures in 2012. So, if you look at the schedule here we have 10%, 10%, 10%, and then we have 10%, so 40% so far has been amortized. And then in the final year because everything comes due then, we have the 60% number.


And, the 60% number comes as a result of this case right here, where we have H435 equals S402. Our loan is mature, now. We’re summing up these four 10% numbers, right here in the prior years. We’re subtracting that from one and that’s how we get to the 60%, right here, which represents the remaining amount of debt that still needs to be paid off. So, this number at the end is this case right here, in our model. So, that’s how that works.

Now, one question you might have is why do we need to do this for subordinated debt, for mezzanine, for the senior notes even, because there is no amortization, because these are all bullet maturity, so why are we bothering doing this?

And again, the answer goes back to making our model as flexible as possible. If we were to not have this in place, then if someone later came along, and decided they wanted to make this all high-yield debt, or all bank debt, or have multiple different term loans or something like that, then they’d have to go in, and change around some of these formulas.

But, if we make this flexible enough to handle this scenario, where you can really say whatever you want for yearly amortization, for prepayment, for maturity, for paid-in-kind years, then our model is extra easy to follow, and extra easy to modify later on, should the time come when we have to actually change it.


For preferred stock, we’re not going to bother with entering anything here, because amortization doesn’t really make any sense. Preferred stock doesn’t amortize, the company doesn’t have to pay it back. It just stays on its balance sheet, indefinitely.

For the new Term Loan A recap, we have to modify this formula, slightly. So, I am still going to pull in from the yearly amortization percentage, right here. So, in the first year, 2011 it’s going to be this formula. But then after that, I am going to take the formula that you see right here, and then modify this a bit.

So, what I am going to say is, instead of the sum coming from L434 to R34, it’s just going to be R34 through R34. And the reason I’m doing this is because this one, our Year 1 for the dividend recap here, is 2011. So, it doesn’t make any sense to start this from 2008.

So, I’m just modifying this formula slightly. So, that’s that. So, now we’ve mapped out what percent amortization we need for each of our debt tranches, in each year here. So, with that in place, now we can get on to the rest of the model, here.


And what I am going to do, the actual calculation of mandatory debt repayments, the actual numbers under mandatory debt repayment, these are actually pretty straightforward. So, what I’m going to do is start with the sources of funds, here.

Now, you’ll notice that even though we are looking at a mandatory debt repayment schedule, I am still using the same nomenclature; sources and uses of funds. And this is very standard in finance in any type of model; you’re always going to see sources and uses of funds, so that’s why I am using it here, as well.

So, for sources of funds, what we’re going to do is basically figure out how much cash flow Yahoo really has available to repay its debt. Because what we want to figure out here is, we know how much Yahoo will have to pay off in each year, it’s spelled out in this debt amortization schedule, right here for Term Loan A, Term Loan A recap, and Term Loan B.

So, we have an idea of how much they actually have to pay off each year. What we want to do is figure out, first off, do they actually have enough cash flow to repay everything? If they don’t have enough cash flow how much of their revolver will they have to borrow? And then finally, if they actually have enough they don’t have to borrow anything from the revolver.


How much extra do they have available that they can use to repay some of the other debt? And that’s going to be the optional repayments, which we’ll be getting into in the next lesson. To start with right here, for the beginning cash balance I’m going to go up to our balance sheet, now.

For 2008, the beginning cash balance here. I want to use the $400 million number right here, because remember our beginning cash balance for 2008, is effectively the same thing as the ending cash balance for 2007. So, we have that, and then the minimum cash balance.

So, remember that in any type of model, the company always needs to have a certain amount of cash on hand. It’s kind of like how, even if you have credit cards and debit cards, you still want to have some cash in your wallet, because you might come across some kind of expense that you can’t use a credit card or debit card for. So, you always want to have some kind of minimum of cash on you at all times.


Similar to how a company always wants to have some kind of minimum cash balance. So, for this one, I’m going to take the negative of our minimum cash balance number, which is spelled out right here, minimum cash balance. I’ve named this variable ‘LBO Min Cash’.

Then we need to add in the cash flow available for debt repayment. Now for this variable, I am going to go up to the cash flow statement, and what I’m going to do here is take the effect of the exchange rates on cash, plus the net cash used in financing activities, plus cash flow from investing, plus cash flow from operations.

This schedule is not fully completed, yet. You will see that we have some blanks up here that we have yet to fill in, and we’ve not yet linked in everything properly, but this is fine for now. I am just trying to get a blueprint sketch of what our final model is going to look like, here. So, we have this number. And you might be wondering, why our cash flow available for debt repayment, is so low here.


The short answer is that there are a couple of problems with this schedule, right now. The two primary problems being that, in cash flow from financing we have these repurchases of common stock, and proceeds from issuances of common stock. When Yahoo becomes a private company, when it’s bought out by a PE firm, these are both going to go to zero.

So, we’re going to need to adjust that in one of our upcoming lessons, where we fully link together the three statements here, but that’s the basic problem with this number, right now. This is actually understated by quite a bit, because of this negative ($1.6) billion in expense each year. So, we’re going to have to fix that later on, and that’s why the cash flow available for debt repayment right now, seems rather anemic. It seems like there’s hardly anything there.

Of course, we’ll have to change some other numbers throughout this as well, but again, the point for now is just to come up with a basic outline of what this going to look like. So, let’s go back down to the debt schedules, and so I am going to take this one, and link up to the cash flow available for debt repayment. So, I’m going to sum up each one of these.


And now what I want to do is I want to compare how much we have available. So, this is effectively our cash flow available to repay debt, and I want to compare this to how much we have to repay each year, which is going to be under this mandatory repayment total, right here. And if you think about the way this formula works, your first instinct might be to simply take the mandatory debt repayment total right here, and then subtract the subtotal, before the revolver.

So, if we have $5.0 billion and we only have $1.2 billion available, then clearly we’re going to need to borrow a lot of money, here. The problem is, as you see right here, that if you do that, you could potentially end up with negative numbers. So, what we have to do instead is say MAX of zero and whatever this comes out to so as to ensure the difference will never be a negative amount and always positive.

So, if this comes out to a negative number in other words, we don’t need to borrow anything on our revolver. Then this is just going to default to zero instead, so it works correctly like that. With that in place, now let’s just add up the revolver borrowing required, and the subtotal before the revolver, right here. And this is telling us, essentially, how much we have available to repay our debt in this model.


So now, let’s just take these formulas and copy them across. Again, the numbers are all completely off right now, because this model is only partially finished. But later on, as we complete it, and finish linking together those statements these numbers will start to make a lot more sense. So, we have all that, and these are our total sources of funds available for our debt repayment.

So, we’re going to look at the mandatory debt repayments, now. As I mentioned before, the calculation for this part is actually very, very simple and mechanical. Actually, everything else we’ve done up to this point is more difficult, so this is really just linking together all of our previous work.

So, if you think about the way this formula works, there are really two cases, here. So, the first case is that we repay the entire remaining balance of the debt. And that’s what happens if, for example, we have $10.0 million remaining balance, and we’re supposed to repay $20.0 or $30.0 million each year, clearly we can repay the entire $10 million remaining balance. So, that’s one case. And to handle that case, I am going to enter a MIN function, right here. I’m going to go up to the balance sheet, once again.


And I am going to go up to Term Loan A, right here. So, this is our beginning balance number. So, I have that, and what I’m going to do here to calculate this, is I’m going to go to the beginning balance of Term Loan A, right here. I’m going to make sure that that does not shift around, so I’m going to anchor the column right there.

And then, for the alternative case I’m going to multiply the entire Term Loan A balance by the amortization percentage in each year (to calculate the standard typical annual amortization payment). That’s why we had to create that schedule in the beginning. So, I’m going to take the minimum of those cases. So, in this case, the beginning balance here is almost $2.0 billion, so in this case the annual yearly amortization number is the lower one (since, again, we are using the MIN function in this formula).
So, it defaults to this case, where we take our original beginning balance, and we multiply it by the yearly percentage, here. So that’s one case. And for the rest of these, the formula is actually pretty much exactly the same. So, let’s just take this formula. We’re going to skip Term Loan A – recap for now, because this comes in, later on. Just use it for the rest of these.


Term Loan B, senior note, subordinated note, and mezzanine, I am just pressing ‘F2’ again, quickly to check all these. Term Loan B, so we have all those in place. Then I’m going to sum up this total using ‘Alt + =’, and get all these numbers.

In this case, you see that our subtotal before the revolver is $1.2 or $1.3 billion. We only have $500 million of mandatory debt repayment, here. So, in this case we don’t need to put anything on our revolver. This one, you see that this number the L457 minus L445 here, comes out to a negative, so it defaults to zero instead, right here.

So, we have that. We’ll copy then paste this by using ‘ALT + E + S + F’. You see that these numbers are the same in each year here, because these all have straight line amortization, Term Loan A and Term Loan B. Let’s take these once again, and copy them over here, use ‘Alt + E + S + F’.


And now you see we have this addition of our new Term Loan A recap, right here, so $1.0 billion of additional debt amortizing at 20% per year, so we have $200 million each year, here. And then finally, the Term Loan B right here, you see the $694 million number. This is because once again, 60% is due back in 2012, because that’s when the maturity date is. So, that is how we calculate the mandatory debt repayment in an LBO model.

And you see also, this is kind of a preview for how we’re actually going to get to the optional debt repayment here. But the concept is very similar that we’re going to look at how much cash flow we’ve actually used for the mandatory debt repayment, and then any extra cash flow that is available, we can allocate to repaying some of our debt, assuming that prepayment is actually allowed on that debt (hence the term ‘optional debt repayments’).

But hopefully, now you should have a better understanding of how we actually calculate, the percentage of amortization in each year here, for each of the tranches of debt, and also how the revolver is actually used in the LBO model. I’ve been referring to it throughout this model, and throughout these tutorials, as a credit card.


Because it really is, whenever your cash flow is not sufficient to finance your mandatory debt obligations that’s where revolver comes in, and you see it first-hand right here. So, hopefully you have a better understanding of all that now.

Coming up in the next lesson, we are going to be getting into the optional debt repayments, right here. And this part is split into a separate lesson, because the concept here and the formula get very tricky. This is probably one of the most difficult formulas in the entire model so I am devoting an entire lesson to it.

Coming up after that, we’re going to be linking everything together, and then we’ll actually be close to finishing up this model, and you’ll see first-hand how to finish linking together all three statements, and then finally how to calculate the returns to the investors, at the end.

For more tutorials on Advanced Financial Modeling click here.