This video dives into the Excel model to see how the IRR hurdle and cash multiple hurdle are implemented.
- [Instructor] Hey, welcome back. In the last lecture, we looked at the implementation of the preferred return. In this lecture, we are going to look at this second hurdle, which is the IRR and cash multiple hurdle, and look at how it's implemented in the calculations. All right, but to look at this, obviously, we're going to not look at the two-tier because the two-tier goes straight to the final split. It'll be easier for us to look at the second hurdle in terms of a four-tier waterfall. Okay, so we're going to look at the calculations here.
All the example assumptions are the same. We're going to look at the Alice investment of 5%, okay. All right, so let's go to the calculations here. I'm going to come back to the top. Right, so here, we did the preferred returns. We saw what that was and then we have the calculations for all the preferred returns for all of the investors. So up here is all of the preferred returns, plus the original investment that's being paid back. Now, you see that there's no remaining cashflow because none of the original capital was paid back until the very end, until the sale of the asset.
And we'll see that here in year 15 after all of the preferred return is finally paid off and all of the equity, original equity investment finally returned, we then had 1.9 million left over. Okay, so that's the remaining cash. Then that goes into the second hurdle. And this is where we look at the IRR and the cash multiple hurdle. Now, I am going to just kind of go over this whole section broadly first.
This whole section is the second hurdle. And how it's broken down conceptually and then we'll dive into the specific formulas, okay. So because there's a cash multiple hurdle and an IRR hurdle, the way it works in the waterfall when you have these two hurdles, when you have a, hey, it must meet this hurdle, this IRR and a 1.5% cash multiple, what it's saying is, it needs to meet both criteria, so we take the bigger criteria, the harder one, the one that is more costly to fulfill in terms of the cashflows, if it fulfills that, then it will fulfill the other.
Okay, so conceptually, we calculate both independently. Right, we calculate the investors' cash multiple here. We calculate the IRR hurdle here. And then whichever one is higher, that has more left to be paid off, that's the one that we're going to use to pay off all the investors, okay. That is how we know we've met both hurdles. All right, so now, let's get into the actual hurdles itself. I'm going to zoom in a little bit now and then we'll go to year 15 again.
Okay, so the multiples hurdle. We look at the investment by the investors times the multiple for that year, right, so if it's 1.5, then it's 1.5 times the investment. So we looked at the total investment for here was like 732,000. Well, 1.5 multiple is just 1.5 times that, right. So that is the total amount that needs to be returned to the investors.
And then if some of it is paid back, we subtract it from that value. Right, so in any given year, if some of it is paid back, then we do that, okay. So we have the tracking here. So we see that by the time we get to year 15, there's not that much left of the return multiple that needs to be paid 'cause a lot of it's already been paid off, right. But the IRR is a much tougher hurdle here in this case.
So if we go back to the other one, the IRR is accruing each year, so it takes what was invested and then it looks at the IRR return for the year. So let's go back to this, the investment of 732,000, and it's 12% a year. This IRR hurdle is compounded. So here's a 12% on the 732,000 and based on what's paid off, the rest of it now accrues at the same 12% rate.
You can see here the one plus the... This is actually the IRR hurdle amount percentage. And you'll see that by year 15, there's a much higher requirement here, right, that is needed to pay this off. So this IRR hurdle, out of the 1.9 million, it needs a little over one million to pay off, to pay for the IRR hurdle. And because that hurdle is at a 90-10%, so that 1.8... 1.088 million is 90% of what's getting paid in this hurdle.
Right, so that means 10% of that is going towards the manager, okay. So between these two options, this part here is called the actual. We're going to take the larger of these, right. You see this value? It takes the max of what is needed to pay off either the multiple hurdle or the IRR hurdle. Well, it requires more to pay off the IRR hurdle, so we take that number.
And then, because that's 90%, this is how it's split among all the investors based on their percentages. Alice, the project manager, included, because she's also an investor. And then this is her 10% bonus from this tier that is going to her as a project manager. Right, now, after all this is paid, well, guess what? There's some money left. Some of that money left goes to the next tier. So in a four tier, what we have is now it goes to a catch-up.
So in the next lecture, we're going to talk about the catch-up and how that works before we get to the final split. So here, the easy part is we got to separate the cash multiple and the IRR hurdle. They're both easy to calculate, formulas-wise, okay. And then this is based on which one we're choosing and then it's split based on their shares. Right, so once you understand that conceptually, you can understand how it's done with the formulas and it's fairly straightforward.
So there's a few things that I did here that may require a little bit of thinking or understanding of the Excel functions to know what the purpose is. Right, so I'll give you an example here. Here, I used this MAX function, right. MAX of zero or that minus that minus that. The reason it needs to be there is because in some scenarios, if there's a very profitable investment and all of it's been getting paid off and paid off, we don't want to have negative number hurdle here, all right.
So by having a maximum of zero or that, it limits this, sort of the minimum it can be is a zero here for calculation. So there are some things that are done in the implementation in Excel that has nothing to do with the real estate concept itself, but it has everything to do with building robust Excel models, so that it doesn't have errors or you know, break in extreme scenarios. Okay, so that's what that MAX function is there for.
But otherwise, you should be able to follow the formulas here logically.
- Risks of real estate investing with partners
- Waterfall examples: 2, 3, and 4 tiers
- Choosing a waterfall model
- Accounting for losses
- Tracking rental income using the waterfall framework
- Analyzing fix and flips using the waterfall framework