This project analyzes a loan portfolio dataset from 2021 to understand lending performance, borrower characteristics, and credit risk patterns.
The goal of this project was to apply structured data analysis to a financial dataset while demonstrating how analytical thinking can support portfolio monitoring and risk awareness.
This project analyzes a consumer lending portfolio using Excel to evaluate loan activities and performance, borrower financial characteristics, and overall portfolio yield.
The objective was to practice structured financial analysis by transforming raw loan data into meaningful portfolio-level insights using pivot tables, financial metrics, and dashboard visualizations.
Financial institutions need to understand how their loan portfolios perform in order to balance profitability and risk.
This analysis answers key questions:
- How large is the loan portfolio?
- Are repayments exceeding the funded loan amounts?
- What borrower characteristics define the portfolio?
- What does the overall portfolio yield look like?
The dataset includes borrower and loan attributes such as:
- Loan Amount
- Interest Rate
- Loan Term
- Total Payment Received
- Debt-to-Income Ratio (DTI)
- Employment Length
- Home Ownership
- Loan Grade and Subgrade
- Verification Status
- Issue Date
Total Records: ~38,576 loans
The Excel model is structured into three analytical layers:
Raw loan-level dataset containing borrower and loan attributes.
Pivot tables and formulas were used to compute key portfolio metrics.
A visual dashboard summarizes the portfolio using KPIs and charts.
The dashboards tracks several portfolio indicators:
- Loan Applications
- Total Funded Amount
- Total Payment Received
- Average Interest Rate
- Average Debt-to-Income Ratio
- Portfolio Yield
Portfolio Yield calculation: Portfolio Yield = Total Payment Received ÷ Total Funded Amount
From the analysis:
- The portfolio funded approximately $435.8M in loans.
- Total payments received are about $473.1M, meaning repayments exceed funded capital.
- The resulting portfolio yield is 108%.
- The average interest rate is about 12%.
- The average borrower debt-to-income ratio is roughly 13%. At an aggregate level, the portfolio appears profitable, though further segmentation is required to evaluate risk concentration.
- The monthly trend indicates consistent growth in loan demand, accelerating after mid-year. Applications reached their annual peak in December (4.3K), suggesting seasonal borrowing behavior and increased financial activity in the final quarter.
- The majority of loans were issued for Debt consolidation (18,214 applications), making it the largest borrowing category by a significant margin.
- Approximately 73% of loans are 60-month loans (28.2K) compared to 36-month loans (10.3K). This suggests that borrowers prefer lower monthly payments over shorter repayment periods.
- Borrowers with 10+ years of employment account for the highest number of loan applications, indicating that the bank likely view long-term employment stability as a key indicator of creditworthiness.
- The majority of borrowers are renters(18.4K), followed by mortgage holders (17.1K). Individuals who fully own homes represent the smallest borrower group. This suggests renters may have higher financial liquidity needs.
The dashboards summarizes portfolio performance through key KPIs and visual insights.
Key views include:
- Loan application volume
- Total funded amount vs payments received
- Interest rate averages
- Borrower debt-to-income levels
- Loan issuance Trends by month , employment length, term of loan, purpose.
- Microsoft Excel
- Pivot Tables
- Financial Ratio Analysis
- Dashboard Design
I studied Civil Engineering, where I developed a strong foundation in structured problem-solving, quantitative reasoning, and risk evaluation.
I am currently developing my skills in Data Analysis and Data Science, working with tools such as:
- Excel
- Python
- SQL
- Data visualization tools
This project is part of my effort to apply analytical thinking to real-world datasets.