A ₹35,000 monthly affiliate payout looks clean until you reconcile it against three commission reports, two processor statements, and one spreadsheet built three years ago. By the time you're done, ₹4,200 sits unaccounted for. Not gone—unaccounted for. It exists in rounding errors, currency conversions that drift 0.2%, payment processor fees that appear nowhere in your commission calc, and duplicate detection logic that was never written down. This is not accounting slop. It's the natural result of stitching together spreadsheets, CRM exports, and processor APIs without validation rules. Nine specific reconciliation gaps hide the leak. Close them systematically, and you'll recover margin you didn't know was bleeding out. The nine reconciliation breaks Most affiliate payout leaks are not fraud. They're the arithmetic cost of moving money across systems with no shared source of truth. 1. Rounding in multi-tier commission splits Your commission structure: 5% for referrals, 2% for repeat bookings, 0.5% bonus when they hit ₹50K. One affiliate drives ₹47,632 in referral revenue and ₹8,910 in repeat bookings. Spreadsheet math: Referral commission: 47,632 × 0.05 = ₹2,381.60 Repeat commission: 8,910 × 0.02 = ₹178.20 Subtotal: ₹2,559.80 No bonus (didn't hit ₹50K) Your spreadsheet rounds to ₹2,560 Fine so far. But when you have 47 affiliates, each with 3–4 commission tiers, rounding errors accumulate. Spreadsheets typically round at the line level, not the reconciliation level. After 12 months, ₹40–80 per affiliate drifts into no-man's-land. Fix: Define rounding at the transaction level, not the total. Round down commission on payout; if the difference exceeds ₹0.50, carry it forward and apply only at month-end settlement. Document this rule once, use it everywhere. 2. Currency conversion spreads You have 12 affiliates. Three are in Malaysia (MYR), two in Indonesia (IDR), one in Singapore (SGD), and six in India (INR). Your base currency is INR. Yesterday, you pulled FX rates at 15:42 UTC to convert that day's ₹35K payout. Today, Stripe settled the MYR and IDR portions at 16:18 UTC rates—six basis points lower than yesterday. That spread alone costs ₹21 on a ₹35K batch. Over 12 months, if you're pulling rates on settlement day vs. payout day, you'll eat 8–15 bps of slippage per month. That's ₹280–525 monthly on a ₹35K book. Fix: Lock conversion rates at the moment of commission calc, not settlement. Use a single FX service (OANDA, XE, or your processor's API) and timestamp every conversion. Never reconvert on payout day. 3. Payment processor fees not deducted from commission Stripe charges 1% for payouts to MYR accounts, 2.5% for IDR, 0.75% for SGD. Your spreadsheet doesn't model this. You've been paying ₹2,560 to the Malaysia affiliate gross; Stripe has been taking ₹25.60 out of your bank account to deliver it. After 12 months, you've subsidized processor fees to the tune of ₹307.20 for one affiliate. Across three international affiliates, that's ₹800–1,100 yearly. Fix: Build processor fee schedules into your commission template. For each affiliate, calculate commission gross, then apply the processor fee rate for their payout method/currency. Show gross and net separately. Processor fees come from your margin, not the affiliate's—but you need to know the true cost. 4. Chargebacks and refunds clawing back commission Affiliate A drove a ₹8,000 booking in Week 1. You paid ₹400 commission in Week 2. In Week 3, the customer requested a refund. In Week 4, your processor clawed back the full ₹8,000 plus a ₹150 dispute fee. Your spreadsheet doesn't know about the clawback. You've already paid the ₹400. Now it's a debt owed to you by the affiliate. Most spreadsheets never track this. It sits as a note in an email thread. Over a year, if 2–3% of affiliate-driven revenue gets refunded, you're chasing ₹210–315 in unpaid clawbacks per affiliate per year. Fix: Link your CRM's transaction table to your commission calc via transaction ID, not just amount. When Stripe webhooks a refund, mark that transaction as refunded in your CRM. Your commission sheet recalculates automatically. Chargebacks trigger a separate clawback line that carries forward until the affiliate settles it or you write it off. 5. Duplicate detection and prevention Your data pipeline: Stripe webhook → CRM → commission calc. Sometimes webhooks fire twice. Your CRM deduplicates on webhook ID, but your commission spreadsheet deduplicates on (affiliate_id, amount, date). Two identical ₹500 referrals on the same day for the same affiliate will not be caught by your spreadsheet dedup logic. You find it three months later. You've overpaid ₹500. Fix: Deduplicate on transaction_id, not transaction attributes. Make transaction_id the primary key in both CRM and commission calc. If a webhook arrives twice, the second one updates the same row, not inserts a new one. 6. Commission date vs. payout date vs. settlement date Commission is earned on the booking date. You pay on th