Credit Risk Analytics: Optimizing Underwriting & Mitigating Loan Default Exposure

1. Executive Summary

Impact Overview: Conducted an end-to-end credit risk evaluation across 32,581 loan records totalling $312.43M for Nova Bank. Diagnosed underlying behavioural, structural, and demographic drivers behind a 21.82% portfolio default rate ($77M in capital losses). Engineered an early-warning distress framework that identified 122 high-risk active accounts for pre-emptive restructuring and formulated strict underwriting guardrails projected to prevent millions in future write-offs.

2. Business Context & Problem Statement

• The Challenge: Nova Bank, a retail and commercial lender operating across North America and the UK, faced escalating credit default losses ($77M across 33K historical loans). Overly permissive underwriting policies and inadequate risk-based pricing structures diluted profit margins, while the risk operations team lacked an early-warning system to identify deteriorating accounts prior to 90-day delinquency.

• Objective: Design an interactive credit risk intelligence system to validate internal credit rating performance, uncover non-linear default thresholds (loan-to-income, interest rates, collateral status), and deliver actionable underwriting policy recommendations.

• Key Business Questions Addressed:

  1. How accurate is Nova Bank’s internal credit grading model, and where do default rates experience structural cliffs?
  2. What specific affordability ratios (Debt-to-Income, Loan-to-Income) trigger exponential increases in default probability?
  3. Which demographic and behavioural markers (homeownership, past delinquencies, credit utilization) reliably predict loan distress versus conventional industry myths?

3. Key Insights & Strategic Recommendations

Key Findings

• The Grade D “Risk Cliff”: While Grades A through C exhibit standard risk progression (5% to 21% default rate), Grade D represents a severe inflection point where default rates surge to 59.0%. Grades F and G reflect catastrophic underwriting failures, experiencing near-100% loss rates regardless of premium interest pricing.
• The Affordability “Point of No Return” (LTI > 0.40): Loan-to-Income (LTI) ratio is the single most decisive solvency indicator. Default rates remain stable below an LTI of 0.25 (~12–18%), but accelerate exponentially past 0.40, where defaults hit 75.0%.
• Homeownership vs. Employment Stability Myth: Analysis disproved the hypothesis that employment category (e.g., self-employed vs. salaried) drives risk, with both groups maintaining near-identical ~21–22% default rates. Conversely, renters default at over 4x the rate of outright homeowners, indicating housing equity is a critical proxy for balance-sheet resilience.
• Adverse Selection in Risk-Based Pricing: Escalating interest rates beyond 18%–20% does not offset credit risk—it magnifies it. Borrowers burdened by hyper-elevated interest charges enter unserviceable debt spirals, accelerating default velocity.
• The Distress “Syndrome”: Cross-variable behavioural segmentation isolated a distinct cohort of 122 active borrowers exhibiting a high-risk profile: prior delinquencies, revolving credit utilization $> 70%$, and LTI ratios $> 0.40$.

Underwriting Grade Default Breakdown:
Grade A [██░░░░░░░░]  5.8% Default Rate
Grade B [███░░░░░░░] 13.2% Default Rate
Grade C [█████░░░░░] 21.4% Default Rate
───────────────────────────────────────────── [CRITICAL RISK CLIFF]
Grade D [██████████████] 59.0% Default Rate
Grade E [██████████████████] 74.2% Default Rate
Grade F/G [████████████████████████] 88.5%+ Default Rate

Actionable Recommendations

1. Immediate Underwriting Policy Freeze:
   • Halt Origination of Grades F & G: Cease all underwriting in these categories immediately; the interest premiums fail to cover write-offs.
   • Implement a Hard LTI Ceiling at 0.40: Restrict maximum loan amounts relative to borrower income to prevent accounts entering the 75% default zone.
2. Proactive Portfolio Intervention (122 Accounts):
   • Deploy retention and workout specialists to contact the 122 flagged active accounts before 90-day charge-offs occur.
   • Offer structured maturity extensions or interest-rate concessions to stabilize debt-service capacity.
3. Re-align Risk-Based Pricing:
   • Cap maximum lending rates below the 18% threshold for vulnerable borrowers to avoid debt trap spirals, shifting underwriting focus toward asset-backed collateral rather than rate compensation.

4. Interactive Dashboard & Data Storytelling

🔗 Click Here to Launch the Live Interactive Dashboard (Insert Power BI Web URL)

Figure 1: Executive Credit Risk Dashboard featuring portfolio default KPIs, rating distribution, affordability thresholds, and behavioural early-warning indicators.

Key Visual Highlights

• Executive Summary Banner: Real-time visibility into Total Portfolio Volume ($312.43M), Loan Counts (32,581), Default Rate (21.82%), and Total Defaulted Principal ($77.0M).
• Underwriting Risk Matrix: Comparative visual isolating default spikes by internal grade tiers (A through G) and loan term lengths (36-month vs. 60-month).
• Affordability & Demographic Deep-Dives: Multi-axis charts demonstrating non-linear default trajectories across LTI thresholds, homeownership categories, and past delinquency counts.
• Operational Early-Warning Registry: Searchable table surfacing flagged active borrowers for automated hand-off to collections and credit advisory teams.

5. Technical Architecture & Methodology

┌─────────────────────────┐
│   Nova Bank Loan Data   │  Raw Portfolio Dataset (32,581 rows x 35 fields)
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│ Feature Engineering &   │  • Engineered LTI (Loan-to-Income) & DTI metrics
│   Risk Segmentation     │  • Modeled Early Warning Flags (Utilization > 70% + LT > 0.4)
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│ Star Schema Data Model  │  Fact_Loans connected to Dim_Borrower, Dim_CreditGrade,
│       (Power BI)        │  Dim_Demographics, and Dim_Date
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│ Interactive BI & Story  │  Executive KPI dashboards, dynamic scenario filters,
│                         │  and automated default exposure matrices
└─────────────────────────┘

Analytical Modelling & DAX Calculations

Core analytical measures were constructed in Power BI to evaluate exposure and risk dynamics:

// Portfolio Default Rate
Default Rate % = 
DIVIDE(
    CALCULATE(COUNTROWS(Fact_Loans), Fact_Loans[Loan_Status] = "Default"),
    COUNTROWS(Fact_Loans),
    0
)

// Total Value at Risk (Defaulted Balance)
Defaulted Principal Exposure = 
CALCULATE(
    SUM(Fact_Loans[Loan_Amount]),
    Fact_Loans[Loan_Status] = "Default"
)

// Early Warning Distress Cohort Indicator
Early Warning Flag = 
IF(
    Fact_Loans[Loan_Status] = "Current" &&
    Fact_Loans[LTI_Ratio] >= 0.40 &&
    Fact_Loans[Credit_Utilization] >= 0.70 &&
    Fact_Loans[Past_Delinquencies] >= 1,
    "High Risk - Intervene",
    "Standard Risk"
)