--- name: loan-amortization-scheduling description: Create and manage loan amortization schedules for term loans, revolving facilities, mortgages, and other debt instruments including payment calculations, principal/interest breakdowns, prepayment analysis, and debt service tracking. Use when setting up new debt, calculating monthly payments, analyzing prepayment impacts, tracking debt service coverage, preparing debt schedules for financial models, or managing multi-loan portfolios. Triggers on phrases like "loan amortization", "amortization schedule", "debt schedule", "monthly payment", "principal interest breakdown", "prepayment calculation", "debt service", "loan payoff", "balloon payment", "interest-only period". --- # Loan Amortization & Scheduling Build accurate loan amortization schedules to track debt service, analyze prepayment impacts, and manage the company's debt portfolio. ## Workflow ### 1. Amortization Schedule Construction ``` AMORTIZATION SCHEDULE — Term Loan A ═══════════════════════════════════════ LOAN PARAMETERS: Principal: $5,000,000 Annual interest rate: 6.50% Monthly rate: 0.5417% (6.50% / 12) Term: 60 months (5 years) Payment frequency: Monthly First payment date: February 15, 2024 Last payment date: January 15, 2029 Payment type: Fully amortizing (fixed payment) MONTHLY PAYMENT CALCULATION: P = [r × PV] / [1 - (1+r)^(-n)] P = [0.005417 × $5,000,000] / [1 - (1.005417)^(-60)] P = $27,083.33 / [1 - 0.7237] P = $27,083.33 / 0.2763 P = $97,986/month AMORTIZATION SCHEDULE (first 12 months): ═══════════════════════════════════════ Month Payment Interest Principal Balance Cumul Principal ───── ───────── ───────── ───────── ───────── ───────────────── 1 $97,986 $27,083 $70,903 $4,929,097 $70,903 2 $97,986 $26,685 $71,301 $4,857,796 $142,204 3 $97,986 $26,285 $71,701 $4,786,095 $213,905 4 $97,986 $25,882 $72,104 $4,713,991 $286,009 5 $97,986 $25,477 $72,509 $4,641,482 $358,518 6 $97,986 $25,070 $72,916 $4,568,566 $431,434 7 $97,986 $24,660 $73,326 $4,495,240 $504,760 8 $97,986 $24,248 $73,738 $4,421,502 $578,498 9 $97,986 $23,833 $74,153 $4,347,349 $652,651 10 $97,986 $23,416 $74,570 $4,272,779 $727,221 11 $97,986 $22,997 $74,989 $4,197,790 $802,210 12 $97,986 $22,575 $75,411 $4,122,379 $877,621 ───── ───────── ───────── ───────── ───────── ───────────────── YTD Total $1,175,832 $293,199 $882,633 $4,117,367 $882,633 KEY OBSERVATIONS: → Interest portion declines over time (front-loaded interest) → Principal portion accelerates (back-loaded principal) → After 12 months: 17.7% of original principal paid → Total interest paid in Year 1: $293,199 (effective cost: 5.86% of avg balance) → Loan-to-value remaining: $4.12M / $5.0M = 82.4% ``` ### 2. Interest-Only + Bullet Payment Structure ``` INTEREST-ONLY LOAN WITH BALLOON PAYMENT ═══════════════════════════════════════ LOAN PARAMETERS: Principal: $3,000,000 Interest rate: 7.25% (fixed) Interest-only period: 24 months Balloon payment: Full principal at month 24 Monthly interest payment: $18,750 ($3M × 7.25% / 12) SCHEDULE: ═══════════════════════════════════════ Month Payment Interest Principal Balance ───── ───────── ───────── ───────── ───────── 1 $18,750 $18,750 $0 $3,000,000 2 $18,750 $18,750 $0 $3,000,000 ... ... ... ... ... 24 $18,750 $18,750 $0 $3,000,000 24+ $3,000,000 $0 $3,000,000 $0 TOTAL INTEREST PAID: $18,750 × 24 = $450,000 TOTAL CASH PAID: $450,000 + $3,000,000 = $3,450,000 RISK CONSIDERATIONS: → Refinancing risk: Must refinance or sell asset at month 24 → Interest rate risk: New loan at potentially higher rates → Cash flow risk: Balloon payment requires significant capital → Covenant risk: LTV ratios at balloon date may restrict refinancing MITIGATION: → Build amortization reserve (set aside monthly for balloon) → SDF (Sweep Debt Facility) to force gradual paydown → Lock in refinancing terms early → Maintain strong DSCR to qualify for new financing ``` ### 3. Prepayment Analysis ``` PREPAYMENT ANALYSIS ═══════════════════════════════════════ CURRENT LOAN STATUS: Original principal: $5,000,000 Remaining balance: $4,122,379 (after 12 payments) Original term: 60 months Remaining term: 48 months Current monthly payment: $97,986 SCENARIO: Make additional $500,000 prepayment at month 13 OPTION A: Reduce Payment (keep term at 48 months) ═══════════════════════════════════════ New principal: $4,122,379 - $500,000 = $3,622,379 New monthly payment (48 months @ 6.50%): P = [0.005417 × $3,622,379] / [1 - (1.005417)^(-48)] P = $19,621.43 / [1 - 0.7729] P = $19,621.43 / 0.2271 P = $86,348/month Payment reduction: $97,986 - $86,348 = $11,638/month Total interest saved: ~$85,000 over remaining life OPTION B: Reduce Term (keep payment at $97,986) ═══════════════════════════════════════ New principal: $3,622,379 Monthly payment: $97,986 Solve for n: n = -ln(1 - r×PV/P) / ln(1+r) n = -ln(1 - 0.005417×$3,622,379/$97,986) / ln(1.005417) n = -ln(1 - 0.2002) / 0.005401 n = -ln(0.7998) / 0.005401 n = 0.2233 / 0.005401 n = 41.3 months Term reduction: 48 - 41.3 = 6.7 months Total interest saved: ~$110,000 over remaining life RECOMMENDATION: → If cash flow constrained: OPTION A (lower monthly payment) → If minimizing total cost: OPTION B (shorter term, more interest saved) → Consider prepayment penalty: If any, net against savings PREPAYMENT PENALTY CHECK: → Original agreement: 2% declining over 3 years → Current penalty (month 13): ~1.33% of prepayment amount → Penalty on $500K: $6,667 → Net interest savings (Option B): $110,000 - $6,667 = $103,333 ✓ ``` ### 4. Multi-Loan Debt Schedule ``` CONSOLIDATED DEBT SCHEDULE ═══════════════════════════════════════ LOAN PORTFOLIO: ═══════════════════════════════════════ Loan Principal Rate Term Monthly Payment Remaining Balance ────────────────────────────────────────────────────────────────────────────────── Term Loan A $5,000,000 6.50% 60 mo $97,986 $4,122,379 Term Loan B $2,000,000 5.75% 36 mo $60,276 $1,456,200 Revolver $100,000,000 SOFR+ Revolving $0 (undrawn) $0 Lines of Credit $5,000,000 4.50% 12 mo $427,907 $3,200,000 Convertible Note $1,000,000 8.00% 24 mo Interest only $1,000,000 ────────────────────────────────────────────────────────────────────────────────── TOTAL DEBT OUTSTANDING: $9,778,579 DEBT SERVICE (monthly): Term Loan A payment: $97,986 Term Loan B payment: $60,276 Line of Credit payment: $427,907 Convertible Note interest: $6,667 ───────────────────────────────────────────── Total monthly debt service: $592,836 Annual debt service: $7,114,032 DEBT SERVICE COVERAGE RATIO: EBITDA: $14,000,000 Less: CapEx (maintenance): ($1,000,000) = Adjusted EBITDA: $13,000,000 DSCR = Adj EBITDA / Annual Debt Service DSCR = $13,000,000 / $7,114,032 = 1.83x Covenant requirement: ≥ 1.25x Status: ✓ IN COMPLIANCE (1.83x vs 1.25x) MATURITY WALL (by year): ═══════════════════════════════════════ Year Principal Due Remaining Term Rollover Risk ──────────────────────────────────────────────────────────── 2024 $1,123,800 — Low 2025 $956,200 — Low (LOC renewal) 2026 $1,725,000 — Medium (Term B matures) 2027 $2,250,000 — Medium (Term A matures) 2028 $500,000 — Low (Convertible matures) 2029 $3,223,579 — High (Revolver matures) ──────────────────────────────────────────────────────────── TOTAL $10,000,000 WEIGHTED AVERAGE COST OF DEBT: → Weighted avg rate: 5.85% → Pre-tax cost: 5.85% → After-tax cost (21% tax): 5.85% × (1 - 0.21) = 4.62% ``` ### 5. Debt Accounting & Journal Entries ``` DEBT JOURNAL ENTRIES ═══════════════════════════════════════ MONTHLY DEBT SERVICE: ═══════════════════════════════════════ Term Loan A (Month 13): Dr Interest Expense $22,150 Dr Debt — Term Loan A $75,836 Cr Cash $97,986 (Record monthly debt service: interest $22,150 + principal $75,836) DEBT ISSUANCE (new loan): ═══════════════════════════════════════ Dr Cash $5,000,000 Cr Debt — Term Loan $5,000,000 (Record proceeds from Term Loan A) Dr Debt Issuance Costs (asset) $50,000 Cr Cash $50,000 (Record origination fees, legal, due diligence) Amortize debt issuance costs (effective interest method): Dr Interest Expense $833/month Cr Debt Issuance Costs $833 ($50,000 / 60 months = $833/month) DEBT PREPAYMENT: ═══════════════════════════════════════ Dr Debt — Term Loan A $500,000 Cr Cash $500,000 (Record prepayment of principal) Dr Interest Expense $6,667 Cr Cash $6,667 (Record prepayment penalty, if applicable) DEBT MODIFICATION: ═══════════════════════════════════════ If debt terms modified (rate change, extension): 1. Calculate present value of modified terms 2. Compare to carrying amount of original debt 3. If >10% different: Derecognize old debt, recognize new 4. If <10% different: Adjust carrying amount prospectively ``` ## Edge Cases - **Variable-rate debt**: Recalculate payment schedule at each rate reset; model rate scenario impacts - **Sweep provisions**: Excess cash flow triggers mandatory principal payments; model sweep scenarios - **Covenant compliance**: Monitor quarterly; stress-test under adverse scenarios - **Foreign currency debt**: Translate to reporting currency; track unrealized FX gains/losses on debt - **Convertible debt**: Calculate if-conversion impact; track conversion price and dilution ## Integration Points - **ERP/GL**: Debt recording, payment processing, amortization - **Debt management systems**: Loan administration, covenant tracking - **Banking platforms**: Loan servicing, payment processing - **Treasury systems**: Debt portfolio monitoring, cash application - **Financial models**: Debt schedule integration for projections - **Covenant monitoring tools**: Automated compliance tracking ## Output ### Debt Portfolio Summary ``` DEBT PORTFOLIO SUMMARY — March 2024 ═══════════════════════════════════════ TOTAL DEBT OUTSTANDING: $9,778,579 WEIGHTED AVG COST OF DEBT: 5.85% (pre-tax) AFTER-TAX COST OF DEBT: 4.62% DEBT SERVICE COVERAGE RATIO: 1.83x (covenant: ≥1.25x) ✓ TOTAL MONTHLY DEBT SERVICE: $592,836 NEXT MATURITY: Term B — Dec 2025 ($956K) COVENANT STATUS: ALL IN COMPLIANCE ✓ AVERAGE TIME TO MATURITY: 3.2 years ```