Affiliate Payout Reconciliation

Q: How often should I run affiliate reconciliation?

Monthly, on the first business day after month-end close. Run it before you wire any affiliate payouts. If you reconcile monthly, drift stays under 1–2%. If you reconcile quarterly or annually, you'll be chasing ₹1L+ in discrepancies that are harder to fix retroactively.

Q: What if my CRM and payment processor totals don't match exactly?

A variance of 0.5–1% is usually rounding or timing (a sale recorded in the CRM on the last day of the month but not settled by the payment processor until the next day). Anything over 1% needs investigation: look for duplicate transactions, missing sales, or timing misalignment between close date and settlement date.

Q: Do I need to reconcile Stripe reserve separately?

Yes. Stripe holds 5–10% in reserve for 180 days. That money is committed but not paid. Track it separately in your reconciliation so you don't accidentally promise an affiliate 100% of commission when you can only pay 90% immediately. Release the reserve as a separate payout when Stripe unfreezes it.

Q: How do I handle chargebacks in reconciliation?

When a chargeback occurs, retract the commission from the next payout. Do not wait until the month after the chargeback posts. If a sale was in June and a chargeback hits in August, add a 'clawback' line item to your July payout for that amount. Document it so the affiliate knows why their July payout was reduced.

Your affiliate spreadsheet is quietly hemorrhaging ₹2,500 to ₹3,500 every month. Not through fraud. Through drift: tier boundaries that don't reconcile to your CRM, exchange rates that round down, commissions paid twice because someone manually re-entered a sale, or tracked in the wrong month. By year-end, you've lost ₹35,000 to math that looked right at the time. The problem isn't the spreadsheet itself. It's that affiliate payouts live at the intersection of four systems—your sales CRM , your payment processor, your invoicing and accounting layer , and a spreadsheet nobody owns. Data moves between them once a month, or when someone remembers. Reconciliation, if it happens, happens after the check is written. This playbook gives you a template that catches drift before payout. It covers Stripe, Razorpay, and manual verification. It maps the nine places commission math breaks. And it gives you a 30-minute audit procedure that turns a risky spreadsheet into an audit trail. Where affiliate math breaks: the nine drift points Before you can reconcile, you need to know where the errors hide. Tier boundary misalignment: Your CRM records a sale as ₹1,00,000 gross. Your affiliate program defines tier 2 at ₹1,00,500 MRR. The spreadsheet applies tier 1 commission, saving 0.5% against the promised 2%. Repeat across 50 affiliates, you're off by ₹40K/quarter. Exchange rate rounding: A Stripe payout in USD converts to INR. Your spreadsheet uses yesterday's rate; your bank statement uses today's. A 2% variance on a ₹5L payout is ₹10K. Multiply by 12 months. Duplicate tracking: One sale appears in both your CRM and your payment processor's webhook log. Someone manually reconciles and double-counts. The affiliate gets paid twice; you discover it in the next audit. Month-end timing slippage: A sale closes on 30th June but doesn't sync to your affiliate spreadsheet until 2nd July. It lands in the July reconciliation. Your accounting team puts it in June. Two different commission periods now track it. Retraction and chargeback lag: A customer disputes an invoice on 15th August. The refund posts 45 days later. The affiliate was paid their commission in August. You remember to claw it back in October, if at all. Multi-level commission misalignment: Affiliate A refers Affiliate B. Both earn commission on sales through B's link. Your spreadsheet tracks it as single-level; your CRM knows it's two. The math diverges by 3–5%. Tax withholding applied twice: Your payment processor withholds 10% TDS on the Razorpay payout. Your accounting system withholds it again at reconciliation. The affiliate gets paid 80% instead of 90%. Manual entries never reconciled back: Someone adds a ₹50K 'adjustment' to the spreadsheet because a sale 'should have counted.' It never gets tied back to a CRM record or an invoice. By year-end, ₹2L of adjustments exist with no audit trail. Affiliate status changes mid-month: Affiliate goes inactive on the 15th. Do they earn commission on sales through the 15th, or the 30th? If your spreadsheet says one thing and your CRM says another, reconciliation fails. Build your reconciliation template: the three-column anchor The fix is a three-column structure that treats your CRM as the source of truth, your payment processor as the validation layer, and your spreadsheet as the audit trail. Column A: CRM-sourced transactions Pull every sale in the period directly from your CRM . Include: Sale ID (unique CRM identifier) Affiliate ID Sale date (use close date , not create date) Sale amount (use final invoice total, not quoted price) Tier applied (based on YTD total for that affiliate, or rolling 12-month MRR if your program defines it that way) Commission rate (%)—derived from tier, not manually entered Commission amount (formula: sale amount × tier rate) Adjustments (retracted sales, chargebacks) with reason codes Net commission for this sale Do not hand-enter data here. Export from your CRM as CSV. Use a vlookup or INDEX/MATCH to join tier thresholds. Any row that doesn't join cleanly gets flagged for manual review. Column B: Payment processor validation Pull your settlement report from Stripe, Razorpay, or your payment gateway: Transaction ID (from processor) Amount settled (what actually hit the bank) Fees, withholding, and net received Settlement date Corresponding CRM Sale ID (manually mapped or auto-joined if your processor sends a reference) Create a lookup to match this to Column A. Any CRM sale without a processor transaction gets flagged. Any processor transaction without a CRM match gets flagged (it's either a duplicate or a missing sale). Column C: Payout tracker This is what you actually pay out to affiliates, broken by period: Affiliate ID Total commission due (sum of Column A, net of retractions) Tax withholding applied Net payout Payment method (Razorpay, Stripe Connect, bank transfer) Payout date Confirmation (receipt, settlement report line item) The 30-minute monthly reconciliation audit Run this procedu