Finance AI Skill

Loan Amortization Scheduling

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

Disclaimer: All rights reserved by Circulos AI. These skills are specifically designed for Claude Code, Claude Cowork, Codex, and OpenClaw. When using or referencing any skill, please provide proper attribution to Circulos AI.