How to Reconcile Marketplace Sales, Fees, Refunds and Payouts in a Spreadsheet

Disclosure: Seller Sheets Lab sells the paid workbook linked at the end. This tutorial is a standalone illustrative example, not a reproduction or test of that workbook.

If you sell on one or more marketplaces, the number that lands in your bank account is rarely the same as “sales.” Platforms deduct fees, sometimes return fees after a refund, and may collect sales tax that never belongs in your income column.

A small spreadsheet can keep those pieces apart. The layout below uses SUM, COUNT and IF with basic arithmetic. Syntax may vary by spreadsheet locale. The numbers are clearly marked sample data so you can check every formula against the arithmetic. This post does not claim a full product test inside Excel or Google Sheets.

What goes wrong when columns are mixed

Three common mistakes:

1. Logging the bank deposit as item sales (fees disappear; gross sales look too low).
2. Subtracting a refund from the original sale row and logging it again in a refunds list (double counting the refund).
3. Treating sales tax collected by the marketplace as your revenue (it usually is not paid out to you as income).

The fix is structural: one sales log for gross and fees, one refunds log, and a simplified sales-and-refunds summary built from those parts.

What you will build

One tab with:

1. A Sales log in columns A:G (channel, item sales, shipping, three fee columns, sales less recorded fees)
2. A Refunds log in columns I:L (channel, refunded to buyer, fees returned, net cost of refund)
3. A Summary block in column N (gross sales, fees, refunds, simplified net after refunds and fees, fee % and refund %)

Keep the two logs side by side so ranges never overlap. Do not put refunds inside the sales fee columns.

This sheet does not include a column for actual bank deposits. Column G and summary N4 are calculated bookkeeping subtotals only.

Layout (so ranges do not overlap)

Sample data (examples only — replace with your numbers)

Use these rows only to verify the formulas. Delete them when you start your own month.

Sales log (sample) — columns A:G

Type fees as positive numbers (amounts taken out). Leave a fee cell at 0 when that fee did not apply. If the marketplace collects and remits sales tax for you, do not add that tax into B or C.

Refunds log (sample) — columns I:L

Type refunded-to-buyer amounts and fee credits returned to you as positive numbers. Record each refund in the period when it settles on the platform statement. Leave the original sales rows untouched when you refund; add a refunds-log row instead. If L is negative (for example fee credit larger than the refund, or a late fee credit with no matching refund in that row), treat it as a signal to review the settlement — do not overwrite the formula to force a preferred sign.

Step 1: Sales less recorded fees (per sales row)

Important: Column G is a simplified bookkeeping subtotal, not the amount necessarily transferred to your bank. Actual payouts may differ because of reserves, holds/releases, payout schedules, chargebacks, refunds, currency conversion, taxes paid out to you and other adjustments.

In G2:

=IF(COUNT(B2:F2)=0,"",B2+C2-D2-E2-F2)

Copy down through G4.

Reading the formula:

• Gross for the row = item sales + shipping (B+C)
• Fees leave as positive values (D+E+F)
• When B:F are empty, COUNT is 0 and the cell stays blank — not a fake 0.00 subtotal
• Enter numeric amounts only in B:F; pasted text in a numeric cell can produce #VALUE!. COUNT is not a full validation; prefer clean numbers or explicit checks rather than hiding every error with IFERROR

Expected results with the sample:

Check: 100+10-6.50-3.30-0 = 100.20, and so on.

Step 2: Net cost of each refund

In L2:

=IF(COUNT(J2:K2)=0,"",J2-K2)

Copy down through L3.

Expected results with the sample:

Fees returned reduce the cost of the refund. An empty refunds row stays blank.

Step 3: Summary totals

Put these labels in M2:M8 and formulas in N2:N8:

Expected results with the sample:

Two checks that should always agree inside this simplified model:

1. N4 should equal N2-N3 (sales less recorded fees = gross − fees) while every sales row uses the Step 1 formula and refunds stay out of the sales log.
2. N8 should equal N4-N7 (simplified net = sales less recorded fees − net refund cost). With the sample: 218.67 − 38.37 = 180.30.

Empty sales and refund ranges sum to 0. That is expected after you delete the sample rows and before you enter your own month.

N4 and N8 are still not bank deposits. They ignore reserves, holds, chargebacks, FX and payout timing.

Step 4: Fee rate and refund rate (guard zero gross)

In N9 (fees as % of gross) and N10 (refunds to buyers as % of gross):

=IF(N2=0,"",N3/N2)
=IF(N2=0,"",N5/N2)

Format as percentages when the cells show numbers.

Expected results with the sample:

• Fees % (N9): 11.11% (27.33 ÷ 246)
• Refunds % (N10): 16.26% (40 ÷ 246)

When gross is zero (N2=0), both cells stay blank. A blank means “rate not defined”, not 0%. Do not replace the blank with 0 — that would look like a real 0% fee or refund rate.

Equivalent pattern with IFERROR (same idea: hide the #DIV/0!):

=IFERROR(N3/N2,"")

IF is clearer when you only want to treat a zero denominator; IFERROR also hides other errors. Prefer IF(N2=0,"",…) for this tutorial.

Step 5: Optional bank-deposit check (statement vs deposit)

For the same currency and settlement batch, reconcile the platform's actual payout statement to the bank deposit. Start from sales less recorded fees, subtract gross refunds settled in that batch, add fee credits, and separately include chargebacks, reserve/hold movements and other settlement adjustments. Differences can be material; this tutorial does not model those adjustments.

Do not subtract net refund cost (N7) in the same step where you also add fee credits (N6) — that would double-count the returned fees. Prefer the gross path:

simplified batch subtotal = sales less recorded fees − refunded to buyers + fees returned
                          = N4 − N5 + N6

In the simplified sample only (one fictional batch, no reserves/holds/chargebacks/FX):

218.67 − 40.00 + 1.63 = 180.30

That equals N8 and also equals N4 − N7, because N7 = N5 − N6. Use one path consistently.

When a real payout hits your bank, compare the deposit to the platform payout statement for that batch — not to column G alone. Investigate material differences; do not force the sales column to equal the deposit.

A simple monthly checklist

1. Enter every order (or payout statement line) with gross item sales and shipping — never the deposit alone.
2. Enter platform, processing and ads fees as positive amounts in their own columns.
3. Log refunds on the refunds side; do not rewrite history in the sales log.
4. Confirm N4 = N2-N3 and N8 = N4-N7 (or N8 = N4-N5+N6) before you close the month.
5. Read fee % and refund % only after gross is non-zero.
6. Treat N4/N8 as bookkeeping subtotals; reconcile the real deposit to the platform payout statement.

Common mistakes

• Pasting the bank deposit into item sales.
• Calling sales less recorded fees the “bank payout.”
• Putting refunds inside the sales log fee columns.
• Entering fees as negative numbers while also subtracting them in the Step 1 formula (double subtraction).
• Subtracting net refund cost and adding fee credits in the same bank-check path (double-counting returned fees).
• Counting marketplace-collected sales tax as item sales.
• Leaving N3/N2 unprotected so a zero-gross month shows #DIV/0!.
• Mixing several currencies in one column without a conversion step.

Want the workbook already built?

You can recreate everything above in a blank sheet.

If you want a ready-made annual seller workbook instead, our Seller Bookkeeping & Tax Estimate Spreadsheet 2027 organizes sales, fees, refunds and expenses across the year, with monthly and quarterly summaries, an estimated tax set-aside you choose, and a Form 1099-K check against your own records. You type or paste your numbers (no automatic import). Tax figures are estimates for planning, not a filed return and not tax advice. One-time purchase, no subscription. This tutorial is not a test of that workbook. It is a separate product from this DIY example; use whichever fits your workflow.

This article is for seller bookkeeping education only. It is not tax, legal, accounting or financial advice. Figures in tax-related product features are estimates based on the data you enter and may change; review filings with a qualified tax professional. Seller Sheets Lab is not affiliated with or endorsed by the IRS, Etsy, eBay, Amazon, Shopify, PayPal, Stripe, Payhip, Gumroad, Google LLC or Microsoft Corporation.

References (optional reading)

• IRS: Understanding your Form 1099-K (A 1099-K gross amount is not the same as taxable income; you may receive a form even below federal thresholds, and business income is reportable either way.)