Card Portfolio Risk Profile

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.

22.1%
eventual default rate
3.20% → 5.73%
monthly inflow to 2+ cycles
31.6% → 30.4%
cure rate, roughly flat
+31.8%
exposure growth
35.2%
of defaults from never-delinquent accounts

The defect that governs everything else

MonthStatus = 1Status = 2Any delinquent
2005-0402,7663,079
2005-0502,6262,968
2005-0623,1593,510
2005-0743,8194,213
2005-08283,9274,438
2005-093,6882,6676,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.

Roll rates, both definitions side by side

TransitionComparable?Inflow, any DQCure, any DQInflow, 2+Cure, 2+Inflow, 3+
1comparable3.2%31.6%3.2%31.6%0.5%
2comparable4.6%24.0%4.6%24.1%0.4%
3comparable6.1%26.2%6.1%26.3%0.7%
4comparable5.8%30.3%5.7%30.4%1.0%
5not comparable11.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.

Where the losses sit

Worst delinquency reached% of bookDefault rate% of exposure% of all defaults
0 never delinquent66.4%11.7%72.5%35.2%
1 reached 1 cycle5.6%25.0%0.3%6.4%
2 reached 2 cycles24.0%43.5%24.1%47.2%
3 reached 3+ cycles4.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.

Utilisation is not monotone

a. zero or credit balance24.8%
b. 0-30%16.9%
c. 30-60%23.2%
d. 60-90%27.6%
e. 90-100%24.3%
f. over limit30.1%

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.

Early warning, and behaviour

First delinquent monthAccountsDefault rate
13,07952.3%
286242.0%
31,23439.8%
41,37138.1%
51,15440.0%
62,36936.0%
never19,93111.7%
Behaviour% of bookDefault rate
a. transactor (pays in full)1.7%17.9%
b. heavy payer29.2%14.3%
c. light payer17.6%19.7%
d. minimum payer51.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.

Limit bands: rate and concentration disagree

Credit limitDefault rate% of exposure% of all defaults
a. under 50k36.1%4.3%23.4%
b. 50-100k26.0%17.3%28.0%
c. 100-200k20.8%26.7%23.2%
d. 200-400k15.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