Loan portfolio analysis: the measures a lender needs, and why they end up in Excel
If your loan portfolio analysis runs in Excel, look at what the loan system keeps. The measures a CRO needs, the history each one depends on, and five RFP asks.
Contents 6 sections
Loan portfolio analysis is the regular review of a loan book’s health: days-past-due buckets, roll rates, vintage curves, collection efficiency, concentration and expected credit loss. If your team rebuilds it in Excel every month, look first at the loan system. Roll rates, vintages and staging read a loan’s history on past dates, and a system that keeps only today’s state cannot supply it.
If you run risk or finance at a lender, your portfolio pack probably arrives through the same route every month: extracts from the loan system, a set of workbooks, and an analyst who knows which tab joins to which. The pack gets built. The question is what it costs to build it again next month, and whether a regulator or a board member can follow how a number was made.
The analyst is not the problem. The gap sits in what the loan management system was designed to keep.
- Portfolio trends read history. Roll rates, vintages, staging and any month-on-month view need a loan’s position on past dates.
- The data model decides it. A system shaped for the transaction, which overwrites the earlier state, leaves the history to be rebuilt by hand.
- Reports outside the default can cost extra. Where the contract bills them, each becomes a vendor request, a custom build or an export project.
- The spreadsheet stack is a risk. It breaks the trace from a reported number to the books, and it limits what AI can learn from the book.
- Ask about history before signing. Five RFP asks surface the problem while the contract is still open.
Which measures does a portfolio review need, and what history does each read?
Each one is a view of the book on a date. The table maps the common measures to the history the loan system has to keep for them.
| Measure | What it answers | History it needs |
|---|---|---|
| Book by days-past-due bucket | How much of the book is current, 1-30, 31-60, 61-90 and beyond | Days past due for every account on each reporting date |
| Roll and cure rates | How accounts move between buckets from one month to the next | The bucket each account sat in at each month end |
| Vintage curves | How each disbursal cohort performs as it seasons | Disbursal month, and balance and delinquency by month on book |
| Collection efficiency | How much of what fell due was collected | Amount billed and amount collected for each cycle |
| SMA and NPA balances | Which accounts are in early stress or non-performing under RBI’s rules | Classification of each account on each date, with the rule that set it |
| Expected credit loss staging | Which loans sit in stage 1, 2 or 3 under Ind AS 109 or IFRS 9 | Credit risk at initial recognition, days past due over time, and the events behind each stage move |
| Write-offs and recoveries | What was written off, and what came back after | Write-off date and amount, and each recovery dated after it |
| Concentration | How much of the book sits with one product, region, sector or borrower | Product, location, sector tag and borrower link on each loan, as at each date |
| Co-lending shares | How balances and collections split between the lender and its partner | The partner share on each loan, and each change to it |
The detail behind three of these rows sits in other posts. Roll rates and cure rates and collection efficiency each have their own formulas, and SMA and NPA classification explains how an account’s status is dated. The same history feeds the loan tape a buyer or rating agency asks for. RBI can also ask for cuts no standard report carries. The Supervisory Returns Directions give the example of exposure to a specific industry cluster in a district over a period (paragraph 13), and the RBI returns guide covers what else they ask for.
Why does that history go missing?
Receipts and disbursals are recorded. What goes missing is derived state: the bucket, the classification and the schedule as they stood on a past date. The loan system was built to process loans. The schema follows the transaction: a disbursal, a receipt, a charge, a change of status. Fields are named for the vendor’s product setup, not for the lender’s reporting categories. And when a loan is rescheduled, a status is reversed or a schedule is recalculated, the cheapest design for the transaction is to overwrite the earlier state rather than keep both.
Reviewers describe the result from the user’s side. One G2 reviewer of a tier-1 global banking core reports that it does not support large data reports and keeps only one year of data in the core application.
A one-year window inside the system of record rules out vintage curves and multi-year loss history from that system. The history then lives in extracts, and the extracts become the source.
There is a commercial side too. Where report building and data access sit outside the standard fee, each report the platform does not produce by default turns into a paid request, a custom build or an export project.
What does the spreadsheet stack cost?
Picture a mid-size NBFC. Loan-level extracts land overnight on a shared drive. A portfolio analyst’s macro joins servicing data to a separate disbursement file. The risk team keeps its own expected credit loss workbook because the loan system cannot produce the vintage cohorts. The reporting team rebuilds bureau files, RBI returns and co-lending partner reports from manual exports because the in-system reports do not tie out.
That stack carries three costs.
- A broken trace. RBI’s returns rules ask for every return to reconcile with the NBFC’s own sources and for the aggregation rules behind it to be kept on record. A figure that passed through three workbooks and three analysts is hard to trace back.
- A limit on AI. A model that ranks accounts for collections or spots early stress learns from each loan’s history across its life. Overwritten history cannot be learned from.
- A harder next migration. Every workaround for a missing report is a requirement the replacement system will be measured against, and few of them are written down.
How does the data trap limit AI in the loan book?
AI in servicing and collections works from the book’s own history. A model that decides which account to call first, or which borrower is sliding toward 30 days past due, learns from how similar loans behaved month by month. That needs clean, joined, dated data over each loan’s full life.
So the limit sits in the data layer, below the choice of model. A system that overwrites earlier state cannot supply dated history to any model. External cash-flow observations need their own coverage dates and consent controls; the Account Aggregator servicing workflow explains how to keep those signals distinct from the loan record.
What should a lender ask before signing an LMS contract?
A team writing an LMS RFP should treat the data layer as a first-class criterion, not a line under “reporting”. The functional requirements template carries the reporting requirements, and these five asks test them:
Can the lender export every loan-level field, including custom fields, audit timestamps and modification history, to a warehouse the lender owns, on its own schedule, without a per-report fee? Ask for a real export against a sizeable book, not a demo dataset.
Does the platform keep each earlier state when a status, a schedule or a co-lending share changes? Ask the vendor to reverse a status and show the trail that survived it.
Can the lender’s own administrator rename a field or add a custom attribute and see it flow through exports and reports without a vendor statement of work? A field rename is the simplest test.
What is the documented schema of the data exposed to the lender’s warehouse, who owns it, and what happens to it when the contract ends? The lender’s leverage is lowest on the way out, so the exit terms belong in the contract before signing.
Are report building, data access and small schema additions inside the standard fee or billed separately? Get the answer in the contract, with the rate for each.
Where the loan system already feeds a general ledger, the loan reconciliation guide covers the other half of the trace, from loan events to the books.
A CRO who wants a portfolio pack the board can question line by line needs the history kept at source. Lokta’s loan management system holds the book on a double-entry, event-sourced ledger, and supports full replay of any book from its history. Replay works on the history a book brings with it: for a book migrated from an older system, dates that system overwrote come across only as far as its archives can rebuild them. The same team co-created Apache Fineract before Lokta.
Frequently asked questions
Which loan portfolio reports should a lender track?
At a minimum: the book by days-past-due bucket, roll rates between buckets, vintage curves by disbursal cohort, collection efficiency, special mention and non-performing balances, written-off balances and recoveries, and concentration by product, geography, sector and borrower. Each is a view of loan history on a date, so each depends on the loan system keeping that history rather than overwriting it.
Why does loan portfolio analysis end up in Excel?
Because the loan system was built to process loans, and the questions a portfolio manager, a CRO or a CFO asks need history the system may not keep. Tables are shaped for the transaction, fields are named for the vendor's product setup, and a status change or a rescheduled plan can overwrite the earlier state. The team rebuilds the history in spreadsheets, and a report the platform does not produce by default can turn into a vendor request.
What is the LMS data trap, and how does it limit AI?
The lender owns the loans, while the vendor decides the shape of the data about them, and the gap is paid for every quarter in analyst hours. It also limits AI. A model that ranks accounts for collections or flags early stress learns from the history of each loan across its life. If the loan system overwrote that history, no choice of model brings it back.
Read next:
- 100 LMS Reviews: The Same Six Complaints Recur: the broader pattern. Six complaints surface across LMS and LOS vendors, and the data-layer complaint is one of them.
- The Customization Trap: When LMS Vendors Bill Every Change: the sibling commercial trap. Every “small change” funnels through a billable vendor project, for the same reason reports do.
- The 30-Call Limit: Why Your LOS API Quietly Caps Your Lending Velocity: the data trap’s architectural cousin. API rate limits are commercial controls dressed as infrastructure, and the reporting layer can work the same way.
- The LMS RFP toolkit: the vendor-neutral kit for putting the five data-layer questions into a running RFP, alongside a checklist, scorecard and security questionnaire.
- Guides and tools for lending teams: the full resources shelf, with the glossary and the RBI model risk readiness tools beside the RFP kit.
Sources:
- G2: a tier-1 global banking core reviews
- RBI, Reserve Bank of India (Non-Banking Financial Companies - Supervisory Returns) Directions, 2026, 31 July 2026: paragraphs 13 to 15.
- RBI, press release on the draft Guidance on Regulatory Expectations for Data Governance, 15 July 2026.
Ashok Auty is the co-founder and Chief Customer Officer of Lokta and co-creator of Apache Fineract. He has spent two decades building lending infrastructure, and reads vendors’ public review records, where the data-layer complaint keeps coming up, regardless of the brand on the box.
