To calculate the IRR, we need to work out the equity value that's going to appear in each year. Then, when we decide our exit year, we can take that equity value, and then spread it. We've got two different groups who can receive equity. The first are the mezzanine warrants, they're going to get some equity, plus their debt back, but then we'll get the equity holders. So, let's start off by finding in J4, our LTM EBITDA to the year December 19. For this, I need to go to the deal model tab, and I need to scroll down, into the income statements. And I find, in J31, we've got EBITDA of 250. I can take that EBITDA, and multiply it by a multiple. I can find that on the input tab. And I've moved the input tab next to the returns tab, just for ease.

That multiple is in C11, I'm going to lock that. So, eight times our EBITDA gives us 2001. I now need items in the enterprise value bridge, such as cash and total debt. Cash is going to be on the deal model tab.

Scroll down to the balance sheets, make sure I'm looking at column J. There's my cash, total debt. That's very handily provided in the debt schedule. So, I'll go to the debt schedule. I need to go all, almost all the way to the bottom. It's in row 185. And, again, I remember I'm going to column J. I've got everything I need to calculate equity value. So, I take the NSPR value, plus cash, minus debt, gets me to 867.7 equity value. Now, let's work out how much of the equity value is going to go to the mezzanine warrants. Well, I need to use an IF function. I want to say if we're in the exit year, then give me a percentage of the equity value. So, I'm going to say, if J10 equals the exit year given to us on the input tab, and here it is.

Then, what I want to give is a percentage away to mezzanine warrants, and they're going to get 1% of the equity. So, I'll multiply that up by the equity.

If the two dates don't equal each other, I want to hard code it zero. So, I'm going to copy lots of this to the right. I want to make sure that it's working in the exit year that we gave, and the exit year we suggested was year 23, and there is 1% appearing. That's not all that the mezzanine warrants get. They're going to get their debt back, as well. So, in order to calculate the IRR, I firstly need to show that investment happening, and it needs to be a negative. So, I go off to the input tab. Again, I look at the amount of money invested by the mezzanine debt holders, and that was 100. They're going to get back that little slice of equity, plus the debt, but only in the correct exit year. So, I need to use that if function again. So, if J10 equals input tab, and I go find the exit date, C8, then I want to give them the debt they're owed, from the debt tab, and I can find that in row 149, or I could have found it further down, in row 184, plus their slice of the equity.

Otherwise, give me a zero.

So, there's the full formula. Now, let's copy that to the right. Let's check that it does give us a value in year 23, and it does, fantastic, 185. So mezzanine invested 100, they're going to get back 185. Let's calculate the XIRR.

Now, XIRR calculates the IRR, but it also takes into account the dates. The regular IRR function doesn't take into account the dates. And we've got a stub period, which means our dates are not equally spaced. So, XIRR is there, to the rescue, 12.4%.

Now we haven't got the interest circular switch turned on yet, and iterations haven't been allowed. So, we might see that IRR come down, ever so slightly. But if we compare it to the mezzanine's interest return, mezzanine, we're getting a 10% return. So, that equity kicker has got them up to 12.4, at the moment. Let's do something similar for our equity holders. I'm now in column I. I really need a zero, here, so I'm going to say, if I10 equals input C8, then give me the equity that we'll find in row eight. I'm being a little bit naughty here, linking to a blank cell, but it's okay. It'll just give me a zero here, multiplied by the 99% that the equity holders get. So, to get that, I've multiplied by one minus, back to the input tab, one minus that 1%, there. Otherwise, zero, and we get a zero. Copy it to the right. And in year 23, we should get a figure, 1,990.9, and there's that big formula. So, what are the equity holder cash flows? We look at how much they invested. That's on the input tab. Equity holders invested 579. That needs to be a negative.

And the amount they get back equals just the cell above. 579 invested, 1,990 returned. So, use the XIRR again, I choose my values. Then, I choose the dates, and I get a whopping figure. 26.5%, that's fantastic. Let's go to the info tab. Let's turn the switch on. And, if I press alt FT, and I go to formulas, I can enable iterative calculations.

What returns do we now see? Well, it was 12.4, it's now come down to 12.1, and our equity IRRR, it was 26.5. It's now 22.9. So, 25% was your threshold. All of a sudden, we're finding the IRR a little bit low. However, 22.9%, still a reasonable annual return.