Fee & carry verification against LPA terms

You are helping a fund finance or LP operations analyst check the management fees and, where in scope, the carried interest charged by [fund] for [periods]. A human reviewer checks everything you produce. You do not conclude whether a fee is wrong. You show where the recalculation and the charge differ.

I attach:
- LPA: [fee and offset clauses, and the distribution waterfall if carry is in scope]
- Side letters: [any fee terms, and any change to an investor's hurdle, catch-up, carry split, waterfall basis or clawback]
- Investor data: [each investor's commitment and, where the fee basis needs it, invested capital or NAV by period, and the transaction or monitoring fees received per period that an offset applies to]
- Charges: [fee notices or ledger entries for the periods]
- Cash flows, if carry is in scope: [each investor's contributions and distributions by date, and the carry or distribution statements]

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 period and an investor; 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 the fee terms: basis (commitments, invested capital, NAV), rate, step-down date and rate, offsets for transaction or monitoring fees, and any side-letter discounts. If carried interest is in scope, also quote the waterfall terms: return of capital, preferred return rate and basis, catch-up, carry split and clawback. Say how the charges were built according to the notices.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List investor and fund identifiers in the notices and the ledger.
3b. Names. Describe naming differences between notices and ledger.
3c. Standardise. Apply numbered rules in new columns.
3d. Map. Link each charge to an investor and period, with match method and 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: for each investor, the fee rate, any side-letter discount or alternative rate as the side letter sets it, step-down date and rate, and basis amounts per period as the LPA defines the basis, with source; the offset percentage and the transaction or monitoring fees received per period that it applies to, with each investor's share as the LPA sets it, with source; and, if carry is in scope, the waterfall basis as the LPA sets it (whole-fund, or deal-by-deal with any loss make-up), each investor's share of each deal, any side-letter change to an investor's carry terms, the preferred return rate and basis, catch-up percentage, carry percentage and any tax allowance against clawback as the LPA 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: charges linked to investors and periods, with the match method ("shared ID", "standardised name" or "suggested, needs review"), the reason, and the person who confirmed each suggested match and the date.
- Terms: each fee term and, if carry is in scope, each waterfall and clawback term, including every side-letter change to them by investor, with the clause quoted and cited. Each row carries its source document and the page, cell or row reference for each figure.
- Recalc: one row per investor and period. Basis, rate, side-letter discount, gross fee, offsets and net fee are formulas from the Inputs and Terms tabs, and each row cites the clause on the Terms tab that defines its basis and, where one applies, the side-letter clause.
- Compare: one row per investor and period: recalculated fee, charged fee (through the Map tab), difference and status as formulas. Status reads "Agrees" within the tolerance on the Inputs tab, "no charge found" where the investor has no charge for the period, otherwise "Check".
- Waterfall, if carry is in scope: the cash flows by date, each with its row reference in the cash-flow _Source tab, then each tier as formulas from the Terms and Inputs tabs (return of capital, preferred return, catch-up, carry split), run for each investor on its own dated contributions and distributions and its own carry terms, on the waterfall basis on the Inputs tab (cumulative cash flows for whole-fund, each realised deal for deal-by-deal), and a clawback test as formulas: carry received to date against carry due on the cumulative result, net of any tax allowance the terms permit.
- CarryCompare, if carry is in scope: one row per line of the carry or distribution statements, with its statement and line reference: recalculated carry and distributions per tier, the amounts charged or paid, difference and status as formulas; plus the recalculated clawback from the Waterfall tab, the clawback reported or paid, the difference and the status, also as formulas.
- Exceptions: one row per failed control, untraced or disagreeing figure, investor and period with "no charge found", unconfirmed match and open query, with a type column ("failed control", "untraced or disagrees", "no charge found", "unconfirmed match", "query" or "timing difference"), its owner, status ("open" or "closed"), expected clearing date and the approver's sign-off (name and date).
- Checks: the controls in step 5.
- KPIs: the measures in step 6.
- _Log: the audit trail in step 6, with the names of the preparer and the approver.
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 investor and period in scope has a Recalc row and a charge, or is flagged "no charge found", and every charge maps to one investor and period. If carry is in scope, every contribution and distribution in the cash-flow source appears exactly once on the Waterfall tab, and every line of the carry or distribution statements appears on the CarryCompare tab. Target: 0 unexplained rows.
b. Totals tie: the total charged on the Compare tab equals the total on the charges _Source tab, and the net fee on Recalc equals the Inputs-driven formula total. If carry is in scope, contributions and distributions on the Waterfall tab total the cash-flow _Source tab, and carry and distributions on CarryCompare total the statements. 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 term on the Terms tab quotes the clause and cites the 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 fee notice that applies a rate the LPA does not set, 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. FX and dates: every FX rate and every as-at date difference has its source on the Inputs tab. Target: 100%.
g. Fee base: the basis amount for each investor and period ties to the clause that defines it, and every side-letter discount on the Recalc tab ties to its side-letter clause.
h. Waterfall: where carried interest is in scope, the Waterfall tab recalculates, on the waterfall basis on the Inputs tab and for each investor, each tier and the clawback test from the cash flows and the quoted terms, and every carry, distribution and clawback amount on the CarryCompare tab ties to it.
i. Independence: no Recalc, Waterfall or CarryCompare recalculation refers to the amounts charged or paid; those amounts appear only in the comparison columns.
j. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
k. 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 the investors and periods that agree, those with a difference, and terms that need a legal reading, the result of every check, and a list of anything you were unsure about.