1. **Problem Statement:** Calculate the amounts returned to the Firm, Funder, and Third Party Intermediary based on the given cash flows and waterfall distribution rules. 2. **Key Formulas and Rules:** - Step One: Funder receives 100% of Distributions until Advances plus 15% return (non-compounding, daily accrual) are paid. - Steps Two to Six: Funder receives a percentage of next $100 million increments, Firm retains the rest. - Step Seven: Firm retains all remaining Distributions. - Bellwether Limits apply to Bellwether Case distributions for 3 years. - After Funder receives 1.1x Advances, Funder gets 85% and Third Party Intermediary 15% of further Distributions. 3. **Calculate Total Advances and Accrued Return:** - Total Advances $= 5,000,000 + 2,000,000 = 7,000,000$ (Scenario A example) - Return owed $= 7,000,000 \times 0.15 = 1,050,000$ - Total owed to Funder in Step One $= 7,000,000 + 1,050,000 = 8,050,000$ 4. **Apply Distributions in Order:** - Jan 21, 2021 Bellwether $8,000,000$: - Apply Bellwether Limits (Year 1: max 25% of distribution and 25% of Advances minus prior distributions). - Max Bellwether to Funder $= \min(0.25 \times 8,000,000, 0.25 \times 7,000,000) = \min(2,000,000, 1,750,000) = 1,750,000$ - Since Step One owed is $8,050,000$, $8,000,000$ goes to Funder (limited by Bellwether to $1,750,000$), remainder $6,250,000$ unpaid. - Aug 30, 2022 Distribution $6,000,000$ (non-Bellwether): - Remaining Step One owed $8,050,000 - 1,750,000 = 6,300,000$ - Entire $6,000,000$ goes to Step One to Funder. - Sep 25, 2023 Distribution $10,000,000$: - Remaining Step One owed $6,300,000 - 6,000,000 = 300,000$ - $300,000$ to Step One to Funder. - Remaining $9,700,000$ goes to Step Two: Funder 35% = $3,395,000$, Firm 65% = $6,305,000$. 5. **Check if Funder reached 1.1x Advances:** - 1.1x Advances $= 7,000,000 \times 1.1 = 7,700,000$ - Total to Funder so far $= 1,750,000 + 6,000,000 + 300,000 + 3,395,000 = 11,445,000 > 7,700,000$ - After $7,700,000$, split 85% to Funder, 15% to Third Party Intermediary. 6. **Summary of Distribution:** - Funder receives Step One amount plus Step Two portion up to 1.1x Advances. - Excess distributions split 85% Funder, 15% Third Party. - Firm receives remainder per waterfall steps. 7. **Excel Formula Suggestions:** - Use simple SUM for Advances. - Calculate accrued return as $=SUM(Advances)*0.15$. - Use IF and MIN functions to apply Bellwether Limits. - Use cumulative sums to track total paid to Funder. - Use simple percentage calculations for waterfall splits. **Final Answer:** - Amount returned to Funder includes Advances plus 15% return, plus waterfall percentages, adjusted for Bellwether Limits and Third Party split. - Amount to Third Party Intermediary is 15% of Distributions after Funder receives 1.1x Advances. - Firm retains remainder per waterfall. This approach standardizes calculations for any cash flow using simple Excel formulas like SUM, MIN, IF, and basic arithmetic.