Payment reconciliation turns processor exports into a dependable record of what customers paid, what the business owes, and what reached the bank. This guide provides a repeatable workflow for matching orders, authorizations, captures, refunds, disputes, fees, foreign-exchange adjustments, reserves, and settlements, with practical controls for daily processing and month-end close.
Overview
Payment reconciliation is the process of comparing records from several systems and explaining every difference. A typical review may involve the ecommerce platform or order database, payment gateway, merchant account or processor, accounting ledger, bank statement, and customer-support or dispute tools. These systems rarely describe the same event in exactly the same way.
An order is a commercial event. An authorization is approval to attempt a card transaction. A capture records that the approved amount should be collected. A refund reverses all or part of a captured payment. A dispute may remove funds temporarily or permanently, while a payout groups many transactions and transfers a net amount to the merchant’s bank account. Reconciliation connects these events without treating the payout as a substitute for transaction-level evidence.
The central control is a transaction-level bridge:
Gross captured payments − refunds − disputes and related adjustments − processor fees ± other adjustments = expected settlement.
The result may not equal a bank deposit on the same date because processors use settlement cutoffs, batching, weekends, holidays, reserves, rolling balances, and payout schedules. The goal is therefore not to force daily totals to match. It is to identify timing differences, explain legitimate adjustments, and investigate unexplained items.
For technical teams, stable payment identifiers and reliable webhooks are essential inputs. The Payment API Integration Checklist covers idempotency, retries, webhook handling, and reconciliation design. For merchants using stored payment details, the payment tokenization guide explains why a customer-facing order reference may differ from a processor or network identifier.
What to track
Build a reconciliation field dictionary
Before matching records, document the meaning and source of each field. Names vary by payment provider, so use your provider’s export documentation and preserve the original column alongside any normalized field.
- Order ID: Your internal order, invoice, or subscription identifier.
- Payment ID: The gateway or processor’s identifier for the payment object.
- Authorization ID: The reference for the approval event, where supplied.
- Capture ID: The reference for the collected amount. One authorization may have one or more captures.
- Refund ID and parent payment ID: The refund reference and the payment it reverses.
- Dispute ID: The reference for a chargeback or other dispute lifecycle.
- Event type: Authorization, capture, refund, reversal, failure, dispute, fee, reserve, release, or payout.
- Event date and settlement date: The date the event occurred and the date it was included in a settlement batch.
- Gross amount, fee amount, net amount, and currency: Keep amounts in their original currency before converting them for reporting.
- Payout ID and bank reference: The processor batch and the corresponding bank deposit reference.
- Exchange rate and converted amount: Record the rate or conversion information supplied by the processor rather than recalculating without documentation.
- Status: Pending, succeeded, failed, refunded, disputed, reversed, or another provider-defined state.
Do not overwrite source exports. Store the original file with its download date, then create a normalized working table. This supports audit trails and makes it easier to identify whether a difference came from a transaction or from a changed report format.
Track event relationships, not only totals
Match each capture to an order or invoice, each refund to its parent payment, each dispute to the affected payment, and each payout to its settlement lines. A partial refund should reduce the refundable captured amount without creating a second sale. A failed payment should not be counted as revenue or cash merely because an earlier authorization existed. An authorization that expires or is reversed should be marked as a non-cash event.
For recurring billing, include the subscription or billing-cycle identifier. This helps distinguish a retry for the same invoice from a new charge. It also makes it easier to reconcile cancellations, prorated credits, and failed-payment recovery. The recurring billing guide provides related implementation considerations.
Cadence and checkpoints
Daily or each processing day
- Import new processor events, payouts, refunds, disputes, and fees.
- Deduplicate records using the provider’s event or transaction identifier.
- Match successful captures to orders or invoices.
- Compare the processor’s payout detail with the expected net amount.
- Separate pending items from settled items and record the expected next action.
- Review unmatched records, duplicate captures, unexpected refunds, and unusually large fees.
Daily reconciliation should be a short control, not a full investigation of every historical exception. Assign an owner and an aging status to each open item so that unresolved differences do not disappear between close periods.
Weekly
Review open exceptions by age, payment method, currency, processor, and event type. Look for repeated patterns such as a missing webhook, an incorrect order-reference mapping, a payout cutoff misunderstanding, or a fee appearing in a separate report. Confirm that bank deposits marked as settled have corresponding payout records.
Month-end
Freeze the reporting period according to the organization’s accounting policy, then reconcile all included settlement batches. Accrue or defer items only under the applicable accounting policy and with appropriate finance review; payment reconciliation does not by itself determine when revenue should be recognized. Reconcile processor fees, reserves, dispute adjustments, foreign-exchange differences, and deposits in transit. Save the source reports, exception log, reviewer sign-off, and explanations for material differences.
A practical spreadsheet structure is to maintain separate tabs for Orders, Payment Events, Refunds and Disputes, Fees and Adjustments, Payouts, and Exceptions. Use one normalized amount column per currency and a separate reporting-currency amount. Avoid mixing transaction date, settlement date, and bank-posting date in one date field.
How to interpret changes
Spreadsheet-ready matching rules
Adapt the following rules to the fields in your provider export:
- First match on a unique payment, capture, refund, dispute, or payout identifier.
- If no unique identifier exists, match on the internal order ID plus event type, currency, amount, and an allowed date window.
- For partial refunds, match the refund ID to the parent payment and compare cumulative refunds with the captured amount.
- For payouts, match payout ID first, then sum the settlement lines by currency and compare them with the processor’s stated net total.
- For bank deposits, match the payout reference and expected net amount; allow for bank posting-date differences.
- Flag any duplicate provider identifier, negative balance, currency mismatch, or captured amount greater than the authorized amount for review.
- Do not match by amount alone. Identical amounts can belong to different customers, dates, or currencies.
Exception-handling decision tree
- Does the processor record exist? If not, check import timing, API pagination, report filters, and webhook delivery before treating it as missing.
- Does the internal order exist? If not, investigate test transactions, manual charges, integration mapping, or an order created after payment.
- Do identifiers agree? If not, trace the payment, capture, and order-reference fields rather than creating a manual match without evidence.
- Do amounts agree? Check partial captures, refunds, tips, taxes, discounts, processor fees, and currency conversion.
- Does timing explain the difference? Compare event date, settlement date, payout cutoff, and bank posting date.
- Is the item a fee, reserve, dispute, or other adjustment? Classify it separately and retain supporting report lines.
- Is the difference still unexplained? Assign an owner, record the amount and currency, set a due date, and escalate according to the close policy.
Common patterns are informative. A processor total higher than the order system may indicate duplicate captures, orders excluded from the report, or a date-filter mismatch. A bank deposit lower than the processor’s gross total is often consistent with fees, refunds, reserves, disputes, or payout timing. A growing number of unmatched refunds can indicate that the refund process is not preserving the original payment ID.
When to revisit
Run the daily control whenever payments settle, perform the exception review at least weekly, and complete a formal reconciliation during each month-end close. Revisit the workflow after adding a payment method, changing a gateway or payment processor, enabling a payment orchestration platform, launching multi-currency checkout, changing payout timing, or introducing recurring billing.
Update the field dictionary and matching rules whenever a processor changes its dashboard, export columns, settlement terminology, fee presentation, or API event model. Also review the process after a failed audit test, a material unexplained variance, a major increase in disputes, or a migration to a new accounting system. Security and compliance changes deserve a separate check; related controls are covered in the PCI DSS compliance checklist.
For the next close, start with three actions: save the latest raw processor exports, confirm that every report field has an owner and definition, and produce an exceptions list with amounts, currencies, identifiers, dates, explanations, and due dates. A reconciliation is complete only when the expected settlement is supported by transaction-level records, timing differences are documented, and unresolved items are visible to the person responsible for clearing them.