Model - Debt Tranche 1
- 04:44
Understand how to model accelerated repayments.
Transcript
Here we want to calculate our debt schedule for our first debt tranche. This is a term loan, with an interest rate of 4.625%. We'll come back, we'll do the interest later on, but it's the debt I want to focus on for the moment. So we start with our beginning balance, and that equals my ending balance of the term loan from the last period. But then it asks me, do you want to make a mandatory repayment? And it's very tempting for me to use the assumption, which shows that we should be paying off 9,000 up here. I might want to use this, absolutely I might want to use this. However, it may be the case that my beginning balance is already below the 9,000 mandatory repayment. How could that be? How could my beginning balance be less than the mandatory repayment that I've been told to pay? Well, the answer is, because we've got these accelerated repayments. In a previous year, we may have overpaid. And if we've overpaid so much, we may find that our beginning balance is now tiny. So what I want to do is I want to use the minus min function. I've put the minus sign in there because I want whatever figure appears here to be shown as a negative. And the first thing I want to include here is my beginning balance, I just want to check that I've still got some debt that I owe.
The second thing I want to check is my assumption, but you might notice I've included this here as a negative of the negative, i.e., I've turned it into a positive. That's because what I want here is a positive 9,000, and I want to compare that with a positive 15,000, great.
Close the brackets and I can see that I am now going to make my mandatory repayment because I owed more than the mandatory repayment, I owed 15,000.
Now you might notice that row 164 changed when we did this. If I just flick back and forward, backwards and forwards, you can see the cash available in 164 changes. That's because this mandatory repayment is linking up into row 152. So as we put the mandatory repayment in, it shoots up into row 152, and then my surplus cash available, cash available, cash available, all drops down.
So because I've done a mandatory repayment, my cash available for accelerated repayments is less. Let's move on to the accelerated repayment now. The first thing I need to ask myself is, how much do I still owe? Well, we had 15,000, we paid off nine, so we still owe six, that's great. So I want to use the minus min. And firstly, just put in the amount of debt we owe, 6,000.
But then I want to compare that to how much cash I've got available. Do I have enough cash to pay off all of that 6,000? Yes, I do, we've got 6080.5 at the moment. So I'm going to close the brackets. I want to do one extra thing though, I want to multiply that by an acceleration switch. This acceleration switch allows us to turn the accelerated repayment off or on. Maybe some term loans don't allow us to do an accelerated repayment, in which case we would turn it off. I'm going to press F4 to get those dollar signs up here, so that when I copy all of these formulas to the right, that reference to E 166 will stay locked on that cell because of those dollar signs.
Great, my ending balance then, the sum of those above, we started with 15,000, we paid off nine, we pay off six. We've paid off all of term loan one, fantastic. Last up then, how much cash do I have available for accelerated repayments? Well, we had 6,080, we then spent 6,000 on an accelerated repayment. But be careful, we don't include the mandatory repayment in this calculation. Remember that mandatory repayment of 9,000 is already included in that 6080.5. It's already been included in there because it's already gone into row 152.
So we don't include the mandatory repayment, and we've still got 80.5 of cash available.