How would you write an Excel formula to capture the following tier of a promote? (you can assume the Partners' have received $50 in distributions up to this point in the calc)
- (A) twenty percent (20%) to such Partner, and (B) eighty percent (80%) to the General Partner until the General Partner has received with respect to such Partner thirty percent (35%) of the total amount distributed to such Partner and the General Partner.
Trending Content
Career Resources
What you are trying to model is a general partner "catch up". Make an IF statement within the distribution row that multiplies the share of cash flow (GP = 80%) until your total GP proceeds equal 35% of what the LP receives up until that point.
Typically watefalls has a pref return where LP's receive all cash flows until they hit a certain hurdle (say 10% IRR). Catch up provisions are not as common. The catch up provisions I have came across were 100% to the GP until the GP receives x % of total cash flows. This makes it easier when creating the waterfall compared to the way you have described. Reason being is that the tier is not circular where the LP is still receiving 20% of the proceeds until the catch up is satisfied.
This is obviously different than an IRR tier within a waterfall and slightly more complex given the circular reference; just logically think it through when constructing this IF statement. In my experience, a little guidance + trail and error is the only way to fully understand the more complex aspects of real estate modeling.
I've never even come across a "catch up."
Great response, though. SB.
Would he have to turn iterations on for this ?
Any time there is a circular reference you will need to turn on iterations.
Should be fairly easy to write a formula that doesn't have iterative calcs.
I typically leave iterations on for any type of waterfall payout modeling (after the model is complete)
Hey I still cant figure this out- it works for year 1 and distributes all excess cashflows but then year 2 it doesnt distribute anything even though the distribution "ceiling" hasnt been met. Can anyone share a file or lay it out in a formula?
Add in a row that captures total economics, this is used as one of the ceilings in a catch-up formula. For example, if you had an 50/50 catch-up over a 9 pref. with a 20% profits being the max to the gp, you'd have pref. and ROE formulas and then you'd have a formula that basically says =min(total dist. available - (pref. payment)-(ROE Payment)*.5, (20% of all economics paid out). The total economics should not include ROC.
Dolores quis minima dolor in. Autem nihil impedit illo rerum voluptatem temporibus ut. Dicta ut aliquam nemo rem iste possimus dolor beatae. Sit consequatur nam esse qui praesentium minima vel. Facilis praesentium deserunt laborum tempora animi. Eum ut in voluptate dicta aspernatur sint libero.
Reiciendis fuga rerum laboriosam velit quia. Voluptas quia ea iure quod. Quaerat velit veritatis doloremque voluptas quae impedit quas.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...