How I Built a Loan Risk Monitoring System That Detects Defaults Before They Happen

Most loan portfolios don’t collapse overnight. They leak.

A borrower misses 30 days. Then 60. Then 90.
By the time it’s officially labeled a “default,” the business has already taken the hit.

The problem is simple:
Most teams track defaults after they happen.

I wanted to answer the question:

Can risk be detected early enough before loans go bad?

The dataset revealed a typical gap:

• No structured visibility into early-stage delinquency (30–60 days)
• No clear tracking of Non-Performing Loans (NPLs) over time
• No segmentation to identify where risk is concentrated

This creates three business problems:

1. Late intervention → collections start too late
2. Hidden risk → bad segments hide inside averages
3. Capital inefficiency → money is deployed without understanding risk

So the objective became:

Build a system that tracks risk progression.

Approach & Methodology

I used loan-level data with key fields:

• Loan amount
• Days past due
• Credit score
• Country
• Origination date
• Monthly reporting snapshot

The system was built entirely in SQL using:

• Conditional aggregation (to define delinquency stages)
• Ratio calculations (to measure exposure)
• Window functions (to track trends over time)
• Segmentation logic (to isolate risk sources)

The goal was structured risk visibility.

Analysis & Processing

1. Measuring Actual Financial Risk

Instead of counting bad loans, I calculated:

• The proportion of total loan value tied to loans 90+ days past due

This gives a risk exposure.

Interpretation

NPL exposure fluctuates between ~3.5% and ~6.2%

This tells us:

• The portfolio is not stable
• Risk is cyclical or reactive to external/internal factors

Impact

A 2–3% swing in NPL exposure is significant in lending. This directly affects:

• profitability
• capital reserves
• risk provisioning

2. Tracking Risk Before Default

I broke delinquency into stages:

• 30 days past due → early warning
• 60 days → elevated risk
• 90 days → default

From results:

• DPD 30 peaks at 20.75%
• DPD 60 ranges around 9.5% — 14%
• NPL rate ~3.5% — 5.75%

A large portion of borrowers are already slipping early.

Impact

• This portfolio has high early-stage stress
• If no intervention happens:
  → NPL will rise in the next cycles

3. Trend Analysis

Using month-over-month comparison:

• April: +1.5% increase in NPL
• May: -1.25% drop
• July: -1.75% drop

This shows:

• Risk is volatile, not consistently improving
• There are short-term improvements, not structural fixes

4. Risk Segmentation : Who Is Risky?

Segmented by credit score:

• Subprime → ~4.57% NPL
• Mid → ~4.75% NPL
• Prime → ~4.26% NPL

This breaks expected behavior.

Get D.O’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Normally:

Subprime should be significantly worse

But here:

• Mid-risk borrowers are performing worse than expected

This suggests:

• flawed credit scoring thresholds
• mispricing of risk
• or incorrect approval strategy

5. Where Defaults Are Concentrated

• Nigeria → 111 NPLs
• UAE → 97
• USA → 89
• Singapore → 89
• UK → 66

this shows where most defaults occur numerically

Key Insights

1. Risk is already building early

• DPD 30 at ~20% is high, Indicates future default pressure

2. Portfolio performance is unstable

• NPL fluctuations show lack of consistent control

3. Mid-tier borrowers are underperforming

• Breaks standard credit assumptions and this uggests structural issue in lending model

4. Risk concentration exists geographically

• Some regions carry heavier default load

5. Current system is reactive

• Trends are tracked but not yet acted upon

Results

This system delivers:

• A clear NPL monitoring framework
• A structured early warning pipeline

Visibility into:

trends

• segments
• cohorts
• geography

Solution & Impact

1. Early intervention : Target borrowers at 30 days, not 90

2. Fix mid-tier borrower strategy

3. Adjust exposure by geography

4. Move from static reports to dynamic insights

Conclusion

Risk doesn’t appear suddenly. It builds quietly in early missed payments, weak segments, and hidden patterns.

The difference between good and bad portfolios is: The good ones detect risk early. The bad ones report it late.