Project Equity IRR Distribution Waterfall Calculator

Purpose and model boundary

This model allocates ten periods of equity contributions and project distributions between a limited partner (LP) and general partner (GP). It supports pro rata ownership or a preferred-return waterfall with return of capital, simple or compound LP preference, optional cumulative GP catch-up, residual carried interest, investor cash flows, periodic IRR, and MOIC.

It is a transparent allocation model, not a partnership agreement, legal interpretation, security valuation, investment recommendation, tax allocation, fairness opinion, or performance guarantee. Waterfall labels describe the workbook's arithmetic and do not determine the meaning of transaction documents.

Inputs and units

Contributions, distributions, capital, preference, and promote use one user-consistent currency scale, shown as currency millions. IRR is a fraction per modeled period, and MOIC is a multiple. The fixed grid contains exactly ten uniquely labeled, equally spaced periods with nonnegative equity contributions and project distributions.

The waterfall route is pro rata ownership or preferred return and carry. Preference accrues simply or compounds. GP catch-up is on or off. LP ownership is from 1% through 99%, preferred return is from 0 through 100% per period, and carried interest is below 100%.

Governing relationships

Each contribution is divided by ownership:

LP contribution_t = Total contribution_t × LP ownership

GP contribution_t = Total contribution_t - LP contribution_t

Those amounts add to unreturned LP and GP capital before the period's distribution. In preferred mode, preference accrues on LP unreturned capital after the current contribution. Simple mode adds LP capital_t × preferred rate to prior unpaid preference. Compound mode also grows prior unpaid preference by the preferred rate.

Project cash first returns LP and GP capital in proportion to their unreturned balances. Remaining cash then pays accrued LP preference. With GP catch-up enabled, the current catch-up target includes any prior catch-up shortfall and the cumulative entitlement created by paid LP preference:

Catch-up entitlement added = LP preference paid × Carry / (1 - Carry)

Cash remaining after catch-up is split:

LP residual = Remaining residual × (1 - Carry)

GP residual carry = Remaining residual × Carry

The LP receives capital return, LP preference, and LP residual. The GP receives capital return, catch-up, and residual carry. In pro rata mode, total project distributions are allocated directly by ownership, while the capital-return calculation still reduces each investor's unreturned capital up to its balance.

Project cash flow is project distributions less contributions. LP and GP cash flows are their respective distributions less contributions. When a series contains at least one negative and one positive value, the workbook applies Excel IRR with its default 10% starting guess. MOIC is total investor distributions divided by total investor contributions, or zero when the denominator is zero.

Calculation sequence

1. Validate waterfall, preference, and catch-up routes, scalar bounds, and ten unique period rows.
2. Allocate each contribution between LP and GP and add it to unreturned capital.
3. Accrue simple or compound LP preference in the preferred route.
4. Return capital from available project cash.
5. Pay LP preference, then any enabled cumulative GP catch-up.
6. Split remaining preferred-route cash between LP residual and GP carried interest, or allocate pro rata cash directly by ownership.
7. Roll forward investor capital, unpaid preference, and catch-up shortfall; build project, LP, and GP cash-flow series.
8. Sum contributions and distributions, calculate IRRs, MOICs, and allocation closure, populate the LP/GP distribution chart, and evaluate status.

Outputs and interpretation

The ordered flow shows contributions, ending unreturned capital after the capital-return tier, LP preference paid and unpaid, GP promote paid, total distribution allocation, and investor returns. GP promote equals GP catch-up plus residual carried-interest distributions and excludes GP return of capital.

Project, LP, and GP IRRs are gross periodic returns for equally spaced rows. They are not annualized unless each row is one year. LP and GP MOIC compare total distributions with total contributions. The allocation check is project distributions less LP and GP distributions and should close to zero within the workbook tolerance.

Validation and status logic

The workbook evaluates status in this order:

The derived gate requires the absolute allocation check to remain within 0.000000001 × max(1, total project distributions). A missing sign pattern sets guarded IRR outputs to zero and returns CHECK rather than an Excel error.

Assumptions and limitations

• All periods are equally spaced. The model uses periodic IRR, not dated XIRR.
• Preferred return accrues once per period on LP unreturned capital. Compound mode also accrues on unpaid preference.
• The enabled GP catch-up is cumulative. Unpaid catch-up survives to later periods and is paid before later residual cash.
• Non-conventional cash flows can have multiple IRRs. The reported root follows Excel's default 10% starting guess.
• Multiple investor classes, fee offsets, clawback, tax distributions, recycling, true-ups, additional hurdle tiers, and legal European/American waterfall interpretation are excluded.
• Returns are gross, pre-fee, and pre-tax.
• All shipped ownership, return, carry, contribution, and distribution values are synthetic illustrations.

Restrictions and non-computing states

The grid must contain exactly ten complete rows. Labels must contain between 1 and 60 characters after trimming and be unique without regard to case. Contributions and distributions are nonnegative and within the published limits. LP ownership is from 0.01 through 0.99, preferred return is from 0 through 1 per period, and carried interest is from 0 through 0.99.

A contribution-only schedule is valid allocation input but returns the documented IRR sign-pattern CHECK. Zero preference and zero carry are valid. Inputs for preference and catch-up remain in the form in pro rata mode but do not change the pro rata distribution result. Errors in the entered values and in the shape of the grid are rejected before calculation; relational label failures return workbook NOT VALID.

Errors and warnings

A rejected entry means an option, number, or fixed-grid entry failed the published input rules. Workbook NOT VALID means the active allocation domain or derived arithmetic failed. Workbook CHECK preserves allocations and MOICs while explaining why project IRR cannot be supported from the sign pattern. A connection or calculation-service failure is not an investment result. OK confirms the modeled allocation and guarded-return arithmetic only.

The periodic IRR identity follows the public definition in the GAO Glossary of Systems Analysis and Planning, Programming, Budgeting. Sponsor equity and project-finance context follows the OCC Comptroller's Handbook: Commercial Real Estate Lending. No partnership agreement, external transaction structure, market data, or proprietary waterfall is embedded.