paypal-stripe-reconciliation

name

paypal-stripe-reconciliation

description

Use when the user wants to reconcile PayPal, Stripe, Square, or any payment processor (PSP) against bank deposits or accounting records. Triggers on phrases like "Stripe payout doesn't match the bank", "PayPal balance is off", "reconcile my Stripe", "match payouts to invoices", "where did the missing $X go", or "Stripe fees aren't showing up right". Also triggers when reviewing PSP statements, finding refunds/chargebacks, or before month-end close for any business taking online payments.

Payment Processor Reconciliation

Overview

PSPs (PayPal, Stripe, Square, Adyen, Braintree) net out fees, refunds, chargebacks, and disputes before depositing to your bank. The bank sees a single net number; your books need the gross. Every reconciliation problem comes from this gross-vs-net gap.

Core principle: For every PSP payout to your bank, you must be able to explain — line by line — what gross sales, fees, refunds, and adjustments add up to that exact net amount. If you can't, something is wrong.

The reconciliation equation

Gross sales (in period)
  − PSP fees
  − Refunds issued
  − Chargebacks / disputes
  − Reserves held
  ± Adjustments (currency, rounding)
  = Net payout to bank

If left ≠ right, find the line that breaks it before adding a plug entry.

Workflow

Step 1: Pull the 3 source documents

For the period being reconciled, get:

1. Bank statement — the deposits from the PSP
2. PSP payout report — Stripe Dashboard → Payouts; PayPal → Reports → Activity Download
3. Accounting record — QBO/Xero PSP clearing account transactions

Step 2: Match payouts 1:1 to bank deposits

Each PSP payout = one bank deposit (usually 1-2 day delay).

PSP payout date	PSP payout amount	Bank deposit date	Bank deposit amount	Match?
2026-05-01	$4,832.17	2026-05-03	$4,832.17	✓
2026-05-08	$3,201.50	2026-05-10	$3,201.50	✓
2026-05-15	$1,876.22	???	—	✗ MISSING

If a payout is missing from the bank: check (a) PSP reserve hold, (b) account-on-hold notice, (c) routing change.

Step 3: For each payout, break down the gross

In Stripe: click the payout → "Show details" → CSV export.

Each payout row should be one of:

• charge (gross sale)
• refund
• adjustment (chargeback, dispute, currency conversion)
• application_fee / stripe_fee
• transfer (account-to-account)

Sum each category for the period. The sum should match the payout amount.

Step 4: Reconcile to accounting

In QBO/Xero, your PSP should appear as a clearing account (current asset). Workflow:

1. Customer pays via PSP → debit Clearing Account, credit Revenue
2. PSP charges fee → debit Fee Expense, credit Clearing Account
3. PSP issues payout → debit Bank, credit Clearing Account
4. End of period: Clearing Account = funds sitting in PSP not yet paid out (should reconcile to the PSP's "Balance" on that date)

If your books are recording net deposits as Revenue, you're losing 2.9% of revenue visibility — and your COGS/margins are wrong. See references/journal-patterns.md for the correct entries.

PSP-specific gotchas

See references/psp-quirks.md for the full list. Top 5:

Stripe

• Instant Payouts have a 1% fee on top of the 2.9% — easy to miss
• Stripe Tax auto-collects sales tax; that goes to Sales Tax Liability, NOT Revenue
• Disputes show as $-15 in payout for the dispute fee, then $-original_amount when lost
• Connect platforms have nested fees (application_fee + Stripe fee)

PayPal

• PayPal Working Capital loan repayments come out of payouts — looks like a fee but it's debt service
• Currency conversion uses PayPal's rate, not market — track the spread separately
• eCheck payments take 3-5 days; show as pending then clear

Square

• Instant Deposit has 1.75% fee; standard deposit is free but 1-2 day delay
• Square Capital loan repayment is a hold-back %, not a fixed amount

Multiple PSPs

• If you use 2+ PSPs, never co-mingle them in one clearing account. One clearing account per PSP.

The reconciliation report (Claude's deliverable)

After reconciling, produce this format:

# PSP Reconciliation — [PSP Name], [Period]

## Summary
- Gross sales (PSP): $X
- PSP fees: $X (Y% effective rate)
- Refunds: $X
- Chargebacks: $X
- Reserves change: $X
- **Net payouts: $X**
- **Bank deposits matched: $X**
- **Unmatched: $X**

## Unmatched items (require investigation)
| Date | PSP txn ID | Amount | Notes |

## Journal entries posted
[List]

## Anomalies
- [Refund rate this period: X% vs. trailing 3mo avg Y%]
- [Effective fee rate X% vs. expected Y%]

Red flags

Investigate immediately:

• Effective fee rate > 4% on Stripe → probably card-not-present + international cards; check pricing tier
• Chargeback rate > 1% → risk of PSP account hold
• Refund rate > 10% → product or fulfillment issue
• Reserve growing → PSP risk team flagged you; call them
• Payout missing > 3 days → account issue, call PSP immediately
• Currency conversion losses > 2% of FX volume → negotiate or switch FX provider

Quick reference

Issue	Where to look
Missing payout	PSP Dashboard → Payouts → Status
Mystery deduction	PSP Dashboard → Disputes + Reserves
Fee anomaly	Pricing page + per-transaction breakdown
Bank deposit doesn't match PSP payout	Currency conversion + reserve hold
Customer paid but no record	Webhook delivery log (Stripe/PayPal)