Build a Full ABS Cashflow Waterfall Model (Excel + Python)
Budget: ₹600 – ₹1,500 INR
Build a Full ABS Cashflow Waterfall Model (Excel + Python)
I am looking for an experienced financial modeler (preferably with ABS/structured finance experience) to build a complete Auto-Loan ABS Cashflow Waterfall Model in both Excel and Python.
The model should replicate real-world ABS deal mechanics and should be clean, modular, and easy to audit.
Please see requirements below.
1. Collateral Modeling
• Build a monthly loan-level or pool-level engine for Auto ABS.
• Include:
• Scheduled amortization (WAC, WAM)
• Prepayments (CPR → SMM logic)
• Defaults (CDR → MDR logic)
• Loss severity (LGD)
• Recovery with lag
• Cumulative loss curve generation
• Output:
• Monthly interest, scheduled principal, prepayments, defaults, recoveries, ending balance.
2. Liability & Tranche Structure
Create a dynamic tranche structure including:
• Senior, Mezzanine, and Subordinate tranches
• User-defined:
• Initial principal
• Coupon rate
• Legal final maturity
• Monthly calculations:
• Beginning balance
• Interest due
• Interest paid / shortfalls
• Principal paid
• Ending balance
• WAL, duration, yield
⸻
3. Waterfall Priority of Payments
Full monthly ABS waterfall including:
1. Servicing fees
2. Trustee/admin fees
3. Senior → Mezz → Sub interest payments
4. Senior → Mezz → Sub principal payments (sequential)
5. Excess spread to residual/equity
6. Triggers (basic Overcollateralization / Interest Coverage optional)
Waterfall must link seamlessly with collateral and tranche sheets.
⸻
4. Scenario & Stress Testing
• Ability to run Base / Moderate / Severe scenarios.
• Adjust CPR, CDR, Severity, and recovery lag.
• Output tables + charts for:
• Cash flow distribution
• Losses by tranche
• WAL changes
• Yield changes
• Break-even loss levels
⸻
5. Python Version
Replicate the entire logic in Python using pandas:
• Collateral engine
• Tranche engine
• Waterfall engine
• Scenario functions
• Output tables & charts
Clean, modular, object-oriented structure preferred.
⸻
6. Deliverables
• Fully functional Excel model with clear formatting and documentation.
• Python script/notebook with complete logic and comments.
• A short read-me / documentation explaining:
• Structure
• Assumptions
• How to modify the model
• Optional (bonus):
• Ability to ingest a Bloomberg CFT export (if feasible)
I am looking for an experienced financial modeler (preferably with ABS/structured finance experience) to build a complete Auto-Loan ABS Cashflow Waterfall Model in both Excel and Python.
The model should replicate real-world ABS deal mechanics and should be clean, modular, and easy to audit.
Please see requirements below.
1. Collateral Modeling
• Build a monthly loan-level or pool-level engine for Auto ABS.
• Include:
• Scheduled amortization (WAC, WAM)
• Prepayments (CPR → SMM logic)
• Defaults (CDR → MDR logic)
• Loss severity (LGD)
• Recovery with lag
• Cumulative loss curve generation
• Output:
• Monthly interest, scheduled principal, prepayments, defaults, recoveries, ending balance.
2. Liability & Tranche Structure
Create a dynamic tranche structure including:
• Senior, Mezzanine, and Subordinate tranches
• User-defined:
• Initial principal
• Coupon rate
• Legal final maturity
• Monthly calculations:
• Beginning balance
• Interest due
• Interest paid / shortfalls
• Principal paid
• Ending balance
• WAL, duration, yield
⸻
3. Waterfall Priority of Payments
Full monthly ABS waterfall including:
1. Servicing fees
2. Trustee/admin fees
3. Senior → Mezz → Sub interest payments
4. Senior → Mezz → Sub principal payments (sequential)
5. Excess spread to residual/equity
6. Triggers (basic Overcollateralization / Interest Coverage optional)
Waterfall must link seamlessly with collateral and tranche sheets.
⸻
4. Scenario & Stress Testing
• Ability to run Base / Moderate / Severe scenarios.
• Adjust CPR, CDR, Severity, and recovery lag.
• Output tables + charts for:
• Cash flow distribution
• Losses by tranche
• WAL changes
• Yield changes
• Break-even loss levels
⸻
5. Python Version
Replicate the entire logic in Python using pandas:
• Collateral engine
• Tranche engine
• Waterfall engine
• Scenario functions
• Output tables & charts
Clean, modular, object-oriented structure preferred.
⸻
6. Deliverables
• Fully functional Excel model with clear formatting and documentation.
• Python script/notebook with complete logic and comments.
• A short read-me / documentation explaining:
• Structure
• Assumptions
• How to modify the model
• Optional (bonus):
• Ability to ingest a Bloomberg CFT export (if feasible)