Reconciling Stripe Payouts Against Your Ledger

The bank deposit from your payment processor is almost never the sum of your sales — it is sales minus processor fees minus refunds minus disputed amounts, batched across a settlement window and deposited a day or two later. Reconciling that single net number against your double-entry ledger posting is the job that proves your books match the money that actually arrived. This page is a concrete instance of Reconciliation & Double-Entry Ledger: how to expand a Stripe payout into its component balance transactions, match each against a ledger entry, and prove the arithmetic to the cent.

You reach for this the first time finance asks why the number in the bank does not equal the revenue in your dashboard. The answer is always fees, refunds, and timing — and a payout reconciliation that models all three is the only way to answer it without a spreadsheet archaeology session.

Trade-offs

How you reconcile payouts trades engineering effort against how quickly a discrepancy surfaces. A monthly bank-statement match is cheap but slow; a per-payout balance-transaction match is more work but catches drift within a settlement cycle. The map ranks the options.

Payout reconciliation strategies Monthly statement matching is slow, daily net matching is faster, and per-payout balance-transaction matching catches drift within a cycle at higher effort. Monthly statement drift found: weeks late bank total only low effort, low fidelity Daily net match drift found: days late payout total vs ledger medium effort Per-transaction drift found: same cycle every leg matched the target
Per-transaction matching catches a discrepancy within the settlement cycle — the difference between a same-day alert and a month-end surprise.

For any business past a few hundred transactions a month, per-payout balance-transaction matching is worth the build: it localizes a discrepancy to a single charge or fee rather than a whole month, and it is the only method that survives an audit question about a specific transaction.

Why the monthly match hides its own errors

The seductive thing about a monthly bank-statement match is that it usually balances — and that is exactly why it is dangerous. Over a thirty-day window, a fee posted a cent low on one charge and a cent high on another cancel out in the aggregate, so the month ties out while two individual ledger entries are wrong. You only discover the offsetting errors when a customer disputes a specific invoice_id and the number you quote does not match the number Stripe settled. Per-payout matching removes the hiding places: each balance transaction has to find its own ledger entry at its own amount, so a compensating pair of one-cent errors surfaces as two discrepancies instead of a clean monthly total. The cost is roughly a day of engineering to build the expansion and matcher versus an afternoon to eyeball a statement, but the day buys you a system that answers “what did we settle for this charge” without a spreadsheet.

Latency to detection has a dollar value

Drift that surfaces weeks late is not just annoying; it is expensive to chase. By the time a month-end match flags a $340 gap, the balance transactions that caused it have scrolled off the top of everyone’s memory, the on-call engineer who captured the odd charge has moved teams, and the investigation turns into archaeology. Catching the same $340 within the settlement cycle means the surrounding context — the retried payment, the partial refund, the currency conversion — is still fresh, and the fix is a five-minute ledger correction rather than a half-day reconstruction. That reduction in mean-time-to-explanation is the real return on per-payout matching, and it compounds every month you avoid a growing backlog of stale unmatched legs.

Step-by-Step Implementation

The four steps ingest the payout and its balance transactions, expand the payout into component legs, match each against a ledger entry, then assert the arithmetic. The expansion is the key idea — a payout is not one number but a sum of signed legs. The diagram shows the decomposition.

Payout decomposition A single net payout expands into positive charge legs, negative fee legs, and negative refund legs whose signed sum equals the deposit. Net payout bank deposit + charges match 1000_CASH − fees processor_fees − refunds refunds_payable signed sum = deposit
A payout is a sum of signed legs — expand it into charges, fees, and refunds before matching, and the arithmetic proves itself.

1. Ingest the payout and its balance transactions

Pull the payout and every balance transaction that rolls up into it. Store them verbatim; do not transform amounts, so the matcher compares apples to apples.

CREATE TABLE payout_lines (
  payout_line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  payout_id      TEXT   NOT NULL,           -- Stripe payout id
  external_ref   TEXT   NOT NULL,           -- balance transaction id
  kind           TEXT   NOT NULL,           -- 'charge','refund','fee','adjustment'
  amount_minor   BIGINT NOT NULL,           -- signed, gateway convention
  currency       CHAR(3) NOT NULL,
  matched_entry  UUID,
  status         TEXT   NOT NULL DEFAULT 'unmatched'
                 CHECK (status IN ('unmatched','matched','discrepant'))
);

Two ingestion details matter more than they look. First, make the ingest idempotent on external_ref: Stripe will happily hand you the same balance transaction twice if a webhook retries or a paginated fetch overlaps a poll, and a duplicate charge leg will make the payout appear to over-settle by exactly one charge. A unique index on (payout_id, external_ref) with an ON CONFLICT DO NOTHING on insert turns that class of bug into a no-op. Second, capture the payout’s own arrival_date and status alongside the lines. A payout can sit in pending, flip to paid, or land in failed and later reversed if the bank bounces the transfer — and a reversed payout whose lines you already marked reconciled will silently overstate cash unless you carry its status forward and unwind on reversal.

2. Expand the payout into component legs

Each balance transaction becomes one row keyed by its external reference. The gross, fee, and net fields on a Stripe balance transaction let you post fees as their own legs — which is what makes fee netting reconcilable.

def expand_payout(stripe, payout_id: str) -> list[dict]:
    legs = []
    for bt in stripe.balance_transactions.list(payout=payout_id, limit=100).auto_paging_iter():
        legs.append({"external_ref": bt.id, "kind": bt.type,
                     "amount_minor": bt.amount, "currency": bt.currency})   # gross leg
        if bt.fee:
            legs.append({"external_ref": f"{bt.id}:fee", "kind": "fee",
                         "amount_minor": -bt.fee, "currency": bt.currency})  # explicit fee leg
    return legs

The reason to split the fee into its own {bt.id}:fee leg rather than trusting bt.net is that the ledger recorded the fee as a distinct debit to processor_fees when the charge was booked. Matching leg-for-leg means the payout reconciliation and the original revenue posting agree on the same fee number; if you collapse to net here, a fee that was mis-booked upstream reconciles anyway and the error lives on undetected. The bt.type field is worth branching on too — charge, refund, payment_refund, adjustment, stripe_fee, and application_fee each map to a different ledger account, and lumping adjustment (which covers dispute reversals and Stripe’s own corrections) in with charge will send a matcher hunting for a customer invoice that never existed.

3. Match each leg against a ledger entry

Match by external reference, then assert amount equality. Anything that fails either step is a discrepancy, never silently dropped.

UPDATE payout_lines p
SET matched_entry = e.ledger_entry_id, status = 'matched'
FROM ledger_entries e
WHERE p.status = 'unmatched'
  AND e.external_ref = p.external_ref
  AND e.amount = ABS(p.amount_minor)
  AND e.currency = p.currency;

4. Assert the payout arithmetic

The payout must equal the signed sum of its legs. Run this before marking the payout reconciled; a non-zero residual is a real bug.

SELECT payout_id, SUM(amount_minor) AS reconstructed, :expected_payout AS actual
FROM payout_lines
WHERE payout_id = :payout_id
GROUP BY payout_id
HAVING SUM(amount_minor) <> :expected_payout;   -- any row = a discrepancy

Match by external_ref first and amount second, never amount alone. Two $49.00 subscription charges for subscription_id values that renewed on the same day are indistinguishable by amount, and an amount-only join will cross-match them — booking charge A against B’s ledger entry and vice versa. The books still balance in aggregate, so nothing alerts, but every downstream report keyed on customer_id is now wrong. Anchoring on the balance transaction id makes the match a bijection: one leg, one entry, or a discrepancy. Keep the residual join direction explicit too — a ledger entry with no matching payout leg (revenue booked but never settled) is a different failure from a payout leg with no ledger entry (money moved but never recorded), and folding them into one “unmatched” bucket loses the signal about which side is behind.

Verification & Testing

The tests prove the arithmetic holds under fees, refunds, and timing. Seed a synthetic payout with a charge, a fee, and a refund, and assert the reconstructed sum equals the deposit. Assert an in-transit charge (settled after the payout window) is excluded from this payout and picked up by the next. Mutate one fee by a cent and assert the residual surfaces as discrepant, not silently matched. The panel lists them.

Payout reconciliation tests A synthetic payout reconstructs to the deposit, an in-transit charge rolls to the next payout, a one-cent fee mutation surfaces as discrepant, and multi-currency payouts balance per currency. Reconstruct charge+fee+refund = deposit In-transit late settle next payout Mutation fee off 1¢ discrepant Multi-currency per currency balances
The in-transit test is the subtle one — a charge that settles after the payout window belongs to the next payout, not this one.

Add two adversarial cases the happy-path suite tends to skip. The first is the duplicate-ingest test: feed the same balance transaction id into the staging table twice and assert the reconstructed sum is unchanged, which proves the ON CONFLICT guard actually holds rather than merely existing in the schema. The second is the reversal test: reconcile a payout, then simulate Stripe reversing it (a bounced bank transfer) and assert the previously matched legs flip back to unmatched and the cash overstatement is unwound. Both are cheap to seed and catch the two bugs that most often reach production, because they only trigger under retries and bank failures that a manual test never exercises.

It is also worth asserting the negative: that a leg which matches no ledger entry stays unmatched and does not get swept into a matched count by an over-eager join. Seed a payout with one orphan adjustment leg — a Stripe correction with no corresponding invoice — run the full matcher, and assert exactly one row remains in the unmatched queue with the adjustment’s external_ref. This guards the property that matters most for trust in the system: nothing is ever marked reconciled unless a specific ledger entry claimed it at the exact amount.

Gotchas & Production Pitfalls

The pitfalls are about the ways a payout differs from a naive “sum of sales”: fee netting, in-transit timing, refunds and disputes inside the window, and cross-currency payouts. The map groups them.

Payout pitfalls Ignoring fee legs, treating in-transit as settled, missing in-window refunds and disputes, and mixing currencies are the recurring pitfalls. Fee netting net only recorded → post fee legs In-transit counted twice → assign by payout In-window refund reduces payout → model as leg Currency mix sum meaningless → balance per ccy
Four ways a payout differs from a sum of sales — modeling fees and refunds as explicit legs is what makes the deposit reconcile.
  • Recording only the net. If you post the net payout without the fee and refund legs, the payout can never reconcile arithmetically because the components are missing. Post charge, fee, and refund as separate ledger legs, then the deposit reconciles as their signed sum.
  • Treating in-transit funds as settled. A charge captured near the window boundary settles in the next payout. Assign each balance transaction to the payout that actually paid it, not to the day it was charged, or you double-count across two payouts.
  • Refunds and disputes inside the window. A refund or a lost dispute in the settlement window reduces the payout. Model each as a negative leg keyed on its own reference so the arithmetic holds.
  • Mixing currencies in one payout total. Summing USD and EUR legs is meaningless. Reconcile per settlement currency; each currency’s payout balances independently.
  • No unmatched queue. A leg that matches no ledger entry must land in an investigation queue, not vanish. A healthy reconciliation matches well over 99% automatically; the remainder is the queue’s job.

The failure that quietly erodes trust in the whole process is a rising unmatched queue that no one owns. Reconciliation only earns its keep if every unmatched item is triaged to a named outcome — post the missing ledger entry, reverse the phantom one, or classify it as a known timing difference that will clear next cycle — and if the queue’s age is itself monitored. An item sitting unmatched for a week is not “pending investigation,” it is an unresolved discrepancy in your books that will surface at period close under time pressure. Give the queue an SLA, alert when its oldest item crosses that age, and record the resolution of each item as a durable, audited action so the same discrepancy pattern recurring becomes visible as a systemic bug rather than a series of one-off fixes.

Timing differences deserve their own category rather than being lumped with true discrepancies, because conflating them trains operators to ignore the queue. A charge captured today but paid out in three days is not unmatched — it is not yet matched, and the reconciler should recognize the in-transit state and expect it to clear, flagging only the items that fail to clear within the expected settlement window. Reconcile against the arrival of funds for the cash view but report revenue against the capture date for the accounting view, and keep the two reconciliations distinct so a normal settlement delay never masquerades as a missing payment. The queue that only ever contains genuine anomalies is the one people actually work.

The negative-payout case nobody tests

On a low-volume day with heavy refunds or a large lost dispute, the signed sum of a payout’s legs can go negative — Stripe does not deposit money, it debits your bank account to recover the shortfall. A matcher that assumes payouts are positive will either skip the debit or take an absolute value somewhere and reconcile the wrong sign, leaving cash overstated by twice the debit. Treat a negative payout as a first-class case: the same signed-sum assertion holds, the deposit is simply negative, and the ledger posting moves cash the other way. Seed one deliberately in tests, because it is rare enough in production that you will not see it until a bad week, and that is the worst time to discover the sign bug.

Reconcile against arrival, report against capture

Keep two dates straight or your cash-timing reports drift. A charge is captured on one date and the funds arrive via payout a day or two later, and reconciliation lives on the arrival side — the bank only ever sees the payout’s arrival_date. Revenue recognition, by contrast, keys off the capture or the service period, not the payout. Blur the two and a charge captured on the last day of a month but paid out on the first of the next lands in the wrong period’s cash figure, and finance spends a morning explaining a gap that is purely a calendar artifact. Store both timestamps on every leg and be explicit about which one each report reads.

Frequently Asked Questions

Why does a payout rarely match the sum of charges? Because fees, refunds, disputes, reserves, and timing differences all land inside it. The reconciliation exists to attribute each of those rather than to make the numbers equal.

How often should payouts be reconciled? As each payout arrives, not monthly. A discrepancy found the same week is a query; the same discrepancy found at month end is an investigation across dozens of payouts.

What should happen to an unexplained difference? It goes to a suspense account with an owner and a deadline, never into revenue. Absorbing it silently means the underlying cause repeats.

Do reserves complicate the picture? Yes, because money is withheld and released on the provider’s schedule rather than yours. Model the reserve as its own balance so held funds are visible rather than appearing as missing cash.