30,000 accounts · 180,000 account-months · April–September 2005
SQL over a card book, answering the three questions a risk committee asks: what is the profile, is it getting worse, and where do the losses actually sit. One data defect decides how every other number here may be read.
| Month | Status = 1 | Status = 2 | Any delinquent |
|---|---|---|---|
| 2005-04 | 0 | 2,766 | 3,079 |
| 2005-05 | 0 | 2,626 | 2,968 |
| 2005-06 | 2 | 3,159 | 3,510 |
| 2005-07 | 4 | 3,819 | 4,213 |
| 2005-08 | 28 | 3,927 | 4,438 |
| 2005-09 | 3,688 | 2,667 | 6,818 |
"One cycle past due" appears 34 times across the first five months and 3,688 times in the sixth. A state cannot do that unless the coding changed.
It is not academic. On the final transition, the natural definition of delinquency shows inflow tripling to 11.06% and the cure rate collapsing to 10.07% — a book in free fall. The comparable definition shows inflow falling to 3.87% and curing rising to 51.50% — a book recovering. Both are the recoding. I had written the first version into the memo before checking.
| Transition | Comparable? | Inflow, any DQ | Cure, any DQ | Inflow, 2+ | Cure, 2+ | Inflow, 3+ |
|---|---|---|---|---|---|---|
| 1 | comparable | 3.2% | 31.6% | 3.2% | 31.6% | 0.5% |
| 2 | comparable | 4.6% | 24.0% | 4.6% | 24.1% | 0.4% |
| 3 | comparable | 6.1% | 26.2% | 6.1% | 26.3% | 0.7% |
| 4 | comparable | 5.8% | 30.3% | 5.7% | 30.4% | 1.0% |
| 5 | not comparable | 11.1% | 10.1% | 3.9% | 51.5% | 0.9% |
Across the four comparable transitions, inflow to 2+ cycles roughly doubled while cure rates held flat. That is an inflow problem, not a collections problem. The greyed row is the recoding.
| Worst delinquency reached | % of book | Default rate | % of exposure | % of all defaults |
|---|---|---|---|---|
| 0 never delinquent | 66.4% | 11.7% | 72.5% | 35.2% |
| 1 reached 1 cycle | 5.6% | 25.0% | 0.3% | 6.4% |
| 2 reached 2 cycles | 24.0% | 43.5% | 24.1% | 47.2% |
| 3 reached 3+ cycles | 4.0% | 62.9% | 3.2% | 11.3% |
The never-delinquent segment has the lowest rate in the book and produces 35.2% of all defaults, because it holds 72.5% of exposure. A strategy aimed only at delinquent accounts addresses two thirds of the problem at most.
Risk climbs with utilisation, except that zero-balance accounts default at 24.8% — higher than every band below 60%. A zero balance bundles dormant accounts with accounts that stopped transacting because something went wrong. Treating "no balance" as "no risk" in a limit rule would be a mistake, and it is the kind of rule that gets written.
| First delinquent month | Accounts | Default rate |
|---|---|---|
| 1 | 3,079 | 52.3% |
| 2 | 862 | 42.0% |
| 3 | 1,234 | 39.8% |
| 4 | 1,371 | 38.1% |
| 5 | 1,154 | 40.0% |
| 6 | 2,369 | 36.0% |
| never | 19,931 | 11.7% |
| Behaviour | % of book | Default rate |
|---|---|---|
| a. transactor (pays in full) | 1.7% | 17.9% |
| b. heavy payer | 29.2% | 14.3% |
| c. light payer | 17.6% | 19.7% |
| d. minimum payer | 51.6% | 26.8% |
Any delinquency at all roughly triples the default rate against 11.7% for an account that never missed — and a first miss in the opening month still leaves six months of warning. Minimum payers are over half the book, carry the largest balances and default at nearly twice the rate of heavy payers.
| Credit limit | Default rate | % of exposure | % of all defaults |
|---|---|---|---|
| a. under 50k | 36.1% | 4.3% | 23.4% |
| b. 50-100k | 26.0% | 17.3% | 28.0% |
| c. 100-200k | 20.8% | 26.7% | 23.2% |
| d. 200-400k | 15.9% | 38.5% | 21.7% |
| e. 400k+ | 11.9% | 13.2% | 3.7% |
The highest-rate band holds 4.3% of exposure; the 200–400k band holds 38.5% and still contributes 21.7% of defaults. Rate sizes the risk per account, not the problem.
UCI Default of Credit Card Clients (Yeh & Lien, 2009), NT$. Public research dataset, no personal identifiers. Six months, one issuer, no origination date and no recovery data, so nothing here is a through-the-cycle estimate. · SQL, workbook and memo on GitHub