Loan Ageing Report
Turn due-date and DPD data into ageing buckets, risk flags and a management summary.
Intermediate · 35–50 min
Objectives
- Understand DPD
- Create ageing buckets
- Flag risky records
- Summarize POS by risk
Project dataset
| Row | A: Loan ID | B: Branch | C: Due Date | D: POS | E: DPD | F: Status |
|---|---|---|---|---|---|---|
| 2 | LN1001 | Mumbai | 2026-03-10 | 125000 | 0 | Active |
| 3 | LN1002 | Pune | 2026-02-15 | 98000 | 45 | PNPA |
| 4 | LN1003 | Nashik | 2026-01-20 | 152000 | 91 | NPA |
| 5 | LN1004 | Mumbai | 2026-03-22 | 74000 | 18 | Active |
| 6 | LN1005 | Pune | 2025-12-18 | 91000 | 67 | PNPA |
| 7 | LN1006 | Nashik | 2025-11-04 | 184000 | 120 | NPA |
Guided checkpoints
Understand ageing data
Identify Loan ID, Due Date, POS, DPD and Status in the dataset.
Understand the fields used in ageing reports.
Create DPD buckets
Create 0–30, 31–60, 61–90 and 91+ buckets.
Group loans by delinquency age.
Flag high-risk loans
Create a risk flag that marks 91+ DPD loans for management review.
Turn ageing into an exception indicator.
Summarize POS
Create a summary of outstanding POS by ageing bucket.
Connect risk categories to exposure.
Management review
Identify the bucket that should receive the strongest attention in this sample.
Prioritize risk.
Download sample project .xlsx · All portfolio projects
Interactive practice runs in your browser.