Spreadsheet to Metro 2: A Worked Example with Synthetic Loans

    A reproducible example for lenders who track loans in a spreadsheet: download two months of a synthetic loan sheet, see which Metro 2 field each column feeds, what changes from one month to the next, and four seeded errors to catch before you report.

    Updated 2026-10-09 · synthetic data, no real person

    The short answer

    If your loans live in a spreadsheet, the reporting tool gets the data from an export of that sheet each cycle: one row per loan, every row describing the same as-of date. The columns are mapped to Metro 2 fields once and the mapping is reused every month. The work that stays with you is keeping a few columns right from month to month: days past due, amount past due, the date a delinquency began, the payoff date, and how each borrower is liable. The two sample months below show exactly that.

    About the sample data

    Every row is synthetic and says so in its first column. The borrowers are invented, the street addresses are invented, and every Social Security number starts with area number 666 and group number 00, which the Social Security Administration does not assign. Amounts are whole dollars and interest is left out, so you can check each balance by hand: a payment reduces the balance by its full amount. Four installment loans cover the situations a small lender meets most: a loan that stays current, one that falls behind, one paid off early, and a joint loan with a co-borrower.

    The September file, as exported

    This is the full text of the September download. The August file has the same columns.

    data_label,as_of_date,loan_id,borrower_first_name,borrower_last_name,borrower_ssn,borrower_dob,borrower_liability,address_line_1,city,state,zip,loan_type,open_date,original_amount,term_months,payment_frequency,scheduled_payment,current_balance,amount_past_due,days_past_due,last_payment_date,last_payment_amount,first_delinquency_date,closed_date,co_borrower_first_name,co_borrower_last_name,co_borrower_ssn,co_borrower_dob,co_borrower_liability
SYNTHETIC SAMPLE - NOT A REAL PERSON,2026-09-30,SYN-1001,Alex,Synthetic,666001001,1985-04-12,Individual,100 Synthetic Data Way,Springfield,IL,62701,Installment loan,2025-12-15,6000,24,Monthly,250,3750,0,0,2026-09-01,250,,,,,,,
SYNTHETIC SAMPLE - NOT A REAL PERSON,2026-09-30,SYN-1002,Blake,Synthetic,666001002,1979-11-03,Individual,200 Synthetic Data Way,Springfield,IL,62701,Installment loan,2025-06-15,9300,30,Monthly,310,5270,620,60,2026-07-01,310,2026-08-01,,,,,,
SYNTHETIC SAMPLE - NOT A REAL PERSON,2026-09-30,SYN-1003,Casey,Synthetic,666001003,1991-06-27,Individual,300 Synthetic Data Way,Springfield,IL,62701,Installment loan,2025-04-15,3600,20,Monthly,180,0,0,0,2026-09-12,720,,2026-09-12,,,,,
SYNTHETIC SAMPLE - NOT A REAL PERSON,2026-09-30,SYN-1004,Drew,Synthetic,666001004,1983-02-19,Joint,400 Synthetic Data Way,Springfield,IL,62701,Installment loan,2026-01-15,12750,30,Monthly,425,9350,0,0,2026-09-01,425,,,Emery,Synthetic,666001005,1984-09-08,Joint

    Column by column: which Metro 2 field each one feeds

    Each link opens the field in BureauRelay's Metro 2 field reference, which gives its position, length and allowed values. The record carries the borrower's identity (name, Social Security number, date of birth, address) and the account's terms, balances, status, key dates and payment history; additional borrowers go in a separate associated consumer segment.

    Spreadsheet columnMetro 2 fieldWhat to get right
    data_labelNot reportedA marker that the row is synthetic. Only in this sample. It is not reported; a real export does not have it.
    as_of_dateDate of Account InformationThe cycle date the row describes. One date for the whole sheet, so balances, statuses and past-due amounts all describe the same day.
    loan_idConsumer Account NumberYour own loan number. Keep it the same every month. If a number has to change, report the change instead of starting a new account (the format has a segment for new account numbers).
    borrower_first_nameFirst NameThe primary borrower's first name. As it appears on the loan documents.
    borrower_last_nameSurnameThe primary borrower's last name. As it appears on the loan documents.
    borrower_ssnSocial Security NumberThe primary borrower's Social Security number, nine digits. This sample uses numbers SSA never assigns (area 666, group 00). A real sheet carries the actual number and has to be stored and sent securely.
    borrower_dobDate of BirthThe primary borrower's date of birth. Use the same date format in every date column (YYYY-MM-DD here).
    borrower_liabilityECOA CodeWhether the borrower is liable alone (individual) or jointly. Kept in plain words here; in the file it becomes an ECOA code (the reference lists the values). Both borrowers on SYN-1004 are jointly liable.
    address_line_1First Line of AddressThe borrower's street address. The current mailing address on file.
    cityCityCity. As it appears in the mailing address.
    stateState CodeTwo-letter state abbreviation. Two letters, for example IL.
    zipZIP CodeZIP code. Keep it as text so a spreadsheet does not drop leading zeros.
    loan_typePortfolio Type, Account TypeWhat the product is, in your own words. The product decides both codes. Every loan in this sample is a monthly-payment installment loan; the product mapping tool covers other products.
    open_dateDate OpenedThe date the loan was opened. Never changes after the loan is opened.
    original_amountHighest Credit/Original Loan AmountThe amount originally lent. The original principal, not today's balance.
    term_monthsTerms DurationThe number of scheduled payments. From the loan agreement.
    payment_frequencyTerms FrequencyHow often a payment is due. Monthly for every loan in this sample.
    scheduled_paymentScheduled Monthly Payment AmountThe payment due each period. The contractual payment, not what was actually paid.
    current_balanceCurrent BalanceWhat is owed on the as-of date. A loan paid in full shows 0.
    amount_past_dueAmount Past DueThe overdue amount on the as-of date. 0 when the account is current. It has to agree with days_past_due: money past due means the account is not current.
    days_past_dueAccount Status, Payment Rating, Payment History ProfileHow late the account is on the as-of date. The account status comes from the delinquency level (30 to 59 days past due, 60 to 89, and so on) or from how the account closed, and each month's state becomes part of the up to 24 months of payment history the record carries.
    last_payment_dateDate of Last PaymentThe date of the most recent payment received. Stays on the last real payment when a payment is missed.
    last_payment_amountActual Payment AmountThe amount of the most recent payment. What was actually received.
    first_delinquency_dateDate of First DelinquencyThe date the current delinquency began. Filled in the month the delinquency begins, then left alone while it continues. Moving it later makes the delinquency look newer than it is.
    closed_dateDate ClosedThe date the loan was paid off or closed. Blank while the loan is open.
    co_borrower_first_nameJ1 First NameThe co-borrower's first name, if any. Each additional borrower goes in the associated consumer segment: J1 when they share the primary borrower's address, J2 when they do not.
    co_borrower_last_nameJ1 SurnameThe co-borrower's last name. As it appears on the loan documents.
    co_borrower_ssnJ1 Social Security NumberThe co-borrower's Social Security number. Same rules as borrower_ssn.
    co_borrower_dobJ1 Date of BirthThe co-borrower's date of birth. Same format as the other date columns.
    co_borrower_liabilityJ1 ECOA CodeHow the co-borrower is liable for the loan. Required whenever a co-borrower is named: the record has to say how each person relates to the account.

    Codes for status, product and liability are covered in the account status code reference, the product mapping tool and the ECOA code reference. Co-borrowers at the same address use a J1 segment; at a different address, a J2 segment.

    What changes from August to September

    LoanAugust 31September 30
    SYN-1001balance 4000, past due 0, 0 days past duebalance 3750, past due 0, 0 days past due
    SYN-1002balance 5270, past due 310, 30 days past duebalance 5270, past due 620, 60 days past due
    SYN-1003balance 720, past due 0, 0 days past duebalance 0, past due 0, 0 days past due, closed 2026-09-12
    SYN-1004balance 9775, past due 0, 0 days past duebalance 9350, past due 0, 0 days past due

    SYN-1001. Current both months. The 2026-09-01 payment of 250 arrived, so current_balance falls from 4000 to 3750 and the last-payment columns move to that payment. Nothing else changes.

    SYN-1002. The payment due 2026-08-01 was missed, so in August the account is 30 days past due with 310 overdue and first_delinquency_date records 2026-08-01. The 2026-09-01 payment was missed too: in September it is 60 days past due with 620 overdue. first_delinquency_date stays 2026-08-01, and the last-payment columns stay on the last real payment (2026-07-01).

    SYN-1003. Paid off early on 2026-09-12 with a final payment of 720. current_balance becomes 0 and closed_date is set. It still appears in the September file, now as a loan closed with a zero balance, one of the closed outcomes the format can report.

    SYN-1004. A joint loan for Drew and Emery Synthetic, who share an address, so the co-borrower columns feed a J1 segment. Current both months; the balance falls from 9775 to 9350 after the 2026-09-01 payment.

    The as-of date, the balances and the last-payment columns are the only values that move for a loan that stays current. When a loan falls behind, days past due and amount past due grow every month while the delinquency date stays put; when it is paid off, the balance goes to zero and the closed date is filled in.

    Four seeded errors to catch before you report

    The third download is the September file with one deliberate mistake per loan. Each is the kind of inconsistency to catch in the sheet before it becomes a file: a furnisher may not report information it knows, or has reasonable cause to believe, is inaccurate (FCRA section 623(a)(1)(A)). BureauRelay's validation is CRRG-aligned and mirrors the checks the bureaus run, and its errors come back in plain English with the field and the fix.

    LoanWhat is wrongWhy it matters
    SYN-1001amount_past_due is 250 while days_past_due is 0.The row says the account is current and also that money is overdue. One of the two is wrong, and the account status is chosen from them.
    SYN-1002first_delinquency_date moved from 2026-08-01 (August file) to 2026-09-01.The delinquency began with the payment due 2026-08-01 and has not been cured. Moving the date later makes it look newer than it is.
    SYN-1003closed_date is 2026-09-12 but current_balance still shows 720, the amount of the payoff payment.A loan paid in full shows a zero balance. A closed account that still owes money is a different situation with a different status.
    SYN-1004co_borrower_liability is blank although a co-borrower is named.The associated consumer segment has to say how the second person relates to the account.

    How the monthly cycle runs in BureauRelay

    1. Export the sheet as of the cycle date and import it as CSV or Excel, or send the same data as JSON, XML or a Metro 2 file, through the REST API, or through an automated SFTP watcher.
    2. The first time, column headers are auto-matched to Metro 2 fields and adjusted with drag-and-drop mapping; the mapping is saved as a reusable template for later cycles.
    3. Validation runs on every record; fix what it reports at the source, in the sheet, so next month starts clean.
    4. BureauRelay builds and maintains each account's 24-month payment history profile from the data sent and generates the fixed-width file with 426-character records, header through trailer.
    5. Furnishing to Equifax, Experian, TransUnion and Innovis is delivered by automated SFTP, on a monthly, biweekly or weekly schedule per bureau.

    The furnisher stays responsible for the accuracy of the data it sends, which is why the columns above matter more than any tool. For the steps before your first file, such as bureau approval and test files, see how to create a Metro 2 file and how to become a data furnisher. For a single record read field by field, see the Metro 2 format example.

    FAQ

    Our loans are tracked in a spreadsheet. How would a reporting tool get that data each month?
    Export the sheet at each cycle's as-of date, one row per loan, and give it to the tool. BureauRelay imports CSV, Excel, JSON, XML or Metro 2 files, and also takes data through its REST API or automated SFTP watchers. Column headers are matched to Metro 2 fields once, with drag-and-drop mapping, and saved as a reusable template, so later months are an upload of the new export.
    Is there a sample CSV or Excel layout for Metro 2 reporting?
    The files on this page are one: a synthetic installment-loan sheet with one row per loan and the columns a small lender usually keeps, in two consecutive months, plus a copy with four seeded errors. Open them in any spreadsheet program. No row describes a real person.
    Do I have to keep a 24-month payment history column?
    No column in the sample holds it. The Metro 2 record carries up to 24 months of payment history, and BureauRelay builds and maintains that payment history profile for each account from the data sent. What the sheet has to get right is each month's days past due and amount past due, because each month's state becomes part of that history.
    Where do co-borrowers go?
    On the same row as the loan, in their own columns, with how each person is liable. They feed the associated consumer segment, which carries each additional consumer's name, Social Security number, date of birth and relation to the account: a J1 segment when they share the primary borrower's address, a J2 segment when they do not.
    Which date should go in first_delinquency_date?
    The date the current delinquency began, entered in the month it begins and left unchanged while the delinquency continues. In the sample, loan SYN-1002 missed the payment due 2026-08-01, so both months show 2026-08-01. For accounts placed for collection or charged off, FCRA section 623(a)(5) requires reporting the month and year the delinquency began.
    Can I see what my own data looks like as a Metro 2 file?
    The free Metro 2 file viewer parses a .dat or CSV file in the browser, decodes every segment and runs a structural validation pass, with no account and without uploading the file. The free developer sandbox at /sign-up/developer adds test API keys and the full validation engine; nothing is sent to the bureaus, and live keys and bureau delivery need a paid plan.
    What does reporting about 50 loans a month cost?
    BureauRelay's Starter plan is $19 per month for up to 100 records per month and a single user, billed monthly, with no setup fees and no long-term contract.

    Inspect a Metro 2 file

    When your reporting file is ready, explore its records in the BureauRelay file viewer.

    Open the Metro 2 file viewer

    Sources

    Statements about the Metro 2 format and the law come from these public sources. Field positions, lengths and allowed values are in the current Credit Reporting Resource Guide, which CDIA publishes and revises; confirm against the current edition.

    Metro 2® and Credit Reporting Resource Guide® are registered trademarks of the Consumer Data Industry Association. BureauRelay and Switch Labs are not affiliated with, sponsored by, or endorsed by CDIA.

    Spreadsheet to Metro 2: A Worked Example with Synthetic Loans