Download the Amazon settlement reports and your bank statement for the same period, total the settlement report by settlement ID, then look up each total against bank credits by amount and date window. Mark each line as matched, pending or different, and investigate differences using the report's line-level detail and your seller agreement.
What you are actually reconciling
Amazon pays you in settlements. Each settlement bundles many orders, refunds, fees and adjustments into one net figure, which then arrives in your bank as a single credit. So the first reconciliation is settlement total to bank credit. Order-level checks come only when that first match fails.
Differences usually come from three places: timing (the settlement closed but the credit has not landed), deductions or reserves that sit inside the net figure, and bank-side items such as a combined or split credit. Knowing which bucket a gap belongs to saves hours.
Download the right files first
Keep the period identical on both sides, with a few extra days of bank data at the end to catch late credits.
- Amazon settlement report: in Seller Central, open the Payments section and its reports repository, then download the settlement or date-range report for your period. Report names and column labels change over time, so check what your download shows.
- Bank statement: download it as Excel or CSV from net banking, not as a PDF. You need date, narration, and credit amount in separate columns.
- Your seller agreement and fee schedule: keep these handy to judge whether deductions look right. Do not rely on remembered rates.
Put each file on its own sheet in one workbook and convert both to Excel Tables. Do not edit the raw data.
Step-by-step matching in Excel
- Clean the dates and amounts. Make sure dates are real Excel dates and amounts are numbers, not text. Remove thousands separators or currency symbols that came in as text.
- Build a settlement summary. Create a pivot table, or a list of unique settlement IDs with a SUMIFS formula, such as =SUMIFS(Settlement!H:H,Settlement!A:A,A2). Add columns for start date, end date and the stated deposit date if your report has one.
- Isolate bank credits. Filter the statement for credits with Amazon-related narration. Narration text varies by bank, so check a few known payouts to learn your pattern.
- Look up each settlement in the bank sheet. Use =XLOOKUP(C2,Bank!D:D,Bank!A:A,0) where C2 is the settlement total and column D holds credits. A returned date means a match; 0 means not found.
- Check the date window. Subtract the settlement close date from the bank date. A gap of a few days is normal timing; a long gap needs a note.
- Compute the difference. Settlement total minus bank credit, per row, then tag it Matched, Pending or Different.
- Reverse-check the bank. Any Amazon credit with no settlement against it is an exception too, so list those separately.
If two settlements share an identical amount, XLOOKUP may return the first date for both. Add a helper column with the bank reference or a running count to separate them.
Common reasons a settlement does not match
| Symptom | Likely cause | What to check |
|---|---|---|
| No bank credit yet | Payout still in transit | Settlement close date, deposit date, bank holidays |
| Bank credit is lower than the report | Reserve, adjustment or deduction posted outside the report window | Next settlement, adjustment lines, account-level notices |
| One credit covers two settlements | Combined payout or bank netting | Sum of both settlements against the credit |
| Small round-off gap | Rounding or bank charge | Bank narration and charges |
| Credit with no settlement | Earlier period or non-settlement payment | Previous month's report |
For deductions, drill into line-level descriptions in the settlement report and compare them with your agreement and current fee schedule. For tax items such as TCS or TDS, confirm the applicable rules and your treatment with your tax adviser and the latest official notifications.
Doing the same for Flipkart
The method is the same: settlement total to bank credit first, line-level detail second. Flipkart payment and settlement reports come from Seller Hub, with different column names, settlement cycles and ways of showing commission, shipping fees and returns. Build a separate mapping sheet for each marketplace that renames its columns into one standard layout: date, reference, gross, deductions, net. Then one summary and one lookup formula work for both.
Make it repeatable, and know when Excel strains
Save the workbook as a template. Each month, paste in new raw data, refresh the pivot, and clear the exceptions list before closing the books. Keep a notes column with the date you raised each query and its outcome.
Excel strains when you have multiple marketplaces, thousands of order lines, or fee checks against agreed terms. MarketplaceMetrics by Fundle is built for that, covering sales and settlement reconciliation, commission and fee checks, and returns and recovery tracking for Indian brands. It reviews Myntra Excel reports today, and other marketplaces are a scope discussion, so confirm coverage before relying on it for Amazon or Flipkart.
Worked example
Illustrative example only. All figures are made up to show the layout.
| Settlement | Report total (₹) | Bank credit (₹) | Difference (₹) | Status |
|---|---|---|---|---|
| S-A | 1,25,000 | 1,25,000 | 0 | Matched |
| S-B | 82,500 | 0 (not yet received) | 82,500 | Pending, re-check in 3 days |
| S-C | 61,200 | 60,000 | 1,200 | Different, review adjustment lines |
S-A needs no action. S-B is a timing item, so diarise a re-check. For S-C, filter the settlement lines for adjustments and compare them with your agreement. If the ₹1,200 is still unexplained, raise a query with the evidence attached.
Questions and answers
Does the Amazon settlement total always equal the bank credit?
Often it should, but not always. Timing, reserves, adjustments posted separately, or a combined payout can make them differ. Treat a mismatch as a flag to investigate using line-level detail, rather than as proof of an error.
Which Excel formula is best for matching settlements to bank credits?
XLOOKUP is the simplest if you have it; INDEX with MATCH works in older versions. Use SUMIFS to total settlement lines by settlement ID first. Add a helper column when two payouts share the same amount.
How often should I reconcile Amazon settlements?
Monthly is the minimum for closing books, but a weekly check on new settlements catches missing payouts and unexplained deductions sooner. Query windows can be limited, so check your seller agreement for the time allowed to raise disputes.
Can I use this method for Flipkart too?
Yes. Flipkart's Seller Hub payment reports use different columns and cycles, so map them into a common layout first. Then match settlement totals to bank credits and review deductions against your Flipkart terms.