Bank NPA ageing: build DPD and SMA buckets the way RBI dates them

The two RBI documents your query has to agree with

The categories come from RBI's Prudential Framework for Resolution of Stressed Assets (7 June 2019): SMA-0 for 1–30 days overdue, SMA-1 for 31–60, SMA-2 for 61–90. For cash credit and overdraft accounts the test is different — how long the balance has stayed above the sanctioned limit or drawing power — and there is no SMA-0 tier.

The dating convention comes from the IRACP clarifications of 12 November 2021. It says overdue flagging happens in the day-end process for the due date, the SMA/NPA date reflects status at the day-end of that calendar date, and gives a worked example: a loan due on 31 March 2021 and not paid is SMA-1 on 30 April, SMA-2 on 30 May and NPA on 29 June.

That example is the most useful test case you have. If your formula puts that loan in NPA on 28 June or 30 June, your day count is off by one.

So the formula is days_overdue = report_date − oldest_unpaid_due_date + 1.

What a branch MIS team usually receives

Three extracts from the core banking system are enough for term loans:

• loan_master — loan_id, branch, product, sanctioned_amt, disbursal date, and a facility-type flag (term loan versus cash credit, because the rule differs).
• schedule — loan_id, inst_no, due_date, emi, often with separate principal and interest columns.
• payments — loan_id, value_date, amount, plus a reversal flag for bounced NACH or cheque credits.

Ask for the reversal flag explicitly. A bounced EMI that was credited and then reversed looks paid in a naive sum.

Find the oldest unpaid instalment with cumulative sums

You do not need row-by-row allocation. Compare the running total of scheduled dues with the total paid up to the report date: the first instalment whose running total exceeds the amount paid is the oldest unpaid one. This mirrors the normal appropriation of payments to the oldest dues first. Data below is synthetic.

CREATE TABLE loan_master (loan_id TEXT PRIMARY KEY, branch TEXT, product TEXT, sanctioned_amt REAL);
CREATE TABLE schedule (loan_id TEXT, inst_no INTEGER, due_date TEXT, emi REAL);
CREATE TABLE payments (loan_id TEXT, value_date TEXT, amount REAL);

INSERT INTO loan_master VALUES
 ('L01','Sector 18','Two-wheeler',90000),('L02','Sector 18','Personal',300000),
 ('L03','Sector 62','Personal',250000),('L04','Sector 62','Business',600000),
 ('L05','Sector 62','Two-wheeler',80000);

-- six month-end EMIs, January to June 2026
WITH RECURSIVE n(k) AS (SELECT 1 UNION ALL SELECT k+1 FROM n WHERE k<6)
INSERT INTO schedule
SELECT l.loan_id, k, date('2026-01-31','start of month','+'||k||' months','-1 day'),
       CASE l.loan_id WHEN 'L01' THEN 4200 WHEN 'L02' THEN 14500 WHEN 'L03' THEN 12000
                      WHEN 'L04' THEN 31000 ELSE 3800 END
FROM loan_master l, n;

INSERT INTO payments VALUES
 ('L01','2026-01-31',4200),('L01','2026-02-28',4200),('L01','2026-03-31',4200),
 ('L01','2026-04-30',4200),('L01','2026-05-31',4200),('L01','2026-06-30',4200),
 ('L02','2026-01-31',14500),('L02','2026-02-28',14500),('L02','2026-03-31',14500),
 ('L02','2026-05-10',14500),
 ('L03','2026-01-31',12000),('L03','2026-02-28',6000),
 ('L04','2026-01-31',31000),('L04','2026-02-28',31000),('L04','2026-03-31',31000),
 ('L04','2026-04-30',31000),('L04','2026-06-15',31000),
 ('L05','2026-01-31',3800),('L05','2026-02-28',3800),('L05','2026-03-31',1000);

CREATE VIEW ageing AS
WITH params AS (SELECT '2026-06-30' AS as_of),
paid AS (
  SELECT p.loan_id, SUM(p.amount) AS paid_total
  FROM payments p, params WHERE p.value_date <= params.as_of GROUP BY p.loan_id),
sched AS (
  SELECT s.*, SUM(s.emi) OVER (PARTITION BY s.loan_id ORDER BY s.inst_no) AS cum_due
  FROM schedule s, params WHERE s.due_date <= params.as_of),
oldest_unpaid AS (
  SELECT s.loan_id, MIN(s.due_date) AS overdue_since,
         MAX(s.cum_due) - COALESCE(pd.paid_total, 0) AS arrears
  FROM sched s LEFT JOIN paid pd USING (loan_id)
  WHERE s.cum_due > COALESCE(pd.paid_total, 0)
  GROUP BY s.loan_id)
SELECT l.loan_id, l.branch, o.overdue_since,
       COALESCE(CAST(julianday(p.as_of) - julianday(o.overdue_since) + 1 AS INTEGER), 0) AS dpd,
       COALESCE(o.arrears, 0) AS arrears
FROM loan_master l CROSS JOIN params p LEFT JOIN oldest_unpaid o USING (loan_id);

SELECT loan_id, branch, overdue_since, dpd, arrears,
       CASE WHEN dpd = 0 THEN 'Standard, no overdue'
            WHEN dpd <= 30 THEN 'SMA-0'
            WHEN dpd <= 60 THEN 'SMA-1'
            WHEN dpd <= 90 THEN 'SMA-2'
            ELSE 'NPA' END AS bucket
FROM ageing ORDER BY dpd DESC;

Output as of 30 June 2026:

Look at L05. The borrower paid ₹1,000 against the March EMI of ₹3,800. A report that counts "months with any payment" would treat March as serviced. The oldest unpaid due is still 31 March, so the account is 92 days overdue.

L04 paid May's EMI late, on 15 June. That payment cleared May, so on 30 June only June's instalment is unpaid and it is on day 1. Run the same query with as_of set to 14 June and L04 shows the May instalment 15 days overdue.

The upgrade rule that most dashboards get wrong

Suppose L03 pays ₹24,000 in July. Its DPD falls, and a DPD-only rule would move it back to SMA-2 or better. RBI's 2021 clarification says an NPA may be upgraded to standard only if the entire arrears of interest and principal are paid (restructured accounts and DCCO cases follow their own rules). So your classification needs memory: keep an npa_flag that is set when DPD crosses 90 and cleared only when arrears = 0. A pure function of today's DPD cannot produce the correct status.

Traps specific to loan books

• Month-end snapshots. A loan that crossed 90 days on the 12th and was fully regularised on the 25th never appears as NPA in a month-end file, but it was one. Credit monitoring wants daily flags, or at least the peak DPD in the month.
• Reversed credits. Filter out reversed NACH or cheque entries before summing payments, or reverse them with negative rows.
• Cash credit accounts in the same query. Do not force overdraft and CC accounts through an instalment calculation. Measure continuous days above the lower of sanctioned limit or drawing power instead.
• Charges and interest mixed with EMI. If penal charges are posted as dues, decide with the credit policy team whether they enter cum_due. Document the choice in the report footnote.

Presenting to the credit head

A credit head reads NPA ageing for movement, not just stock. Give three things on one page:

1. 1Count and arrears by bucket for this month and last month, by branch.
2. 2A "will cross 90 in the next 30 days" list: every SMA-2 account with DPD of 61 or more. These are the collection team's priorities this week.
3. 3Accounts that left NPA this month, with the arrears they cleared, so nobody wonders whether an upgrade was premature.

Always print the report date and the day-count convention on the page. Two teams arguing about 91 versus 90 days is usually two teams using different conventions.

This kind of query — cumulative sums, a dated rule, a flag with memory — is typical of the SQL taught in the Data Analyst programme. For the next step in a lending book, see how delinquency moves month to month in credit card roll rates, or collections performance in NBFC collection efficiency.