Equity Waterfall Excel Example
August 30, 2023
by a searcher from The University of Georgia - Terry College of Business in Austin, TX, USA
I am building my pro-forma investment model for an acquisition and am planning to utilize the following financing structure:
Funding: 80% Seller Debt at 12%, 15% Preferred Equity at 10%, 5% Common equity.
Returns: 50-50 catch-up to a 20% IRR, then 80%-20% Common-Preferred split
I wanted to see if anyone would be willing to share their excel model that contains an equity waterfall section for a scenario anywhere close to this.
Thanks!
in Charlotte, NC, USA
I posed your question as a query to ChatGPT:
-----------------------
I am a deal maker who is building my pro-forma investment model for an acquisition and am planning to utilize the following financing structure. I am planning to use the following structure: Funding: 80% Seller Debt at 12%, 15% Preferred Equity at 10%, 5% Common equity. Returns: 50-50 catch-up to a 20% IRR, then 80%-20% Common-Preferred split. Build me a CSV model that contains an equity waterfall section for a scenario anywhere close to this.
-----------------------
Below is the reply (please note that I didn't verify the numbers nor the breakdown)
-----------------------
To help you with the equity waterfall section for this pro-forma investment model, I will generate a CSV model structure that includes the funding and returns you specified. Here’s a breakdown of the key components you mentioned: Funding Structure: - Seller Debt: 80% at 12% interest - Preferred Equity: 15% at 10% return -Common Equity: 5%
Returns Structure: - 50-50 catch-up to a 20% IRR - After the catch-up, 80%-20% split between common and preferred equity.
The CSV model will include: - Cash Flows (in terms of principal and interest), - Distribution Sequence (first to debt holders, then to preferred equity holders, and finally common equity holders), - Catch-up provision, I- RR target and splits after the catch-up.
I'll start by preparing this model in CSV format for you. Let me first generate the template with some data for you to work with:
===========================
Year, Seller Debt, Preferred Equity, Common Equity, Total Investment, Seller Debt Interest, Preferred Equity Interest, Catch-Up, Common Equity Return, Preferred Equity Return, Common Equity Final Distribution, Preferred Equity Final Distribution, Total Cash Flow Distribution 1, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 2, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 3, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 4, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 5, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 6, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 7, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 8, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 9, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0 10, 800000, 150000, 50000, ###-###-#### , 96000, 15000, 0, 0, 0, 0, 0
===========================
This model includes the debt interest and preferred equity returns for each year and helps track distributions, including a placeholder for the catch-up. Since I am building a waterfall model, let's confirm: The total investment amount: The funding sources (Seller Debt, Preferred Equity, and Common Equity) are $1,000,000 combined. You can adjust the cash flows based on actual performance. Let me know if you'd like to tweak it, such as adding specific assumptions, cash flow scenarios, or if you would like more details on the waterfall structure calculations.
in Jaipur, Rajasthan, India