Loan Portfolio Performance & Risk Analysis
A Complete Data-Driven Guide
Understand how data analytics measures, monitors, and improves loan portfolio health using real-world banking data.
Discover how financial institutions assess portfolio health, identify early delinquencies, and track credit risk metrics like PAR30, PAR90, and NPL using powerful data visualization and analytics techniques.
Understanding PAR30 and PAR90
Key Indicators of Loan Portfolio Health
PAR (Portfolio at Risk) is a vital metric that helps banks and NBFCs measure the share of loans that are overdue. PAR30 represents loans delayed by more than 30 days — an early warning signal for potential risk — while PAR90 indicates loans overdue for over 90 days, often classified as non-performing assets (NPA).
Tracking these indicators allows financial analysts to monitor asset quality, detect delinquency trends early, and take data-driven actions for collection and recovery. A consistently rising PAR30 or PAR90 trend signals weakening credit discipline and requires closer risk supervision.
📈 PAR30
Loans overdue for more than 30 days. Indicates early repayment delays and short-term stress signals.
⚠️ PAR90
Loans overdue for more than 90 days. Typically treated as NPA (Non-Performing Assets).
What is Loan Portfolio Performance Analysis?
A concise explanation of portfolio monitoring, PAR and NPL metrics
Loan portfolio performance analysis measures the health and credit quality of a lender’s outstanding loans. It focuses on early detection of delinquency, concentration risk, and recovery performance using metrics such as PAR30, PAR90, and NPL ratio.
- PAR30 / PAR90: Percent of outstanding overdue >30 / >90 days.
- NPL Ratio: Outstanding on loans classified as NPL divided by total outstanding.
- Delinquency Buckets: Current, 1–30, 31–60, 61–90, 91–180, >180 days.
- Vintage Analysis: Cohort performance by disbursement month.
PAR Trend (Example)
PAR30 and PAR90 trends indicate rising portfolio risk and help prioritize collections.
Why Risk Analysis is Crucial for Banks & NBFCs
Early detection of repayment stress protects capital, reduces provisioning, and improves profitability. Risk analysis guides policy, collections, and underwriting.
- Protects capital: Detect troubled accounts before they become write-offs.
- Improves collections: Prioritise accounts using risk scores and PAR buckets.
- Regulatory compliance: Accurate provisioning & NPA classification.
- Product strategy: Price risk and design credit limits per segment.
Risk Distribution by Branch
Use this chart to spot branches with rising PAR or NPL concentration.
Key Performance Indicators (KPIs) for Loan Portfolios
These KPIs allow analysts to quickly judge portfolio health and act on early warning signals.
Total Outstanding
—
Total value of all active loans. It represents the exposure that needs monitoring and recovery tracking.
PAR30
—
Portfolio at Risk beyond 30 days — an early warning of repayment stress. A rising PAR30 means more borrowers are slipping on payments.
PAR90
—
Portfolio at Risk beyond 90 days — serious delinquencies often classified as NPA. These require recovery action or restructuring.
NPL %
—
Non-Performing Loan Ratio — percentage of portfolio classified as non-performing (commonly overdue >90 days). Lower is better.
- Recovery Rate: Percent of defaulted amounts recovered.
- Write-off Ratio: Total write-offs / total outstanding.
- Top Exposure Share: Share of top 10 borrowers in outstanding.
Step-by-Step Risk Analysis Workflow
A practical sequence to compute KPIs and build dashboards from your data.
- Data Cleaning: Trim, parse dates, coerce numeric fields, remove duplicates.
- Merge Tables: loans ← payments (last_payment_date), loans ← customers (credit_score).
- Days Past Due: Use last_payment_date or maturity_date to compute days_past_due.
- Create Buckets: current, 1–30, 31–60, 61–90, 91–180, >180 days.
- Compute KPIs: PAR30, PAR90, NPL, Recovery, Top Exposure.
- Visualize & Report: Build Chart.js or Power BI dashboards and export insights.
Delinquency Buckets (Example)
Bucketed aging helps estimate provisioning and prioritise collection actions.
Visualization & Dashboard Insights
Interactive charts to monitor portfolio health — PAR trends, aging, risk bands and concentration.
PAR Trend (PAR30 vs PAR90)
Delinquency Buckets
Risk Band Distribution
Top 10 Exposures
Case Study — Top 20 Exposures by Outstanding
Concentration risk example: identify the largest borrowers and their share of portfolio exposure. Use this to set exposure limits and credit decisions.
Below is an example Top-20 exposures chart. High concentration (large share of a few borrowers) increases portfolio risk — monitor top borrowers and apply exposure limits or collateral requirements.
Top 20 Borrowers — Outstanding (Horizontal)
Use the CSV to inspect borrower IDs and exact outstanding values.
Vintage Cohort Analysis — Loan Performance by Disbursement Month
Track how loan cohorts (by disbursement month) age into delinquency or charge-off. Cohort charts reveal origination quality and deterioration patterns over time.
Note: The sample chart defaults to 6 months for clarity in a blog layout — it keeps the visualization clean and easy to read. For production dashboards you can use 12–36 months (or more) to study long-term cohort deterioration. Use the buttons to view 6 / 12 / 24 month windows.
Cohort Stacked Bar
Each bar is a disbursement month cohort. Stacked segments show outstanding in each delinquency bucket.
Dashboard Summary — Quick View
Key quick-glance metrics and a risk radar to help management make fast decisions.
Risk Radar
A snapshot of five risk dimensions — higher values indicate greater risk.
Conclusion
Data-driven portfolio monitoring helps prevent credit deterioration, reduce write-offs, and improve recovery. Use PAR30 and PAR90 to spot trends early and act decisively.
- Monitor: Track PAR30 monthly to catch early stress.
- Investigate: Use branch & cohort breakdowns to find hotspots.
- Act: Prioritise collections, restructure high-value accounts, and set provisioning rules.
Want to build a live dashboard or connect your Google Sheet? Get in touch with our analytics team.
Quick Start
- Prepare your loan & payment tables (CSV/Sheets).
- Publish sheet or upload CSV for the dashboard to fetch data.
- Link your data with
window.LOAN_DASHBOARD_DATAor auto-fetch via script.
How to Create Loan Portfolio Dashboard in Excel
Build your own Loan Portfolio Performance & Risk Analysis Dashboard in Excel — using Power Query, Pivot Tables, and smart visual charts. Follow these steps to turn raw loan data into powerful insights.
-
Step 1 — Prepare your raw data:
Collect 3 main Excel sheets or tables:- Loans Table:
Loan_ID,Customer_ID,Disbursement_Date,Loan_Amount,Outstanding,Branch - Payments Table:
Loan_ID,Payment_Date,Amount - Customers Table:
Customer_ID,Credit_Score,Risk_Band
- Loans Table:
-
Step 2 — Load and clean data using Power Query:
Go toData → Get Data → From Table/Rangefor each dataset.
Inside Power Query:- Use
Remove Rows → Remove Blanksto clean missing data. - Change column types to Date or Decimal Number as needed.
- Rename columns with readable names (e.g., “Outstanding_Amount” instead of “OutAmt”).
- Close & Load each query back to Excel.
- Use
-
Step 3 — Merge datasets:
In Power Query →Home → Merge Queries→ join Loans with Payments onLoan_ID.
Then join the result with Customers onCustomer_ID.
You now have one master table combining loan, payment, and customer details. -
Step 4 — Create “Days Past Due” (DPD):
Add a new column in Power Query:
Days_Past_Due = DateTime.LocalNow() - [Last_Payment_Date]
Convert to number format.
Then create a Bucket column using conditional logic:if [Days_Past_Due] ≤ 30 then "Current"else if [Days_Past_Due] ≤ 60 then "PAR30"else if [Days_Past_Due] ≤ 90 then "PAR60"else "PAR90"
-
Step 5 — Build Pivot Tables for KPIs:
Insert →PivotTablefrom the Master Table:- Place Bucket in Rows, Outstanding_Amount in Values → this gives total outstanding per bucket.
- Add a calculated field for PAR ratios:
- PAR30%: = (Outstanding in PAR30 / Total Outstanding)
- PAR90%: = (Outstanding in PAR90 / Total Outstanding)
- NPL Ratio: = (Outstanding in PAR90 or above / Total Outstanding)
-
Step 6 — Create Excel Charts:
📈 PAR Trend Chart (Line Chart)
- Insert →
Line Chart - X-Axis → Month; Y-Axis → PAR30% and PAR90%
- Use two series (gold for PAR30, light brown for PAR90)
- Add Data Labels → Inside End
📊 Delinquency Buckets (Column Chart)
- Insert →
Clustered Column Chart - X-Axis → Buckets (Current, 1-30, 31-60, etc.)
- Y-Axis → Outstanding Amount
- Apply Conditional Formatting → darker gold for higher buckets
🌀 Risk Band Distribution (Doughnut Chart)
- Insert →
Doughnut Chart - Legend → Risk Bands (Excellent, Good, Fair, Poor, Very Poor)
- Values → Count of Customers
- Color palette → from light gold to deep bronze
📋 Top 10 Exposures (Horizontal Bar Chart)
- Insert →
Bar Chart - Sort data descending by Outstanding Amount
- Show top 10 Loan IDs or Borrower Names
- Add data labels → “₹” formatted values
- Insert →
-
Step 7 — Design your dashboard layout:
- Use
Insert → Shapes → Rectangleto create gold-bordered KPI cards. - Add key numbers (Total Outstanding, PAR30%, PAR90%, NPL%) using text boxes linked to Pivot values (
=B5style links). - Align charts neatly using
View → Snap to Grid. - Apply background color: #f39c12 accents on black or dark gray.
- Use
-
Step 8 — Automate with Power Query refresh:
- Use
Data → Refresh Allto update latest payment and outstanding data automatically. - Optional: Add a small macro button to trigger refresh instantly.
- Use
-
Step 9 — Make it interactive:
Add Slicers for:- Branch
- Product Type
- Risk Band
- Month
-
Step 10 — Save & Share:
Save the workbook asLoan_Dashboard.xlsx.
Optionally export charts as PNG to insert into PowerPoint or a website (like this blog).
Disclaimer
This Excel dashboard guide and all visuals are designed for educational and training purposes only. The sample data used here is fictional and does not represent any real customers or financial institutions. Always follow your company’s data security and compliance guidelines when using real data.