Guide

Payment Reconciliation Template: Free Spreadsheet + Automation Guide

A payment reconciliation template gives your finance team a structured starting point for matching transaction records against PSP settlement files. This guide covers what a good template must include, how to structure it for fintech-scale operations, and when a spreadsheet stops working — so you know exactly when to move to purpose-built infrastructure.

What a Payment Reconciliation Template Must Include

Most reconciliation templates fail because they are designed for bank statement matching, not PSP settlement matching. Payment reconciliation for fintechs involves three data sources — your internal transaction records, the PSP settlement file, and your bank statement. A complete template must reconcile all three.

Tab 1: Internal Transaction Register

Required columns: Transaction ID, Order ID, Customer ID, Transaction Date, Transaction Amount, Currency, PSP Name, PSP Reference ID, Payment Method, Status (authorized/captured/settled/refunded), Settlement Expected Date. Optional: Merchant Reference, Split Amount (for marketplace sellers), Platform Fee.

Tab 2: PSP Settlement File

Required columns: PSP Reference, Merchant Reference, Settlement Date, Gross Amount, PSP Fee, Net Amount, Currency, Settlement Batch ID, FX Rate (for cross-currency), Transaction Type (payment/refund/chargeback/adjustment). These columns map directly to Stripe's Balance Transaction Report, Adyen's Settlement Details Report, and Checkout.com's Reconciliation Report.

Tab 3: Matching & Exceptions

The matching tab performs VLOOKUP (or INDEX/MATCH) between Tab 1 and Tab 2 on PSP Reference. Required columns: Transaction ID (from Tab 1), PSP Reference (from Tab 2), Match Status (matched/unmatched/partial), Amount Variance, FX Variance, Timing Gap (days between transaction date and settlement date), Exception Type (timing/FX/missing/amount), Resolution Status, Notes.

Tab 4: Bank Reconciliation

Match your PSP net settlements against bank credits. Required columns: Bank Statement Date, Bank Credit Amount, Currency, PSP Settlement Batch ID (matched against Tab 2 batch totals), Match Status, Variance. This is the final proof of funds layer — confirming that money recorded in the PSP settlement files actually arrived in your bank account.

How to Use the Template

Daily Reconciliation Workflow

Step 1: Export today's transaction records from your application database into Tab 1. Step 2: Download the PSP settlement file (or configure auto-export) and paste into Tab 2. Step 3: Run the VLOOKUP on Tab 3 — every unmatched row is an exception requiring review. Step 4: At end of business, compare PSP settlement totals against bank credits on Tab 4. Step 5: Log unresolved exceptions with exception type and expected resolution date.

Handling Multi-Currency Payments

Add a currency conversion column to Tab 3: =(B2*FX_RATE)-C2 where B2 is the original transaction amount, FX_RATE is the settlement FX rate from Tab 2, and C2 is the settled net amount. Flag any variance greater than your tolerance threshold (typically ±0.5% for high-volume operations). Do not hard-fail reconciliation on FX variance — apply a tolerance window and escalate outliers only.

Tracking Chargebacks and Refunds

Add a Transaction Type filter to Tab 2 to separate payments, refunds, chargebacks, and adjustments. Refunds and chargebacks appear as negative settlement amounts — match them back to the original payment PSP Reference. Unmatched chargebacks (orphaned disputes without a source payment match) should be flagged immediately for investigation.

Template Limitations: When Spreadsheets Break

Spreadsheet-based payment reconciliation works reliably up to approximately 500–1,000 transactions per day. Beyond that, several failure modes emerge that no template can solve.

Scale Failure: Row Limits and VLOOKUP Performance

Excel and Google Sheets handle 1M rows technically, but VLOOKUP across 500K rows is slow and error-prone. More importantly: a single row formula error in a shared spreadsheet corrupts the entire reconciliation. At 5,000+ transactions per day, formula maintenance alone becomes a full-time job.

Multi-PSP Failure: Format Inconsistency

Every PSP delivers settlement data in a different format. Stripe uses CSVs from the Balance API. Adyen uses its own settlement file format. Checkout.com uses SFTP-delivered reports. A single template cannot normalize all three — you need a separate tab per PSP, with manual reformatting on every import. When you add a second PSP, your template complexity at least doubles.

Audit Failure: No History, No Lineage

Spreadsheets don't maintain reconciliation history. When an auditor asks 'show me how transaction X was reconciled on March 15,' a spreadsheet cannot answer that question — the match state from 60 days ago is gone. Compliance-grade reconciliation requires an immutable audit trail per match decision, including the matching rule applied, the timestamp, and the operator who resolved exceptions.

Marketplace Failure: Multi-Leg Settlement

Marketplace payment reconciliation requires matching a single gross payment to multiple downstream payouts — one per seller, plus the platform fee, plus the PSP fee. This is a multi-leg reconciliation problem. There is no spreadsheet formula that handles it reliably at scale. Each leg requires its own reference chain, and errors compound across the ledger.

Beyond the Template: Reconciliation Infrastructure

When your reconciliation template hits its limits, the decision is: build your own reconciliation engine, or deploy purpose-built infrastructure. Most fintech engineering teams underestimate the complexity of building reconciliation properly — idempotent ingestion, multi-PSP normalization, deterministic ID matching, probabilistic fallback for exceptions, an audit trail, and real-time exception alerting. NAYA is that infrastructure. It deploys in days, not months, and handles multi-PSP, multi-currency, multi-leg reconciliation as a managed service.

Frequently Asked Questions

Common questions about this topic

QWhat is a payment reconciliation template?

A payment reconciliation template is a structured spreadsheet that matches your internal transaction records against PSP settlement files and bank statements. It typically includes tabs for your transaction register, PSP settlement data, matching results, and exception tracking. Templates are useful for low-volume operations (under 1,000 transactions per day) and for teams learning the reconciliation process before moving to automated infrastructure.

QWhat columns should a payment reconciliation template include?

A payment reconciliation template needs: Transaction ID, PSP Reference, Merchant Reference, Transaction Date, Gross Amount, PSP Fee, Net Amount, Currency, FX Rate, Settlement Date, Settlement Batch ID, Match Status, Amount Variance, FX Variance, Exception Type, and Resolution Status. For marketplace operations, add: Seller ID, Platform Fee, and Per-Seller Net Payout columns.

QHow do you reconcile Stripe payments using a template?

To reconcile Stripe payments using a template: (1) export your internal transaction records into Tab 1; (2) download Stripe's Balance Transaction Report (CSV via Dashboard or API) into Tab 2; (3) use VLOOKUP on charge_id or payment_intent_id to match records; (4) flag unmatched rows as exceptions. Key gotcha: Stripe payouts are net amounts (gross minus fees minus refunds). Reconcile at the individual transaction level, not the payout level.

QWhen should I stop using a reconciliation spreadsheet?

Stop using a reconciliation spreadsheet when: (1) daily transaction volume exceeds 1,000; (2) you operate with more than one PSP; (3) you process multi-currency payments; (4) you need an audit trail for compliance; (5) reconciliation takes more than 2 hours per day. At that point, the maintenance cost of the spreadsheet exceeds the cost of purpose-built reconciliation infrastructure.

QHow do you reconcile multi-currency payments in a spreadsheet?

To reconcile multi-currency payments in a spreadsheet, add a currency conversion column: multiply the original transaction amount by the settlement FX rate and compare to the net settlement amount. Apply a tolerance threshold (typically ±0.5%) to avoid flagging normal FX rounding as exceptions. For Adyen, use the FX rate from the Settlement Details Report. For Stripe, use the exchange_rate field in the Balance Transaction export.

QWhat is the difference between payment reconciliation and bank reconciliation?

Payment reconciliation matches individual transaction records against PSP settlement files — verifying that every payment was processed correctly and fees are accurate. Bank reconciliation matches bank statement credits against PSP net settlement totals — verifying that money left the PSP and arrived in your bank account. Both are required for complete financial accuracy. Payment reconciliation is more granular and complex; bank reconciliation is a higher-level proof-of-funds check.