Work sample 09 / 14Payments + accounting
Stripe payouts, fees and refunds reconciled to the bank feed in Xero
How I'd approach card payouts for a premium service group on Stripe and Xero, so each payout is checked to its charges, fees and refunds, then to the bank deposit, and the clearing account returns to zero.
Worked example on representative data.
- Area
- Payments + accounting
- Tools
- Stripe · Xero · Bank feed
- Typical scope
- US$1,500–3,000
- Timeline
- About 3 weeks · 4 fixed milestones
01Context
An established premium service group takes deposits and invoice payments by card through Stripe, and keeps its books in Xero. The bookkeeper reconciles the bank feed once a month.
The systems in play
In the example the group has several branches that share one Stripe account and one Xero organization. A separate company in the group has its own Stripe account and its own Xero organization, and is handled the same way.
| System | What it does for the business |
|---|---|
| Stripe | Takes the card payment, deducts its fee, handles refunds and disputes, and pays out a net amount to the bank. |
| Xero | The ledger: invoices, payments, the bank feed and the reports the accountant relies on. |
| Bank | Receives one deposit per payout. The deposit matches no single invoice. |
| Bookkeeper | Ticks off the bank feed by hand against the Stripe dashboard at month-end. |
02Problem
What the owner sees
- Invoices marked paid, but bank deposits that match nothing
- Fees that never appear as a cost
- Refunds that leave an invoice showing as paid
- A month-end reconciliation that takes days
A Stripe payout is the sum of many charges, less fees, refunds and disputes. The bank shows one number, so the match has to be rebuilt from the Stripe report every time.
| Everyday situation | What goes wrong |
|---|---|
| Net deposits | The bank line is lower than the invoices it covers, because fees come off before the deposit |
| Refunds | A refund reduces a later payout, so the deposit matches neither the invoice nor the refund |
| Disputes | A lost dispute takes the amount and a dispute fee out of a payout, with no entry in Xero |
| Payments with no reference | A charge that carries no invoice number cannot be matched to an invoice |
| Deposits paid before an invoice exists | The money arrives first and has nothing to attach to |
| Timing | A payout in transit at month-end looks like a missing deposit |
03Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookA read-only review of the Stripe payout reports, the Xero bank feed and last month’s reconciliation. Nothing is changed.
- Agree the rulesThe accounts, the matching key and the treatment of fees, refunds and disputes are written down.
- Set upA Stripe clearing account in Xero, payments recorded against invoices, and a payout entry for every deposit.
- CheckEach payout is checked to its charges, then to the bank line, and the clearing account must return to zero.
| Item | Example treatment |
|---|---|
| Customer payment | Recorded against the invoice, into the Stripe clearing account |
| Stripe fee | Recorded as a cost from the payout report, out of the clearing account |
| Refund | Recorded as a credit note and refund against the original invoice, out of the clearing account |
| Dispute lost | The payment is reversed, the invoice reopens, and the dispute fee goes to the fees account |
| Payout | A transfer from the clearing account to the bank account, for the exact deposit amount |
| Matching key | The Xero invoice number carried on the Stripe charge, with a second check on customer and amount |
| Small differences | Written off only up to an agreed limit; anything above needs approval and is logged |
04What the setup includes
What the setup includes, and why each piece is there. The detail of how it is configured is agreed in the scoping call.
Show the setup, layer by layer
| Layer | What is set up and why |
|---|---|
| Clearing account | A Stripe clearing account in Xero, so money in transit has a home and the balance is a check in itself. |
| Invoice reference | The invoice number travels with each charge, so a payment can be matched without guessing. |
| Payout report | One payout report per deposit, listing its charges, fees, refunds and disputes. This is the source for the entry. |
| Payout entry | Each deposit is booked as a transfer from the clearing account to the bank, so the bank-feed line reconciles to a single entry. |
| Fees and disputes | Fee and dispute-fee accounts, so the cost of taking cards is a figure in the reports. |
| Exceptions list | A weekly list of duplicates, short payments, refunds still marked paid, and timing differences. |
| Opening position | Balances left from earlier months are cleared and listed so the first month starts from zero. |
How the data moves
- Customer pays by card
- Payment recorded against the invoice
- Fees and refunds recorded from the payout report
- Payout transferred to the bank
- Bank feed line reconciled
- Clearing account at zero
05Edge cases
These are agreed in advance and tested on a past month before go-live.
| Situation | How it is handled |
|---|---|
| Partial refund | Recorded as a credit note for the refunded part. The invoice shows the remaining amount as paid. |
| Refund of a payment from an earlier month | Recorded in the month it is refunded, against the original invoice, and listed on the exceptions list |
| Fee on a refunded charge | Stripe may not return the fee. It stays as a cost and is not netted against the refund. |
| Lost dispute | Payment reversed, invoice reopened and dispute fee recorded. The customer balance is followed up by a person. |
| Customer deposit before an invoice | Held as a prepayment and applied when the invoice is raised |
| Payout in another currency | The converted deposit is the bank amount. The difference to the charge total goes to an exchange account. |
| Failed or returned payout | The transfer to the bank is reversed, and the amount stays in the clearing account until it is paid again |
| Charge with no invoice reference | Matched on customer and amount. If that is not possible, it goes to the exceptions list. |
| Duplicate charge | A second charge for the same invoice is flagged and refunded after approval |
| Short payment | The invoice stays open for the difference. A write-off above the limit needs approval. |
| Company with its own Stripe account | Posted to that company’s own Xero organization, with a clearing account of its own |
| Who changed what | Each entry carries a user and a date in Xero, and the payout report used is saved with the month |
06Worked example
The tables below use two payouts on representative data, at an example fee of 2.9% plus US$0.30 per card charge. They show the shape of the checks, not a client result.
| Invoice | Gross | Fee | Net | Xero status |
|---|---|---|---|---|
| INV-3101 | 1,200.00 | 35.10 | 1,164.90 | Paid |
| INV-3102 | 450.00 | 13.35 | 436.65 | Paid, US$100.00 refunded |
| INV-3103 | 3,000.00 | 87.30 | 2,912.70 | Paid |
| INV-3104 | 250.00 | 7.55 | 242.45 | Paid |
| Deposit D-882 | 600.00 | 17.70 | 582.30 | Prepayment held |
| Total | 5,500.00 | 161.00 | 5,339.00 |
| Line | Amount |
|---|---|
| Charges | 5,500.00 |
| Fees | -161.00 |
| Refund on INV-3102 | -100.00 |
| Expected payout | 5,239.00 |
| Bank line | 5,239.00 |
| Difference | 0.00 |
| Line | Amount |
|---|---|
| Charge, INV-3105 | 2,000.00 |
| Fee on the charge | -58.30 |
| Dispute lost, INV-3098 (earlier month) | -800.00 |
| Dispute fee | -15.00 |
| Expected payout | 1,126.70 |
| Bank line | 1,126.70 |
| Difference | 0.00 |
| INV-3098 | Payment reversed, invoice reopened |
| Line | Amount |
|---|---|
| Payments recorded against invoices (P-1042, P-1043, P-1044) | 8,400.00 |
| Fees and dispute fee | -260.70 |
| Refunds and lost dispute | -900.00 |
| Payouts transferred to the bank (P-1042 and P-1043) | -6,365.70 |
| Clearing balance in Xero | 873.60 |
| Payout P-1044 in transit per Stripe | 873.60 |
| Difference | 0.00 |
07How the result is checked
The clearing account is the main check. If it does not equal the payouts still in transit, something has been missed.
| Check | How | Pass condition |
|---|---|---|
| Payout to its charges | The payout report is compared to the entries made for it | Charges, fees and refunds add up to the deposit |
| Payout to the bank | Each bank-feed line is reconciled to one payout entry | No unmatched deposits |
| Clearing account | Balance compared to payouts in transit at month-end | Difference is zero |
| Fees | Fees booked compared to the fee total on the Stripe report | Equal |
| Invoices marked paid | Each paid invoice compared to a Stripe charge or another recorded payment | No paid invoice without a payment |
| Exceptions list | Duplicates, short payments, open refunds and timing items | Each has an owner and a reason |
08Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Health Check | Read-only review of Stripe reports, the Xero bank feed and the last reconciliation, a 45-minute call, written findings and a fixed quote |
| Accounting rules | Accounts, matching key and the treatment of fees, refunds and disputes, in writing |
| Xero set-up | Clearing, fees, dispute and exchange accounts, plus the payout entry method |
| First month | One past month reconciled end to end, with opening differences cleared and listed |
| Exceptions list | A weekly list with owners, and a month-end clearing check |
| Second company | The same set-up for a company with its own Stripe account and Xero organization, where there is one |
| Handover | Written SOPs, a recorded walkthrough and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing or payroll. |
| Also outside the scope | Changes to Stripe pricing or contracts. Moving to different accounting software. Software subscriptions (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Health Check and rules | Findings, a fixed quote and the accounting rules in writing | Week 1 | 20% |
| M2 Xero set-up | Accounts and the payout entry method working on a test month | Weeks 1 to 2 | 30% |
| M3 First month reconciled | A past month reconciled end to end, clearing at zero | Weeks 2 to 3 | 35% |
| M4 Handover | Exceptions list, SOPs, walkthrough, 14 days of fixes | Week 3 | 15% |
Typical scope for this kind of project: US$1,500–3,000
About 3 weeks across 4 fixed milestones. The Systems Health Check (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I'll say where I'd look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Health Check is the right first step.