Learn / Portfolio

Collection efficiency: formula, types and the SQL to calculate it

By the Bucketwise team · 7 min read · Updated 13 November 2026

Collection efficiency, or CE, tells you how much of what was due in a month the lender actually collected, usually as collections divided by amount due. It is quoted everywhere and defined differently almost everywhere, so the first job is to say which version you mean.

How is collection efficiency calculated?

• Collection efficiency = amount collected divided by amount due, times 100.

There is no single standard definition. What counts as due and what counts as collected changes the answer, and a figure without its definition cannot be compared with anyone else's.

Which three versions of collection efficiency will you see?

Current CE is the strictest on timeliness. Total CE is lower when there is a pile of old arrears, because that pile sits in the denominator. Count-based CE ignores ticket size, so a few large unpaid accounts hide inside it.

Worked example: collection efficiency for one month

The last line is a common mistake. It puts arrears collected in the numerator and leaves arrears out of the denominator, so the ratio looks 6 points better than the current CE and 16 points better than the total CE.

Count-based CE needs account counts. If 8,700 of 10,000 accounts with an instalment due paid in full, it is 87%. Those numbers are made up.

Why does the way a payment is split matter?

When a customer pays less than total due, the lender decides how the payment is split between overdue and current. Oldest due first is common. Under that rule a part payment clears old arrears first, so current CE falls and total CE does not change. The allocation rule moves the split, never the total. State your rule next to the number.

What data do you need?

If your system stores only payments, build this table first. The data guide and the derived fields guide show how to get demand and overdue from the schedule and the payments.

How do you calculate collection efficiency in SQL?

One row per month with current CE, total CE and the share of accounts that paid their current demand in full. We ran this on sample data and checked every number against a separate calculation.

SELECT month_end,
       100.0 * SUM(coll_current) / SUM(current_demand)            AS current_ce_pct,
       100.0 * SUM(coll_current + coll_overdue)
             / SUM(current_demand + overdue_bf)                   AS total_ce_pct,
       100.0 * SUM(CASE WHEN coll_current >= current_demand THEN 1 ELSE 0 END)
             / COUNT(*)                                           AS paid_in_full_pct
FROM   demand_collection
GROUP  BY month_end
ORDER  BY month_end;

The query uses standard SQL (SUM, CASE, GROUP BY). Replace the table and column names with yours. Only the current-CE line and the paid-in-full line depend on your allocation rule.

What should you check before you publish the number?

• Collections reversed after a bounce are netted off, not left in.
• Prepayments and advance payments are either out of the numerator or shown separately. Otherwise CE can pass 100% without the book improving.
• Closed loans and written-off loans follow one rule in both numerator and denominator.
• The month and the cut-off day are the same as the DPD report you will read it beside.

Why read collection efficiency next to DPD, not instead of it?

A CE of 95% can sit beside a worsening book. The 5% that is not collected may be the same customers each month, rolling from bucket to bucket. Put CE beside the bucket table and the roll rate matrix. CE tells you how much came in. Roll rates tell you who is slipping.

Frequently asked questions

Keep reading