**Institutional-Grade Excel Waterfall Model for 2801 Broadway** *(Copy/paste this logic into Excel or Google Sheets. Formatting: Inputs in blue, formulas in black.)* --- ### **Section 1: Assumptions & Inputs** | **Variable** | **Value** | **Notes** | |-----------------------------|--------------------|------------------------------------| | Purchase Price | $950,000 | 6.8% cap rate on pro forma NOI | | Hold Period | 5 years | | | Senior Debt (65% LTV) | $617,500 | 6.5% interest, 30-yr amortization | | Mezzanine Debt (15% LTV) | $142,500 | 10% interest-only | | Equity Contribution (20%) | $190,000 | | | Exit Cap Rate | 6.5% | | | Annual Rent Growth | 4% | | | Operating Expense Growth | 2% | | | AirBnB Occupancy Rate | 65% → 75% | Ramp over 3 years | --- ### **Section 2: Pro Forma Income Statement** **Year 1** | **Line Item** | **Formula** | **Value** | |-----------------------------|------------------------------------------|-----------------| | Gross Rental Income | =SUM(Long-term rents + AirBnB income) | $117,400 | | **Vacancy Loss** | =5% of Gross Rental Income | ($5,870) | | **Effective Gross Income** | =Gross Income - Vacancy | $111,530 | | **Operating Expenses** | =2023 Expenses + 2% growth | ($61,518) | | **NOI** | =EGI - OpEx | **$50,012** | **Year 2–5:** - Rent = Prior year rent * (1 + 4%) - Expenses = Prior year * (1 + 2%) - AirBnB Income: Year 1 = $22,500 → Year 5 = $28,800 (occupancy ramp) --- ### **Section 3: Debt Schedule** **Senior Debt (30-yr amortization):** | **Year** | **Beginning Balance** | **Payment** | **Interest** | **Principal** | **Ending Balance** | |----------|-----------------------|-------------|--------------|---------------|--------------------| | 1 | $617,500 | =PMT(6.5%/12,360,-617500)*12 | $39,638 | $6,912 | $610,588 | | 2 | $610,588 | ... | $39,128 | $7,422 | $603,166 | **Mezzanine Debt (Interest-Only):** | **Year** | **Interest Payment** | |----------|----------------------| | 1–5 | =142,500 * 10% | $14,250 |