Commercial Credit Debt Service Coverage Modeling Specification

Role: Senior Commercial Credit Risk Officer and Financial Modeling Lead specializing in structured corporate debt underwriting.

Context

• Target borrower sector: {{target_borrower_sector}}
• Credit facility hierarchy: {{credit_facility_hierarchy}}
• Macroeconomic stress vectors: {{macro_stress_vectors}}
• Financial covenant thresholds: {{covenant_threshold_matrix}}
• Primary cash flow drivers: {{cash_flow_driver_logic}}
• Collateral valuation and haircut schedules: {{collateral_haircut_rules}}

Task

Generate a comprehensive financial model design specification for a multi-scenario commercial credit underwriting workbook that models Debt Service Coverage Ratios (DSCR), dynamic leverage profiles, and collateral adequacy under baseline and stressed conditions.

Method

1. Define the fundamental chart of accounts and dynamic Three-Statement financial statement integration tailored to {{target_borrower_sector}}.
2. Detail the exact algebraic formulas for EBITDA adjustments, Free Cash Flow conversion, and Available Cash Flow for Debt Service (CFADS) based on {{cash_flow_driver_logic}}.
3. Establish the debt waterfall calculation engine mapping amortization, interest payments, cash sweeps, and payment priority across {{credit_facility_hierarchy}}.
4. Design the covenant monitoring module calculating rolling 12-month DSCR, Fixed Charge Coverage (FCCR), and Senior Debt-to-EBITDA against {{covenant_threshold_matrix}}.
5. Construct the stress-testing matrix incorporating multi-variable shocks from {{macro_stress_vectors}} across revenue, input margin compression, and benchmark interest rate hikes.
6. Formulate the borrowing base and collateral liquidation valuation logic applying markdown percentages from {{collateral_haircut_rules}}.
7. Detail the model validation framework, including circularity breakers, dynamic error checks, and auditable assumption change logs.

Constraints

• MUST define explicit formulas for every financial metric using standard financial modeling naming syntax (e.g., Excel/VBA/Python naming conventions).
• MUST NOT permit unhedged floating interest rate assumptions without explicit baseline forward curve modeling.
• Include both point-in-time snapshot formulas and trailing-twelve-month (TTM) dynamic roll logic.
• Assumptions must account for seasonality and working capital swings typical in commercial lending.

Output format

Provide the specification structured under these mandatory sections:

1. Model Architecture & Structural Overview
2. Cash Flow Driver Mechanics & Adjusted EBITDA Definitions
3. Debt Amortization Waterfall & Seniority Schedule
4. Dynamic Covenant Compliance Engine
5. Multi-Vector Sensitivity & Macro Stress Testing Matrix
6. Collateral Assessment Engine & Model Auditing Controls Total length should be between 1,200 and 1,800 words.

Self-review

1. Verify that all facility tiers in the hierarchy are sequenced accurately within the debt waterfall.
2. Ensure the stress test methodology simulates simultaneous margin compression and interest rate escalation.
3. Confirm that all covenant calculations explicitly handle potential negative denominator or zero-cash edge cases.