📑 Table of Contents
What is Payment Gateway Reconciliation?
Payment gateway reconciliation is the accounting process of verifying that the payments processed through your payment gateway match the funds actually deposited into your merchant bank account. It ensures that every transaction is accounted for and that the correct amounts have been settled after deducting fees and handling chargebacks.
The Three-Way Match
Complete reconciliation involves matching data from three sources:
- Order/Sales Records: Transactions from your e-commerce platform, POS system, or internal database
- Payment Gateway Reports: Settlement reports from Razorpay, Stripe, PayU, etc.
- Bank Statements: Actual credits received in your merchant account
Why Reconciliation is Critical
Financial Accuracy
- Revenue Protection: Identify and recover missing settlements worth lakhs annually
- Fee Verification: Catch overcharges in MDR and gateway fees (0.1-0.5% overcharges are common)
- Chargeback Tracking: Monitor chargeback deductions and dispute timelines
- Tax Compliance: Ensure accurate GST calculations on transaction fees
Operational Benefits
- Cash Flow Management: Know exactly when to expect settlements
- Audit Readiness: Maintain complete transaction audit trails
- Dispute Resolution: Quickly resolve payment disputes with documented proof
- Gateway Performance: Compare settlement accuracy across multiple gateways
Regulatory Compliance
- SOX Compliance: Required for publicly traded companies
- PCI-DSS: Financial reconciliation is part of compliance requirements
- RBI Guidelines: Merchant payment reconciliation standards
- GST Compliance: Accurate input tax credit claims
Step-by-Step Reconciliation Process
Step 1: Gather Data Sources
Collect all necessary reports and statements for the reconciliation period (typically daily, weekly, or monthly).
- Payment gateway settlement reports (CSV/Excel format)
- Bank account statements showing credit entries
- Internal sales/order reports with transaction details
- Previous reconciliation reports for reference
Step 2: Standardize Data Format
Convert all data sources to a consistent format for easy comparison.
- Date Format: Use YYYY-MM-DD format across all sources
- Amount Format: Remove currency symbols, standardize decimals
- Transaction ID: Ensure consistent ID format across systems
- Status Codes: Map different status terminologies to standard codes
Step 3: Primary Matching
Match transactions using primary identifiers.
- Transaction ID Match: Match gateway transaction IDs with order IDs
- Amount Match: Verify gross transaction amounts
- Date Match: Confirm transaction dates align (accounting for settlement cycles)
- Customer Match: Validate customer identifiers when available
Step 4: Settlement Amount Verification
Verify that net settlement amounts are correct after fee deductions.
- Calculate Expected Net: Transaction Amount - Gateway Fees - GST
- Compare with Actual: Match with bank credit amount
- Tolerance Check: Flag differences beyond acceptable threshold (e.g., ±₹1)
- Fee Validation: Verify MDR percentage matches agreement
Step 5: Exception Identification
Flag all unmatched or problematic transactions.
- Missing in Gateway: Transactions in orders but not in gateway report
- Missing in Bank: Gateway shows settled but no bank credit
- Amount Mismatch: Differences in expected vs actual settlement
- Timing Differences: Settlements outside expected cycle
Step 6: Exception Resolution
Investigate and resolve each flagged exception.
- Gateway Inquiry: Contact gateway support for missing settlements
- Batch Review: Check if transaction was in different batch
- Chargeback Check: Verify if amount was deducted due to chargeback
- Fee Correction: Request refund for incorrect fee deductions
Step 7: Documentation & Reporting
Document findings and create reconciliation report.
- Match Summary: Total matched vs unmatched transactions
- Exception Log: Detailed list of all discrepancies
- Resolution Status: Track status of each exception
- Financial Impact: Calculate total value of discrepancies
Data Matching Techniques
Exact Matching
Direct comparison of transaction identifiers and amounts.
| Field | Matching Method | Success Rate | Best For |
|---|---|---|---|
| Transaction ID | Exact string match | 95-98% | Primary matching key |
| Order ID | Exact alphanumeric match | 90-95% | Secondary identifier |
| Amount | Exact value match (2 decimals) | 85-90% | Verification field |
| Date | Date range match (T+0 to T+3) | 98-100% | Time window validation |
Fuzzy Matching
Used when exact matches fail due to data inconsistencies.
- Amount Range: Match amounts within ±₹5 tolerance
- Date Range: Expand settlement window to T+5 for delayed settlements
- Customer Name: Use Levenshtein distance for similar names
- Partial ID Match: Match last 8 digits of transaction ID
Batch Matching
Group matching for efficiency with large volumes.
- Date-Based Batches: Match all transactions from same day
- Amount Totals: Match total settlement amount for batch
- Gateway Batches: Use gateway's batch ID for group matching
- Reconciliation Window: Process in weekly or monthly batches
Exception Handling Workflow
Exception Categories
1. Missing Transactions (Gateway Side)
Symptom: Transaction shows in orders but not in gateway settlement report
Possible Causes:
- Payment failed before authorization
- Customer abandoned payment at gateway
- Transaction pending and not yet settled
- Gateway system error during processing
Resolution Steps:
- Check transaction status in gateway dashboard
- Verify payment wasn't marked as failed or cancelled
- Contact gateway support with transaction ID
- Request manual settlement if payment was successful
2. Missing Settlements (Bank Side)
Symptom: Gateway report shows settled but no bank credit
Possible Causes:
- Settlement outside reconciliation date range
- Bank holiday delaying credit
- Held for additional verification
- Incorrect bank account details
Resolution Steps:
- Extend search window to T+5 or T+7
- Check for combined batch settlements
- Verify bank account number with gateway
- Request settlement proof from gateway
3. Amount Discrepancies
Symptom: Settled amount differs from expected amount
Possible Causes:
- Incorrect MDR calculation
- Hidden fees not disclosed upfront
- GST calculation errors
- Chargeback deduction
Resolution Steps:
- Calculate expected net: (Amount × (1 - MDR%)) - GST
- Compare with actual settlement
- Request detailed fee breakdown from gateway
- Raise dispute for overcharges
4. Duplicate Entries
Symptom: Same transaction appears multiple times
Possible Causes:
- Customer clicked pay button multiple times
- System error created duplicate records
- Refund processed as separate transaction
- Data export included test transactions
Resolution Steps:
- Check transaction timestamps
- Verify customer actually paid multiple times
- Process refund for genuine duplicates
- Clean up system records
Exception Resolution Checklist
- Document every exception with transaction details
- Set severity level (High/Medium/Low) based on amount
- Assign owner responsible for resolution
- Set resolution deadline (24-48 hours for high severity)
- Track communication with gateway support
- Update reconciliation report after resolution
- Create prevention plan for recurring issues
Manual vs Automated Reconciliation
| Aspect | Manual Reconciliation | Automated Reconciliation |
|---|---|---|
| Time Required | 40-60 hours/month for 10K transactions | 2-4 hours/month (setup + review) |
| Accuracy Rate | 90-95% (human errors common) | 99.5-99.8% (algorithm-based) |
| Cost | ₹50K-100K/month (labor cost) | ₹5K-50K/month (software) |
| Scalability | Requires more staff as volume grows | Handles 10K to 1M+ transactions |
| Real-Time | End-of-day or weekly reconciliation | Real-time or hourly updates |
| Exception Detection | Only catches obvious discrepancies | Identifies subtle patterns and anomalies |
| Reporting | Manual Excel reports, time-consuming | Automated dashboards, instant insights |
| Best For | Small businesses (<1000 transactions/month) | Growing businesses (1000+ transactions) |
Best Reconciliation Tools & Software
Dedicated Reconciliation Platforms
1. Settlement Analyzer (Our Tool)
- Best For: E-commerce, SaaS, multi-gateway businesses
- Price: Free for basic, ₹999/month for advanced
- Features: Real-time matching, 15+ gateway support, automated alerts
- Accuracy: 99.8% matching rate
2. Razorpay Thirdwatch (for Razorpay users)
- Best For: Businesses using only Razorpay
- Price: ₹15,000/month
- Features: Razorpay-native integration, fraud detection
- Limitation: Single gateway only
3. HighRadius
- Best For: Enterprise businesses (₹100Cr+ revenue)
- Price: Custom pricing (₹5L-20L annually)
- Features: AI-powered matching, complete AR automation
- Implementation: 3-6 months
4. BlackLine
- Best For: Large corporations, multi-country operations
- Price: Custom (₹10L+ annually)
- Features: Full accounting close automation, audit trail
- Complexity: High, requires dedicated team
Excel-Based Solutions (For Small Businesses)
DIY Excel Reconciliation Template
- Create three sheets: Orders, Gateway, Bank
- Use VLOOKUP to match transaction IDs
- Use SUMIF to calculate batch totals
- Conditional formatting for mismatches
- Pivot tables for exception analysis
- Macro for automated import (if comfortable with VBA)
Automate Your Reconciliation Today
Stop spending 40+ hours monthly on manual reconciliation. Try our free settlement analyzer.
Start Free Reconciliation →Reconciliation Best Practices
Frequency Recommendations
- Daily: For businesses processing ₹5L+ daily or high-value transactions
- Weekly: For moderate volume (₹10-50L weekly)
- Monthly: Only for very small businesses (<₹10L monthly)
- Real-Time: For financial institutions or high-risk industries
Data Management
- Backup All Reports: Keep gateway reports for 7 years (tax requirement)
- Version Control: Track changes to reconciliation files
- Access Control: Limit who can modify reconciliation data
- Audit Trail: Log all manual adjustments and reasons
Process Optimization
- Standardize File Names: Use consistent naming (e.g., Gateway_YYYYMMDD.csv)
- Automate Downloads: Use gateway APIs to fetch reports automatically
- Set Tolerance Thresholds: Don't chase ₹1 discrepancies on ₹1L transactions
- Priority-Based Resolution: Focus on high-value exceptions first
Team & Responsibilities
- Segregation of Duties: Different people for matching vs exception resolution
- Clear Escalation Path: Define who handles what severity level
- Regular Training: Keep team updated on gateway changes
- Performance Metrics: Track match rate, resolution time, recovery amount
Frequently Asked Questions
Recommended frequency: Daily for businesses processing ₹5L+ daily, weekly for ₹10-50L weekly, and monthly only for very small volumes. Daily reconciliation helps catch issues quickly while they're easier to resolve. The longer you wait, the harder it becomes to track down missing settlements or resolve disputes with gateways.
Industry benchmarks: Manual reconciliation achieves 90-95% match rate, while automated systems reach 99.5-99.8%. Anything below 90% indicates serious issues with your process or data quality. Unmatched transactions should be investigated and resolved within 48-72 hours.
Use the transaction date as your primary reconciliation key, not the settlement date. Create a reconciliation window based on your settlement cycle (T+2 means check bank statements 2 days after transaction date). Group transactions by transaction date, then match with settlements that arrive within the expected window. This accounts for weekend and holiday delays.
Step 1: Verify the transaction was actually marked as "settled" in gateway dashboard. Step 2: Check if settlement date was outside your search period (extend to T+5). Step 3: Look for combined batch settlements where multiple days are grouped. Step 4: If still missing, contact gateway support with transaction ID and request settlement proof. Step 5: File a formal complaint if not resolved within 48 hours.
Yes, but you need to reconcile each gateway separately first, then consolidate. Each gateway has different settlement cycles, fee structures, and reporting formats. Use a unified reconciliation tool that supports multiple gateways or create separate Excel sheets for each gateway, then merge the final results. This approach helps identify which gateway has more discrepancies.
Formula: Net Settlement = (Transaction Amount) - (MDR Fee) - (GST on MDR). Example: ₹10,000 transaction with 2% MDR and 18% GST: MDR = ₹200, GST = ₹36, Net = ₹9,764. Always verify the MDR percentage matches your agreement. Some gateways charge different MDR for different payment methods (cards vs UPI vs wallets).
Yes, absolutely. Refunds should match with your refund reports and appear as debits from future settlements. Chargebacks should be tracked separately as they have their own lifecycle and may take weeks to fully process. Create separate reconciliation sheets for: 1) Regular settlements, 2) Refunds, 3) Chargebacks, 4) Fees and adjustments. This prevents confusion and helps maintain clean records.