PERE Watefall Case Study

Hi guys and gals,

Currently struggling with a case study relating to a waterfall. Can you please help? Would be much appreciated! It is the following:

"An equity fund has been raised to invest in residential multifamily properties. The capital contributing partners ("Limited Partners") will contribute all capital to the fund, and will receive a return of their investment along with a preferred return before the manager of the fund ("Manager") receives any incentive compensation ("Carried Interest"). The calculated fund‐level cash flows are included in Exhibit A, and are shown net of leverage.

Based upon the accompanying net cash flows, build a model that displays what the Manager's carried interest would be under the following distribution structure:

• First, 0.0% to the Manager and 100.0% to the Limited Partners until Limited Partners have received a 9.0% internal rate of return on their invested capital, net of distributions received as of such period;
• Second, 15.0% to the Manager and 85.0% to the Limited Partners until the Limited Partners have received a 14.0% internal rate of return on their invested capital, net of distributions received as of such period;
• Third, 50.0% to the Manager and 50.0% to the Limited Partners until the Manager has received an amount equal to 15% of the fund level net profit through such period (as determined on a cash flow basis);
• Fourth, 15.0% to the Manager and 85.0% to the Limited Partners until the Limited Partners have received a 1.80X equity multiple on total Limited Partner invested capital to date;
• Fifth, 100.0% to the Manager and 0.0% to the Limited Partners until the Manager has received an amount equal to 20.0% of the fund level net profit through such period (as determined on a cash flow basis), and thereafter;
• 20.0% to the general partner and 80.0% to the capital contributing partners.

The Exhibit A is : Year 1: Contributions: -127 702 974 ; Distributions: 2 035 024
Year 2: Contributions: -190 652 894 ; Distributions: 9 623 472
Year 3: Contributions: -178 389 545 ; Distributions: 21 465 356
Year 4: Contributions: -157 523 ; Distributions: 31 486 823
Year 5: Contributions: 0 ; Distributions: 38 724 408
Year 6: Contributions: 0 ; Distributions: 257 133 213
Year 7: Contributions: 0 ; Distributions: 358 535 081
Year 8: Contributions: 0 ; Distributions: 323 083 224

The net yearly cash flow is the difference between contributions and distributions. 

The model should include input cells for all of the underlined variables above for dynamic term modification. A table should indicate the amount of cash flow distributed to both the Limited Partners and the Manager pursuant to each tier of the waterfall noted above, and the resulting internal rates of return and equity multiples for both the Limited Partners and the total investment.

Thanks a lot in advance for your help!