Risk management project · Credit risk · SQL, Python and R

Which card borrowers miss next month's payment, and how much capital the book needs.

30,000 credit card accounts from Taiwan, with six months of bills, payments and repayment status. The data is checked and segmented in SQL, a probability of default (PD) scorecard is built and validated in Python, the whole book is run through expected loss, Basel IRB capital and a Monte Carlo loss model, and the key numbers are recomputed independently in R.

Accounts30,00022.1% defaulted the next month

Test AUC0.769Gini 0.54 · KS 0.42 on 9,000 held out accounts

Expected lossNT$478M14.5% of NT$3.29B exposure at default

Loss at 99.9%NT$940MMonte Carlo; IRB formula capital NT$444M

Python vs RAgreeLargest gap 1.2e−9 on a coefficient

01 / How it is built

Three languages, one pipeline, every number traceable.

One command, python3 run.py, rebuilds the database from the raw file, fits and validates the model, runs the loss model, calls R to recheck it, and writes the figures on this page. Every file is published as it runs.

SQL

Load, check and segment

Schema, ten data quality checks, the model features as a view, and default rates by segment with window functions.

Python

Model, validate and size the loss

WoE binning, feature selection, logistic scorecard, validation, expected loss, IRB capital, Monte Carlo and stress tests.

data_layer.py 68 lines
pd_model.py 278 lines
portfolio_risk.py 116 lines
run.py 96 lines

Check it independently

Refits the model with glm() and recomputes expected loss and capital in base R. The run stops if the answers disagree.

crosscheck.R 49 lines

02 / The data, checked in SQL

Know what is wrong with the data before modelling it.

Default of Credit Card Clients, UCI Machine Learning Repository (id 350). Repayment history April to September 2005; default measured in October 2005. Each row is one account: credit limit, six months of repayment status, bills and payments, and whether the next payment was missed. The checks below come from sql/02_quality.sql.

Check	Accounts	How it is handled
Accounts	30,000	Matches the UCI documentation
Defaults in the following month	6,636	Matches the documentation: 22.1% of accounts
Duplicate account rows	35	Kept. The source copy has no account ID, so identical rows can be separate accounts
Education code not in the documentation (0, 5, 6)	345	Not used: demographic fields stay out of the model
Marital status code not in the documentation (0)	54	Not used: demographic fields stay out of the model
September status coded -2 (undocumented)	2,759	Kept as its own group, commonly read as no card use; the data sets its risk
September status coded 0 (undocumented)	14,737	Kept as its own group, commonly read as revolving credit
Negative September bill (credit balance)	590	Drawn balance floored at zero when measuring exposure
September bill above the credit limit	2,115	Kept: over limit is a risk signal. Undrawn limit floored at zero
No bill and no payment in all six months	795	Kept and disclosed; see the next row
Of which flagged as default next month	299	37.6% of inactive accounts are labelled default, so the label does not always mean a missed payment on a balance

03 / Where default concentrates

Payment history separates risk far better than limit or utilisation.

Default rate by segment, from sql/04_segments.sql. The dashed line is the book average of 22.1%. A segment holding more of the defaults than of the accounts is where risk sits.

Default rate by repayment status in September

No use (-2) · 2,759 accounts13.2%

Paid duly (-1) · 5,686 accounts16.8%

Revolving (0) · 14,737 accounts12.8%

1 month late · 3,688 accounts34.0%

2 months late · 2,667 accounts69.1%

3+ months late · 463 accounts71.9%

Segment	Accounts	Default rate	Share of accounts	Share of defaults
Credit limit, NT$
Under 50k	4,311	36.1%	14.4%	23.4%
50k to 150k	10,979	25.2%	36.6%	41.7%
150k to 300k	9,593	16.8%	32.0%	24.3%
300k and over	5,117	13.7%	17.1%	10.5%
Months delayed, last six
None	19,931	11.7%	66.4%	35.2%
1 to 2	6,325	32.5%	21.1%	31.0%
3 to 4	2,105	53.8%	7.0%	17.1%
5 to 6	1,639	68.0%	5.5%	16.8%
Utilisation, September
None or credit	2,598	24.8%	8.7%	9.7%
Under 30%	12,194	16.9%	40.6%	31.1%
30% to 70%	5,686	24.0%	18.9%	20.6%
70% to 100%	7,407	26.0%	24.7%	29.0%
Over limit	2,115	30.1%	7.0%	9.6%

Accounts two or more months late in September are 10.4% of the book and 32.8% of next month's defaults. Smaller limits default more often, which is consistent with lenders already giving riskier borrowers less credit. High utilisation matters, but less than payment history.

04 / PD scorecard

7 candidate features in, 4 kept, every decision written down.

Each feature is binned and converted to Weight of Evidence (WoE) on a 70% training sample. Rules set before fitting: Information Value (IV) of at least 0.02, drop the weaker of any pair whose WoE correlates above 0.7, and every coefficient must have the expected sign. Sex, age, education and marital status are never offered to the model.

Feature	IV	Decision
Repayment status, September	0.878	Kept
Months delayed, last six	0.860	Dropped: WoE correlation 0.76 with Repayment status, September, which has higher IV
Worst status, last six months	0.773	Kept
Share of August bill paid in September	0.191	Dropped: positive coefficient after fitting (unstable sign)
Credit limit	0.182	Kept
Average utilisation, six months	0.123	Kept
Utilisation, September	0.077	Dropped: WoE correlation 0.72 with Average utilisation, six months, which has higher IV

Logistic regression on WoE	Coefficient	Std. error	p-value
Intercept	-1.250	0.019	< 0.001
Repayment status, September	-0.652	0.025	< 0.001
Worst status, last six months	-0.456	0.028	< 0.001
Credit limit	-0.455	0.046	< 0.001
Average utilisation, six months	-0.385	0.055	< 0.001

Every coefficient is negative, as it should be: a higher WoE means a safer bin, so it lowers the PD. Repayment status has an IV of 0.88, above the 0.5 level usually treated as suspicious. It is not leakage: September status is known before the October payment falls due, and being two months behind is simply one step from missing the next payment.

Scorecard points by bin

Scaled so 600 points means odds of 50 good accounts to 1 bad, and every 20 points doubles the odds. A borrower's score is the sum of one bin from each feature; test scores run from 443 to 561. Counts are training accounts.

Feature and bin	Accounts	Default rate	WoE	Points
Repayment status, September
No use (-2)	1,972	13.3%	0.62	142
Paid duly (-1)	3,973	16.9%	0.33	137
Revolving (0)	10,342	12.8%	0.66	143
1 month late	2,522	33.9%	-0.59	120
2 months late	1,874	69.4%	-2.08	92
3+ months late	317	72.2%	-2.21	89
Worst status, last six months
No use in any month	1,504	13.6%	0.59	139
Paid duly at worst	2,163	14.7%	0.50	137
Revolving at worst	10,320	10.9%	0.84	142
1 month late	1,166	24.9%	-0.15	129
2 months late	5,005	43.6%	-1.00	118
3+ months late	842	62.1%	-1.75	108
Credit limit, NT$
Up to 30k	2,868	36.1%	-0.69	122
30k to 50k	2,583	27.1%	-0.27	127
50k to 70k	1,083	26.9%	-0.26	127
70k to 100k	2,248	25.2%	-0.17	129
100k to 140k	1,979	23.2%	-0.06	130
140k to 180k	2,326	17.6%	0.29	135
180k to 210k	1,744	16.9%	0.33	135
210k to 270k	2,039	15.7%	0.42	136
270k to 360k	2,410	14.8%	0.49	137
Over 360k	1,720	12.3%	0.71	140
Average utilisation, six months
Up to 0.4%	2,100	25.9%	-0.21	128
0.4% to 1.7%	2,100	19.7%	0.15	132
1.7% to 5.1%	2,100	14.3%	0.53	137
5.1% to 13.3%	2,100	14.3%	0.53	137
13.3% to 28.4%	2,100	16.1%	0.39	135
28.4% to 45.2%	2,100	19.3%	0.17	133
45.2% to 61.4%	2,100	24.6%	-0.14	129
61.4% to 76.0%	2,100	25.2%	-0.17	129
76.0% to 88.9%	2,100	29.4%	-0.38	127
Over 88.9%	2,100	32.4%	-0.52	125

05 / Validation

Tested on 9,000 accounts the model never saw.

Three questions a validator asks. Does it rank risky borrowers above safe ones (AUC, Gini, KS)? Are the PDs the right size (calibration, Brier, Hosmer-Lemeshow)? Is the score stable between samples (PSI)?

Measure	Training	Test	Reading
Accounts	21,000	9,000	Stratified 70%/30% split, fixed seed
Default rate	22.1%	22.1%	Same mix in both samples
AUC	0.765	0.769	Test matches training: no sign of overfitting
Gini	0.530	0.538	2 × AUC − 1
KS	0.408	0.419	Largest gap between bad and good score distributions
Brier score	–	0.1377	0.1723 if every account got the average PD
Hosmer-Lemeshow	–	22.7	p = 0.0037: rejects exact calibration
PSI, train to test	–	0.001	Under 0.1 is stable by the usual rule

Predicted vs observed default rate, by tenth of test accounts

Score band, riskiest first	Score range	Accounts	Default rate	Defaults captured so far
Band 1	443 to 478	900	68.6%	31.0%
Band 2	478 to 509	900	41.7%	49.8%
Band 3	509 to 521	900	27.2%	62.1%
Band 4	521 to 536	900	18.2%	70.4%
Band 5	536 to 541	900	17.8%	78.4%
Band 6	541 to 544	900	12.4%	84.0%
Band 7	544 to 547	900	12.7%	89.8%
Band 8	547 to 550	900	9.4%	94.0%
Band 9	550 to 554	900	8.2%	97.7%
Band 10	554 to 561	900	5.0%	100.0%

The riskiest tenth of test accounts by score holds 31% of the defaults and the riskiest three tenths hold 62%, so the score is useful for limits and collections. Calibration is close at the top but not exact: Hosmer-Lemeshow rejects it, mainly because tenth 6 is predicted at 14.1% against 17.3% observed and tenth 1 at 8.0% against 5.0%. Before using these PDs for pricing or provisions, a bank would recalibrate them.

06 / Expected loss and capital

From each borrower's PD to a loss distribution for the whole book.

Expected loss = PD × LGD × EAD for each account, summed. Capital comes two ways: the Basel IRB formula for revolving retail, and a Monte Carlo of 20,000 scenarios using the one-factor model that formula is built on. LGD and CCF are not in the data, so they are stated assumptions and stressed in section 07.

Input	Value	Basis
PD	Model PD per account	Floored at 0.1%, the Basel floor for revolving retail
Loss given default (LGD)	80%	Assumption for unsecured card balances
Credit conversion factor (CCF)	50%	Assumption: share of the unused limit drawn before default
Asset correlation	0.04	Basel value for qualifying revolving retail exposures
Confidence level	99.9%	Basel IRB standard

Credit limits	NT$5.02B
Drawn balances, September	NT$1.54B
Exposure at default (drawn + 50% of unused limit)	NT$3.29B
Exposure weighted PD	18.1%
Expected loss	NT$478M
IRB capital, formula	NT$444M
Simulated loss at 99.9%	NT$940M
Unexpected loss at 99.9% (simulated, less mean)	NT$463M
Expected shortfall beyond 99.9%	NT$985M

Simulated portfolio loss, share of exposure, 20,000 scenarios

Repayment status, September	Share of exposure	Share of expected loss
No use (-2)	10.9%	6.2%
Paid duly (-1)	18.8%	13.5%
Revolving (0)	52.3%	32.9%
1 month late	9.9%	18.2%
2 months late	7.2%	25.4%
3+ months late	1.0%	4.0%

Late accounts are 18.1% of the exposure but 47.5% of the expected loss. The IRB formula and the simulation agree within 4% on unexpected loss: the formula assumes an infinitely large book, while the simulation keeps the randomness of 30,000 individual accounts, and only 20 of its scenarios lie beyond the 99.9% point, so the tail estimate carries sampling error. Expected loss is what pricing and provisions should cover; capital covers the bad year on top of it.

07 / Stress tests

Correlation moves capital more than anything else.

Each case changes one input, except the combined case, and reruns the whole book. Stress simulations use 10,000 scenarios each.

Scenario	Expected loss	IRB capital	Loss at 99.9%	vs base
Base	NT$478M	NT$444M	NT$940M	–
PD × 1.5	NT$711M	NT$480M	NT$1.19B	+27%
LGD 90%	NT$538M	NT$500M	NT$1.04B	+10%
CCF 100% (full limit drawn)	NT$697M	NT$679M	NT$1.38B	+47%
Correlation 0.15	NT$478M	NT$992M	NT$1.45B	+54%
Combined: PD × 1.5, LGD 90%, correlation 0.15	NT$800M	NT$1.15B	NT$1.94B	+106%

Raising the correlation to 0.15 leaves expected loss unchanged but takes formula capital from NT$444M to NT$992M: when borrowers default together, the average is the same and the bad year is much worse. Higher PDs mostly move expected loss (NT$478M to NT$711M). The combined case reaches NT$1.94B at 99.9%, 59% of exposure.

08 / Independent check in R

Same inputs, different code, same answers.

crosscheck.R reads the inputs Python used, refits the model with glm() and recomputes expected loss and capital from the formula. run.py stops if any gap is above its tolerance. R version 4.3.3 (2024-02-29).

What is compared	Largest gap
Model coefficients	1.2e−9
PD of any account	3.5e−10
Test AUC	4.7e−13
Expected loss (relative)	4.8e−13
IRB capital (relative)	1.0e−12

09 / Limitations

What this model cannot tell you.

One month, not one yearThe target is a missed payment next month. Basel PDs cover twelve months, so the capital figures show the method, not a regulatory number for this book.

One market, one periodTaiwan, 2005, a period of heavy card defaults, which helps explain why 22.1% of accounts defaulted. There is no later sample, so the split is random rather than out of time.

Assumed LGD and CCFThe data has no recoveries or drawdown history. Both are stated, kept fixed per account, and stressed.

CalibrationHosmer-Lemeshow rejects exact calibration on the test sample. Ranking is reliable; the PD levels would be recalibrated before use.

Undocumented codesStatus codes −2 and 0 are not defined in the documentation. They are kept as separate groups so the data sets their risk, and their labels are the common reading, not a fact.

Approved accounts onlyEvery account was already approved, so the model has not seen rejected applicants. No reject inference is attempted.