Project Summary
RevoFin is a fintech company specializing in innovative lending solutions for individuals and small businesses. The project focuses on assessing loan portfolio quality, uncovering loss drivers, and identifying targeted risk optimization opportunities. It leverages data-driven insights to uncover valuable information that can inform business strategies and enhance lending practices across various financial health indicators.
Goals
The primary objective is to measure portfolio size and evaluate risk using metrics like OS, ENR, and TKB30. The project aims to analyze performance across customer segments and loan cohorts to identify high–risk loans. Ultimately, it seeks to recommend strategies to improve portfolio health and provide better financial outcomes for the organization.
TOOLS
BigQuery, Google Sheets, Tableau
Methods
Data cleaning, Data transformation, Portfolio analytics, Validation, Ad hoc analysis, Cohort analysis, RCA & Issue Tree
Process
The process involved data cleaning and transformation using BigQuery for portfolio analytics. Ad hoc analysis and validation were conducted in Google Sheets, followed by creating visualizations and risk dashboards in Tableau. Analysis focused on RCA and issue trees covering borrower risk, product risk, and loan purposes to diagnose high loss rates.
Output
Portfolio quality is strong at 98.11% TKB30, but $279M in losses are concentrated in debt consolidation and higher interest rate segments above 16%. To mitigate this, RevoFin should optimize concentration risk limits, tighten underwriting in high-loss segments, apply risk-adjusted pricing discipline, improve early risk monitoring, and rebalance growth toward higher-quality customer segments.
Scope of Work / Achievements
- Analyzed a $4.17B loan portfolio using BigQuery to evaluate repayment performance, achieving a high 98.11% TKB30 rate.
- Identified $279M in charged-off loans by segmenting loss drivers, uncovering significant risk concentration within debt consolidation purposes.
- Evaluated risk-yield trade-offs across interest rate bands, identifying structural loss increases in segments with rates exceeding 16%.
- Formulated five strategic risk optimization recommendations to rebalance growth and improve early monitoring for 270K total borrowers.
Click here to see Full Deck, BigQuery SQL, Google Sheets and Tableau
