In the following workout, we're gonna have a look at calculating internal rate of returns. In workout number one you can see we've got a series of scenarios. This column, column C shows you the valuation of the firm at entry, when we first purchased the business. And then we have one, two, and three different exit cases. In Exit Case 1, we expect the EBITDA to have grown to 340. We expect to sell the business for seven times EBITDA and we expect debt to remain the same. But in Exit Case 2, we expect EBITDA to remain the same but for us to have multiple expansion. However, we are also expecting in Case 2 for the debt amount to fall from 1,500 to 1,200. And then in exit case three we expect EBITDA to rise from 300 to 340 the multiple to stay constant at seven times but the debt to have dropped from 1,500 at entry down to 1,000. In each case, we are going to assume that we're going to sell the business in year three. Let's get started by first calculating the enterprise value. This is the value of the firm at a specific moment in time and it's the value of the whole firm available to both debt, and equity holders. We can calculate that, by taking the EBITDA number times the EBITDA multiple which in the first case is seven times. This means we expect to buy the firm for 2,100. I can now copy that across to calculate the enterprise of the firm, enterprise value of the firm when we expect to exit in case 1, in case 2, and case 3. So I'm going to shift + arrow right and control + R to copy that right, and you can see in all three cases the value of the firm has increased.

We can also in the next line, row 12 calculate the debt to EBITDA multiple. And this is one of the ways in which we'll establish the debt capacity of the firm. So if I take the 1,500 which is the total debt amount at entry and divide by the EBITDA at entry of 300 you can see that when we bought the business it was leveraged at five times EBITDA. When we sell the business, in case 1 if I copy that right, you can see that the leverage has fallen as a multiple of EBITDA not because the debt has fallen but because the EBITDA has risen. So in case 1, the debt amount has stayed constant but the EBITDA has gone from 300 to 340. If I copy that right, again to case 2, you can see the debt EBITDA multiple has gone down even further. And this is mainly because what only because the debt amount has fallen because the EBITDA has stayed constant. In case 3, we've seen the EBITDA grow to 340 but the debt to fall to 1,000. So there's been a fairly dramatic drop in the leverage of the firm and the debt to EBITDA multiple has gone from 5 times to 2.9 times. The next stage is to calculate the value of the equity. And at entry, we're going to calculate how much equity we will have had to put into the business to buy it, assuming that the business borrowed 1,500 at the time we purchased it. So in this case, if I take the total value of the whole firm, the 2,100, and subtract how much financing came from banks, and other lenders of 1,500 this means when we bought the firm we must have made an investment of 600 million. Now we can do exactly the same calculation at exit in case 1, case 2, and case 3. Here, just as we did at entry we'll take the enterprise value minus the debt amount. Now, in case 1, the equity value has increased not because we've repaid debt, not because there's been multiple expansion, but because the EBITDA has increased. In case 2, if I copy this right the equity value has increased even further, not because EBITDA has increased because of the same at entry, but we've had multiple expansion and the debt amount has fallen. And in case 3, the equity value has increased even further because EBITDA has increased, the multiple has stayed constant, but there's been a dramatic fall in the debt amount. The business will have paid off the debt during that period. Now, assuming in this case that we have an investment horizon of three years which is shown in row 14, we can calculate the internal rate of return. Again, the only two cash flows, the entry cash flow which in this case is 600, and the exit cash flow which is the exit value of the equity in case 1, case 2, and case 3. This means because there are only two cash flows, we can use the compound annual growth rate formula. And that is taking the exit value of the equity or the value of the equity when we sell the business divided by the entry value of the equity. And I'm going to absolute reference that, so it's fixed. Close parenthesis. And then I'm gonna take that to the power of open parenthesis, one divided by the number of growth periods. And in this case, if we're selling it in year three that means there're three growth periods. I'll close parenthesis and then minus one and hit Enter. This means in case one where we have a growth of EBITDA, but no multiple expansion, and no debt repayment we get a return of 13.6%. In case 2, where we do have multiple expansion but no increase in EBITDA and some debt repayment our returns are 26%. And in case 3, where we have an increase in EBITDA the same exit multiple as the entry multiple but a significant repayment of debt, our returns are 32%. We can calculate the value, the total amount of value created by taking the equity value at exit minus what we put into the deal initially. And I'll absolute reference that by putting the F4 which puts the dollar signs in the reference. So in case 1, we created 280 million of value. In case 2, we created 600 million of value. And in case 3, a massive 780 million of value.

We can now take a look to the extent at which each variable has contributed to the value creation. The debt repayment is fairly straightforward. We can just take the difference between the debt amount when we bought the business, the 1,500 and I'm going to absolute reference that minus the amount of debt when we sold the business. And in our case 1, there's been no change in the the debt. So there's no value creation from that. But in case 2, and 3, there's been a fairly substantial value creation to equity holders because we repaid debt. For the EBITDA improvement, we're gonna take the EBITDA at exit minus the EBITDA at entry because that's the growth in the EBITDA. And I'm going to absolute reference that, close parenthesis, and I'm going to multiply that by the entry multiple for the firm. And I'm gonna go up and get that. And that's the multiple at entry, which is seven times. And I'm going to absolute reference that. This calculates the increase in the EBITDA and then multiplies it by our entry multiple. And that will give us the amount of value that's being created because we've increased the profits of the firm. So in the first case there's been a fairly substantial increase in EBITDA so a substantial increase in the value of the equity. In the second case, there's been no change to EBITDA so there's been no value increase. And in the third case, there's been a significant increase in EBITDA. So there's been a significant increase in value. Next point is the multiple expansion. Here, we are going to take what we sold the business on as a multiple of EBITDA which is seven times in our first case, minus the multiple of EBITDA that we bought the business one on. And in this case, in case one, we sold the business at seven times and we bought the business at seven times. I'm going to just absolute reference that second reference, close parenthesis. And then what I'm going to do is I'm going to multiply the potential change in EBITDA by our exit EBITDA number. And this will calculate how much value is being created by the fact that the EBITDA increased. And you can see in this case there was no increase in the multiple. So even though the EBITDA went up there's no value that's been created. In our second case, there was multiple expansion because the EBITDA multiple went from seven times to eight times. And in the third case, there's no multiple expansion because there's no change in the EBITDA multiple. So the value created if I sum up the debt repayment value creation, the EBITDA improvement value creation, and the multiple expansion contraction, in the first case is 280, in the second case is 600, and the third case is 780. And these numbers are exactly the same as the value created here which is just the difference in the equity value. But this allows us to assess whether this investment has been successful or not. And generally speaking, we want to avoid situations of incentivizing the executives who put the deal together simply if the value created was made by multiple expansion, because that tends to be lucky. It's depending on the M&A market rather than some fundamental structuring as in the debt repayment or improving the profitability of the business. Now that said, of course you can be lucky and perhaps you can buy a business cheaply and in which case, if you buy a business cheaply perhaps you would be able to get some multiple expansion. But it's a fairly controversial issue, that. At least this way we know exactly how much value is being created by debt repayment, improving the profitability, and also multiple expansion.