Profit 15 min read Intermediate

Reconcile Shopify Payouts in Excel: Complete Guide to Fees, Refunds & Reserves

Stop guessing why your bank deposit doesn't match your sales. This guide shows you exactly how to reconcile Shopify payouts in Excel, accounting for every fee, refund, and reserve down to the penny.

Platforms: Shopify Shopify Payments
Reconcile Shopify Payouts in Excel: Complete Guide to Fees, Refunds & Reserves

Why Your Bank Deposit Never Matches Your Sales Dashboard

You made $10,000 in sales yesterday. You check your bank account. The deposit is $9,347.23. Where did $652.77 go? This is the daily frustration of e-commerce accounting. Shopify (and every other platform) deducts multiple fees before sending your payout. They also hold reserves, deduct refunds from previous periods, and apply varying fee structures based on your plan and payment methods. Without proper reconciliation, you're flying blind. You might think you're profitable when fees are eating your margins. You might miss unauthorized charges. Your accountant will hate you at tax time. This guide will show you exactly how to build a reconciliation system in Excel that accounts for every cent.

Understanding the Payout Anatomy

Before we dive into Excel, you need to understand what goes into (and out of) a Shopify payout.

Components of a Payout:

IN (adds to payout):
  • Gross sales from orders
  • Shipping charged to customers
  • Taxes collected
OUT (reduces payout):
  • Shopify subscription fees
  • Transaction fees (0.5-2% depending on plan and payment method)
  • Payment processing fees (2.9% + 30¢ for Shopify Payments in US)
  • Chargeback fees ($15-25 per incident)
  • Refunds issued (both order amount and fees)
  • Adjustments and corrections
  • Reserve holds (if applicable)

Shopify Fee Structure by Plan (US)

Shopify Plan Monthly Fee Online Card Rate Transaction Fee (non-Shopify) In-Person Card Rate
Starter $5/mo 5% + 30¢ N/A N/A
Basic $39/mo 2.9% + 30¢ 2.0% 2.7% + 0¢
Shopify $105/mo 2.7% + 30¢ 1.0% 2.5% + 0¢
Advanced $399/mo 2.4% + 30¢ 0.5% 2.4% + 0¢
Plus $2,300/mo 2.15% + 30¢ 0.15% 2.15% + 0¢
Key Points:
  • Transaction fee only applies if you use third-party payment processors (not Shopify Payments)
  • International cards have higher rates (additional 1-1.5%)
  • Chargebacks cost $15 USD regardless of plan
  • These rates are for US merchants; other countries have different structures

The Critical Concept: Payout ID

Don't try to match daily sales to daily deposits. It doesn't work. Instead, every payout has a unique Payout ID that groups all transactions that will arrive in that specific bank deposit. An order from Monday might be in Wednesday's payout. A refund from last month might be deducted from today's payout. The Payout ID is your source of truth.

Step-by-Step Excel Reconciliation Process

1

Export Shopify Payout Data

In Shopify Admin, go to Finances > Payouts. Click on the payout you want to reconcile. Export the 'Transactions' CSV. This file contains every order, refund, fee, and adjustment in that payout.

Export Shopify Payout Data
2

Set Up Your Excel Workbook Structure

Create four worksheets: (1) Raw Data - paste your CSV here, (2) Calculations - formulas and analysis, (3) Summary - final reconciliation, (4) Variance - investigate discrepancies. Keep raw data untouched so you can always verify against source.

Set Up Your Excel Workbook Structure
3

Calculate Gross Sales by Payout ID

In Calculations sheet, use =SUMIFS(RawData!Amount, RawData!PayoutID, PayoutID, RawData!Type, "charge") to sum all charges. This gives you gross sales before any deductions.

Calculate Gross Sales by Payout ID
4

Sum All Fee Categories

Create separate calculations for each fee type: transaction fees (=SUMIFS for "transaction_fee"), processing fees ("payment_processing_fee"), refunds ("refund"), and chargebacks ("chargeback"). Each has its own row in your Summary sheet.

Sum All Fee Categories
5

Build the Reconciliation Waterfall

Start with Gross Sales. Subtract each fee category line by line. The final number should equal your Expected Payout. Formula: =GrossSales - TransactionFees - ProcessingFees - Refunds - Chargebacks - Adjustments

Build the Reconciliation Waterfall
6

Compare to Actual Bank Deposit

In your Summary sheet, enter the actual deposit amount from your bank statement. Calculate variance: =ExpectedPayout - ActualDeposit. If variance is more than $0.01, you have a problem to investigate.

Compare to Actual Bank Deposit
7

Investigate Discrepancies with Pivot Tables

If there's a variance, create a pivot table from Raw Data with Type as rows and Amount as values. This quickly shows if you missed a fee category or if Shopify made an adjustment you didn't account for.

Investigate Discrepancies with Pivot Tables

Excel Formulas You Need to Know

SUMIFS for Conditional Summing

=SUMIFS(amount_range, payout_id_range, "po_123456", type_range, "charge")
This sums all amounts where the payout ID matches AND the type is "charge".

VLOOKUP for Fee Rate Lookup

=VLOOKUP(payment_method, fee_table, 2, FALSE)
If you have different fee rates by payment method (credit card vs. debit), use a lookup table.

COUNTIFS for Transaction Count Verification

=COUNTIFS(payout_id_range, "po_123456", type_range, "charge")
Count how many orders are in this payout. Cross-check against Shopify's payout details.

Advanced: Power Query for Automation

If you reconcile payouts regularly, use Power Query to connect directly to your exported CSV folder. New files automatically refresh your workbook.

Common Mistakes to Avoid

Mistake: Trying to match order dates to deposit dates
Solution: Use Payout ID, not dates. An order from Monday can be in Friday's payout depending on your payout schedule (daily, weekly, monthly).
Mistake: Not accounting for refunds from previous payout periods
Solution: Refunds are deducted when issued, not when the original order was placed. Check refund date against payout date.
Mistake: Forgetting about Shopify subscription fees
Solution: Your monthly Shopify plan fee is usually deducted from the first payout of each billing cycle. It's a transaction type "service_fee".
Mistake: Rounding errors from currency conversion
Solution: If you sell internationally, track exchange rates. Shopify converts at their rate, not yours. Use at least 4 decimal places in Excel.
Mistake: Not reconciling every single payout
Solution: Missing even one payout throws off your entire accounting. Make reconciliation a non-negotiable part of your weekly routine.

Verification Checklist

  • Your Expected Payout in Excel matches Shopify's Payout Details page within $0.01
  • Your Expected Payout matches your actual bank deposit within $0.01
  • You can explain every single fee line item and trace it back to source transactions
  • Your refund amounts match the Refunds section in Shopify Analytics
  • Transaction count in your Excel matches transaction count in Shopify payout details

Frequently Asked Questions

This usually means you missed counting a transaction fee. Shopify charges 30¢ per transaction for Shopify Payments. Recheck your SUMIFS formula for transaction_fee type.
Fee rates vary by your Shopify plan (Basic, Shopify, Advanced), your location, and payment method (credit, debit, international). Check Settings > Payments for your exact fee structure.
Export the Shopify payout details as CSV again (sometimes export glitches). Check for adjustments or corrections in the transaction list. Still stuck? Contact Shopify Support with your Payout ID.
Shopify keeps payout data indefinitely. You can export and reconcile any past payout. The further back you go, the longer the export might take.
Yes. Use Power Query in Excel to connect to a folder where you save payout CSVs, or use an automated tool like Spreadsheet Broccoli that pulls payout data via API and generates reconciliation reports automatically.