Covenant tracking in Excel: when it works, and when it breaks.

If you are staying on spreadsheets

How to build a covenant tracker that holds up

Since most lenders reading this will keep using a spreadsheet for a while, here is what separates the workbooks that survive an examination from the ones that do not.

1. One row per covenant, not per loan. A loan with three financial covenants and four reporting obligations is seven rows. Collapsing them into one row per loan is the first structural mistake, because the testing dates differ.
2. Record the defined terms, not just the threshold. A column that says "3.50x" has discarded whether funded debt includes capital leases and which add-backs are capped. At minimum, note the section reference so the next person can find it.
3. Separate test date from delivery deadline. These are different dates and conflating them means either chasing documents too early or testing too late.
4. Model step-downs as a schedule, not a single number. If the covenant tightens over time, the applicable threshold depends on the test date. A single cell will eventually be wrong.
5. Never overwrite a computed value. Append each period's result to a history tab. Trend is the most valuable output of covenant monitoring and it is destroyed by overwriting.
6. Link every row to the executed document. A hyperlink to the agreement or amendment in the document repository makes verification a thirty-second task instead of a half-hour one.
7. Give amendments a defined intake step. Decide explicitly who updates the workbook when an amendment executes, and make it part of the closing checklist rather than a matter of memory.
8. Lock the formula cells and version the file. Protecting calculation cells prevents the most common corruption, and a dated copy each quarter gives you something to point to later.

The financial covenants page covers what should be captured for each covenant type.

When to move, and what triggers it

Loan count is the weakest signal, though it is the one most often cited. These are better indicators:

• You have found a divergence. A covenant was being tested against superseded terms. One occurrence is a process failure; two is a systemic one.
• Quarter-end is a scramble that consumes the credit team. If most of two weeks goes into assembling data rather than reviewing exceptions, the bottleneck is mechanical.
• Portfolio questions take days to answer. When a credit committee asks for covenant trend by industry and the answer requires a special project, aggregation has become the binding constraint.
• An examination raised the question of evidence. A finding or even a pointed question about how determinations are documented is a strong signal.
• The person who maintains it is leaving. This is the most common actual trigger, and the worst moment to start the project.
• Reporting exceptions are chronic. If late financial statements are routine and nobody can say precisely which are outstanding today, the calendar has outgrown its container.

Broader manual processes beyond spreadsheets, tickler files, shared inboxes, calendar reminders, are covered in alternatives to manual covenant tracking.

FAQ

Frequently asked questions

How do you track loan covenants in Excel?

A workable covenant tracker has one row per covenant rather than per loan, and columns for the borrower, facility, covenant name and type, threshold and direction, the defined terms the calculation depends on, testing frequency, testing period basis, first test date, next test date, the reporting deliverable that supplies the inputs, its due date, the most recent computed value, the resulting status, and a link to the source clause in the executed agreement. Separate tabs hold the period-by-period calculation history so values are not overwritten.

Is it acceptable to track loan covenants in a spreadsheet?

Yes, for many lenders. A well-built covenant workbook maintained by an experienced credit administrator is a defensible process at smaller scale, and institutions have run commercial books this way for decades. The constraints are structural rather than a matter of professionalism: spreadsheets do not version, do not aggregate across files, do not link to executed amendments, and do not produce an audit trail of who changed what.

When should a lender move off spreadsheet covenant tracking?

The common triggers are portfolio growth past roughly one to two hundred commercial relationships, covenant packages complex enough that a single threshold column loses essential terms, heavy amendment traffic, the departure of the person who maintained the workbook, or an examination finding about how covenant determinations are evidenced. Any one of these is a reasonable trigger; the number of loans on its own is the weakest signal.

What is the biggest risk in Excel covenant tracking?

Divergence between the workbook and the executed documents. An amendment resets a leverage covenant, the executed document is filed in the document repository, and the tracker still holds the old threshold. Every subsequent test is then run against superseded terms, and the error can persist for quarters before anyone notices, usually when a breach appears that the borrower disputes.

Can you automate covenant tracking without replacing your spreadsheets?

Partly. Reporting calendars and reminders can be moved out of a workbook without touching the calculations, which addresses missed deadlines. Automating the calculation while leaving covenant definitions in the spreadsheet is less useful, because the definitions are where the errors actually live. Most lenders who make an incremental move start with document collection and deadline tracking, then address covenant definitions separately.