ALCO portfolio stratification dashboard

The credit packet is due Wednesday

A consumer-lending dataset on the shared retail_* core with 8 tables, ~17K lending rows, and planted HMDA/servicing traps. Mis-coded HMDA action_taken values, ARM rows missing arm_reset_date, late-posted payments coded as on-time, trailing-space loan-officer names that split GROUP BYs, and a vintage 2019 ARM cohort whose 7-year reset wall lands in the next 6 months.

The consumer-lending analyst role at a regional or money-center bank: HMDA action-taken decomposition, fair-lending disparity analysis, vintage curves and first-payment-default cohorts, ARM reset wall identification, refi candidate targeting, delinquency aging and roll rates, charge-off recovery, CECL-style loss reserve decomposition, and ALCO-ready portfolio stratification — against a realistic mortgage / auto / HELOC / personal lending dataset with planted HMDA and servicing reconciliation traps.

Answer questions from the Chief Credit Officer, Mortgage Sales Director, Fair Lending Compliance Officer, and Loan Servicing Lead — weekly origination dashboards, HMDA LAR reconstructions, ARM reset walls, and CECL-style loss reserves.

Consumer Lending Scenarios

lending_applications (HMDA-shaped), lending_originations (funded loans with FICO/DTI/LTV at orig), lending_payments (past installments with days_late), and lending_geography (census tract + LMI). Joins to the shared retail_* core for customer demographics.

Authentic Lending Schema

HMDA action-taken decomposition, denial-reason rollups, LMI-tract approval gap analysis, vintage curves with cohort EXISTS, ARM reset arithmetic, roll-rate matrices, charge-off recovery ratios, and the multi-CTE rollups that real credit teams ship.

HMDA + Vintage + Servicing Skills

Every mission maps to a real lending-analyst task — weekly origination packets, HMDA LAR submissions, fair-lending memos, and the SQL that lending teams at JPMC / BofA / Wells Fargo / Rocket Mortgage actually screen for.

Portfolio-Ready Skills

The portfolio is seasoning. The vintages are diverging.

Peek at the application book

Loan product taxonomy

Funded originations this quarter

Applications by state

Net new mortgages last 30 days

Approval rate by product

Avg funded amount by loan purpose

HMDA action_taken distribution

30+ delinquent loan count

Originations by LO (top 10)

Approval rate by FICO band

HMDA denial-reason rollup

DTI bucket pivot for originations

Pricing exception detector (rate vs benchmark)

Late-payment rate by vintage month

Early-Payment Default rate by vintage month

ARM reset wall (next 6 months)

Refi candidate cohort

Delinquency progression: first miss × current status

Approval gap by LMI tract

Delinquency aging bucket pivot by product

Portfolio LTV stratification with seasoning

HMDA LAR row reconstruction

Charge-off rate + principal recovery by vintage year

Cumulative loss curve by vintage × months-to-charge-off

Fair Lending disparity index by FICO band (Black vs White)

Rate-shock stress test on ARM payments (+200bp)

CECL-style loss reserve decomposition

Lending capstone scorecard (8-KPI single-row panel)

customer_id

household_id

first_name

last_name

date_of_birth

state

segment

fico_current

estimated_income

kyc_risk

acquisition_channel

onboarded_date

status

retail_customers

product_id

product_code

product_name

subcategory

default_apr

retail_products

account_id

open_date

close_date

ownership_type

interest_rate

original_balance

fico_at_orig

maturity_date

retail_accounts

tract_id

msa_code

county

lmi_flag

minority_pct

lending_geography

app_id

application_date

requested_amount

requested_term

loan_purpose

action_taken

denial_reason_1

aus_recommendation

rate_spread

lien_status

occupancy

ethnicity

race

age_bucket

property_tract

lending_applications

origination_id

funded_date

funded_amount

term_months

rate_type

arm_reset_date

dti_at_orig

ltv_at_orig

property_value

lo_name

channel

current_status

closed_date

lending_originations

payment_id

due_date

paid_date

scheduled_amount

principal_paid

interest_paid

escrow_paid

days_late

lending_payments

retail_branches

ALCO portfolio stratification dashboard

The Brief

You'll practice