Lesson 1.2: The loan lifecycle and the data it leaves behind
Follow a loan from application to closure and learn which tables it creates, how days past due are counted, and why rejected applicants have no outcome.
By the end of this lesson you will be able to follow a loan through its life, name the data each stage creates, and say which tables a credit risk analyst needs and what one row means in each of them. You will learn how days past due are counted and grouped into buckets, why a monthly snapshot is the standard shape of portfolio data, and why an analyst can only see the repayment behaviour of people the lender said yes to. You should have read Lesson 1.1, so that PD, LGD and EAD are familiar. You need no knowledge of databases. A basic idea of what a table is, from the Data Science track, is enough.
Every number in credit risk begins as a row in a system somebody built for another purpose. The loan origination system exists to approve and disburse loans. The collections system exists to chase late payments. Neither was designed for analytics. The analyst's first job is therefore to understand how the lender's day-to-day work leaves its traces in data, and what each trace can and cannot tell you.
The life of a loan
Follow a personal loan of ₹2 lakh over 24 months. It passes through the same stages as almost every retail loan.
1. Application. The customer, or a partner such as an app or a dealer, submits details: name, income, employment, address, the amount and tenure wanted.
2. Verification and policy checks. The lender checks identity (KYC), looks for fraud signals and applies simple rules such as minimum age and income. These are called policy rules, and they come before any score.
3. Credit bureau check. The lender pulls the applicant's record from a credit bureau: past loans, repayment history, current debts and recent enquiries from other lenders.
4. Scoring and decision. A scorecard or model gives a risk score, and a decision rule approves, declines or sends the case for manual review. The rule often sets the amount and the interest rate too.
5. Disbursal. If the customer accepts, money is paid out and an instalment schedule starts.
6. Servicing and repayment. Each month an instalment falls due. The customer pays on time, pays late or does not pay.
7. Delinquency and collections. If a payment is missed, the collections process begins with reminders and calls, and later visits or legal steps.
8. Default, write-off and recovery. If the borrower stays unpaid long enough, the loan is classified as defaulted, and eventually the unpaid part may be written off. Recoveries may continue afterwards.
9. Closure. The loan is repaid in full, settled for less, or written off.
Each stage leaves data, and each stage is also a decision point where credit risk analytics can help: the score at stage 4, the limit and price at stage 4, the early warning at stage 6, the collections strategy at stage 7. Throughout this track, ask yourself which stage a technique belongs to.
What data each stage creates
Stage · Data produced · Typical table · Example fieldsApplication · What the customer says · Application table · Applicant id, date, requested amount, income declared, employment type
Verification · Checks and flags · Verification table · KYC status, fraud flags, address match
Bureau · Outside credit history · Bureau table · Bureau score, number of loans, worst delinquency in 12 months, enquiries in 6 months
Decision · Score and outcome · Decision table · Score, decision, reason code, approved amount, rate
Disbursal · The loan itself · Loan master · Loan id, disbursal date, amount, tenure, rate, product
Repayment · What was due and paid · Instalment ledger · Loan id, due date, amount due, amount paid, paid date
Collections · Contact and outcomes · Collections table · Call date, outcome, promise to pay, field visit
Recovery · Money after default · Recovery table · Loan id, recovery date, amount, cost
Look at the "typical table" column and you can see that the tables describe different things. That is the idea of grain from Lesson 1.1 of the Data Science track: what one row stands for. In the application table, one row is one application. In the loan master, one row is one loan. In the instalment ledger, one row is one instalment of one loan. Joining tables without keeping this straight is the quickest way to count the same loan many times.
The ledger of 106,080 rows is 4,420 loans times 24 instalments. If an analyst joined the loan master to the ledger and then counted rows, they would report 106,080 "loans". The correct count is the number of distinct loan ids, 4,420. This is not a hypothetical mistake. It is the most common first error in credit data.
The funnel and the population you cannot see
Not everyone who applies gets a loan. The numbers shrink at every stage, and they shrink in a way that matters for modelling. Suppose a lender receives 10,000 applications. Policy rules screen out 20 per cent, leaving 8,000. The score approves 65 per cent of those, which is 5,200. Of the approved customers, 85 per cent accept and are disbursed, which is 4,420.
With the default rates, 44.2 per cent of applicants become loans and 55.8 per cent never produce a repayment record. This is the selection problem. A model learns from the loans that exist, which are the ones the old rules chose. That group is safer than the full applicant pool, because the lender rejected the riskiest applicants. A new model trained only on approved loans has never seen how declined applicants would have behaved, and will therefore be unreliable if the lender plans to approve more of them. The technique for handling this is called reject inference, and a later lesson covers it. For now, remember that the data is a record of what the lender did, not a record of what the world does.
The repayment ledger and days past due
The instalment ledger is the most important table in credit risk, because everything about delinquency comes from it. Take a loan of ₹2 lakh over 24 months with an instalment of ₹9,500 due on the 5th of each month. The first three rows of its ledger might look like this.
loanid · instalmentno · duedate · amountdue · paiddate · amountpaidL5001 · 1 · 2026-02-05 · 9,500 · 2026-02-05 · 9,500
L5001 · 2 · 2026-03-05 · 9,500 · 2026-03-20 · 9,500
L5001 · 3 · 2026-04-05 · 9,500 · (not yet paid) · 0
The second instalment was paid 15 days late. The third is unpaid. How many days late is this loan on, say, 20 April? The measure used throughout the industry is days past due, written DPD: the number of days since the oldest unpaid amount first fell due. Here the oldest unpaid instalment fell due on 5 April, so on 20 April the loan is 15 days past due.
DPD is then grouped into buckets, which are ranges used for reporting and for the roll rate analysis that comes in Module 3. Common buckets are 0 (current), 1 to 30, 31 to 60, 61 to 90 and 90 plus. Exact definitions differ between lenders and products, so always check the lender's own.
Predict first: a borrower has missed two instalments of ₹9,000, the oldest due 45 days ago. Which bucket are they in, and how much is overdue? Decide before you try the calculator.
They are in the 31 to 60 bucket, with ₹18,000 overdue, assuming the second instalment is not yet 30 days old. Use the calculator to see how the picture changes with the days.
Nobody wants to look at a ledger row by row, so analysts convert it into a snapshot.
The monthly snapshot: the shape of portfolio data
A snapshot records the state of every loan on a fixed date, usually month-end. One row is one loan at one month-end: its outstanding balance, its DPD, its bucket and the number of instalments paid. Stack twelve months of snapshots and you have a table of loan-months, which is the standard input for delinquency analysis, vintage curves and PD models.
python
import pandas as pd
ledger = pd.readcsv("ledger.csv", parsedates=["duedate", "paiddate"])
loans = pd.readcsv("loans.csv", parsedates=["disbursaldate"])
asof = pd.Timestamp("2026-04-30") # snapshot date
instalments due by the snapshot date that are still unpaid at that date
due = ledger[ledger["duedate"] <= asof].copy()
due["unpaid"] = due["paiddate"].isna() | (due["paiddate"] > asof)
oldestunpaid = due[due["unpaid"]].groupby("loanid")["duedate"].min()
snap = loans.setindex("loanid").join(oldestunpaid.rename("oldestdue"))
snap["dpd"] = (asof - snap["oldestdue"]).dt.days.fillna(0).clip(lower=0)
snap["bucket"] = pd.cut(snap["dpd"], [-1, 0, 30, 60, 90, 10000],
labels=["current", "1-30", "31-60", "61-90", "90+"])
snap["bucket"].valuecounts()
Two details deserve attention. First, the snapshot uses only what was known as of the snapshot date: payments made after that date must not change the picture. Second, a loan that has not yet reached its first due date has no ledger rows, so it should show as current and not as missing. Both are typical places for quiet errors.
The "as of" idea is the single most important rule in credit data work, and it is stated more fully in the next section.
Time: what was known when
Imagine building a model to predict, at the moment of application, whether a customer will default within 12 months. The model may use only what the lender knew at that moment: the application, the bureau record on that day, and the score inputs. It must not use anything that happened afterwards, such as the customer's repayments in month 3. That sounds obvious, but it is easy to break. A table that is overwritten each month, such as a "current bureau score", holds the latest value and not the value at application. Using it quietly lets the future leak into the past.
Credit modelling therefore splits time into two windows around an observation date.
The observation point is the date at which the lender takes its decision, or at which a loan is looked at. Everything the model uses must be known on or before this date.
The performance window is the period after it, usually 12 months, in which you watch what happened and record whether the loan defaulted.
Lesson 1.4 sets out how to choose these windows and how to define the outcome. For now, keep one rule in mind: for each row, features come from before the observation date and the outcome comes from after it.
Bureau data
The credit bureau record is one of the most valuable inputs, because it shows how a borrower behaves with other lenders. Bureau data typically includes the number of active loans, total outstanding debt, the worst delinquency in the last 6 or 12 months, the number of enquiries by other lenders in recent months, the age of the oldest account and a bureau score. A rising count of recent enquiries often signals a borrower seeking credit from many places at once, which is a risk sign.
Bureau data has its own cautions. It reflects what lenders report, and reporting can be delayed or incomplete. A thin-file customer, someone with little credit history, has few fields to work with, and many first-time borrowers fall in this group. The data for a customer also changes over time, so the bureau pull must be stored with its date. A lender that keeps only the latest pull cannot rebuild what it knew at decision time.
Data quality in credit tables
The cleaning ideas from the Data Science track apply directly, with credit-specific forms.
Duplicate loans or applications: the same customer applying twice in a week, or a loan reported twice after a system migration.
Date problems: a payment date earlier than the disbursal date, due dates that do not match the tenure, or a default date before the first missed payment.
Impossible balances: an outstanding balance larger than the sanctioned amount, or negative balances after an over-payment.
Missing outcomes: loans still open that have not reached the end of the performance window should be excluded from a 12-month default rate and not counted as non-defaults.
Product and channel mixing: one table covering personal loans and two-wheeler loans together hides that they have different risk. Keep the product column and split by it.
The last bullet about missing outcomes is subtle and important. A loan disbursed three months ago has not had 12 months to default. If you count it as a "good" loan, you understate the default rate, because it may default in month 8. Always check that each loan in a 12-month analysis has had a full 12 months, or use a method that handles partial observation.
A worked example: reading a month-end snapshot
Take the 4,420 loans from the funnel, six months after disbursal, on one month-end snapshot. Counting loans by bucket gives this picture.
Bucket · Loans · Share of loansCurrent (0 DPD) · 4,010 · 90.7%
1 to 30 · 215 · 4.9%
31 to 60 · 85 · 1.9%
61 to 90 · 55 · 1.2%
90 plus · 55 · 1.2%
Total · 4,420 · 100%
The first check is the total: 4,010 + 215 + 85 + 55 + 55 = 4,420, so no loan has gone missing. The second is to turn the table into the figures a risk team quotes. Any delinquency is 410 loans, or 9.3 per cent. Loans more than 30 days past due number 195, or 4.4 per cent, and loans more than 60 days past due number 110, or 2.5 per cent. The 90-plus share of 1.2 per cent is the closest thing the table has to a default rate for this month.
Two cautions come with such a table. First, it counts loans, not rupees. If the delinquent loans carry larger balances than the average, the share of money at risk is higher than the share of loans, so analysts report both a count basis and a balance basis. Second, it is one date. The 215 loans in the 1 to 30 bucket are not a fixed group: many will pay and return to current, and some will move to the next bucket. Whether they do, and how often, is exactly the question that roll rate analysis answers in Module 3. A single snapshot shows where loans are. Only two snapshots, side by side, show where they are going.
Common mistakes
Counting rows in the wrong table. Know the grain of every table before counting, and count distinct loan ids when you mean loans.
Using today's value for a past decision. Use the value stored as of the observation date, not the latest one.
Treating open loans as good loans. A loan that has not had a full performance window has no outcome yet.
Forgetting rejected applicants. Repayment data describes only approved customers. Say so when you describe model results.
Mixing definitions of DPD and buckets. Confirm how the lender counts days, whether partial payments cure the delinquency, and where the bucket edges are.
Joining tables without checking row counts. After each join, check that the number of rows is what you expected.
Key takeaways
A loan passes through application, verification, bureau check, decision, disbursal, repayment, collections, default or write-off, and closure. Each stage leaves data.
The core tables are the application table, the loan master, the instalment ledger, collections and recovery. Each has a different grain, and joining them carelessly multiplies rows.
Days past due counts days since the oldest unpaid amount fell due, and buckets group DPD into ranges such as 1 to 30 and 31 to 60.
The monthly snapshot, one row per loan per month-end, is the standard shape of portfolio data, and it must use only what was known as of that date.
Features come from before the observation date, and the outcome from the performance window after it. Loans without a full window have no outcome yet.
Only disbursed loans produce a repayment record, so a lender's data describes the customers it chose, which is the selection problem.
Check yourself
1. A lender's loan master has 5,000 rows and its instalment ledger has 90,000 rows. If the tenure is 18 months, how many loans are in the ledger, and what is the risk in counting rows after a join?
90,000 ÷ 18 = 5,000 loans. Counting rows after a join would report 90,000, which is the number of instalments, not loans.
2. A loan's oldest unpaid instalment fell due on 10 March. What is its DPD on 9 April, and which bucket is it in?
30 days, so it is in the 1 to 30 bucket, at the top of it. One more day and it moves to 31 to 60.
3. Why does a 12-month default rate exclude loans disbursed four months ago?
They have not had the full 12 months to default. Counting them as good would understate the rate.
4. Of 20,000 applications, 75 per cent pass policy, 60 per cent of those are approved and 90 per cent of those are disbursed. How many loans are made and what share of applicants have a repayment history?
20,000 × 0.75 × 0.60 × 0.90 = 8,100 loans, which is 40.5 per cent of applicants.
5. Why is it wrong to use a customer's "current bureau score" when building a model for decisions made a year ago?
The current score includes what happened after the decision. The value on the decision date must be used, or the future leaks into the past.
Try this
You need no real data for this exercise, but a sample ledger from a spreadsheet helps. Set aside forty minutes.
1. Invent a small ledger of 6 loans with a 12-month tenure and instalments due on the 5th of each month. For each loan, choose a payment pattern: always on time, one late payment, two missed payments, and so on. Write about 20 rows for the interesting loans.
2. For a snapshot date of your choice, work out by hand the DPD and bucket of each loan, using the rule of days since the oldest unpaid due date.
3. List the tables that would exist in a real lender for this portfolio, and write one sentence saying what one row means in each.
4. Pick a decision date and a 12-month performance window. For each loan, say which fields you may use as features and which you must not, because they fall after the observation date.
5. Use the funnel simulator with your own guess of the stage rates for a product you know. State what share of applicants would have a repayment history.
6. Write a short note: what would be missing if you built a model on this lender's data and then applied it to every applicant?
A good result is a one-page data map: the tables, their grains, the key that joins them, the snapshot rule and the two time windows. It is the page every credit analyst draws before writing any code, and the next lesson uses the same loan to learn exactly how instalments are calculated.
Tags: Credit Risk Analytics Loan lifecycle Credit data Days past due Beginner