Your Loan Tape: What Capital Providers Look At

A loan tape is a table with one record per loan, showing agreed fields for every loan in the pool as at a single stated date. Capital providers form their first detailed view of your book from it, and problems that have nothing to do with credit quality can dominate the early conversation. Duplicated identifiers, a delinquency column that mixes blanks with zeros, a balance field that quietly includes accrued interest. None of that is a lending judgement. All of it invites a reviewer to ask what else cannot be reproduced.

Write the field dictionary before you write the query

A tape without a dictionary is a spreadsheet of assertions. For every column, state the field name, the data type, the unit, the source system, the definition in one sentence, the permitted values, and whether nulls are allowed and what a null means. One page is usually enough. Send it with the file every time, including refreshes, because the person reading the March tape may not be the person who read January.

Identifiers that survive a system change

The loan identifier is the spine of everything that follows. Providers use it to join the tape to your monthly reports, to pick a file sample, to trace a payment in a servicing report, and eventually to test whether a backup servicer can import your data at all. If the identifier changes when you migrate systems, historical comparisons break unless you keep a crosswalk mapping old identifiers to new ones. Build and retain that crosswalk at the point of migration, because reconstructing it afterwards from balances and dates is slow and rarely complete.

Keep the loan identifier and the borrower identifier separate. Collapsing them makes obligor concentration invisible, which matters because concentration limits often bite at the obligor level rather than the contract level. A borrower with four loans looks like four small exposures until somebody runs the aggregation. Where your facility caps exposure to connected groups as well as individual obligors, carry a group identifier too, so concentration can be measured at both levels rather than only the one your extract happens to support.

Keep raw personal identifiers out of the tape entirely. Use a masked key and hold the mapping separately under a controlled release process. That is a data protection question for your adviser, not a formatting preference, and it is easier to design in at the start than to retrofit after a file has been distributed.

Balances and flows belong in different columns

Point-in-time fields describe a state at the as-of date: principal outstanding, days past due, status, remaining term. Period fields describe movement between two dates: principal collected, interest collected, fees collected, charge-offs, recoveries. Label the period explicitly with a start and end date rather than relying on a file name.

Reviewers cannot reliably derive a flow by differencing two snapshots once the pool changes, because originations, prepayments, repurchases and charge-offs all move balances at the same time. Give them the flows directly and you remove a round of clarifying questions before it starts.

Prove the tape with a principal roll-forward

A roll-forward is one of the most useful pages you can attach. It shows that opening balances, activity during the period and closing balances belong to the same reality.

The interest line is where confusion usually starts. The USD 0.15m is interest received in cash during the period. It does not reduce principal and it does not belong anywhere in the roll-forward arithmetic, so the closing balance stays at USD 10.50m. It is also not the same figure as interest income recognised for the period. Under an accrual basis, income is recognised as it is earned, so part of this cash may relate to interest accrued in earlier periods and some interest earned this period may still be uncollected. Keep the cash flow and the accounting measure in separate columns and label which basis each one uses. If your servicing system posts a combined payment amount, split it before the tape is produced rather than asking a reviewer to unpick it.

Principal itself needs a stated definition. Capitalised amounts such as arrangement fees or capitalised arrears may legitimately form part of contractual principal, and there is nothing wrong with including them provided the tape says so and carries them in a separate column. What causes disputes is a principal field that silently mixes net cash disbursed with contractual principal across different rows. State which basis the field uses, and where the two differ, supply both.

When the roll does not tie, the cause is usually identifiable: reclassifications between products, repurchases out of the pool, capitalised fees, or a charge-off recorded gross in one system and net of recoveries in another. Name the cause and quantify it. A reconciling item with a label is normal. An unexplained residual is a controls finding.

Dates, currencies and the meaning of a blank cell

Use one date format throughout, ideally year, month, day, and state the convention in the dictionary. Define which date each column holds, because origination, first payment, maturity, last payment received and charge-off dates all get conflated in extracts assembled by hand.

For multi-currency books, carry the contract currency on each row, plus the reporting currency, the rate applied and the rate source. Providers will re-run your conversion, and a rate with no source is a rate they cannot verify.

Decide what a blank means and say so. A blank days past due field might mean current, might mean the loan has not reached its first payment date, or might mean the value failed to extract. Those are three different facts, and the difference between zero and null shows up immediately in any delinquency calculation.

Every ratio needs its denominator stated

The same discipline applies to every metric you publish. Cure rates, roll rates, recovery rates and yield can each be built on balances or counts, on originations or outstandings, on gross or net figures. State the basis next to the number in the file rather than in a separate methodology document that arrives two weeks later.

Portfolio-level ratios also flatter a growing book, because unseasoned originations keep enlarging the denominator while their losses have not yet emerged. Provide cohort data as well, tracked by origination period, so performance can be read at comparable ages. That is what static pool analysis is for, and providers will build it themselves if you do not. Track dilution separately too, since refunds, cancellations and billing adjustments reduce balances without ever appearing in your delinquency or charge-off statistics.

Resist the urge to summarise credit quality with a single threshold. Score bands and cutoffs mean different things across models and vendors, and a threshold presented without the model behind it invites a question you would rather answer on your own terms. Currency and basis fields matter for the same reason: a number whose unit and basis are ambiguous cannot be checked, only queried.

Checks to run before you send the file

• Row count in the file matches the row count in the source system for the same as-of date, on the same scope and status filter. A tape that excludes charged-off or paid-off loans will not match a system count that includes them, so state the filter with the count.
• Sum of principal balances agrees to the general ledger, with any difference explained item by item.
• No duplicate loan identifiers, no maturity dates before origination dates. Investigate negative principal balances rather than assuming corruption: an overpayment awaiting refund is a legitimate state, so either correct the error or document it as a known exception with a count and a total.
• Book principal reflects your stated charge-off treatment. If charged-off balances are excluded from book principal while the borrower's obligation continues, say so in the dictionary and supply the charged-off population separately, because the amount you can still collect is not the amount on your balance sheet.
• Days past due is calculated on a stated convention, normally from the due date of the oldest unpaid contractual instalment, adjusted for any grace period, partial payment allocation or payment holiday rules in your policy. Set out those rules in the dictionary so the number can be reproduced.
• Status and days past due do not contradict each other on any row.
• Mandatory fields contain no nulls, and optional nulls are explained in the dictionary.
• Roll-forward ties from opening to closing balance.
• File name carries the as-of date and version, and the dictionary version matches the file.

Run these on every refresh, not just the first one. Consistency across three consecutive tapes tells a provider more about your controls than any single clean file. The wider evidence base those tapes sit inside is covered in preparing your data room, and how reviewers will test the tape against sampled files is set out in the diligence process.