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...

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