Project Excel link :https://drive.google.com/file/d/1pzPWO5yfPVww968cgLCH8F3bs0ULJAmq/view?usp=sharing
The Loan Portfolio Performance Dashboard provides a detailed analysis of loan data to understand risk, profitability, and borrower behavior in consumer lending. This Excel-based project helps financial analysts and institutions make data-driven decisions by visualizing key lending metrics, trends, and risk factors in an interactive dashboard.
- Evaluate loan performance across regions, income groups, and loan purposes.
- Identify default patterns and risk profiles for better decision-making.
- Analyze the Loan Profitability Index (LPI) to measure portfolio health.
- Forecast loan amounts and visualize year-over-year lending trends.
This project analyzes a real-world consumer lending dataset to explore the relationship between loan amount, income, interest rate, and default risk. Using Excel’s analytical and visualization features, it uncovers insights into borrower behavior, loan performance, and financial recovery patterns.
Dataset: Uploaded in github section
The dataset includes borrower demographics, loan attributes, interest rates, repayment status, and recovery details, providing a holistic view of consumer lending operations.
- Default Rate: 12.81%
- Average Interest Rate: 13.69%
- Grade F and G loans show the highest Loan Profitability Index (LPI).
- Debt consolidation and credit card loans have the highest default rates.
- Ulster and Northern Ireland show higher default ratios.
- The average loan amount is projected to increase in upcoming years.
- Total Loans: 70K
- Average Loan Amount: $14.1K
- Total Recovered Principal: $650M
- Recovery Rate: 5%
- Loan Distribution by Purpose and Region
- Loan Profitability Index (LPI) by Grade, Term, and Income
- Trend Analysis Over Time (2007–2014)
- Risk Profile Heatmap
- Loan Status Overview (Good vs. Bad Loans)
- Forecasting Average Loan Amount
- Scatter Plot: Loan Amount vs. Annual Income
- High-Risk and High-Interest Loan Comparison
| Category | Techniques |
|---|---|
| Data Cleaning | Handling missing data, filtering invalid entries |
| Analysis | Pivot Tables, SUMPRODUCT, INDEX-MATCH, MODE, IF statements |
| Formulas Used | AVERAGEIF, SUMIFS, COUNTIFS, VLOOKUP, FORECAST |
| Visualization | Conditional formatting, slicers, charts, and heatmaps |
| Dashboard Controls | Interactive slicers, refresh and clear buttons |
- Download the Excel dashboard file and open it in Excel (2019 or later).
- Use slicers to filter by year, grade, region, income, and loan purpose.
- Explore charts to monitor key metrics like default rate, ROI, and loan profitability.
- Click the Refresh or Clear buttons to update or reset the dashboard.
- Data Analysis and Modeling in Excel
- Financial Risk Profiling
- Dashboard Design and Storytelling
- Forecasting and Trend Analysis
- Advanced Excel Formulas and Automation
- Microsoft Excel
- Data Cleaning and Manipulation
- Pivot Tables and Charts
- Conditional Formatting
- Forecasting Tools
This Excel dashboard demonstrates how data analysis and visualization can help financial institutions understand loan risk, borrower behavior, and portfolio profitability. It acts as a strategic tool for risk mitigation, lending optimization, and informed decision-making in the banking sector.