CRS & FATCA investor self-certification review and classification

You are helping a fund investor-services or tax analyst review the CRS and FATCA documentation of the investors in [fund] for reporting year [year], before the annual filing to [tax authority]. A human reviewer checks everything you produce. You do not decide a classification, you do not decide that a self-certification is reasonable or that an indicium is cured, and you do not file anything.

I attach:
- Self-certifications: [the CRS self-certification forms received per investor and controlling person]
- IRS forms: [the W-9, W-8BEN, W-8BEN-E and W-8IMY forms received]
- GIIN list: [the IRS FFI list extract as at the date of the GIIN list check]
- Onboarding record: [the AML/KYC extract per investor: addresses, phone numbers, place of birth, standing payment instructions, powers of attorney, in-care-of addresses, controlling persons]
- Register: [investor accounts open at any time in the year, with investor type, account currency, account opening date, year-end balance and the distributions and redemption proceeds paid in the year]
- Rules: [the local guidance and your onboarding policy pages that set the classification categories, the reportable jurisdictions, the FATCA reporting scope under the applicable IGA and any account-balance thresholds]

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 an investor, an account and a controlling person; 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
For each investor, say which forms exist, which version of each form was used, who signed, in what capacity and on what date, and whether the form is complete. Quote the declared FATCA status, the declared CRS status, every jurisdiction of tax residence and every TIN, or the reason given for no TIN. For an entity declared a passive NFE, list the controlling persons named and whether each has its own self-certification. Note where the onboarding record holds information the form does not.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers on the forms, the onboarding record and the register: investor number, account number, GIIN, LEI, registration number, TIN (masked). Say which appear in all three and how complete each is.
3b. Names. Where no shared identifier exists, describe how the names differ: legal name vs trading name, nominee or trustee wording, name order, transliteration, abbreviations.
3c. Standardise. Write numbered rules for investor names and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: register account, form or forms, onboarding record, match method ("shared ID", "standardised name" or "suggested, needs review"), reason. A suggested match is never confirmed.

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 year.
- Inputs: reporting year, reporting jurisdiction, the as-at date of the review and the date of the GIIN list check. The list of reportable jurisdictions as the local authority publishes it, and the FATCA and CRS classification categories as the local guidance and your policy set them. For CRS, the new-account cut-off date and the pre-existing account categories (individual or entity and, for pre-existing individual accounts only, lower or high value, with their balance limits), as the CRS and local guidance set them. For FATCA, its own cut-off date and pre-existing account categories, as the applicable IGA and local guidance set them, since the two regimes can use different dates and limits. For each regime and account category, the due-diligence procedure: a reasonableness test against AML/KYC information for new accounts, an indicia search for pre-existing accounts. For each procedure, the indicia list, its version and the cure documents, kept separately for CRS and FATCA where their rules differ. The form type each investor type needs (for example W-9, W-8BEN, W-8BEN-E or W-8IMY for FATCA, and the individual, entity or controlling-person self-certification for CRS), as the IRS instructions and local guidance set it. The validity period for each IRS form type, as the IRS instructions and local guidance set it; a CRS self-certification has no fixed expiry and stays valid until a change in circumstances. The TIN format rules per jurisdiction, any account-balance thresholds the local rules set and whether the fund applies them. The FATCA reporting scope for non-participating financial institutions and payments made to them, as the applicable IGA model and local guidance set it. 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: the mapping table from step 3d, with the person who confirmed each suggested match and the date.
- Forms: one row per form, as read: investor, form type, version, signatory, capacity, date signed, declared status, residence jurisdictions, TINs, the reason given for any missing TIN, GIIN, with the page reference for each field; a TIN format-valid flag against the format rules on the Inputs tab, computed in the approved tool on the unmasked TIN before any masking; and a change in circumstances recorded on the onboarding record since signing, as a formula. Each row carries its source document and the page, cell or row reference for each figure.
- Indicia: one row per account, regime (CRS or FATCA) and item of onboarding information that the procedure for that account names on the Inputs tab, the jurisdiction it points to, and whether it agrees with the self-certified residence, as a formula. For a new account, a proposed reasonableness result ("Reasonable" or "Review") with the reason; this is a proposal for the reviewer, never a decision. For a pre-existing account, whether the item is an indicium on the indicia list and, if so, whether the cure documents for it are held, with their reference.
- Classification: one row per investor account. Account opening date, CRS category (new or pre-existing; individual or entity; for pre-existing individual accounts only, lower or high value), CRS procedure, CRS procedure result, FATCA category, FATCA procedure and FATCA procedure result are formulas against the register _Source tab, the Indicia tab and the Inputs tab, each regime using its own cut-off date and categories. The form type required (a lookup from the investor type on the register to the Inputs tab), form held matches, proposed FATCA status, proposed CRS status, form valid on the as-at date, GIIN found, threshold test (year-end balance against any account-balance threshold the Inputs tab says the fund applies, with the result "Below threshold" or "In scope"), open due-diligence item, controlling persons covered and reliance status are formulas against the Forms, Indicia, Persons and Inputs tabs and the GIIN list _Source tab. Reliance status reads "Can rely", "Cure needed" or "Check". A column for the reviewer's decision, and a note column for the reason wherever that decision differs from the proposed status, stay blank for a person to fill.
- Persons: one row per controlling person of a passive NFE named on a form or on the onboarding record. Each row holds the investor account, its source ("form", "onboarding record" or both), the residence, TIN, TIN format-valid flag, reason given for no TIN and self-certification reference. These are formulas from the Forms tab and the onboarding record _Source tab. A self-certification held flag is a formula.
- Reportable: a formula-driven list, by regime (CRS or FATCA), of every reportable account: for CRS, every account held by a person resident in a reportable jurisdiction or by a passive NFE with a controlling person resident in one; for FATCA, every account held by a specified US person or by a passive NFFE with controlling persons who are specified US persons; and, where the FATCA reporting scope on the Inputs tab requires it, every non-participating financial institution with the payments made to it. Each account has one account row (row type "account") holding regime, account number, name (masked), jurisdiction, TIN (masked in this working file) or the reason given for no TIN, TIN format-valid flag, account currency, year-end balance, distributions and redemption proceeds paid. Each reportable controlling person has its own row (row type "controlling person") holding regime, the account number it belongs to, its name (masked), jurisdiction, TIN (masked) or the reason given for no TIN and TIN format-valid flag from the Persons tab; the report output shows the account's balance and payments beside it, looked up from the account row, but those amounts are never counted again in any total. All are formulas from the register _Source tab and the Classification, Forms and Persons tabs. The list for filing uses unmasked TINs and is produced inside the firm's approved environment only. Each row carries its source document and the page, cell or row reference for each figure.
- Exceptions: a formula-driven list of every "Cure needed" and "Check", missing or expired form, missing TIN without a stated reason and TIN not format-valid. It lists every GIIN not found on the GIIN list, passive NFE with no controlling person and controlling person with no self-certification. It lists every unmapped account and unconfirmed match. It also lists every failed control, untraced or disagreeing figure, open query and timing difference. Each row has a type column with one of these values: "Cure needed", "Check", "missing or expired", "missing TIN", "TIN not valid", "GIIN not found", "no controlling person", "no self-certification", "unmapped account", "unconfirmed match", "failed control", "untraced or disagrees", "query" or "timing difference". Each row also has 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 and the date the approver approved the Reportable tab.
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 account open at any time in the reporting year on the register appears once on the Classification tab, and every controlling person named on a form or on the onboarding record for a passive NFE appears on the Persons tab. Target: 0 unexplained rows.
b. Totals tie: the account count on the Classification tab equals the register count (target: a difference of 0). Separately, for each regime, the year-end balances, distributions and redemption proceeds on the account rows of the Reportable tab, each account counted once and controlling-person rows left out, equal the register figures for the same accounts, by currency (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 declared status, residence, TIN and GIIN on the Forms tab cites its form and page, and every balance and payment on the Reportable tab cites the register row. 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 form signed by someone with no authority on the onboarding record, 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. Form validity: every account has the form type its investor type needs, signed and dated, still valid on the as-at date under the validity period on the Inputs tab, and no change in circumstances recorded on the Forms tab since it was signed. A form that fails goes to Exceptions as "missing or expired".
h. Due-diligence procedure: for CRS and for FATCA separately, every account on the Classification tab has the category its opening date and balance give under that regime's cut-off date and categories on the Inputs tab, and the procedure and procedure result that category requires. A new account with a "Review" reasonableness result, and a pre-existing account with an indicium and no cure documents held, read "Cure needed". No single item, such as a phone number, decides reasonableness on its own; the reviewer decides each "Review" row. No account reads "Can rely" while either regime has an open item.
i. GIIN: every investor that declares itself a financial institution with a GIIN has that GIIN found on the IRS list as at the check date on the Inputs tab, or it is flagged.
j. Controlling persons: every passive NFE has at least one controlling person on the Persons tab, and each controlling person has a self-certification or is flagged.
k. Thresholds: every account the Classification tab marks "Below threshold" has a year-end balance at or below the threshold on the Inputs tab, and the Inputs tab records that the fund applies that threshold; any other account reads "In scope".
l. TIN: every account row on the Reportable tab has a TIN format-valid flag of TRUE on the Forms tab, and every controlling-person row has a TIN format-valid flag of TRUE on the Persons tab, or the row carries a stated reason for no TIN, or it is flagged.
m. FATCA scope: every non-participating financial institution on the Classification tab appears on the Reportable tab with its payments where the FATCA reporting scope on the Inputs tab requires it.
n. Classification decided by a person: the reviewer's decision column on the Classification tab is filled for every account before the Reportable tab is approved, and every row where it differs from the proposed status carries a note. HUMAN STEP: a person decides every "Cure needed" and "Check" row; the model never fills that column.
o. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
p. 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 year, 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, every classification decision with who made it, 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 year: back-test recall (run on a past reporting year with known answers; share of known contradictions and misclassifications found; target 100%, and do not adopt the workbook below that); accounts classified 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 number of accounts reviewed, the number that can be relied on, the number needing a cure, the number of reportable accounts and controlling persons by jurisdiction, the result of every check, and a list of anything you were unsure about.