Calculating Preferred rate of return

Great to hear from you Josh!

First, allow me to make an important point and then I’ll answer your questions. How you model the partnership waterfall of a given deal very much depends on the wording of the partnership agreement in that deal. What makes partnership-level modeling so difficult, is that every structure is different.

Generally speaking, you can build a property-level model and it will handle most of the situations you run into. But I could show you 10 partnership structures, and all 10 might have enough nuance between them that it would require building 10 modules (or at least modifying them) to handle each structure individually. Thus, this course (and the subsequent waterfall modeling lectures in the Advanced Concept course), set out to teach you to think about waterfall modeling in terms of the timing of the contributions by and distributions to the partners. If you can internalize and master that framework, with enough practice you’ll master partnership-level modeling.

So now to your questions.

How to calculate the LP’s required rate of return.

This is the XIRR vs IRR/12 conundrum in modeling partnership-level waterfalls. Which methodology to use really depends on the language of the partnership agreement. I’ve had several GP’s email me via A.CRE over the years complaining that they’d just realized the partnership agreement they signed calls for use of Excel’s IRR/12 function to calculate the LP’s preferred return rather than XIRR. Using IRR/12 is to the LP’s benefit, as it requires distributing more cash flow to the LP to hit each hurdle due to annual compounding of IRR compared to the monthly compounding of XIRR. These GPs made the mistake of not reading and/or fully understanding the ramifications of the XIRR vs IRR/12 negotiation.

With that said, generally speaking if the partnership agreement calls for monthly compounding and assuming the distributions are monthly, you will likely be using the XIRR function. And if you’re using the XIRR function with monthly periods, you’ll need to convert the annual rate to a monthly rate to arrive at the appropriate annual return.

Simply taking the annual rate and dividing it by 12, when using XIRR, will result in an actual annual return greater than the hurdle rate. This is because of the monthly compounding of the cash flow.

You can see what I mean in this quick example I put together (click here to download Excel file). In the top example, I simply take the hurdle rate and divide it by 12 to arrive at a monthly rate. The resulting annual return is higher than the hurdle rate. In the bottom example, I convert the annual rate to a monthly rate using the (1+hurdle rate)^(1/12)-1 logic. The result is an annual return that is roughly equal to the hurdle rate.

An important side comment. Many partnership agreements will expressly call out how to calculate the preferred return, including calling out the XIRR() specifically. However, and this is the reason I said “generally speaking” above, the XIRR function is not always the methodology called out in the partnership agreement. So it’s important to read the agreement before finalizing your partnership cash flow module.

If somehow, there’s still cash leftover, do we then pay down the LP capital account in each hurdle until zero’d, before distributing any remaining cash to the GP? Or does the GP get his share too in each hurdle, and the money is distributed in each hurdle until there’s none left?

It depends on the wording of the partnership agreement. In the structure we modeled, return of capital to the GP/LP is made parri passu (i.e. at the same time). So in your example above, where there’s insufficient cash flow to return all capital, whatever excess cash flow is remaining would be distributed pro rata based on the partners’ respective ownership share.

Some structures however call for 100% of excess cash flow to first go to the LP until the LP’s capital account is zero and the LP has achieved an X preferred return, after which 100% of the excess cash flow is distributed to the GP until the GP’s capital account is zero and has achieved an X preferred return (i.e. the GP catch up).

Note that I show you a couple ways to model a GP catch up in one of the lectures in the Advanced Concepts in Real Estate Financial Modeling course.