Let's work out how much new debt this company can take on, and then whether it can pay off the old debt. And then we'll be able to work out whether it can pay a dividend to its shareholders, the crucial parts of any dividend recap model. So I'm on the recap tab. I'm going to scroll down and we're going to be working in the recapitalization amount section. So I only want this new debt recapitalizations come in if we're in the recap year. So let's use an IF function to check if we're in the right year. So I'll press equals IF character row 16. I've got my year counts, so I want to say if G16, I'm gonna lock onto the row 16 if G16 equals the recap year. Now you might notice it's in C7, but we've named it recap year. So if we're in the correct year, take the senior debt EBITDA multiple of 3.5, and I'm going to lock onto that column and multiply it by the EBITDA. Now we're gonna take the adjusted EBITDA from the base case recap tab.

There's my adjusted EBITDA. I'm going to lock onto the row by pressing F4 twice. So I've said if we're in the correct year, give me the debt multiple multiplied by EBITDA. But what if we're not in the correct year? Then I'll press comma and just a 0. We don't want a calculation now, rather unhelpfully we get a 0. We're just in the first year. We're not meant to have any recapitalization here, but let's copy through to year three and see if it works there. And it does. We can see that we've got 1,531 of new debt coming through. As part of the recap, we can copy this down to the unsecured notes recap, and we can see that we've got another 656.3 of debt come through. Now if we just compare those two numbers to the old debt, it's more than enough to pay it off. So we're hoping that we can have an excess paid out as a dividend, but of course there are some other uses of this debt as well. So let's total that up.

And now let's go down to the use of recapitalization proceeds. The first two, hopefully a little obvious, we're going to use that new debt to pay off the old debt, but there are a few extras we need to pay any arrangement fees on the new debt, but as well as prepayment fees on the old debt, we may not be allowed to pay off our old debt without incurring some kind of fee punishment. So let's do the senior debt repayment. First I wanna to say if this is a 0 I.e., if there's no recapitalization happening, so if that equals 0, then just give me 0.

But if there is a senior debt recapitalization happening in row 32, then I do want to repay the old debt. So I'll go back up to row 19 and we'd pay off that amount there again, copy it through into year three. And what do we see? Yes, we're paying off that old amount. Awesome. We can copy that down into the unsecured notes. Yes, we're paying off again, the unsecured notes, 562.6, but we also need to decide if there's an arrangement fee on this as well. I'll take the total refinancing, I'll go up to the top left hand corner and is there an arrangement fee? There is an arrangement fee of 2%, so we multiply by that, lock onto it. Copy to the right. An extra 43 needs to be paid, so that's another use for the new debt. But what about the prepayment fees? Well, I'm looking at the senior debt to start with and we've got a switch here to decide if a prepayment fee is due or not. Now in this case, the senior debt does not have a prepayment fee. This is because in this case, the senior debt just allows an early repayment. I'm going to lock onto column C to allow this to copy down, and I'll multiply that by the senior debt repayment and we've got that in row 37.

And then I'll multiply that by the prepayment fee of 1% up here. Now I'm gonna have to be very careful how I do this. I'm gonna lock onto that with $2 signs.

Copy to the right, there's no repayments.

But if I copy this down into the prepayment fees for unsecured notes, we see that there is a prepayment fee due of 5.6. Right. What are the total repayments? The total repayments come to 1,124. This is looking very good for the shareholders. The total refinancing or the new debt that's being taken out is 2187.8, but only 1124.1 of it is earmarked towards paying off the old debt and paying fees. The excess is now available, so our excess the difference between the two 1063.7, and we're going to pay out a percentage of that. We've got that as a separate input up in the top left, it's 100% not a lock onto that.

So this is great. The old debt is repaid, fees are paid. And after all of that being repaid, we've still got 1063 to be paid out as a dividend to the shareholders and hopefully that will improve their IRR.