How to reconcile PayPal transactions with your bank statement

Why the totals never match directly

Customers pay into your PayPal balance. PayPal deducts its fee, holds some funds, converts currencies, and only some of the balance is transferred to your bank. So the bank shows a few large transfers, while PayPal shows hundreds of small payments.

Step by step

1. Download the activity report for the month as CSV.
2. Add a Category column based on the transaction type: Sale, Fee, Refund, Transfer to bank, Conversion, Hold.
3. Pivot by Category to get the month's totals.
4. Filter Transfer to bank rows and match each to a bank deposit: =COUNTIFS(Bank[Amount], -[@Amount], Bank[Date], ">="&[@Date], Bank[Date], "<="&[@Date]+5).
5. Check: opening balance + sales − fees − refunds − transfers ± conversions = closing balance.

Transfers usually reach the bank one to a few working days later, hence the 5-day window. The PayPal amount is negative (money leaving PayPal), so compare it with the positive bank deposit.

Worked example

420 + 3,180 − 112.40 − 95 − 3,000 = 392.60, matching PayPal's closing balance. Both transfers (£1,800 and £1,200) were found on the bank statement two days later.

Common problems

• Held or pending payments appear in the activity but not in the available balance; keep them on a separate line.
• Currency conversions create pairs of rows (one per currency). Reconcile in your main currency only.
• Fees shown on the payment row rather than as a separate row: split them out with a formula on the fee column.

Card processors work the same way — see reconciling shop orders with Stripe payouts.

Categorising PayPal rows with a formula

The activity download has a type or description column. Its wording varies by account, country and report version, so build a small mapping table rather than hard-coding the text. List each distinct type once (a PivotTable or =UNIQUE(PayPal[Type]) gives you the list), then map it to your category.

Category =XLOOKUP([@Type], Map[Type text], Map[Category], "Unmapped")

Filter for “Unmapped” every month. A new type appearing is often the first sign of something you need to look at, such as a reversal or a new fee.

Google Sheets and Mac

In Google Sheets, import the CSV with File → Import, then use XLOOKUP or VLOOKUP with the same mapping table on a second tab. COUNTIFS with a date window works identically. In Excel for Mac the steps are the same as Windows; if your CSV opens with every value in column A, use Data → From Text/CSV (or Text to Columns) and choose comma as the delimiter. Dates exported in US format can be read the wrong way round in a UK-locale workbook, so check that 03/09 means 3 September before matching on dates.

Second example: a currency conversion

A US customer pays $100 into a sterling account. PayPal shows a USD payment row, a USD fee and a pair of conversion rows. The figures below are illustrative.

The USD rows net to zero (100 − 3.98 − 96.02 = 0), and only the £73.50 lands in the sterling balance. Reconcile in sterling: count £73.50 as the sale, net of fee and conversion. If you need the gross sale in sterling for your books, convert $100 at the same implied rate (73.50 ÷ 96.02 ≈ 0.7655), giving about £76.55 gross and £3.05 fee.

Troubleshooting

Checking the result

1. The rebuilt closing balance equals the balance PayPal reports for the last day of the month.
2. Every Transfer to bank row is matched to exactly one bank deposit, and no deposit is matched twice.
3. Sales in PayPal agree with the orders your shop marks as paid by PayPal — use the same approach as matching Stripe payments to invoices on the order or invoice number.
4. Fees in PayPal equal the fee expense posted in your ledger.

Keep each month's CSV and workbook together. Next month's opening balance must equal this month's closing balance; if it doesn't, a row was edited or reported late. For the bank-side process, see bank reconciliation in Excel.

Repeating it every month

1. Save the mapping table and the matching sheet as a template workbook, with the PayPal data in a table called PayPal and the bank lines in a table called Bank.
2. Paste the new month's CSV over the old rows (or point a Power Query at a folder of monthly downloads and refresh).
3. Enter last month's closing balance as this month's opening balance — never retype it from PayPal, so a late-reported row shows up as a difference.
4. Review Unmapped types, unmatched transfers and any hold still open from earlier months.
5. Save the workbook with the month in the file name and keep the original CSV alongside it.

If you also sell through a card processor, run both reconciliations against the same bank sheet so a deposit can only be claimed once. The compare spreadsheets tool is a fast way to spot which transfers differ between two months' downloads if PayPal has restated anything.

Which method to use

For a few dozen transactions a month, the formulas above in one workbook are enough. If you sell through several channels into one PayPal account, or need a year of history for your accountant, stack the monthly CSVs first (see combining CSV files in Excel) and pivot the whole year by category and month.

Frequently asked questions

How do I reconcile PayPal with my bank account?

Match PayPal's transfers to bank against bank deposits by amount within a few days, and reconcile sales, fees and refunds within the PayPal balance.

Why is my PayPal balance different from my sales?

Fees, refunds, holds, currency conversions and the timing of transfers to your bank all sit between sales and the cash in your bank.

How long do PayPal transfers take to reach the bank?

Typically one to a few working days, depending on your bank and country, so use a date window when matching.

Related guides

• How to reconcile payroll with timesheets
• How to reconcile shop orders with Stripe payouts
• How to reconcile Square sales with bank deposits
• How to reconcile a stock count with inventory records
• How to reconcile a rent roll with rent payments