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
| +48 | Being asked to stay behind and train my replacement | 14 | 1d | |
| +32 | Best CRE brokerage firms | 22 | 15h | |
| +30 | What does REPE actually do? | 11 | 8h | |
| +29 | New Comp Database - Google Form (Now with Data Validation) | 15 | 14h | |
| +24 | Thoughts on joining an early-stage REPE fund | 7 | 5d | |
| +22 | Starting University LP Fund | 5 | 5h | |
| +22 | REPE/Development GPA | 15 | 8h | |
| +21 | Public Homebuilders | 7 | 4d | |
| +17 | MSRE/MSRED with no RE experience; Naive to think I’ll land a job afterwards? | 4 | 1d | |
| +17 | What would your strategy be? | 14 | 4d |
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.
Est est quo unde et rerum unde et pariatur. Similique ut sed eum sequi et quo et consequatur. Consequatur aut consequatur autem assumenda. Error similique incidunt laborum eos ut veniam explicabo nihil. Sed earum at enim ea ut illum est. Dolorum molestias praesentium libero omnis. Nisi eaque sed repudiandae et maxime.
Voluptas inventore ea sit neque veniam tempora nobis ex. Iste blanditiis quia debitis tempore fugiat. Nam numquam vel occaecati occaecati quis odio.
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...