Free tools / Roll rate
Roll rate SQL prompt and checks
Shows how many loans in one bucket move to a worse, same or better bucket next month. It is free, needs no sign-up, and works from table and column names alone.
What you enter
You type names only: table and column names, never customer data.
- Monthly snapshot table
- Loan id column
- Month-end date column
- Bucket column (or DPD column)
- Balance column
- From month
- To month
- Measure
- Bucket order
How to use it
- You need a table with one row per loan per month. Build it first if you only have transactions.
- Run the matrix for the last 12 months.
- Read the forward rates from left to right, then watch the trend.
Checks to run on the answer
AI can write SQL that looks right and is wrong. Run these checks before you trust the result.
- Each from-bucket row sums to 100% including the closed and written-off category.
- Opening count for each month equals the closing count of the previous month, less closures, plus new loans.
- No loan appears twice in the same month.
- Month gaps do not exist, or are handled.
- Balance-based and count-based matrices tell the same story, or you know why not.
Open the tool
You get the ready prompt, the checks above, what healthy and warning results look like, and how to present the result to an approver.
Open the free toolsRelated guides
Roll rate analysis: how to read a roll-rate matrix
A roll-rate matrix shows where loans in each bucket end up next month. Learn to build one, read it, and use it to forecast how many loans reach 90+.
BasicsDelinquency buckets: 1-30, 31-60, 61-90 and 90+ explained
What delinquency buckets are, how DPD maps to them, how they link to RBI's SMA categories and Ind AS 109 stages, and how to report them.