Make multicurrency affiliate commissions reproducible by keeping each sale in its transaction currency, recording the commission terms, and saving the exchange rate used for conversion. Declare the rate source, effective date, quote direction and rounding rule before the payout period starts. Keep the calculated commission separate from the amount a payment provider delivers to the creator.
A workbook should let another person reconstruct one commission without asking which exchange-rate website you opened that morning. The specification below uses hypothetical USD, EUR and GBP orders paid in USD. It is an operational recommendation, not a platform default or guidance on tax treatment.
Decide which amount the workbook controls
Name each currency separately:
- Transaction currency. The currency attached to the customer's order amount.
- Commission currency. The currency in which the program records what the creator earned.
- Payout currency. The currency sent to the creator through the payment provider.
Also keep the merchant's billing currency and the creator's final bank receipt currency when they differ. A currency symbol alone is insufficient. Use codes such as USD, EUR and GBP.
Platform mechanics can limit your choices. Shopify's Collabs payment documentation says automatic payments are handled in USD and charged in the currency of the merchant's Shopify bill. A creator who cashes out in another currency pays a payout conversion fee. Collabs also tracks commissions through affiliate links or discount codes.
If a platform calculates and pays the commission, import its recorded amounts and currencies first. Use your workbook to reconcile them. Do not replace them with a fresh conversion and assume the difference is an error. Ask the provider for the rate basis when the export does not explain it.
If your program controls the calculation, agree on the following workbook rules before using them. Set the underlying eligible-sales definition with commission terms that match your program.
Specify five workbook tabs
Keep raw exports unchanged beside the workbook. Each imported row needs a source filename and row identifier. Save approved workbook versions so a later correction does not rewrite what a creator previously received.
| Tab | Required fields | Purpose |
|---|---|---|
| Rules | Version, effective period, eligible-sales definition, commission rate, conversion event, timezone, rounding rule, fee responsibility | Records the terms used for this run |
| Orders | Order ID, creator ID, event time, transaction currency, eligible amount, terms version, attribution evidence, approval status | Establishes the amount eligible for commission |
| Rates | Rate ID, source, source-file reference, effective timestamp, retrieval timestamp, source currency, target currency, rate | Makes the conversion repeatable |
| Adjustments | Adjustment ID, original order ID, reason, eligible amount reversed, original rate ID, commission change | Preserves refund and correction history |
| Payouts | Batch ID, creator ID, included row IDs, currency, gross commission, fees, amount sent, transfer ID, payment status | Connects approved earnings to payment |
Where a platform supplies a commission, preserve that value in its own column. Give your independently calculated value a different column name. A difference column then has a clear meaning.
Use a unique order-and-event identifier to prevent repeated imports from paying the same sale twice. An unresolved attribution dispute should remain on hold, even when its currency data is complete.
Freeze the rate and its meaning
Define every rate as target-currency units per one transaction-currency unit. Multiplication then converts the commission into the target currency. Save the full precision supplied by your source rather than copying a shortened display value.
An effective date and a retrieval date answer different questions. One identifies the rate's applicable time; the other records when you obtained it. Store both, including timezone. Choose a declared conversion event, such as order capture, and specify what happens when no valid rate exists. Holding that row for review is safer than silently using today's rate.
Do not assume a public reference rate is the rate your payment provider will execute. The European Central Bank's reference-rate page quotes currencies against the euro. It normally updates on working days, with specified closing-day exceptions. The ECB says the rates are informational and strongly discourages their use for transactions. A missing weekend observation is therefore different from a missing order.
For a program-defined calculation, identify the agreed provider or dataset and retain its dated export. For a provider-controlled payment, retain the actual conversion record when available. Keep any later bank conversion separate.
Work through three hypothetical orders
All numbers, dates, rates and records below are invented to demonstrate the method. They are neither market quotes nor Shopify's calculation rules.
The hypothetical terms define eligible sales as merchandise after discounts, excluding shipping and sales tax, less eligible merchandise refunds. This is a commission-base definition only. All three orders belong to creator C01, have approved attribution, and use a 12.5% commission rate.
The hypothetical rate source is a saved demonstration provider export, demo-rates-2026-09-01.csv. Its effective and retrieval timestamp is September 1, 2026, at 18:00 UTC. The example assigns these rates at order capture and locks them. Payout currency is USD.
| Hypothetical order | Transaction currency | Eligible sales | Rate ID | USD per currency unit | Unrounded USD commission | Payable USD |
|---|---|---|---|---|---|---|
| O101 | USD | 100.05 | R1 | 1.000000 | 12.506250 | 12.51 |
| O102 | EUR | 89.99 | R2 | 1.100000 | 12.373625 | 12.37 |
| O103 | GBP | 75.55 | R3 | 1.250000 | 11.8046875 | 11.80 |
| Batch total | USD | Not added across currencies | 36.68 |
Use these formulas for each order:
unrounded_commission = eligible_sales × commission_rate
unrounded_payout = unrounded_commission × locked_conversion_rate
payable_commission = round_to_USD_cents(unrounded_payout)
batch_gross_commission = sum(payable_commission)Here, rounding happens once per order, after conversion. Round to the nearest cent; exact half-cent ties go away from zero. Keep the intermediate commission unrounded. This is the example's chosen policy. Use the payment system's supported precision when extending the workbook to other payout currencies.
Summing rounded rows makes the creator's statement agree with its line items. Rounding only the final batch total is another possible policy, but switching between policies can change the payment. Document one method and keep it across runs.
Reverse the original commission without changing its rate
For this hypothetical policy, refunds use the original locked rate. A full reversal cancels the original rounded commission. For partial refunds, recompute the commission on the remaining eligible amount using the original terms and rate. The adjustment is the new rounded entitlement minus the previously recorded entitlement. This prevents repeated partial refunds from leaving a rounding residue.
| Hypothetical event | Calculation | USD adjustment |
|---|---|---|
| EUR 20.00 eligible refund on O102 | Remaining EUR 69.99 × 12.5% × 1.10 = USD 9.623625; round to 9.62, subtract prior 12.37 | -2.75 |
| Full eligible refund on O103 | Reverse its original payable commission | -11.80 |
| Revised batch entitlement | 36.68 minus 2.75 minus 11.80 | 22.13 |
These are workbook rules, not a claim that every platform handles refunds this way. Shopify says Collabs automatically cancels commissions on canceled and fully refunded orders during its holding period. That statement does not establish a universal partial-refund formula. For timing and already-paid balances, use the separate process for reconciling commissions after returns.
Check the batch before payment
Have a second person reproduce one order per currency using only the stored evidence. Hold rows with missing rates, ambiguous currency fields or unresolved approval status. Confirm that every adjustment points to an original order and that every included commission appears in only one payment batch.
Separate gross commission, creator-borne fees and amount sent. Record merchant-paid fees outside the creator deduction column. Keep the transfer receipt even if the provider converts the amount again. Modash's affiliate payment guide also recommends reconciling sales before payment and retaining per-affiliate payment history. The workbook adds the rate evidence needed to reproduce that history across currencies.
The creator-facing statement should identify the order, commission basis, rate, adjustments and payment status. Use an explained affiliate payout statement to choose what to show without exposing other creators' records.
Keep disclosure checks separate from currency calculations. For endorsements affecting US consumers, the FTC's disclosure guidance calls for clear disclosure of financial relationships with brands, placed with the endorsement. Posting from another country does not necessarily remove that obligation. This workbook addresses neither tax treatment nor jurisdiction-specific legal requirements.
Before your next payout, select one real order in each transaction currency and reconstruct it from the saved export to the transfer record. Resolve every unexplained difference before approving the batch.
