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 1.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. 2.Total Funded Amount: Comprehend the total amount of funds disbursed as loans. Illustrate MTD and MoM changes. 3.Total Amount Received: Track the total amount received from borrowers to assess the bank's cash flow from repayments. Analyze MTD and MoM changes. 4.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. 5.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. 1.Monthly Trends by Total Applications : Use a line chart to visualize monthly trends in total loan applications. 2.Regional Analysis by State: Use a filled map to illustrate the regional distribution of funded amounts. 3.Loan Term-Based Analysis: Use a donut chart to depict the proportion of short-term (36 months) and long-term (60 months) loans. 4.Funded Loan by Employee Length: Use a bar chart to demonstrate the total funded loan amount by employee length. 5.Total Aoan Amount by Purpose: Use a column 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. Result Key Performance Indicators (KPIs) in the Report: 1.Total Loan Applications: 36.6k total applications MTD: 4.3k applications MoM Growth: +6.91% 2.Total Funded Amount: $435.8 million total funding MTD: $54.0 million MoM Changes: +13.06% 3.Total Received Amount: $473.1 million total received MTD: $58.1 million MoM Changes: +15.84% 4.Average Interest Rate: Overall: 12.05% MTD: 12.36% MoM: +3.47% 5.Average Debt-to-Income Ratio (DTI): DTI: 13.33% MTD: 13.67% MoM: +2.73%
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 -Good Loan Percentage: 86.18% -Good Loan Applications: 33.2K -Good Loan Total Funded Amount: $370.2 Million -Good Loan Received Amount: $435.8 Million
Bad Loan -Bad Loan Percentage: 13.82% -Bad Loan Applications: 2.5K -Bad Loan Funded Amount: $65.5 million -Bad Loan Received Amount: $37.3 million
Insights and Analysis -Total Applications by Month: The highest Applications was in December, with approximately 4.3k. -Total Applications by Term: Short-term loans (36 months) are 28.237k, while long-term loans (60 months) are 10.339k. -Total Applications by Employee Length: Employees with 10+ years of experience received the highest funded amounts. -Total Applications 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.