Bank Loan Data Analysis Using Power BI

The Bank Loan Portfolio Analysis project aims to provide comprehensive insights into the bank's loan portfolio using Power BI visualization tools. By leveraging these technologies, we seek to enhance decision-making processes, mitigate risks, and optimize loan management strategies.

Link

POWER BI PDF File

Problem Statement

Dash Board 1: Summary

Total Loan Applications:

We need to calculate the total number of loan applications received during a specified period. Additionally, it is essential to monitor the Month-to-Date (MTD) Loan Applications and track changes Month-over-Month (MoM).

Total Funded Amount:

Understanding the total amount of funds disbursed as loans is crucial. We also want to keep an eye on the MTD Total Funded Amount and analyse the Month-over-Month (MoM) changes in this metric.

Total Amount Received:

Tracking the total amount received from borrowers is essential for assessing the bank's cash flow and loan repayment. We should analyse the Month-to-Date (MTD) Total Amount Received and observe the Month-over-Month (MoM) changes.

Average Interest Rate:

Calculating the average interest rate across all loans, MTD, and monitoring the Month-over-Month (MoM) variations in interest rates will provide insights into our lending portfolio's overall cost.

Average Debt-to-Income Ratio (DTI):

Evaluating the average DTI for our borrowers helps us gauge their financial health. We need to compute the average DTI for all loans, MTD, and track Month-over-Month (MoM) fluctuations.

Good Loan v Bad Loan KPI’s

Good Loan:

1. Good Loan Application Percentage
2. Good Loan Applications
3. Good Loan Funded Amount
4. Good Loan Total Received Amount

Bad Loan:

1. Bad Loan Application Percentage
2. Bad Loan Applications
3. Bad Loan Funded Amount
4. Bad Loan Total Received Amount

Loan Status Grid View

In order to gain a comprehensive overview of our lending operations and monitor the performance of loans, we aim to create a grid view report categorized by 'Loan Status.’ By providing insights into metrics such as 'Total Loan Applications,' 'Total Funded Amount,' 'Total Amount Received,' 'Month-to-Date (MTD) Funded Amount,' 'MTD Amount Received,' 'Average Interest Rate,' and 'Average Debt-to-Income Ratio (DTI),' this grid view will empower us to make data-driven decisions and assess the health of our loan portfolio.

CHARTS

Dash Board 2: Overview

1. Monthly Trends by Issue Date (Line Chart): To identify seasonality and long-term trends in lending activities

2. Loan Term Analysis (Donut Chart): To allow the client to understand the distribution of loans across various term lengths.

3. Employee Length Analysis (Bar Chart): How lending metrics are distributed among borrowers with different employment lengths, helping us assess the impact of employment history on loan applications.

4. Loan Purpose Breakdown (Bar Chart): Will provide a visual breakdown of loan metrics based on the stated purposes of loans, aiding in the understanding of the primary reasons borrowers seek financing.

5. Home Ownership Analysis (Tree Map): For a hierarchical view of how home ownership impacts loan applications and disbursements.

GRID

Dash Board 3: Details

Need for a comprehensive 'Details Dashboard' that provides a consolidated view of all the essential information within our loan data. This Details Dashboard aims to offer a holistic snapshot of key loan-related metrics and data points, enabling users to access critical information efficiently.

Screenshots

🛠 Skills

• Microsoft Power BI

• Microsoft Excel