You've built something worth promoting. At 50+ active affiliates, your referral program generates real revenue—and real complexity. Commissions accrue in your CRM or booking tool. Payouts live in Stripe or a payment processor. Your spreadsheet tries to be the truth table. It fails quietly, and 8–12% of what you owe simply vanishes into timing gaps, FX rounding, duplicate detection failures, and chargeback clawbacks that never make it back into the ledger. The leak isn't fraud. It's structural. Spreadsheets don't timestamp; Stripe doesn't know which commission triggered which payout; tax withholding gets calculated on the wrong window; duplicate affiliate records stay invisible until someone notices two people got paid for the same deal. By then, you've already wired the money twice. Here's how to find the drift and why native payout logic stops it cold. The nine reconciliation checkpoints Every month, open your spreadsheet, your CRM, and your Stripe or payment processor dashboard. Walk through these nine gates. At each one, calculate the difference between what you expect to pay and what you've actually recorded. That difference is your leak. 1. Commission accrual vs. payout date A deal closes on March 5. The affiliate's commission accrues on March 5. But you don't pay until the 15th—sometimes the 20th. In that window, which date does your spreadsheet use? Which date does your payment processor record? If they don't match, a commission can appear in two months' reports, or vanish from both. Audit step: Pull your last three months of payouts. For each one, find the deal close date, the commission accrual date, and the payout date. They should be in a strict sequence. If any payout was made before the deal closed, or if the accrual date is later than the payout date, flag it. 2. Foreign exchange rounding You have affiliates in three countries. One deal is ₹100,000 INR. The commission is 10%, so ₹10,000. Your affiliate in Singapore wants it in SGD. Your affiliate in the US wants it in USD. Stripe's conversion rate on March 5 was different from the rate on March 15. Your spreadsheet rounded to two decimals; Stripe rounded to four. The difference per transaction is cents. Across 50+ affiliates over a year, it's ₹5,000–₹8,000 of silent leakage. Audit step: For every multi-currency payout in your records, recalculate the FX conversion using the date your payment processor settled it, not the date the deal closed. Compare that to what's in your spreadsheet. Sum the variance. If it's more than ₹500 monthly, you have a rounding policy problem. 3. Duplicate affiliate records Someone signs up as "Alice Smith." Two weeks later, another person signs up as "alice.smith@company.com". Your CRM treats them as different affiliates. A deal attributed to both gets two 10% commissions paid out. Now you've overpaid by 10% on that deal—and you won't notice until months later when someone asks why they've been paid twice. Audit step: Export your affiliate list. Sort by email domain, then by last name. Look for near-duplicates: same person, slightly different name formatting. Cross-reference with your payment processor's recipient list. If a person has more than one active commission account, they're a duplicate candidate. 4. Chargeback clawback timing An affiliate drove a sale. You paid them 10%. The customer charged back 60 days later. Stripe reversed the payout. But your spreadsheet doesn't know that. Your affiliate still shows as having been paid. The next month, you don't deduct the clawback; you treat it as a separate, administrative line item—if you remember to record it at all. Audit step: Export your Stripe disputes and chargebacks for the last six months. For each one, find the associated affiliate payout. Check: did you record the reversal in your commission ledger, or did you only record it in Stripe? They should match. If there's a gap, you owe the affiliate a clawback that your spreadsheet never captured. 5. Partial vs. full commission eligibility Your affiliate agreement says: full commission on new customers, 50% on upgrades, zero on downgrades. A customer who signed up via Alice upgraded, then downgraded. Your spreadsheet flagged it as eligible for full commission. Your CRM's deal pipeline marked it as an upgrade. Stripe paid based on the original deal value. You've now overpaid by 50% on that transaction. Audit step: For every payout you made last month, verify the deal type (new, upgrade, downgrade, renewal) and cross-check it against your affiliate agreement. If the commission rate in your spreadsheet doesn't match your agreement, you've got a policy breach—either you owe more, or you need to claw back. 6. Tax withholding variance You're required to withhold 10% tax on affiliate payouts in one country, 15% in another, 0% for US 1099 contractors. Your spreadsheet has a column for "tax withheld." But it's using a flat 10% on everyone. Your accountant applies the right rates at year-end. Now there's a ₹50,000 gap be