Reconcile UPI Payments with Orders, Refunds & Settlements

This guide walks you through reconciling UPI payments against your orders, refunds, and settlement reports. You’ll learn a step-by-step process to match every transaction, identify discrepancies, and resolve them quickly. Before you start, ensure you have access to your payment gateway dashboard, your order management system, and a spreadsheet tool like Excel or Google Sheets.

What You’ll Need

• Access to your UPI payment gateway dashboard (e.g., Razorpay, Paytm, PhonePe, or your bank’s portal)
• Your order management system or e-commerce admin panel (to export order and refund data)
• A spreadsheet application (Excel, Google Sheets, or similar)
• Your bank statement or settlement report for the period you’re reconciling
• A clear understanding of your business’s order statuses (e.g., paid, pending, refunded, failed)

Step-by-Step

1. Choose a reconciliation period. Decide whether you’re reconciling daily, weekly, or monthly. Most businesses reconcile daily to catch issues early. Pick a specific start and end date and stick to it.
2. Export your orders list. From your order management system, export all orders placed during the period. Include columns like order ID, order date, order amount, payment status, and payment method. Filter to show only UPI orders (if possible).
3. Export your refunds list. Similarly, export all refunds issued during the same period. Include refund ID, order ID, refund amount, refund date, and refund status. Note that refunds may occur after the original order date, so include refunds initiated within your reconciliation period.
4. Download your UPI settlement report. From your payment gateway dashboard, go to the settlements or transactions section. Download the report for the same period. It should include columns like transaction ID, order ID (if passed), UPI reference number, amount, transaction date/time, and settlement status (e.g., settled, pending, failed).
5. Download your bank statement. If your settlement report doesn’t show actual bank credits, download your bank statement for the settlement date(s). Look for UPI settlement entries (often labeled with a settlement ID or batch reference).
6. Set up a reconciliation spreadsheet. Create a new sheet with columns: Order ID, Order Amount, Payment Status, Refund Amount, Refund Status, Settlement Amount, Settlement Status, and Difference. You’ll populate this as you match transactions.
7. Match orders to settlements. For each order, find the corresponding transaction in the settlement report. Use the order ID or transaction ID if available; otherwise, match by amount and date. Mark each matched pair as “OK” if the amounts match.
8. Match refunds to settlements. Refunds may appear as negative transactions in your settlement report or as separate deduction entries. Match each refund to its corresponding deduction. Ensure the refund amount matches the deduction amount.
9. Identify unmatched transactions. After matching, you’ll have three categories: orders with no settlement (unsettled), settlements with no order (unknown credits), and refunds with no deduction (unprocessed refunds). Flag these for investigation.
10. Investigate unmatched items. For unsettled orders, check if the payment failed or is still pending. For unknown credits, look up the UPI reference number in your gateway dashboard to find the associated order. For unprocessed refunds, verify if the refund was actually initiated and if it’s still in process.
11. Adjust for settlement fees and charges. Your settlement report may show deductions for payment gateway fees, GST, or other charges. These will make the settlement amount slightly lower than the order amount. Account for these by adding a “Fees” column and verifying the math: order amount – fees = settlement amount (for successful orders).
12. Reconcile the totals. Sum the total order amounts, total refunds, total settlements (credits), and total deductions (debits). The formula should be: Total orders – Total refunds = Net sales. Net sales should equal Total settlements – Total gateway fees + any adjustments. If these don’t match, you have a discrepancy to resolve.
13. Document and resolve discrepancies. For each discrepancy, note the reason (e.g., pending refund, gateway fee mismatch, duplicate entry). If it’s a genuine error (e.g., wrong amount settled), contact your payment gateway support with the transaction IDs. Keep a log of all actions taken.
14. Mark the reconciliation as complete. Once all items are matched and discrepancies resolved (or documented as pending), save your spreadsheet with a clear name like “UPI_Recon_2025-01-01_to_2025-01-31.xlsx”.

Common Mistakes

• Mixing time zones. Your gateway may record transactions in IST, while your order system uses UTC. Always convert to a single time zone before matching.
• Ignoring gateway fees. Forgetting to subtract fees from settlements will cause false discrepancies. Always include the fee column.
• Counting pending refunds as settled. A refund that is “processing” is not yet settled. Only match refunds that have a settlement status of “settled” or “completed”.
• Using order date vs. settlement date. An order placed on Jan 31 may settle on Feb 1. If you’re reconciling Jan, you’ll see it as an unsettled order—this is normal. Use settlement date for matching, not order date.
• Duplicate entries. Sometimes the same transaction appears twice in the settlement report (e.g., one for authorization and one for capture). Check for duplicates before matching.

Tips

• Automate with formulas. Use Excel’s VLOOKUP or XLOOKUP to match order IDs automatically. This saves time and reduces manual errors.
• Keep a running log. Maintain a separate sheet for “Adjustments” where you note any manual corrections, like gateway fee waivers or chargebacks.
• Reconcile frequently. Daily reconciliation is easier than monthly because there are fewer transactions to match and issues are still fresh.
• Use UPI reference numbers. When matching, use the UPI reference number (UTR) from both the gateway and bank statement—it’s the most reliable identifier.
• Set up alerts. If your gateway supports it, enable webhooks for payment failures and refunds to get real-time notifications, making reconciliation easier later.

Troubleshooting

• If you find an order with no settlement: Check the payment status in your gateway. If it’s “failed,” the customer wasn’t charged. If it’s “successful” but not settled, wait 1-2 days—settlements often lag. If it’s been longer, contact gateway support.
• If you find a settlement with no order: Search the UPI reference number in your gateway’s transaction log. It might be a test transaction, a duplicate, or an order from a different channel. If it’s a genuine payment, create a manual order in your system and link it.
• If the totals don’t match: Re-check your fee calculations. Also, look for “chargebacks” or “reversals” that might appear as negative settlements. If you still can’t find the difference, export both reports again—there might have been a data update.
• If a refund is not showing in settlement: Verify the refund status in your gateway. If it’s “pending,” it will settle in the next cycle. If it’s “failed,” you need to re-initiate the refund.
• If you see a “partial settlement” (e.g., order amount ₹500 but settled ₹480): This is usually due to gateway fees. Check the fee breakdown in your settlement report. If the deduction is unexplained, contact support.

Frequently Asked Questions

How long does a UPI settlement take?

Typically, UPI settlements happen within 1-2 business days, but it can vary by bank and gateway. Your settlement report will show the actual settlement date.

What is a UTR number and why is it important?

UTR stands for Unique Transaction Reference. It’s a 12-digit number assigned to each UPI transaction. It’s the most accurate way to match a payment between your gateway and bank statement.

Can I reconcile UPI payments automatically?

Yes, many payment gateways offer reconciliation APIs or built-in tools. You can also use accounting software like Zoho Books or QuickBooks that integrate with gateways to auto-match transactions.

What if a customer claims they paid but I don’t see the settlement?

Ask the customer for their UPI transaction ID or screenshot. Search that ID in your gateway dashboard. If it’s not there, the payment may have failed or been sent to a different account. If it’s there but unsettled, wait for settlement.

How do I handle gateway fees in reconciliation?

Subtract the gateway fee from the order amount to get the net settlement. For refunds, the fee is usually reversed, so add it back. Your settlement report will show these details.

What should I do if the settlement amount is less than the order amount?

First, check if gateway fees were deducted. If not, look for any “adjustments” like chargebacks or reversals. If you can’t explain the difference, contact your payment gateway support with the transaction details.

Related

• How to Merge Two iTunes Libraries
• Document an Endangered Language with Community Consent
• Razer Opus THX Certified Noise Canceling Headphones