Reconcile UPI Payments with Bank Statement

Reconciling UPI payments with your business bank statement ensures every digital transaction matches your records, catching missing payments, duplicate charges, or bank errors. This guide walks you through a simple, repeatable process using your UPI settlement report and bank statement.

What You’ll Need

• Your UPI payment gateway or app’s settlement report (CSV/Excel export) – usually downloadable from your merchant dashboard.
• Your business bank statement covering the same period (PDF/CSV).
• A spreadsheet program (Excel, Google Sheets) or accounting software.
• Access to your UPI transaction IDs (UTR or reference numbers).

Step-by-Step

1. Set the reconciliation period. Choose a clear date range (e.g., one month) that matches both your UPI report and bank statement. Ensure both cover the same start and end dates.
2. Download both reports. Export your UPI settlement report from the payment gateway (usually under ‘Reports’ or ‘Settlements’). Also download your bank statement for the same period. Save them as CSV or Excel files.
3. Clean the data. Open both files in a spreadsheet. Remove any header rows, notes, or unrelated transactions. Ensure columns align: date, amount, transaction ID/UTR, and status.
4. Create a combined sheet. In a new spreadsheet, copy the UPI transactions into one sheet and the bank transactions into another. Add a column in each called ‘Matched’ and leave it blank.
5. Match by amount and date. Sort both sheets by amount and then by date. Look for exact matches – same amount, same date (or within a day for bank processing delays). Mark both rows as ‘Matched’ (e.g., with ‘Y’).
6. Use a VLOOKUP for efficiency. If you have hundreds of transactions, use a formula to automate matching. In the UPI sheet, add a column: =VLOOKUP(transaction_id, bank_sheet_range, column_index, FALSE). If it returns a value, it’s matched; if #N/A, it’s unmatched.
7. Investigate unmatched UPI transactions. For each UPI transaction not matched, check: Is the bank settlement delayed? Is it a refund or chargeback? Did the payment fail? Mark these as ‘Pending’ or ‘Exception’.
8. Investigate unmatched bank transactions. For bank entries not matched, verify if they are UPI payments from a different gateway, or if they are fees, chargebacks, or reversals. Add notes.
9. Adjust for fees and settlement timing. UPI settlements often arrive as a lump sum (net of fees) a day later. If your bank shows a single credit for multiple UPI transactions, match the total against the sum of those transactions.
10. Create a reconciliation summary. At the bottom of your sheet, tally: total UPI transactions, total bank credits, matched amount, unmatched UPI amount, and unmatched bank amount. The difference should be zero after adjustments.
11. Resolve discrepancies. Contact your bank or payment gateway for any unmatched items. Provide transaction IDs and amounts. Keep a log of resolutions.
12. Document and file. Save the reconciled spreadsheet with a clear name (e.g., ‘UPI_Recon_Mar2025’). Store it with your financial records for audits.

Common Mistakes

• Matching by exact date only. UPI settlements can be credited a day later, especially after weekends. Use a 1-2 day window.
• Ignoring gateway fees. The bank credit might be net of fees, so the amount won’t match the gross UPI transaction. Always compare net amounts.
• Mixing refunds with sales. Refunds appear as debits or negative amounts. Treat them separately.
• Using only transaction IDs. Some bank statements don’t show the UPI reference number. You may need to match by amount and date instead.
• Forgetting pending transactions. UPI transactions at the end of the month may settle in the next month. Exclude them or adjust the period.

Tips

• Use a dedicated reconciliation tool like accounting software (QuickBooks, Zoho Books) that can auto-import bank feeds and match transactions.
• Reconcile weekly rather than monthly to catch issues early and reduce workload.
• Keep a master transaction log with a unique ID for every UPI payment, even if it’s just the gateway reference number.
• Standardize date formats across both files to avoid mismatches.
• Create a template with pre-set formulas so you only need to paste new data each period.

Troubleshooting

• If the bank statement shows a credit that matches no UPI transaction: Check if it’s a reversal of a previous chargeback, or a settlement from another payment method (like card). Verify the UTR number with the bank.
• If a UPI transaction is marked ‘successful’ but no bank credit appears: Contact your payment gateway support. It could be a settlement failure or a technical glitch. Provide the transaction ID and timestamp.
• If the total difference is not zero: Re-check for bank fees (e.g., monthly charges) that you didn’t account for. Also, check for pending settlements that crossed the period boundary.
• If you can’t download a report: Some gateways only allow exports for the last 90 days. For older periods, manually copy from the transaction history.
• If the bank statement is in PDF: Use a PDF to Excel converter, or manually input the data if the volume is small.

Frequently Asked Questions

Why do UPI payments sometimes appear in the bank statement a day later?

UPI settlements are typically batched and processed by the bank on the next business day. The payment is successful instantly, but the money reaches your account after the bank’s settlement cycle.

What is a UTR number and why is it important?

UTR (Unique Transaction Reference) is a 12-digit number assigned to each UPI transaction. It’s used to trace and match payments between the gateway and bank records.

How do I handle partial settlements?

Some gateways settle a portion of your total transactions, deducting fees. In that case, match the bank credit against the sum of multiple UPI transactions. The difference should equal the fees.

What if I can’t find a transaction in either report?

First, check if the payment was actually completed (the customer may have cancelled). Then, search with different date ranges and amounts. If still missing, contact your gateway for a full transaction log.

Do I need to reconcile every single UPI payment?

Yes, for accurate accounting and to detect fraud or errors. Even small discrepancies can add up over time.

Can I automate the reconciliation process?

Yes, using accounting software or custom scripts that import both files and match by transaction ID. This is especially useful for high-volume businesses.

Related

• Private-First Research Tools: Best Alternatives to AI Search
• Meta AI: What It Is and How Meta’s Chatbot Works
• Best RTS Games: Top Picks for Every Kind of Player