Debt is the line most often modelled badly. A facility goes into the workbook as a single balance, reduced each month by a repayment that was estimated once and never revisited, with interest applied at a rate that happened to be correct on the day the model was built. The approach holds together while the business has one loan and nobody asks a question about it.

A debt schedule replaces that with structure. Every facility gets its own block of rows, and each block runs opening balance, drawdowns, repayments, interest, fees and closing balance across every period in the forecast. Those closing balances feed the debt line on the balance sheet and the financing section of the cash flow statement. The same blocks produce the ratios a lender tests each quarter, which means the covenant position falls out of the model and does not have to be rebuilt by hand at reporting time.

This guide covers the structure of a facility schedule, the interest convention question that catches out most builds, the difference between amortising, interest-only and revolver facilities, and the way covenant headroom is presented so a finance manager sees pressure building months before a covenant certificate falls due.

Structure the Schedule Around Facilities

The schedule should be organised facility first, period second. Each facility gets a header block of inputs and a grid underneath it, with the periods running across the columns. The inputs hold everything that is a decision or an external fact, covering facility type, total limit, opening drawn balance, interest basis, margin, line fee, amortisation profile and maturity date. The grid holds formulas only.

Keeping the inputs in one column makes the schedule auditable. A reviewer can see every driver of the debt balance on a single screen, and a change to the margin flows through interest, cash flow and the covenant tests without anyone hunting through nested formulas.

A facility register for a mid-sized Australian business might look like this.

Each row becomes a block in the schedule below it. The register is the index and the blocks are the detail. Where a facility carries a fixed rate, the rate is an input. Where it floats, the rate is a projection plus the margin, which lets a single margin change flow through every period of the forecast.

Rolling Each Facility Forward

The roll-forward has four lines and one closing line, and the logic is identical across facility types. Only the repayment rule changes.

opening drawn      = prior period closing drawn    (period 1 = opening balance input)
available limit    = total limit - opening drawn
drawdowns          = MIN(required funding, available limit)
repayments         = MIN(scheduled repayment, opening drawn + drawdowns)
closing drawn      = opening drawn + drawdowns - repayments
undrawn commitment = total limit - closing drawn

Two details decide whether the schedule behaves. The drawdown line must be capped at the undrawn commitment, so the model can never draw past the limit and quietly overstate the funding available. The repayment line must be capped at the drawn balance plus that period's drawdowns, so a facility that pays down to zero does not generate a negative debt balance and a positive interest credit in the following period.

For an amortising facility the scheduled repayment comes from the loan contract. A $600,000 term loan at 6.00 percent over sixty months carries a level payment of about $11,600 a month, which returns roughly $96,000 of interest across the life of the loan. That figure belongs in an input cell, checked once against the bank's amortisation table, so that each facility can be reconciled line by line.

For a revolver the repayment rule is a cash sweep. Repayments equal surplus cash after operating and investing outflows, applied only where the closing cash balance would otherwise fall below the minimum cash policy. The sweep needs a switch in an input cell, because a lender conversation will always include a version of the forecast where no surplus cash is applied to the facility.

Interest on Opening or Average Balance

The interest line is where the schedule either connects cleanly to the rest of the model or creates a circular reference that makes the workbook unstable.

interest, opening basis = opening drawn * rate / periods per year
interest, average basis = ((opening drawn + closing drawn) / 2) * rate / periods per year

Opening basis interest is the safer default. Interest depends on the balance at the start of the period, which has already been determined, so the schedule closes without a loop. The cost is accuracy when the balance moves sharply within a period. A rate of 6.50 percent applied to a $500,000 opening balance produces $2,708 of monthly interest, and where the balance is drawn down halfway through the month the schedule overstates the charge by roughly half a month on that movement.

Average basis interest is more accurate for revolvers, where the balance swings with working capital and the bank is charging on daily balances. The trade-off is the loop. The closing balance depends on repayments, repayments depend on cash flow, cash flow depends on interest, and interest depends on the closing balance.

That circle has three honest resolutions, and a schedule should adopt one of them deliberately. The first is opening basis interest with a separate adjustment row that captures the difference between the two conventions. The second is enabling iterative calculation in Excel, which lets the loop resolve numerically at the cost of some speed and a warning to every reviewer who opens the file. The third is to break the loop by defining repayments from the opening cash position instead of the closing one, which removes the dependency at the cost of a one-period timing approximation. Naming the resolution in a comment on the interest row saves the next person a long afternoon.

Amortising, Interest-Only and Revolver Facilities

The facility type drives almost every other decision in the schedule. The three common structures behave differently on the balance sheet, in the cash flow and in the covenant tests.

An amortising term loan is the least demanding to model because the repayment schedule is contractual. The schedule still needs the split between principal and interest, since only the principal reduces the balance and only the principal belongs in the financing section of the cash flow.

An interest-only facility carries the drawn balance through to maturity, and the model has to show the bullet repayment in the month it falls due. A property loan sitting at $2,400,000 against a $3,500,000 valuation runs at an LVR of 68.6 percent, and that ratio is the one the lender will test. Modelling the facility as interest-only and then letting it amortise quietly over twenty years produces a cash flow that no credit team will recognise.

A revolver is the facility most often modelled as a permanent balance. Its limit is committed for a review period and then renewed, so the schedule needs a renewal assumption and a fallback position if the limit is cut. A working capital facility funding sixty days of receivables is fine while the receivables exist. The model should be able to answer what happens to cash if the limit drops by a third at the next review date.

Linking the Debt Schedule to the Balance Sheet and Cash Flow

The schedule earns its place only if it ties to the statements. Three links matter.

Balance sheet, debt (closing)      = SUM of closing drawn, all facilities
Cash flow, proceeds from borrowing = SUM of drawdowns, all facilities
Cash flow, repayment of borrowing  = -SUM of repayments, all facilities
Check: closing debt = opening debt + drawdowns - repayments

The balance sheet line needs a current and non-current split. Current debt is the principal due within the next twelve months, which the amortisation profile gives directly. Everything beyond that sits in non-current liabilities. A schedule that reports the whole balance as non-current distorts the liquidity position the lender is measuring, and it will be picked up in the first review.

The financing section of the cash flow statement holds drawdowns as an inflow and repayments as an outflow, with interest paid sitting in the operating section under most presentations. If the model plugs the cash balance, the debt schedule should carry the plug, because an opening cash plug hides the borrowing requirement and the balance sheet will disagree with the cash flow at the end of every period.

Covenant Tracking With a Headroom Table

Covenants are the reason the schedule exists, and a headroom table is the format that makes them readable. Each covenant gets a row, the covenant level sits in an input column, the forecast comes from the model, and the difference between them is headroom.

With EBITDA of $450,000, debt service of $300,000, EBIT of $400,000, interest of $80,000, net debt of $1,215,000 and a property loan of $2,400,000 against a $3,500,000 valuation, those forecasts reconcile to the definitions. DSCR of 1.50x is $450,000 divided by $300,000. Interest cover of 5.00x is $400,000 divided by $80,000. Gearing of 2.70x is $1,215,000 divided by $450,000. LVR of 68.6 percent is $2,400,000 divided by $3,500,000.

Ratio headroom is useful at certificate time, and dollar headroom is what a management meeting actually argues about. Converting each test gives the distance to breach.

DSCR headroom ($)      = (forecast DSCR - covenant DSCR) * debt service
                       = (1.50 - 1.25) * 300,000 = 75,000

Interest cover ($)     = forecast EBIT - (covenant cover * interest)
                       = 400,000 - (3.00 * 80,000) = 160,000

Gearing headroom ($)   = (covenant gearing * EBITDA) - net debt
                       = (3.00 * 450,000) - 1,215,000 = 135,000

Valuation headroom ($) = property valuation - (loan / covenant LVR)
                       = 3,500,000 - (2,400,000 / 0.70) = 71,429

A dollar headroom row turns each covenant into a tolerance. EBITDA can fall by $75,000 before DSCR breaches. Net debt can rise by $135,000 before gearing breaches. The property valuation can fall by $71,429 before LVR breaches. Those are figures a board can act on months ahead of the test date.

Conditional formatting on the headroom row makes the position scannable across every period in the forecast.

Red   : =headroom_cell <= 0
Amber : =AND(headroom_cell > 0, headroom_cell < 0.10 * covenant_cell)
Green : =AND(headroom_cell >= 0.10 * covenant_cell, headroom_cell > 0)

Applied to the headroom row, those three rules produce a strip that shows a covenant turning amber in month nine, which is the decision point a finance manager needs in month six. Running the test on a forward basis is the entire purpose of holding the schedule inside the model.

The Pitfalls That Break a Debt Schedule

Most broken debt schedules fail for one of a short list of reasons.

Interest on average balances without a resolution is the first. The model reports a circular reference warning, someone switches on iterative calculation with the default tolerance, and the numbers change slightly every time the file recalculates. Where the schedule uses average balance interest, the tolerance needs tightening and the loop needs documenting.

Drawdown timing is the second. Drawdowns get modelled at period end because that is where the cash flow statement closes, when in reality the cash was needed in the first week of the period. The interest charge and the covenant test then run on a balance that was wrong for most of the month, and the funding requirement appears a month later than it occurs.

Facilities that never amortise are the third. A revolver or an interest-only facility has no repayment profile, so a schedule built on scheduled repayments alone carries the balance forward forever. The interest keeps accruing on debt that has been repaid, and gearing stays above the covenant while the balance sheet shows a liability that no longer exists.

A covenant test using the wrong EBITDA convention is the fourth and the most expensive. Lenders test contracted EBITDA, usually on a rolling twelve months, with agreed add-backs and a defined treatment of acquisitions and one-off items. Testing the statutory EBITDA from the profit and loss account, or testing a single month of EBITDA against an annual covenant level, produces a headroom figure that means nothing. Keeping a reconciliation between statutory EBITDA and covenant EBITDA inside the workbook makes the difference visible before the certificate is signed.

Fees are the fifth. Line fees apply to the undrawn commitment, establishment fees are usually capitalised and amortised, and break costs appear when a facility is repaid early. A revolver with a $400,000 limit and $140,000 undrawn attracts a line fee on the undrawn portion every period, and a schedule that omits it understates the cost of the facility.

The classification of debt between current and non-current is the sixth. Current debt is the principal falling due within twelve months, and the balance sheet presentation depends on getting that split right.

The habit that prevents all six is a reconciliation tab with one line per facility, tying the closing balance to the opening balance plus drawdowns less repayments, and tying total closing debt to the balance sheet. A schedule that reconciles every period is a schedule that can be handed to a lender, an auditor or a new analyst without a covering explanation.

Related reading

• Securing Bank Loans: What Lenders Look For
• Three-Way Financial Models That Drive Business Decisions
• Understanding Home Loans