Build a Loan Covenant Compliance Report

A business with a bank loan usually signs up to covenants — financial tests it must pass every quarter, typically that debt stays below a multiple of earnings, that earnings comfortably cover interest, and that a minimum amount of cash is held. Breaching one can let the bank demand repayment, so finance teams track them closely and send a compliance certificate with the quarter's figures. The calculations are simple but fiddly: the tests use the last twelve months of earnings, not the quarter's; net debt is measured at a point in time; definitions follow the loan agreement, not the accounts. And the number management really wants is not the ratio but the headroom — how far earnings could fall before a test fails. This page builds the quarterly covenant report with pandas from monthly management accounts: rolling twelve-month EBITDA and interest, net debt, the three tests against their limits, headroom in money terms, a look ahead using the forecast, and a workbook whose ratios are live formulas a lender can check. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is monthly management accounts from January 2025, with EBITDA, interest paid, gross debt and cash at month end, actuals to September 2026 and the forecast for the following two quarters. The loan agreement sets three covenants, tested at each quarter end: leverage (net debt ÷ last-twelve-month EBITDA) at most 3.0×, interest cover (LTM EBITDA ÷ LTM interest) at least 4.0×, and cash of at least £750,000.

Step 1 — Twelve-month figures at each quarter end

import pandas as pd

acc = pd.read_excel("monthly_accounts.xlsx")
acc["Month"] = pd.PeriodIndex(acc["Month"], freq="M")
acc = acc.set_index("Month")
acc["LTM EBITDA"] = acc["EBITDA"].rolling(12).sum()
acc["LTM interest"] = acc["Interest"].rolling(12).sum()
acc["Net debt"] = acc["Gross debt"] - acc["Cash"]

quarter_ends = acc[acc.index.month.isin([3, 6, 9, 12]) & acc["LTM EBITDA"].notna()]
tests = quarter_ends[["Type", "LTM EBITDA", "LTM interest", "Net debt", "Cash"]].copy()
print(tests.round(0).to_string())

Covenant tests almost always use a rolling twelve-month figure for earnings and interest, so one weak or strong month does not swing the result. A twelve-month rolling sum over monthly figures gives that for every month; the quarter-end rows are the ones the tests use. The first test date with a full twelve months of history is December 2025, when twelve-month EBITDA was £4.94 million against net debt of £13.27 million. Net debt is a balance-sheet figure at the test date — gross borrowing less cash — and the cash test uses the month-end balance directly. Loan agreements often adjust these definitions — adding back one-off costs to EBITDA, excluding certain leases from debt — and those adjustments belong in this step, written out explicitly, so the calculation matches the agreement rather than the management accounts.

Step 2 — The tests and their headroom

LIMITS = {"Leverage": 3.0, "Interest cover": 4.0, "Minimum cash": 750_000}

tests["Leverage"] = tests["Net debt"] / tests["LTM EBITDA"]
tests["Interest cover"] = tests["LTM EBITDA"] / tests["LTM interest"]
tests["Leverage OK"] = tests["Leverage"] <= LIMITS["Leverage"]
tests["Cover OK"] = tests["Interest cover"] >= LIMITS["Interest cover"]
tests["Cash OK"] = tests["Cash"] >= LIMITS["Minimum cash"]

# headroom: how far LTM EBITDA (or cash) could fall before each test fails
tests["EBITDA headroom (leverage)"] = tests["LTM EBITDA"] - tests["Net debt"] / LIMITS["Leverage"]
tests["EBITDA headroom (cover)"] = tests["LTM EBITDA"] - tests["LTM interest"] * LIMITS["Interest cover"]
tests["Cash headroom"] = tests["Cash"] - LIMITS["Minimum cash"]
tests["Tightest"] = tests[["EBITDA headroom (leverage)", "EBITDA headroom (cover)"]].idxmin(axis=1).str.extract(r"\((\w+)\)")[0]
show = ["Type", "Leverage", "Interest cover", "Cash", "EBITDA headroom (leverage)", "EBITDA headroom (cover)", "Tightest"]
print(tests[show].round(2).to_string())

Each test is a comparison of a ratio or balance with its limit, and the pass/fail columns are what goes on the certificate. Headroom is more useful to management, and the clearest way to express it is in money: for leverage, the EBITDA at which net debt divided by EBITDA would equal 3.0× is net debt ÷ 3.0, so the headroom is current LTM EBITDA minus that figure; for interest cover, the floor is LTM interest × 4.0. Expressed this way, EBITDA could fall by £344,000 before leverage breaches is a sentence a board understands immediately, while 2.78× against 3.0× needs explaining. The tightest column names the covenant with the least headroom at each date — usually leverage for a business with substantial debt.

In this example every test passes at every date, but leverage is clearly the covenant to watch. A weak second quarter in 2026 pulls twelve-month EBITDA down just as a cash outflow in June raises net debt, so leverage rises from 2.55× in March to 2.78× in June and the EBITDA headroom more than halves, from £746,000 to £344,000 — a fall of 7% in twelve-month earnings would breach the covenant. Interest cover, at 4.73× against a minimum of 4.0×, still has over £700,000 of headroom, and cash never comes near its £750,000 floor. The forecast quarters show leverage easing back to 2.57× by March 2027 as debt is repaid and earnings recover.

Step 3 — Look ahead with the forecast

ahead = tests[tests["Type"] == "forecast"][["Leverage", "Interest cover", "Cash", "EBITDA headroom (leverage)"]]
print(ahead.round(2).to_string())
lowest = tests["EBITDA headroom (leverage)"].idxmin()
print(f"lowest leverage headroom: £{tests.loc[lowest, 'EBITDA headroom (leverage)']:,.0f} at {lowest}")

Because the forecast months are in the same table, the same calculation gives the covenant position at the next two test dates. That is the point of automating the report rather than calculating each quarter by hand: a forecast that shows headroom shrinking towards zero gives months of warning, which is enough time to talk to the bank, change the timing of a capital project or draw on a facility — instead of discovering a breach when the quarter's accounts are closed. A sensitivity on top — rerunning the tests with forecast EBITDA reduced by 10% or 20% — is a two-line change and shows how much bad news the business can absorb. Here the December forecast has £500,000 of leverage headroom on twelve-month EBITDA of £4.7 million, so a 10% shortfall in the coming quarter alone would be absorbed, but a repeat of the second quarter's weakness would bring the test very close to its limit.

Step 4 — A workbook with visible formulas

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
ws.title = "Covenants"
ws.append(["Test date", "Type", "LTM EBITDA", "LTM interest", "Net debt", "Cash", "Leverage", "Limit", "Interest cover", "Limit", "Minimum cash"])
for c in ws[1]:
    c.font = Font(bold=True)
for r, (period, row) in enumerate(tests.iterrows(), start=2):
    ws.append([str(period), row["Type"], round(row["LTM EBITDA"]), round(row["LTM interest"]), round(row["Net debt"]), round(row["Cash"]),
               f"=E{r}/C{r}", LIMITS["Leverage"], f"=C{r}/D{r}", LIMITS["Interest cover"], LIMITS["Minimum cash"]])
    for col in "CDEFK":
        ws[f"{col}{r}"].number_format = "£#,##0"
    for col in "GI":
        ws[f"{col}{r}"].number_format = '0.00"×"'
wb.save("covenant_compliance.xlsx")
print("rows:", ws.max_row - 1)

The workbook that goes with a compliance certificate should let the lender check the arithmetic, so the inputs — LTM EBITDA, LTM interest, net debt and cash — are written as values and the ratios as formulas that divide them, with the limits alongside. Anyone opening it can click a ratio and see exactly how it was calculated, which saves a round of questions every quarter and makes the certificate easy to sign off. The ratios use a custom number format that shows 2.40×, the way covenants are usually quoted. A second sheet with the monthly data behind the twelve-month sums completes the audit trail.

Common pitfalls

• Quarterly EBITDA instead of twelve months. Covenants use rolling LTM figures.
• Accounting definitions instead of the agreement's. Apply the agreement's adjustments explicitly.
• Reporting only pass or fail. Show headroom in money and the tightest covenant.
• Testing only the past. Run the same tests on the forecast for early warning.
• Ratios as typed numbers. Write them as formulas so the lender can check them.

Conclusion

A covenant report takes monthly accounts, builds rolling twelve-month EBITDA and interest, measures net debt and cash at each quarter end, and compares leverage, interest cover and cash with the limits in the loan agreement. Headroom expressed as how far EBITDA or cash could fall before a breach, the tightest covenant at each date, and the same tests run on the forecast give management early warning, and a workbook with the ratios as live formulas gives the lender a checkable certificate.

Frequently asked questions

How do I calculate LTM EBITDA in pandas? Use a rolling twelve-month sum over monthly EBITDA: df["EBITDA"].rolling(12).sum().

How is leverage calculated for a covenant? Usually net debt — borrowing less cash — divided by last-twelve-month EBITDA, using the loan agreement's definitions.

How do I express covenant headroom? As the amount EBITDA could fall before the ratio reaches its limit: LTM EBITDA minus net debt divided by the leverage limit.

Why include the forecast in a covenant report? It shows whether a breach is coming at the next test dates, while there is still time to act.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Loan Amortization Schedule in Excel with Python — the debt side.
• Build a Cash Flow Forecast Report — where the forecast comes from.
• Running Totals and Year-Over-Year Growth in pandas — rolling sums.