Financial TemplatesFree with emailExcel workbook (.xlsx)
Loan Portfolio Delinquency Tracker
An Excel workbook that turns a list of loans into PAR 30, 60 and 90, the balance in each overdue bucket, risk by product, and a month-on-month roll-rate matrix. Paste your loans in and the sheets update by themselves.
Who it is for
Credit analysts, microfinance and lending teams, and small lenders who track overdue loans in Excel.
The problem it solves
Overdue loans are often tracked with ad hoc filters that are slow to repeat and hard to compare month to month, so nobody sees whether the book is getting healthier or sicker.
What is inside
- PAR 30, PAR 60 and PAR 90 calculated from outstanding balance
- Balance and number of loans in each bucket: Current, 1-30, 31-60, 61-90 and 90+
- PAR 30 by product, with a chart
- Roll rates: what share of each bucket got worse or recovered since last month
- Handles up to 500 loans, plain formulas only, no macros
How to use it
- Open the Loans sheet and delete the sample rows in the yellow cells (the sample data is made up).
- Paste your loans into columns A to F: ID, product, customer type, disbursed, outstanding balance and days past due.
- For roll rates, add last month's days past due in column G. Leave it blank if you do not have it.
- Read the results on the Summary and Roll rates sheets.