Free Excel templates

A free KYC remediation capacity model in Excel

A customer due diligence back book sized end to end: segmentation by remediation route and by priority tranche, analyst-days and headcount, a one-year plan against a five-year plan, the outreach funnel and the exceptions register.

A bank with 250,000 customers in scope has to refresh its customer files under the new EU AML rules. The work comes to 82,062 analyst-days, 373 analyst-years. Spread over five years that is a team of 120; done in one year it is 373, 3.1 times larger.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

KYC_Remediation_Capacity_Model.xlsx (25 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The EU AML Handbook, which explains the method behind it, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

8 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

The Inputs
The Segmentation
The Effort
Two Plans
The Asymmetry
Outreach
The Premium
Checks

What the worked case returns

MeasureValue
Customers in scope250,000
Tranche 1, enhanced due diligence10,000
Tranche 4, everyone else157,500
Total effort, analyst-days82,062
Five-year steady-state team (analysts)120.1
One-year team (analysts)373
Last month from which a one-year window can be recovered8
Customers to contact148,750
Cumulative response after three attempts64.9%
Exceptions register (customers)52,155
Closable without contact40.5%
Analyst-days per 100,000 customers32,825

Even after three contact attempts, 20.9% of the portfolio ends on the exceptions register. Non-response is a workstream of its own, and it scales with the portfolio: 20,862 customers for every 100,000.

How to use it on your own case

  1. Enter the customers in scope, the remediation routes and the priority tranches on The Inputs.
  2. Set the files per analyst-day for each type of review, the working days and the analyst cost.
  3. Read the effort and the two staffing plans, then the month after which no headcount recovers a one-year deadline.
  4. Enter outreach response rates and read the population to contact and the exceptions register.

Questions people ask about it

What is a KYC back book remediation?

The review of existing customer files to bring them up to a new due diligence standard, as opposed to applying the standard to new customers at onboarding.

How many analysts does a KYC remediation need?

It depends on the number of files, the type of review each needs and the deadline. The file converts each tranche into analyst-days and the deadline into a headcount.

Does it reflect the EU AML Regulation?

The structure follows the remediation problem the regulation creates. Throughput, response rates and costs are illustrative inputs to replace with your own.

Is this template really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The EU AML Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The EU AML Handbook. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.