Commission spreadsheets hemorrhage money in silence. You capture the order, calculate the rate, queue the payout—and 12 months later, you're off by 8 to 12 percent. Nobody knows where it went. It's not fraud; it's drift. And drift is worse, because it's systematic, invisible, and compounding. The leak doesn't happen in one place. It happens in nine. Why 12% annual drift is real, and your spreadsheet hides it Most affiliate programs run on a mix of manual entry, email confirmations, and batch settlement. Each handoff is a chance for a number to slip: Order capture: A sale lands in Shopify, WooCommerce, or your custom API. The affiliate ID matches—or doesn't. The amount rounds. The currency is unclear. Affiliate attribution: Did the link fire? Did the tracking pixel load? Did the customer clear cookies before checkout? You're guessing at 5–8% of orders. Commission rate logic: Different tiers apply to different products. A promo code overrides the base rate. The new rate isn't live yet on partner portals. Someone pays the old rate. Multi-currency settlement: An affiliate in Singapore drove a sale to a US store. You calculate USD, then convert to SGD. The conversion rate moved between order and payout. Rounding stacks. Tax withholding: US affiliates owe federal withholding. Singapore and Malaysia have contractor tax. You withhold on some payouts, forget on others, or batch wrongly. Refund reconciliation: A customer returns the order. The refund hits your bank account three days later. The affiliate payout went out five days ago. You track the reversal in a separate sheet. Late-batch posting: Payment processing APIs queue payouts. Some clear in 24 hours; some wait. A payout sent on Friday doesn't settle until Wednesday. Your weekly reconciliation thinks it's still pending. Rounding and currency pairs: You pay 1,237.49 SGD. That's 920.18 USD at today's rate. Tomorrow it's 921.05. Which one do you reconcile against? Manual adjustments: You spot an error, add a correction row to the spreadsheet, send a one-off payment—and forget to log it in the reconciliation export. Most leaks aren't malice; they're asynchrony. A spreadsheet snapshot taken on Tuesday doesn't match one taken on Friday, because the real-world data never stopped moving. Mapping the nine handoff points: where the audit breaks To plug the leak, you need a single source of truth that stays true as data moves through each stage. Here's how to audit each one: 1. Order capture and affiliate attribution Every order must link to an affiliate ID before the payout queue forms. Set up a log that captures: Order ID and timestamp Affiliate ID (or "none" if no match) Sale amount in original currency Tracking method (link UTM, pixel, coupon code) Confidence score (100% = verified link, 50% = pixel delayed, 0% = manual lookup) Export this log weekly and count the orphaned orders—sales with no affiliate attached. If that number is above 5%, your tracking is broken. Fix the pixel, extend the cookie window, or add a fallback coupon code. 2. Commission rate application Create a matrix of rates by product, region, and affiliate tier. Version it with an effective date. Before you calculate commission on any order, log: Which rate row you used (include the version date) The amount before and after commission Any override reason (promo, dispute, adjustment) Every month, recalculate all commissions from the past 90 days using the current rate matrix. If the recalculated total differs from what you actually paid by more than 0.5%, you used the wrong rates. Find the drift source and fix it forward. 3. Multi-currency conversion Convert once, at order capture. Lock the exchange rate to the date the order was placed, not the date you pay. Log it: Original currency and amount Target currency and amount Exchange rate used (and source: Stripe, XE, your bank) Conversion date Do not reconvert at settlement. A payout in SGD to a Singapore affiliate should be calculated on day one, locked, and paid on day 30. If you reconvert, you're gambling that rates move in your favor—and gambling always costs. 4. Tax withholding and compliance Build a tax table by affiliate country. US affiliates need 1099 withholding if you pay over $600/year. Malaysian contractors may need withholding too, depending on structure. Singapore affiliates rarely do. Log every withholding as a separate GL line: Gross commission Tax withholding (with statute/code) Net payout If you're unsure about a region, consult an accountant once, then bake the rule into code. Do not guess at scale. 5. Refund reversal tracking When a customer refunds, you must reverse the affiliate commission. But you cannot reverse a payout that's already been made and cleared. Instead: Flag the order as refunded Calculate the commission reversal as a negative line item Queue it for the next payout batch (do not pay it separately) Include the original order ID in the reversal note At settlement time, net the reversals against new commissions. Log