SmartHelping / Joint Venture Waterfalls / Excel
Preferred Return Model in Excel
Model up to 10 years of investor and sponsor distributions. Enter annual project contributions and cash available for distribution, set the investor's preferred return, and define how the remaining cash is shared.
Versions 1 and 2 are available to download immediately after purchase. By purchasing, you agree to the Terms of Service.
Video walkthrough & spreadsheet previews
See how project cash flows become investor and sponsor distributions.
From project cash flow to participant returns
Set the contributions, preferred return, and profit split.
The waterfall starts with annual project-level contributions and distributions. Your funding percentages and preferred return terms determine the cash flows allocated to each party.
Enter annual project cash flows
Input the equity required and cash available for distribution for each year. The model supports contributions after year 0 and contributions and distributions occurring in the same year.
Define who contributes the equity
Set the investor and sponsor percentages of project-level contributions. These inputs determine how the equity funding is divided between the two parties.
Set the investor's preferred return
Enter the preferred return and choose how unpaid amounts are treated when available cash falls short. The model calculates the investor's preferred return before allocating the remaining profit-sharing cash.
Choose the post-preferred-return split
Enter the investor and sponsor percentages for remaining cash. These can match the contribution percentages or use a different split. The model then calculates each party's cash flows and return outputs.
Preferred return shortfalls
Choose what happens when cash falls short.
Use the accrual and compounding settings to compare how unpaid preferred returns affect later distributions.
Accrue without compounding
Carry unpaid preferred returns forward in an accrual balance without adding them to the base used to calculate future preferred returns. When sufficient cash becomes available, accrued amounts are paid to the investor before profit sharing.
Accrue with compounding
Include unpaid preferred returns in the balance used to calculate the next year's preferred return. The calculation uses equity plus unpaid preferred returns from the prior year-end, and the added balance reduces as those unpaid returns are paid.
Reset each year
Turn off accrual so unpaid preferred returns do not carry forward. Each year's preferred return is treated separately, without retaining earlier shortfalls.
Separate funding and profit-sharing percentages
Keep each party's share of contributions separate from its share of remaining cash. Use matching percentages for a proportional split or enter different percentages for the agreed profit-sharing terms.
Both versions included
One purchase includes two waterfall versions.
Use the screenshot previews above to compare the layouts and distribution logic.
Version 1: Preferred return and profit sharing
Model the investor's preferred return and the remaining cash allocated between investor and sponsor under the selected profit-sharing percentages.
Version 2: Additional profit hurdle
Use the version with an extra profit hurdle triggered by full equity repayment. Version 2 is included alongside Version 1 in the $45 purchase.
Cash flow & return outputs
Review the timing and results for each party.
Annual cash flows
Review the investor and sponsor cash flows year by year, with a chart showing each party's annual results.
Cumulative cash position
Track each party's cumulative cash flow and see when its position reaches break-even. A second chart visualizes the cumulative results.
Investor and sponsor IRR
Review each party's internal rate of return based on its modeled contributions and distributions.
Equity multiple
Review the equity multiple output alongside the cash flow and IRR results when comparing your scenarios.
Detailed scenario walkthrough
Take a closer look at the preferred return scenarios.
Watch the deeper walkthrough to follow the template's settings and cash flow logic across different scenarios.
Custom GP catch-up structures
Need a different catch-up calculation?
Separate custom preferred return models are available with GP catch-up calculations based on a percentage of the LP's total profits received or total distributions received. These structures are offered separately, with pricing provided on request.
More templates in one purchase
Explore the bundles that include this model.
Questions before you start
A few useful details.
What do I enter into the model?
Enter annual project contributions and distributions, investor and sponsor contribution percentages, the preferred return, and the percentages used to split cash after the preferred return. Choose the accrual and compounding settings for unpaid preferred returns.
Do profit shares have to match contribution percentages?
No. The model has separate inputs for contribution percentages and post-preferred-return profit shares, so you can model matching or different splits.
Can unpaid preferred returns accrue without compounding?
Yes. You can carry unpaid amounts forward without compounding, carry them forward with compounding, or reset each year without carrying shortfalls forward.
Can contributions occur after year 0?
Yes. The model supports later-year contributions and allows contributions and distributions in the same year across its 10-year period.
Are both versions included?
Yes. The $45 USD one-time purchase includes Versions 1 and 2. Version 2 adds an extra profit hurdle triggered by full equity repayment.
Are the custom GP catch-up models included?
The custom GP catch-up structures described above are offered separately and priced on request. Contact SmartHelping to discuss which structure fits your scenario.
How do I get the Excel template?
Versions 1 and 2 are available to download immediately after purchase. This template is also included in the Real Estate and Joint Venture bundles linked above.
Investor & sponsor cash flow analysis
Model the preferred return and the cash that follows.
Preferred Return Model in Excel — $45, one-time purchase. Both versions included.
