In order to calculate the IRR, we need to work out the cash flows to and from equity holders. To get the cash flows from equity holders, the amount they invested in year zero, we can find that easily in the sources and uses of funds. But to find the equity at exit, we'll first have to calculate the EV exit, then the net debt at exits, and then finally we'll have the ordinary equity at exit. So let's start with them. To find our EV or enterprise value exits, we're going to need an exit multiple, and we've got that up near the top of the model. In row 25, we have an exit EBITDA multiple of 17.1, and I'll lock onto that.

We then need to multiply that by an EBITDA, and there are quite a few to choose from. We're going to be choosing the adjusted EBITDA, and we can find that in our income statements. It's in row 93, although alternatively, you could have used the same number from the cashflow statements or from the debt ratios.

That gets us an EV of 2,159.8. Next, I need the net debt. My net debt figures are all available just above. We've got total debt in row 140, and we've got the cash in 138. So subtract the cash away from the total debt, gives us net debt, and EV minus net debt gives us ordinary equity.

So now the cash flows for equity holders. I'm moving into column E now. I want to look at the amount of equity invested at the beginning of the deal by the equity holders, and we've got that in the sources of funds. So if we scroll up to the sources of funds, we can see in cell G45 that they invested 1,389.1. I'm going to want that to be a negative. I want it to be a negative so that I can see it being a cash outflow from the equity holders.

As I move into year one, I now want to ask myself, are we in the exit year? And if we are in the exit year, then I want to bring this ordinary equity down as a cashflow. I.e. pay it out to the equity holders.

So I'm going to use an IF function to ask myself if I'm in the correct year. So if 1 equals my exit year, again, I'm gonna go up to the top left hand corner of the model, find my exit year.

The exit year here is a five, and I'll lock onto that. So if we're in the correct year, what do we want? Well, we want the ordinary equity at exit. Unfortunately, we're not in the exit year here. We're in year one, we should be in year five. So if the value is false, I just want a hard coded 0, And that's what I get. If I now copy these to the right, I should hopefully find that we end up with a cashflow in year five, and there it is. Fantastic. Finally, we've got everything we need to calculate our IRR. So I'm going to use the IRR function and it asks me for a number of values. Well, I start with the equity that's invested by the equity holders, and then I include all of their cash flows to the right, even these zeros. And when I press enter, it tells me that we've got an IRR of 12.9. It could be a bit higher. Unfortunately, when we're doing an LBO, we typically want to see an IRR above 20, even 25% unfortunately. We also need to turn the interest on because the interest isn't on at the moment, so I'm going to go to my options. So file options go down to the formula section, and I want to make sure that we've enabled iterative calculations, and you can see that I've ticked that already.

I also need to go to the info tab where we have a circular switch and the circular switch is going to turn that interest on. The enable iterations then allows the interest to be circular. So we turn that on with a 1 and that IRR has now come down to 12.5%. So not a classic deal for private equity to have an LBO.