Blog · Finance metrics and formulas
Calculate affiliate marketing ROI after refunds, commissions and delivery costs. Build a settlement-based spreadsheet, reconcile the totals and compare affiliates on contribution rather than gross sales.
Affiliate marketing ROI is contribution after affiliate costs divided by those costs. Calculate it from settled orders, refunds and commission records, rather than the sales total in a partner report. The important check is that a returned order reduces revenue and that its commission is reversed only when the settlement actually provides a credit.
Attributed affiliate ROI = (contribution before affiliate costs - affiliate costs) / affiliate costs
Contribution before affiliate costs = net attributed sales - product costs - variable fulfillment costs
Affiliate costs = commissions charged - commission reversals + other affiliate costs
This is a return on the affiliate investment convention. Other ROI conventions use a broader denominator: Google's ROI guidance illustrates a calculation including production and advertising costs. Name your convention so two teams do not present different ratios under the same label. The existing marketing ROI versus ROAS guide explains the general distinction.
Agree which network fees, partner placement fees, campaign labor and incentives belong in affiliate costs. If a cost is already in product or fulfillment costs, keep it out of that denominator. The answer here is contribution on the included costs, before any excluded corporate expenses or taxes.
Start with an order export containing order ID, affiliate ID, order date, merchandise sales, discounts and tax. Add the refund register, product cost records, fulfillment charges and affiliate settlement export. Retain settlement IDs so finance can find every commission charge and reversal.
Use sales after discounts and excluding sales tax. Then subtract refunds. IRS Publication 334 explains that credits for returned merchandise and other sales allowances reduce gross sales in arriving at net sales. This is a useful net-sales distinction, rather than a prescription for your company's accounting policy.
Join refunds to their original order IDs, even when posted later. Use product cost after documented inventory recovery; a refunded sale does not automatically mean the product cost vanished. Retained fulfillment charges also remain costs. The credit notes and returns guide covers that revenue reconciliation in detail.
Give each order one approved affiliate allocation. Unmatched refunds and unidentified commission adjustments need an exception list, not a guessed partner. Where acquisition is the decision, keep customer acquisition cost alongside this return calculation.
A fictional US retailer reviews a completed order cohort after its agreed refund and settlement window. All inputs below are USD. Product costs already reflect approved recoveries; fulfillment is the cost retained after refunds. Other affiliate costs are allocated network and support fees.
Use columns A:H in this order:
| Affiliate | Gross sales (USD) | Refunds (USD) | Product cost (USD) | Fulfillment (USD) | Commission charged (USD) | Commission reversed (USD) | Other affiliate cost (USD) |
|---|---|---|---|---|---|---|---|
| A | 60,000 | 6,000 | 24,000 | 3,000 | 6,000 | 600 | 900 |
| B | 40,000 | 8,000 | 16,000 | 2,000 | 4,000 | 800 | 1,200 |
| C | 20,000 | 1,000 | 9,000 | 1,000 | 2,000 | 100 | 400 |
| Total | 120,000 | 15,000 | 49,000 | 6,000 | 12,000 | 1,500 | 2,500 |
In Excel or Google Sheets, add columns I:N. Enter these formulas in row 2 and fill down through row 4:
I2 = B2-C2
J2 = I2-D2-E2
K2 = F2-G2
L2 = K2+H2
M2 = J2-L2
N2 = IF(L2=0,"",M2/L2)
I is net sales, J contribution before affiliate costs, K net commission, L affiliate cost, M contribution after affiliate costs and N ROI. Format N as a percentage to one decimal place; calculate using unrounded amounts. A blank ROI flags a zero denominator for review.
| Affiliate | Net sales (USD) | Contribution before (USD) | Net commission (USD) | Affiliate cost (USD) | Contribution after (USD) | ROI |
|---|---|---|---|---|---|---|
| A | 54,000 | 27,000 | 5,400 | 6,300 | 20,700 | 328.6% |
| B | 32,000 | 14,000 | 3,200 | 4,400 | 9,600 | 218.2% |
| C | 19,000 | 9,000 | 1,900 | 2,300 | 6,700 | 291.3% |
| Total | 105,000 | 50,000 | 10,500 | 13,000 | 37,000 | 284.6% |
These are illustrative results, not affiliate performance benchmarks. Compare partners with similar order maturity and cost scope before using the ranking to change a budget.
For the total ROI, divide summed contribution after affiliate costs by summed affiliate costs:
=SUM(M2:M4)/SUM(L2:L4)
The full reconciliation is $105,000 net sales minus $49,000 product cost, $6,000 fulfillment and $13,000 affiliate cost, leaving $37,000. The net commission total is separately $12,000 charged minus $1,500 reversed, or $10,500. Tie those inputs to the order, refund and settlement registers.
Use this identity as a spreadsheet check; it must return zero:
=SUM(I2:I4)-SUM(D2:D4)-SUM(E2:E4)-SUM(L2:L4)-SUM(M2:M4)
Keep unresolved commissions visible. If the cohort is still receiving reversals, label the result provisional and rerun the same cohort after settlement. Broader budget actions belong in improving marketing ROI.
Bring your order, refund and affiliate settlement files to Covirage. Covirage maps the columns, its tools compute and check the contribution figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See customer profitability for the contribution behind each sale. Covirage is not affiliated with Microsoft or Google.
Yes. Include commissions as affiliate marketing costs, reduced by documented reversals on canceled or refunded orders. Keep commissions outside the contribution figure used before marketing, or remove them from that figure before subtracting affiliate costs, so they are deducted once.
Attach each refund to its original order and affiliate. Reduce sales by the refund, apply any product cost recovery allowed by the finance records, and subtract only commissions that remain payable. A refund and a commission reversal are separate adjustments.
No. That ratio measures revenue per commission dollar. It ignores the cost of the goods, fulfillment, refunds and other affiliate expenses. A contribution-based ROI shows what remains after those costs relative to the agreed affiliate investment.
No. An affiliate attribution rule assigns observed orders to a partner; it does not prove those orders would disappear without that partner. Describe the result as attributed ROI unless separate evidence supports an estimate of the additional contribution the affiliate caused.