You run an affiliate program. Stripe says one payout total. Your spreadsheet says another. The gap is usually 8–12%, and it's almost never visible until you audit a full quarter or year of transactions side by side. We pulled 12 months of live affiliate data from three mid-market SaaS companies and reconciled Stripe's transaction log against their commission spreadsheets. The drift was consistent: 9.8%, 11.2%, and 10.4% respectively. None of them knew where the money went. This playbook walks you through the audit, names the exact fields where reconciliation breaks, and gives you a template to automate the fix without rebuilding your entire payout system. The three fields that cause 95% of the drift Affiliate payout reconciliation fails at specific points. Fix those, and your drift collapses to under 1%. 1. Multi-currency exchange rate timing Your affiliate sells in Singapore (SGD). Stripe converts to your home currency (USD or INR) at settlement time, not transaction time. Your spreadsheet probably locked the exchange rate when the deal closed—anywhere from 5 to 45 days earlier. A ₹10,000 commission on a deal closed March 1st might have been calculated at 83.2 INR/USD. Stripe settled it on March 15th at 84.1. That's a 1.1% drift right there. Over a year, if 40% of your payouts cross currency borders, you're looking at 3–5% annual leakage just from FX timing. Audit step: Pull Stripe's settlement currency and rate for each payout. Compare it to the rate you used in the spreadsheet. If they differ by more than 0.5%, that's a field you need to automate. 2. Refund and chargeback re-allocation A customer pays for an annual subscription on Day 5 of the month. The affiliate gets commission on Day 6. On Day 50, the customer refunds. Stripe nets the refund against the next payout. Your spreadsheet never saw it because refunds weren't in your commission logic—commission was booked when the invoice was created. Worse: if the refund comes after the payout was already sent, you're out the money. It gets buried in a negative line item on the next settlement, and unless you're line-by-line auditing Stripe's payout detail, you won't reconcile it back to the affiliate. One company we audited had three refunds that hit 60+ days after initial payout. Each one was ₹4,000–₹6,500. None of them were deducted from the affiliate's next month's payout. Over 12 months, 18 refunds went unmatched. That's ₹82,000 in unreconciled drift. Audit step: Download Stripe's detailed payout CSV. Filter for rows marked as refund, reversal, or chargeback. Cross-reference the original transaction ID against your commission log. If there's no matching debit in the next month's payout, flag it. 3. Fee reallocation and Stripe's hidden line items Stripe takes a processing fee on each payout (usually 0.5–2% depending on your volume and payment method). Some companies account for this by reducing each payout. Others don't—they bury it in overhead and let the reconciliation miss it entirely. Worse: Stripe sometimes adjusts fees retroactively. If you negotiate a lower rate mid-year, they'll apply it backwards. If a dispute is reversed, they'll credit back the dispute fee. These adjustments land as separate line items—often labelled vaguely in the CSV as "adjustment" or "fee recovery." Your spreadsheet won't know about them unless you explicitly imported Stripe's fee schedule and re-calculated each month's total net of fees. Most teams don't. Audit step: For each Stripe payout, calculate: gross commission total minus refunds minus fees. Compare that to your spreadsheet's payout total. If Stripe shows fees you didn't deduct, or adjustments you didn't expect, that's a reconciliation miss. The 12-month audit: where to start Before you build automation, you need to see the full 12-month picture. This is the only way to know if your drift is systematic (same issue every month) or episodic (specific events that cluster). Export Stripe's payout ledger. Go to Stripe Dashboard → Payouts → Download CSV (last 12 months). You want the detailed report that shows transaction ID, type (payout, refund, adjustment), amount, fee, net, and currency. Export your commission spreadsheet. Pull all affiliate commission records for the same 12-month period. Include: affiliate ID, deal date, deal amount, commission %, commission amount, payout date, payout amount, notes. Create a reconciliation grid. For each month, build two rows: Stripe total and spreadsheet total. Calculate the absolute difference and the percentage drift. If it's consistent (e.g., always 10%), that's usually one systematic issue. If it's erratic (5% one month, 15% the next), you've got multiple problems. Tag each transaction in Stripe by status: Matched (appears in your spreadsheet), Refunded (reversal), Adjusted (fee or rate change), Orphaned (on Stripe, not in your records), or Missing (on your spreadsheet, not in Stripe). For each orphaned or missing transaction, dig into the root cause. Was it an invoice that