Multi Tranche Loan Amortization Covenant Calculator

Purpose and model boundary

This model rolls four synthetic debt tranches through as many as seven annual periods. It applies mandatory amortization, optional priority cash sweeps, all-in or benchmark-plus-spread interest, an illustrative EBITDA-to-CFADS conversion, and three entered covenant tests. It is a debt-schedule and arithmetic screening model, not a borrowing base, legal covenant interpretation, solvency opinion, credit decision, or financing commitment.

Inputs and units

The three route selectors choose all-in versus benchmark-plus-spread rates, beginning versus average-after-mandatory interest balance, and no sweep versus excess-cash sweep. Term is one through seven whole years. EBITDA, cash, debt balances, and reported debt service are in user-currency millions. Growth, cash taxes, capital expenditure, change in net working capital, tranche rates, mandatory amortization, and sweep percentage are annual fractions. Leverage, interest coverage, and DSCR thresholds are multiples.

The fixed five-column grid contains exactly four tranches: text name, opening balance, all-in rate or spread, mandatory amortization rate, and integer sweep priority. Names and priorities must be unique. Benchmark is active only in benchmark-plus-spread mode, and sweep percentage is active only in excess-cash-sweep mode.

Governing relationships

For year t, EBITDA grows from year one as:

EBITDA_t = opening EBITDA × (1 + growth)^(t-1)

The simplified cash flow available for debt service is:

CFADS_t = EBITDA_t × (1 - cash tax % - capex % - change in NWC %)

For tranche j, let B_j,t be beginning balance, B_j,0 original balance, a_j mandatory amortization rate, and r_j the all-in rate or entered spread. Mandatory principal and post-mandatory balance are:

Mandatory_j,t = min(B_j,t, B_j,0 × a_j)

PostMandatory_j,t = B_j,t - Mandatory_j,t

The active interest rate is r_j in all-in mode and benchmark + r_j in spread mode. Interest uses either B_j,t or (B_j,t + PostMandatory_j,t)/2, according to the selected balance basis. Cash before sweep is:

CashBeforeSweep_t = BeginningCash_t + CFADS_t - total interest_t - total mandatory_t

In sweep mode, the available pool is:

SweepPool_t = max(0, CashBeforeSweep_t - minimum cash) × sweep %

The pool is applied in unique priority order 1 through 4, limited at every priority by the matching post-mandatory balance. Ending cash subtracts the allocated sweep, and each tranche's ending balance subtracts its priority allocation.

Annual covenant measures are:

Debt/EBITDA_t = ending total debt_t / EBITDA_t

Interest coverage_t = EBITDA_t / total interest_t

DSCR_t = CFADS_t / (total interest_t + total mandatory_t)

When a coverage denominator is zero, the workbook returns the disclosed sentinel 9,999 rather than dividing by zero.

Calculation sequence

1. Validate routes, active scalar assumptions, the minimum-cash relationship, and four complete unique tranches and priorities.
2. Calculate year-specific EBITDA and beginning cash, rolling prior ending cash and tranche balances into the next year.
3. Apply original-balance mandatory amortization, choose each tranche's interest rate and balance basis, and calculate cash interest.
4. Convert EBITDA to CFADS and calculate cash before sweep.
5. Calculate the optional sweep pool and allocate it sequentially by priorities 1 through 4 without taking a tranche below zero.
6. Calculate ending debt and cash plus leverage, interest coverage, DSCR, and annual breach flag.
7. Aggregate ending debt, cumulative mandatory amortization, sweep, interest, worst covenant values, breach count, first breach year, and ending cash.
8. Verify debt roll-forward closure and nonnegative debt/cash, then build the ending-debt and EBITDA chart.

Outputs and interpretation

Headline results show ending debt, cumulative mandatory amortization and sweep, minimum interest coverage and DSCR, maximum debt/EBITDA, and the number of annual breaches. Supporting values include initial debt, cumulative cash interest, first breach year, and ending cash. First breach year is zero when none occurs.

A breach flag is set when any active year has debt/EBITDA above its maximum, interest coverage below its minimum, or DSCR below its minimum. The chart compares the modeled debt paydown path with EBITDA; it does not forecast either value independently of the entered assumptions.

Validation and status logic

The workbook evaluates status in this order:

The derived closure requires initial debt minus ending debt minus cumulative mandatory amortization minus cumulative sweep to be within 1e-8 × max(1, initial debt). Input and derived failures take precedence over covenant checks.

Assumptions and limitations

• Mandatory amortization is based on original balance and is limited by current balance.
• The benchmark-plus-spread route applies one benchmark to all four tranches.
• Cash sweeps are discretionary in this model and are excluded from the DSCR denominator.
• The CFADS build is an illustrative EBITDA conversion, not a tax model or three-statement forecast.
• There is no revolver draw, borrowing base, PIK, fee amortization, default interest, prepayment premium, tax shield, collateral, rating, covenant cure, basket, holiday, or legal-document nuance.
• Passing entered thresholds proves only arithmetic compliance with those inputs, not legal compliance, credit quality, liquidity, or solvency.

Restrictions and non-computing states

The request always contains exactly four complete tranches. Names must be nonblank text of at most 40 characters and remain unique after trimming, cleaning, case normalization, and nonbreaking-space removal. Sweep priorities must be the unique integers 1 through 4. Opening balances are nonnegative; tranche rates/spreads and mandatory rates are from 0 through 1. Minimum cash cannot exceed opening cash. A negative linked cash or debt balance, nonfinite covenant measure, or failed debt closure prevents a result.

Errors and warnings

This calculator refuses unknown options, malformed grid shape, blank cells, quoted numerics, and entries outside their allowed ranges before calculation. Workbook NOT VALID distinguishes invalid assumptions/tranches from an invalid roll-forward. Workbook CHECK means the schedule calculated but at least one entered covenant threshold was breached. A connection or calculation-service failure is not a covenant finding or a zero-debt result.

Commercial-credit and repayment-capacity context follows the OCC Commercial Credit Handbooks. Cash-flow, leverage, and loan-risk control context follows the FDIC Risk Management Manual of Examination Policies. The workbook independently implements the disclosed arithmetic and embeds no borrower, lender, rating, or transaction data.