Excel Guides

How to Trace Equal Stripe Refunds to Different Payouts in Excel

You close November and see two $65 refunds against the same October Stripe charge. They look like duplicates. In this practice workbook, one reduced the November 6 payout and the other reduced the November 13 payout. A third $65 refund belongs to a different charge.

Check two things separately: how much was refunded against the sale, and which payout each refund reduced. Deleting a row because its amount repeats would get both checks wrong.

Download the practice workbook

Download Jolene_Stripe_Partial_Refund_Practice.xlsx

All IDs, dates, amounts and fees are synthetic. The fees are simplified example values, not a quotation of Stripe pricing. The file has seven itemized balance transactions, three bank deposits and a Refund review sheet. All amounts are USD dollars.

Which Stripe export to use

For automatic payouts, download the Itemized CSV from Stripe’s Payout reconciliation report. Include the original charge’s payout and the later payouts being checked. Manual payouts generally require the Balance report instead. Stripe cannot identify the transactions included in instant payouts. See Stripe’s report instructions and column definitions.

Keep payout ID, charge ID, source ID, balance transaction ID, reporting category, currency, gross, fee and net. Include charge_id and source_id in the report configuration if missing. Keep expected payout arrival separate from the bank’s posted date.

1. Find all refunds against the original sale

The original charge is ch_demo_410: $400 gross and $388 net in the October 30 payout, at 'Stripe itemized'!B2:J2. Filter charge_id for this charge and reporting_category for refund.

Refund source IDAmountPayoutSource cells
re_demo_810$65po_demo_nov06‘Stripe itemized’!B5:J5
re_demo_812$65po_demo_nov13‘Stripe itemized’!B8:J8

The two refund IDs are different. Together they refund $130 against the $400 charge. Stripe permits multiple partial refunds against one charge, provided their total does not exceed the charge. Stripe’s refund documentation explains this.

The third refund, re_demo_811, is also $65, but its charge is ch_demo_411. Check 'Stripe itemized'!C6:D6. It reduces the November 6 payout but does not belong in the $130 refunded against ch_demo_410.

2. Reconcile each payout to the bank

Group the itemized net amounts by automatic_payout_id and currency. Do not group by charge ID for this check: one payout includes transactions from several charges, and one charge can have refunds in later payouts.

PayoutNet calculationBank depositSource cells
October 30$388 + $194 = $582$582‘Stripe itemized’!J2:J3; ‘Bank deposits’!E2
November 6$485 − $65 − $65 = $355$355‘Stripe itemized’!J4:J6; ‘Bank deposits’!E3
November 13$582 − $65 = $517$517‘Stripe itemized’!J7:J8; ‘Bank deposits’!E4

Each deposit has a unique amount and matching date in this sample. If candidates overlap in your own statement, check payout details and the bank reference before assigning a match. A matching amount alone is insufficient.

The Refund review formulas in C6:C8 sum net by payout and currency. C13 sums gross refunds for the target charge and reverses their negative sign for display. The formulas cover the seven practice rows. Extend the source ranges when using larger exports.

Before removing any duplicate

If downloads overlap, check repeated balance_transaction_id values within the same Stripe account. Repeated copies of one balance transaction differ from separate refunds on one charge. Conflicting versions of the same ID need investigation. Preserve the original exports.

Try the source tabs in Jolene.pro

Open the workbook in Jolene.pro and select Stripe itemized and Bank deposits as the source tabs.

Trace refunds for charge ch_demo_410. Give each refund ID, amount and automatic payout ID. Identify any same-amount refund linked to a different charge. Total Stripe net by payout and currency, compare each total with the bank deposits, and cite the cells supporting each match.

Compare the response with $130 refunded on the target charge and bank matches of $582, $355 and $517. These figures and cell references were independently checked against the synthetic workbook. A live Jolene.pro result has not been verified.

For the wider sales-to-bank calculation, read why $20,000 in collected payments can produce an $18,400 bank deposit. Browse Workbook Studies or try your own exports at Jolene.pro.

1 comment

Leave a comment