Loan Tape Template: Field Definitions and Fast Normalization for Messy Tapes

TL;DR

• A loan tape is loan-level data investors use to price a pool.
• Regulation AB II can require up to 270 mortgage fields per loan.
• Standard fields cover identifiers, terms, credit, collateral, payments, and servicing.
• Field names can obscure different delinquency, credit-score, balance, and combined-loan-to-value calculations.
• Normalizing a tape for analytics requires profiling, schema mapping, code decoding, validation, and reconciliation.

Open a servicer tape and you might see columns like SELLERLOANID, MEASOFDATE, INVNUMBER, and PRININT_PYMT, real field names pulled from a public securities exhibit. None of them tell you what "balance" means, how delinquency is counted, or whether a 9 is a code or missing data. Until every field is mapped and reconciled, no one can price the pool.

This guide covers the loan-tape template investors expect, the calculation traps behind identical field names, and a five-step normalization approach. Our take: agentic data preparation with AI agents in the loop is how analytics teams normalize messy tapes without giving up governance.

Loan tape definition

A loan tape is the full loan-level record a debt investor needs to assess a pool; it tracks each loan from application through repayment. In a whole loan sale, the data tape is a large spreadsheet whose accuracy drives pricing, the buyer's diligence, and downstream compliance work.

The SEC adopted Regulation AB II in 2014, requiring standardized loan-level disclosure for asset-backed securities (ABS) backed by residential mortgages, commercial mortgages, auto loans, auto leases, debt securities, and resecuritizations. For each mortgage in registered residential mortgage-backed securities (RMBS), that means roughly 105 data points as a baseline and up to another 165 upon certain events, for a total of up to 270 per loan; agency RMBS run about 110. Issuers, investors, rating agencies, and third-party review (TPR) firms also built the Prime RMBS Data Tape, which adds a best-practice flat file for at-issuance disclosure.

Six field groups every tape should cover

Whatever the asset class, a complete tape maps to six categories, though the same column name can mean different things depending on who built the tape:

• Identifiers: A loan ID that should not change through the life of the transaction, plus pool ID, originator, servicer ID, and an anonymized borrower ID that links multiple loans to one borrower.
• Origination terms: Origination date, original loan amount, original interest rate, rate type (fixed, adjustable, step, other), original amortization term, interest-only indicator, prepayment penalty indicator, and, for adjustable-rate mortgages (ARMs), the gross margin added to the index.
• Borrower credit: Credit score plus the score model type, debt-to-income ratio (DTI), income relied on in underwriting, and occupancy status (primary, second home, investment).
• Collateral: Original loan-to-value (LTV), original combined loan-to-value (CLTV), property type, number of units, and geographic identifiers such as census tract.
• Payment performance: Current unpaid principal balance (UPB), delinquency status with the counting methodology named, the as-of date, voluntary prepayments, foreclosure alternatives, and actual loss data.
• Servicing: Current servicer, origination channel (retail, broker, correspondent), and servicer actions like modifications and forbearances.

The cost of bad tape data

When teams skip normalization, the consequences show up in the historical record:

• Widespread misreporting: Many loans in non-agency RMBS securitizations had at least one of three forms of misreporting: overstated appraisal, undisclosed second lien, or misreported owner occupancy. Loans with misrepresented occupancy carried roughly a higher likelihood of default than truthfully reported peers.
• Sampling let defects through: In diligence reviews, some loans reviewed failed underwriting guidelines outright, and banks waived a large share of those failures into pools anyway.

The mechanism behind all of it is a data governance failure that any team can suffer from today, which is why a repeatable normalization process matters.

Normalizing a messy tape in five steps

The five steps below describe the analytics work which sits downstream of ingestion and includes the additional transformation analysts typically need, with data cleaning treated as one facet of transformation rather than a separate stage.

• Profile before you touch anything: Profile the tape first by verifying all expected files arrived, record counts match control totals, and date ranges align with the processing period. Scan for empty records, duplicates, and missing values that signal extraction problems.
• Map to a canonical schema: Don't invent your target; the industry already built several. For example, the Mortgage Industry Standards Maintenance Organization (MISMO) publishes a Reference Model that supplies standard names, definitions, enumerations, and formats, and the Prime Data Tape covers at-issuance RMBS disclosure. Teams might pick one target and map every incoming tape to it once, so the next tape from the same servicer reuses the mapping.
• Decode values with lookup tables: Use value-mapping tables rather than hard-coded rules; translate servicer codes into canonical values through something like a parameters table covering P/S/I/9 occupancy codes, 0–4 delinquency buckets, and ND missing-data codes. Enforce types at ingestion too; loading everything as strings can lose decimal precision, produce unexpected nulls during casting, and leave formatting mismatches that surface only when the cash flow model fails.
• Validate at ingestion and during processing: Apply strict validation at ingestion that rejects non-conforming records, warning-level checks at egress, and cross-field rules that encode domain logic. For example, origination date before maturity date, CLTV ≥ LTV, and current UPB ≤ original UPB for a non-negative-amortization loan. Validation during processing catches data that passes ingestion but violates business rules before it reaches fact tables.
• Reconcile and document every change: Reconciliation compares datasets across systems after changes, rather than checking a single tape's field-level validity; compare record counts and control totals between the raw tape and the normalized output, and keep audit logs of every data change. You can't fully validate a tape from the tape itself. Tape-to-file source review against source documents is what Rule 15Ga-2 diligence exists for.

Governed loan-tape normalization with Prophecy

Loan-tape normalization stalls in two familiar places. A backlog-blocked analyst’s queue, or the back and forth between the analyst and the portfolio managers. Neither path gives a business team a fast, governed path from raw servicer tape to a pool-ready dataset.

Prophecy addresses that gap as an AI data prep and analysis platform that delivers AI-accelerated data preparation through visual workflows.. AI agents draft mappings and transformations, and the analyst or business user refines them through review before completing analyses, so you bring the domain judgment the model lacks

Here's how Prophecy supports governed loan-tape normalization end to end:

• AI agents: Multiple agents draft schema mappings, transformation logic, and documentation, so analysts get first drafts fast and keep human judgment in the loop for delinquency methods, CLTV scopes, and sentinel codes.
• Visual interface: Each normalization step is easy to review, share, and reuse across servicers and deal teams.
• Built-in governance: Workflows run as governed code, and column-level lineage and audit logs live inside the data platform team's environment, so data management and governance stay where they belong.
• Deployment to cloud data platforms: Prophecy deploys to your existing Databricks, Snowflake, or BigQuery environment, so analytics teams work alongside the tools already in the stack instead of silohed legacy software.

With Prophecy, your team can normalize messy servicer tapes into pool-ready datasets without waiting on backlogged analysts or losing governance. Ready to see agentic data preparation applied to your own loan tapes? Book a demo to explore how Prophecy's AI agents and visual workflows can help your team normalize messy tapes on Databricks, Snowflake, or BigQuery.

FAQs

What is a loan tape?

A loan tape is loan-level data that debt investors use to price a pool. It tracks each of your loans from application through repayment and includes identifiers, origination terms, borrower credit, collateral, payment performance, and servicing details.

How many fields does Regulation AB II require per mortgage?

For your registered residential mortgage-backed securities, Regulation AB II requires roughly 105 baseline data points per loan and up to another 165 upon certain events, for a total of up to 270 fields per loan.

Why do identical loan tape fields carry different meanings?

Field names like CLTV, delinquency status, or UPB can carry different operational definitions across GSE disclosures, servicer files, and regulator templates. You need to confirm the calculation method, scope, and code set for each source before you join or compare fields.

How do you normalize a messy loan tape?

You profile the tape first, map columns to a canonical schema like MISMO, decode servicer codes with lookup tables, validate records at ingestion and during processing, then reconcile counts and control totals against the raw tape while logging every change you make.