Project Objective or Overview

To monitor and assess bank lending activities, a thorough bank loan analysis report was developed. The objective was to provide insights into key loan-related metrics and their changes over time, aiding data-driven decision-making, tracking loan portfolio health, and identifying trends for lending strategy.

KPI Requirements

Total Loan Applications: Compute the total number of loan applications received during a specific time period. Monitor changes on a month-to-date (MTD) and month-over-month (MoM) basis.

Total Funded Amount: Comprehend the total amount of funds disbursed as loans. Illustrate MTD and MoM changes.

Total Amount Received: Track the total amount received from borrowers to assess the bank's cash flow from repayments. Analyze MTD and MoM changes.

Average Interest Rate: Calculate the average interest rate across all loans. Monitor MTD and MoM variations to gain insights into the overall cost of the lending portfolio.

Average Debt-to-Income Ratio (DTI): Evaluate the average DTI for borrowers to gauge their financial health. Compute average DTI for all loans and analyze MTD and MoM changes.

Good Loan vs. Bad Loan KPIs Requirement

Good Loan Application Percentage: Calculate the percentage of loan applications classified as good loans. This category encompasses loans with a fully paid status.

Good Loan Applications: Identify the total number of good loan applications.

Good Loan Funded Amount: Calculate the total funded amount in the good loan category.

Good Loan Total Received Amount: Identify the total received amount in the good loan category.

Bad Loan Application Percentage: Calculate the total bad loan percentage. This category encompasses charged-off loan status.

Bad Loan Applications: Identify the total number of bad loan applications.

Bad Loan Funded Amount: Identify the total bad loan funded amount.

Bad Loan Received Amount: Identify the total received amount via the bad loan category.

Dashboard Overview Requirements

The dashboard will present crucial loan-related metrics and trends using various chart types to provide a clear and insightful view of lending operations.

Monthly Trends by Funded Amount: Use a line chart to visualize monthly trends in total loan applications and total funded amount. Employ the issue date as the x-axis.

Regional Analysis by State: Use a filled map to illustrate the regional distribution of funded amounts. Loan Term-Based Analysis: Use a donut chart to depict the proportion of short-term (36 months) and long-term (60 months) loans.

Funded Loan by Employee Length: Use a bar chart to demonstrate the total funded loan amount by employee length.

Total Loan Amount by Purpose: Use a bar chart to illustrate the total funded amount by purpose.

Details Requirement

Utilize a table to present detailed information for each loan, including loan ID, customer name, gender, state, funded amount, received amount, grade, subgrade, and purpose.

Key Performance Indicators (KPIs) in the Report:

Total Loan Applications:

▫ 66.38k total applications ▫ MTD: 2.54k applications ▫ MoM Growth: +5.05%

Total Funded Amount:

▫ $2.11 billion total funding ▫ MTD: $79.98 million ▫ MoM Changes: +4.75%

Total Received Amount:

$3 billion total received MTD: $115.11 million MoM Changes: +7.42%

Total Interest Amount:

▫ $890 million ▫ MTD: $35.13 million ▫ MoM Changes: +14.02%

Average Interest Rate:

▫ Overall: 10.11% ▫ MTD: 9.78%

Average Debt-to-Income Ratio (DTI):

▫ DTI: 1.24%

Grid View:

In addition, a detailed table breaks down loan types, funded amounts, total applications, and received amounts, providing specific insights into different loan statuses.

Good Loan vs Bad Loan Analysis

▫ Good Loan Percentage: 75.51% ▫ Good Loan Applications: 50,130 ▫ Good Loan Total Funded Amount: $1.57 billion ▫ Good Loan Received Amount: $2.24 billion

Bad Loan

▫ Bad Loan Percentage: 24.49% ▫ Bad Loan Applications: 16,250 ▫ Bad Loan Funded Amount: $537.17 million ▫ Bad Loan Received Amount: $758.97 million

Insights and Analysis

▫ Total Funded Amount by Month: The highest funded amount was in October, with approximately $800 million.

▫ Funded Amount by State: Texas had the highest funded and received amounts.

▫ Funded Amount by Term: Short-term loans (36 months) accounted for 58%, while long-term loans (60 months) held the remaining 42%.

▫ Funded Amount by Employee Length: Employees with 10+ years of experience received the highest funded amounts.

▫ Funded Amount by Purpose: Debt consolidation was the primary purpose for loans.

Detailed Loan Information

A table provides comprehensive details for each loan, including loan ID, customer name, gender, state, funded amount, received amount, grade, subgrade, and purpose. This information enables a granular understanding of the bank's lending portfolio.

If you find merit in my hard work and believe I'm eligible for a Power BI Developer or entry-level Data Analyst role, your support in finding opportunities would be greatly appreciated.

Dont Forget to follow me on LinkedIn

Presented by-Saddam Ansari @ Aspiring Data Analyst

Thank you❤.