Covenant & portfolio monitoring (private credit)

You are helping a private credit portfolio analyst test the financial covenants for [borrowers] as at [test date]. A human reviewer checks everything you produce. You do not decide whether a default has occurred.

I attach:
- Credit agreements: [covenant definitions and thresholds]
- Compliance certificates: [borrower-submitted]
- Accounts: [management accounts for the period]
- Amendments and waivers: [every executed amendment, waiver and consent letter for each facility, with its date]

Work through the six steps below. Show the result of each step before you move on.

STEP 1. MAP THE SOURCES
For each file, record: who produced it; the as-at date it states (not the date it was sent); the currency; what one row or section represents; the fields that identify a borrower and a covenant; the value fields and how each is labelled; and any subtotals, hidden rows, merged cells or footnotes that change how it should be read. Where the files differ in as-at date, period or currency, list each difference and say how you will handle it: convert at an FX rate from the Inputs tab with its source, or compare like periods only. Stop and ask me only if a difference means two figures cannot validly be compared.

STEP 2. TRACE THE PREPARATION
Quote each covenant definition, including add-backs, caps and the test period. Say how the borrower's certificate computed each figure.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List borrower and facility identifiers.
3b. Names. Describe naming differences across agreements, certificates and accounts.
3c. Standardise. Apply numbered rules.
3d. Map. Link each certificate to a facility, with reason.

STEP 4. BUILD THE LINKED WORKBOOK
Build an .xlsx file with these tabs, in this order:
- Index: a link to each tab, one line on what it holds, the as-at date, and the steps to refresh the workbook next period.
- Inputs: test date, review date (the date this workbook is run, against which cure status is judged), thresholds per facility and covenant with their direction (maximum or minimum) as the credit agreement sets it, headroom alert level, and for each facility its amendment and waiver history (each amendment with its effective date, each waiver with the test and period it covers) and any cure period or equity-cure right as the credit agreement sets it; plus the as-at date of each source file, with any differences between them and how each is handled; any FX rates needed, with their source and date, and the tolerance for agreement with documents (0.00 wherever exact agreement is required). Each value carries its source beside it, and every formula that needs one points here.
- _Source tabs: each file exactly as received, one tab per file. Never edit these tabs.
- Map: certificates and accounts linked to facilities, with match method ("shared ID", "standardised name" or "suggested, needs review") and reason. For each suggested match, the person who confirmed it and the date.
- Terms: one row per facility and covenant, with the definition and threshold in force on the test date quoted, including add-backs, caps and the test period, the amendment that sets that wording (or "original agreement") with its date, and any waiver or cure right that applies, with its reference, the cure period and how the agreement counts it (calendar or business days, and which holiday calendar), the event that starts it (for example delivery of the compliance certificate), the date that event occurred with its source document and page, and the cure deadline: as the agreement, waiver or notice states it, with its source, or else calculated under that counting rule from the start date and flagged for a person to confirm. Where the start date or counting rule is not evidenced, the deadline reads "not evidenced" and goes to Exceptions. Each row carries its source document and the page, cell or row reference for each figure.
- Test: one row per facility and covenant, with the Terms row it applies, our computation, the borrower's figure from its certificate, the difference between them, the threshold and headroom as formulas, the test result by formula ("pass" or "breach"), and beside it the waiver or cure status as at the review date on the Inputs tab ("none", "waived", "in cure period" while the review date is on or before the cure deadline, or "cure period expired") with the waiver reference and the cure deadline looked up from the Terms tab, and each input to the computation with its source document and page and the accounts _Source cell it points to.
- Watch: a formula-driven list of breaches (including waived breaches and breaches in a cure period, with that status), low headroom and differences from the certificate.
- Exceptions: one row per failed control, untraced or disagreeing figure, unconfirmed match and open query, with its type ("failed control", "untraced figure", "disagreeing figure", "unconfirmed match", "open query" or "timing difference"), a description, its owner, status ("open" or "closed"), expected clearing date and the approver's sign-off.
- Checks: the controls in step 5.
- KPIs: the measures in step 6.
- _Log: the audit trail in step 6.
Every derived number is a formula that leads back to a _Source tab or the Inputs tab. Nothing is typed in as a value. Use Excel tables and XLOOKUP or INDEX/MATCH. Wrap lookups in IFERROR and show "Not found", never zero. Dates as yyyy-mmm-dd, negatives in brackets, units in every value header, header rows frozen, no decorative formatting.

STEP 5. CONTROLS (EVERY ONE MUST PASS BEFORE SIGN-OFF)
On the Checks tab, write each control as a formula that returns TRUE or FALSE, with the count or difference beside it. A FALSE is not a score to improve: it blocks sign-off until it is resolved, by correcting the figure with a note or by getting the source corrected. An explanation alone does not clear it. The only item that may stay open at sign-off is a timing difference with an expected clearing date: it is listed by name on the Exceptions tab as a reconciling item and re-checked next period. A document tie-out failure is never a timing difference and can never be carried. Show every FALSE at the top of the Index tab under "Checks failing".
a. Complete: every facility has a Test row for each covenant. Target: 0 unexplained rows.
b. Inputs tie to the accounts: every input used on the Test tab equals the figure on the accounts _Source tab it points to. Target: a difference of 0.00.
c. Formula-linked: every calculated cell is a formula (test with ISFORMULA), and there are no #N/A or #REF errors. Target: 100%.
d. Tied to documents: every definition on the Terms tab and every input on the Test tab cites its source document and page. Every figure must be traced (it cites its document and page) and must agree (it equals the figure in that document), within the tolerance on the Inputs tab and no wider. Any figure that is untraced or disagrees goes to the Exceptions tab at once. If the document itself looks wrong, for example a compliance certificate whose figures differ from the accounts it says it is based on, raise it as a query; never change the workbook to make it agree. Target: 100%.
e. Matches confirmed: count the rows matched by shared ID, by standardised name and by suggestion. Target: 0 suggested matches left unconfirmed at sign-off.
f. Test period: every certificate and set of accounts covers the test period ending on the test date on the Inputs tab, and every FX rate used has its source on the Inputs tab. Target: 100%.
g. Definitions in force: every test uses the definition and threshold in effect on the test date, after the amendments on the Inputs tab, including add-backs and caps. A waiver or cure period never changes the calculated result: the Test tab keeps the result ("pass" or "breach") and shows the waiver or cure status beside it, so the Watch tab still lists "breach, waived" with the waiver reference, "breach, in cure period" with the cure deadline, or "breach, cure period expired".
h. Borrower figure: every test shows the difference from the borrower's own certificate.
i. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
j. Four-eyes: the _Log names the preparer and the approver, who must be different people, and the approver signs off every Exceptions line.

STEP 6. REUSABLE, AUDITABLE AND MEASURED
- Reusable: next period, a person replaces the _Source tabs and updates the Inputs tab, and every other tab recalculates. Keep the Map tab so confirmed matches carry forward.
- Auditable: on the _Log tab, list each source file (name, producer, as-at date, date received, row count, control total), every rule and match you applied with the reason, every assumption, and who prepared and approved. Never overwrite source data. A correction goes in a separate column with a note.
- Measured: add a KPIs tab that the team fills in each period: back-test recall (run on a past period with known answers; share of known differences found; target 100%, and do not adopt the workbook below that); items agreed first time with no manual fix; items flagged that turned out fine; corrections made by the reviewer before sign-off; corrections found after sign-off (target 0); manual edits outside the Inputs and _Source tabs at refresh (target 0); and cycle time in hours from receiving the files to sign-off, against the manual baseline.

If your tool cannot create an .xlsx file, give me each tab as a table plus the exact formula for the first row of every calculated column.

At the end, give me covenants passing, covenants near the threshold, and differences from the borrower's certificate, the result of every check, and a list of anything you were unsure about.