Credit Portfolio Analysis

OVERVIEW

BrightShore Lending, since its launch in 2019, has served over 75,000 customers with personal, education, and small business loans. Despite rapid expansion, the company faced increasing challenges from delinquent and defaulting loans, posing a significant threat to profitability and regulatory standing. This project was commissioned to address the lack of consolidated insights across disbursed loans, repayment patterns, credit grades, and borrower profiles, ultimately aiming to strengthen lending decisions. 

OBJECTIVES

The primary objectives of this project were to:

• Develop a unified dataset of all active and closed loans.

• Segment loans by performance into "Good" (Fully Paid) and "Bad" (Charged Off).

• Identify key risk indicators such such as Debt-to-Income (DTI), loan term, income, credit grade, and home ownership.

• Generate key performance indicators (KPIs) for the loan portfolio, including Month-to-Date (MTD)/Month-over-Month (MOM) trends, average interest rates, and funded versus received amounts.

• Create a multi-dashboard Power BI report for continuous loan monitoring and informed credit strategy development.

• Provide actionable recommendations to differentiate good from bad loans, identify factors that elevate risk, and understand how various attributes influence loan performance.

KEY COLUMNS

The analysis leveraged a comprehensive dataset with the following key columns:

• Loan ID: Unique identifier for each loan record.

• address_state: Region code representing the borrower's location in the UK.

• application_type: Type of loan application (e.g., INDIVIDUAL or JOINT).

• emp_length: Length of employment of the borrower.

• emp_title: Borrower's job title or occupation.

• grade: Credit grade assigned to the loan (A-G).

• home_ownership: Borrower housing status (e.g., Buying with mortgage, Owner Outright, Private Rent).

• issue_date: Date the loan was issued.

• last_credit_pull_date: Date of the most recent credit report pulled.

• last_payment_date: Date of the last payment made on the loan.

• loan_status: Current status of the loan (Charged Off, Current, Fully Paid).

• member_id: Internal system identifier for the borrower.

• purpose: Reason for the loan (e.g., Loan refinancing, Small business funding, Wedding expenses).

• sub_grade: More detailed classification within the credit grade.

• term: Duration of the loan in months (e.g., 36 months, 60 months).

• verification_state: Whether the borrower's information was verified or not.

• annual_income: Total annual income of the borrower in GBP.

• dti: Debt-to-income ratio of the borrower.

• installment: Fixed monthly loan payment amount.

• int_rate: Annual interest rate charged on the loan.

• loan_amount: Total amount of the loan disbursed.

• total_acc: Total number of borrower's open credit accounts.

• total_payment: Total amount repaid by the borrower so far.

• income_to_loan_ratio: Ratio of annual income to loan amount.

APPROACH

The project followed a structured workflow consisting of four phases:

Phase 1: Data Preparation (SQL Server)

• Involved cleaning, transforming, and preparing the raw loan data for analysis within SQL Server.

Phase 2: KPI Query Development (SQL Server)

• Developed and tested SQL queries to calculate various portfolio health KPIs, including:

  • Total Loan Applications, Total Funded Amount, Total Amount Received.

  • Average Interest Rate, Average DTI, Average Income-to-Loan Ratio.

  • Count and percentage of Good vs. Bad Loans.

  • Loan Status report.

  • Borrower Profile by Loan Grade & Purpose.

Phase 3: Power BI Visualization

• Connected Power BI to SQL Server (using live or import mode).

• Modeled the data by establishing relationships and defining DAX measures.

• Built three interactive dashboards:

  • Report 1: Overview Report: Covered key KPIs, metrics, and the Loan Status Report.

  • Report 2: Risk Report: Showcased monthly trends by issue date, regional analysis by state, loan term analysis, employee length, loan purpose breakdown, and home ownership analysis.

  • Report 3: High Risk Drivers: Provided a comprehensive, user-friendly detailed dashboard consolidating how specific variables such as loan term, loan grade, loan purpose, and loan amounts impact the likelihood of loans being charged off or paid back.

Phase 4: Delivery & Documentation

• Exported the Power BI file (.pbix) with narrative bookmarks and tooltips.

• Documented the SQL codebase with comments and logic.

• Prepared a Project Summary Report (.docx) detailing insights and recommendations.

• Optionally, packaged reusable DAX measures and KPIs as templates.

KEY INSIGHTS

The detailed analysis of BrightShore Lending's loan portfolio yielded several critical insights into credit risk and loan performance:

• Strong Repayment Success: 80.63% of the total loan amount ($351.4 million) was categorized as "Fully Paid," indicating a very successful collection rate and strong financial health.

• Significant Growth: From January to December 2024, the total loan amount disbursed increased by 115.65%, and the number of loan applications rose by 84.99%, demonstrating rapid expansion.

• Higher Risk for Longer Terms: 60-month loans exhibited a significantly higher charged-off rate (22.34%) compared to 36-month loans (10.71%), highlighting a critical risk factor.

• Loan Grade Impact: Lower credit grades (G, F, E) were identified as exceptionally high-risk, with Grade G loans having a 31.31% charged-off rate. Conversely, Grade A loans were the safest (5.70% charged-off rate).

• Purpose-Based Risk: "Small Business Funding" (26-27% charged-off rate) and "Home Purchase" (approx. 18% charged-off rate) carried higher-than-average risk.

• Loan Amount Threshold: Loans above $15,175 were 1.43 times more likely to be charged off.

• DTI Ratio as a Predictor: Loans with DTI above 12.62% were 1.24 times more likely to be charged off, with "Current" loans having the highest average DTI.

• Geographic Risk: Specific address states (GMN and KEN) showed slightly increased charge-off likelihood.

IMPACTS

This project delivered significant impacts for BrightShore Lending:

• Enhanced Credit Risk Models: The identified risk factors and insights provide a robust foundation for enhancing existing credit risk models, leading to more accurate default predictions.

• Reduced Non-Performing Loans: By understanding the characteristics of bad loans, BrightShore can implement targeted strategies to reduce NPL ratios and improve loan recovery.

• Improved Lending Decisions: The comprehensive dashboards and actionable recommendations empower decision-makers to make more informed and strategic lending decisions, optimizing loan performance and profitability.

• Regulatory Compliance: The project supports compliance with regulatory requirements (e.g., FCA and GDPR) by providing transparent and data-driven insights into the loan portfolio's health.

• Optimized Product Offerings: Insights into loan purpose and borrower affordability allow for better alignment of product offerings with customer repayment capacity.

DELIVERABLES

The key deliverables for this project included:

• SQL Query Pack: A collection of .sql files containing all developed queries.

• Interactive Power BI Dashboard: A .pbix file providing dynamic visualizations and insights.

• Project Summary Report: A .docx document detailing the analysis, key findings, and recommendations.