You ran the numbers on your 50-affiliate program last month. Payouts hit ₹67,500. Your accounting team reconciled it against the CRM and Stripe ledger. The gap: ₹8,100—12% unaccounted for. You blamed rounding. You blamed timing. You moved on. That ₹8,100 compounds. Over twelve months, on a program that size, you're hemorrhaging ₹97,200. Larger programs leak ₹150K+. The leak isn't fraud. It's nine separate sync breaks, each small enough to ignore individually, each systematic enough to repeat every payout cycle. This playbook maps those nine checkpoints, shows you exactly where the leak occurs in your workflow, and hands you a reconciliation framework—both manual and automated—to recover your margin. Why 12% vanishes: the five sources of payout drift Before you audit, you need to understand the shape of the problem. Manual affiliate payout tracking fails in five distinct ways: Commission formula mismatch: The CRM calculates commissions one way (e.g., deal amount × rate), your spreadsheet another (with thresholds, tiered rates, or lagged calculation windows). The two never match exactly. Duplicate and reversed transactions: A deal gets recorded twice, then reversed, but the reversal doesn't clear the commission. Your commission log includes the original and the reverse, netting to zero—except the payout already went out on the original. Timing and currency variance: You payout in INR on the 15th. The affiliate earned commissions across four currencies over thirty days. FX rates moved 2–3%. Manual conversion on payout date leaves a ghost difference from what was promised on deal close. Manual adjustments without audit trail: The ops person adds ₹500 'to make it right' after a customer complained. No note. No linked transaction. The next auditor finds ₹500 with no source. Reconciliation delay: You finalize payouts on the 15th. The bank clears them on the 16th. Your accountant reconciles on the 20th. Between day 15 and day 20, new deals close, new commissions accrue, and the window between 'payout locked' and 'payout reconciled' becomes invisible. Checkpoint 1: Validate the commission formula against deal data Open your CRM. Pull the last thirty days of closed deals. For each deal, hand-calculate the commission using your stated formula. Compare the result to what the CRM shows as the commission for that deal. What to look for: Deals with non-standard rates (e.g., enterprise deals at 5% instead of 10%)—these often get logged wrong. Deals that span tier boundaries (e.g., a ₹95K deal counted toward a ₹100K tier threshold). Lagged commissions—deals marked closed on day 1 but commission calculated on day 5 after finance review. Expected variance: 0.5–1.5% from rounding. Anything above 2% signals a formula break. Checkpoint 2: Find and void duplicate commissions Query your affiliate commission log for duplicate deal IDs. A deal ID should appear exactly once per affiliate. If it appears twice, one is usually a manual correction that wasn't reversed on the payout. Audit steps: Export your commission log (from the last 90 days). Sort by deal ID. Highlight any deal that appears more than once for the same affiliate. Check if a reversal entry exists. If yes, net the two amounts. If no, flag it as a duplicate to recover from the next payout. Common culprit: A deal gets re-opened, a new commission is created, the old one isn't cleared. You end up paying two commissions on one sale. Checkpoint 3: Check payout timing against deal lock date The moment a deal is marked 'closed' in your CRM, that affiliate's commission is earned. The moment you execute a payout (let's say the 15th of the month), you're cutting a cheque for all commissions earned up to and including the 15th—but only those deals that were already locked into the system by close-of-business on the 15th. The audit: Pull a payout report dated the 15th. Cross-reference each affiliate's commission total against the CRM's deal log filtered to 'closed by the 15th'. If the payout includes commissions from deals closed on the 16th or 17th, you've over-paid this cycle and under-paid last cycle. Impact: A single week's timing slip across a 50-affiliate program can cost ₹1,200–1,800 in misplaced commission. Checkpoint 4: Reconcile multi-currency FX rates at payout If your affiliates earn in multiple currencies, your payout ledger should show the rate applied to each deal on payout day, not on deal close day. Audit process: Pull a payout dated (e.g.) November 15th. Find all commissions in USD, EUR, GBP, etc. Check the FX rate shown in the payout against the actual INR rate on November 15th (use XE.com historical or your bank's ledger). Recalculate each commission at the actual rate. Compare to what was paid. Typical leak: 0.5–1.2% per currency per cycle. On a ₹50K payout with 30% in foreign currency, you lose ₹150–300 per month. Checkpoint 5: Audit manual adjustments and chargebacks Open your commission adjustment log. Every manual entry should have: A date An amount (positiv