Learn / Data guide
Derived fields: what to calculate before any credit-risk analysis
Most analyses need fields that no table stores. DPD, months on book, bad flag and roll flag are calculated, and each lender calculates them a little differently. This guide lists the common ones with a plain definition, a SQL pattern and the choice you must make first. Patterns use the table and column names from the data guide. Swap in your own names, and write your choices down once so every analysis uses the same ones.
Rules before you calculate anything
- Fix the grain first. One row per loan, or one row per loan per month-end. Mixing them is the main cause of double counting.
- Reduce each source to one row per key before you join it. Never join the schedule table straight to the payments table: every instalment pairs with every payment and the rows multiply.
- Use left joins from the base table so loans with no payments or no bureau record stay in.
- State the as-of date once and use it everywhere.
- After building, check three things: the row count equals the base table, the key is unique, and ten loans match a hand calculation.
Delinquency fields
| Field | What it means | SQL pattern (PostgreSQL) | Decide first |
|---|---|---|---|
| DPD | Days since the oldest unpaid instalment fell due, on the as-of date | GREATEST(0, DATE '2026-09-30' - MIN(due_dt) FILTER (WHERE cum_due > paid)) where cum_due is the running total of due_amt per loan and paid is total successful payments to the as-of date | Allocation rule (oldest due first is usual), grace days, which payment statuses count, how reversals are netted |
| Bucket | The DPD band the loan sits in | CASE WHEN dpd <= 0 THEN 'Current' WHEN dpd <= 30 THEN '1-30' WHEN dpd <= 60 THEN '31-60' WHEN dpd <= 90 THEN '61-90' ELSE '91+' END | Boundary days. 91+ means more than 90 |
| Overdue amount | What is due up to the as-of date and not yet paid | GREATEST(0, SUM(due_amt) FILTER (WHERE due_dt <= DATE '2026-09-30') - COALESCE(paid, 0)) | Principal only, or principal plus interest and charges |
| Unpaid instalments | How many instalments are still open | COUNT(*) FILTER (WHERE cum_due > paid) over the schedule rows due by the as-of date | How a part payment is treated |
| Worst DPD in a window | The highest DPD a loan reached in a period | MAX(dpd) FILTER (WHERE mob BETWEEN 0 AND 6) | Window length, and whether it counts month-end values or any day |
Time fields
| Field | What it means | SQL pattern (PostgreSQL) | Decide first |
|---|---|---|---|
| Disbursal month (vintage) | The month the loan was disbursed, used to group cohorts | DATE_TRUNC('month', disb_dt) | Disbursal date or sanction date |
| Months on book (MOB) | How many months have passed since disbursal | (EXTRACT(YEAR FROM snap_dt) - EXTRACT(YEAR FROM disb_dt)) * 12 + EXTRACT(MONTH FROM snap_dt) - EXTRACT(MONTH FROM disb_dt) | Whether the disbursal month is MOB 0 or MOB 1 |
| Maturity date | The month the last instalment is due | disb_dt + (tenure_m || ' months')::interval | Contract tenure, or the last due date in the schedule |
| Age of loan in days | Days since disbursal | DATE '2026-09-30' - disb_dt | Used for early-default windows |
Outcome flags
| Field | What it means | SQL pattern (PostgreSQL) | Decide first |
|---|---|---|---|
| Bad flag | Whether the loan reached your default definition inside a fixed window | MAX(CASE WHEN dpd > 90 AND mob <= 12 THEN 1 ELSE 0 END) per loan over its monthly snapshots | The DPD line, the window, and what to do with loans too young to have a full window |
| First-payment default | The first instalment was not paid in time | CASE WHEN paid_by_first_due_plus_n_days < first_due_amt THEN 1 ELSE 0 END, where paid_by_first_due_plus_n_days sums payments up to the first due date plus N days | N days, and the amount that counts as paid |
| Early default | A bad outcome in the first few months | MAX(CASE WHEN dpd > 30 AND mob <= 3 THEN 1 ELSE 0 END) | Both the DPD line and the number of months |
| Active flag | Whether the loan is still live on the as-of date | CASE WHEN status = 'Active' AND pos_amt > 0 THEN 1 ELSE 0 END | Treatment of written-off and restructured loans |
| Prepayment flag | The loan closed before its scheduled maturity | CASE WHEN closure_dt < maturity_dt THEN 1 ELSE 0 END | A tolerance for loans closed within a few days of maturity |
Movement fields
| Field | What it means | SQL pattern (PostgreSQL) | Decide first |
|---|---|---|---|
| Previous bucket | The bucket at the last month-end | LAG(bucket) OVER (PARTITION BY loan_id ORDER BY month_end) | Check the previous row really is the previous month. A gap makes LAG point to the wrong month |
| Next bucket | The bucket at the next month-end | LEAD(bucket) OVER (PARTITION BY loan_id ORDER BY month_end) | Same check. Also what to do with loans that close in between |
| Roll flag | Worse, same or better than last month | CASE WHEN next_idx > idx THEN 'Roll forward' WHEN next_idx < idx THEN 'Cure' ELSE 'Stay' END, where idx numbers the buckets 0 to 4 | Count or balance, and which loans are in the base |
| Collected in month | Money actually received in the month | SUM(pay_amt) FILTER (WHERE txn_status = 'SUCCESS' AND pay_dt BETWEEN month_start AND month_end) | Whether reversals are netted and whether failed presentations are excluded |
Application and bureau fields
| Field | What it means | SQL pattern (PostgreSQL) | Decide first |
|---|---|---|---|
| Bureau score at application | The latest bureau score before the application date | SELECT DISTINCT ON (app_id) app_id, bureau_score FROM bureau_pull WHERE pull_dt <= app_dt ORDER BY app_id, pull_dt DESC | Maximum age of a pull you accept, and how to treat the no-history value |
| FOIR | Fixed obligations as a share of income | (existing_emi + proposed_emi) / NULLIF(monthly_income, 0) | Which obligations count, and declared or verified income |
| Score band | Bureau score grouped into bands | CASE WHEN score = -1 THEN 'No history' WHEN score < 650 THEN '<650' WHEN score < 700 THEN '650-699' WHEN score < 750 THEN '700-749' ELSE '750+' END | Band edges. Use the ones your policy already uses |
| Ticket and tenure bands | Loan size and tenure grouped into bands | CASE WHEN sanction_amt < 50000 THEN '<50k' WHEN sanction_amt < 200000 THEN '50k-2L' ELSE '2L+' END | Band edges, and sanctioned or disbursed amount |
| Utilisation | Share of a limit that is in use | balance / NULLIF(credit_limit, 0) | Limit at the month-end, or the original limit |
What we checked
We ran these patterns on a loan data set with known answers. DPD, bucket and overdue amount matched the known answer on every loan whose schedule was complete. A base table built by reducing each source to one row per key came out with exactly one row per loan, while a direct join of schedule to payments produced about 140 rows per loan.
The roll flag worked as a pattern, but built from stored month-end snapshots it differed from the known answer in 3 of 25 cells, because a few stored month-end values were wrong. If the result matters, recalculate the bucket from payments for both months instead of trusting the stored value.
The bad flag, first-payment default and the band fields depend on your own definitions, so there is no single right answer to test against. Write your definition down and keep it fixed.
Where to go next
The Analysis base table tool writes the query that builds one table with all of these fields, using your table names. Run its five checks before you start the analysis.
Try it on your own tables
The free tools give you the prompt, the checks and how to present the result. You enter table names only.
See the free toolsFrequently asked questions
Why do I have to calculate these fields myself?
Source systems store facts: what was due, what was paid, when. Fields like DPD, months on book and the bad flag are interpretations, so each lender defines them a little differently and rarely stores them.
Why does joining schedule to payments inflate my numbers?
Each instalment pairs with every payment on the loan, so one loan becomes many rows. Total each source to one row per loan first, then join.
Can I copy these patterns as they are?
Use them as a starting point. Replace the names with yours, and make the choices in the last column before you run anything.
Keep reading
Credit-risk data guide: the tables and columns behind every tool
A plain-English guide to the loan, repayment, payment, bureau and application tables used in credit-risk work: what each column means, where to find it, and what goes wrong.
BasicsWhat is DPD (days past due) and how to calculate it
DPD counts how many days a loan is overdue. Learn the rule, a worked example with several instalments, and the mistakes that make DPD wrong.