Merchant profitability analysis for payment portfolios

Merchant profitability analysis compares a payments provider's fee revenue with the processing, service and loss costs attributable to each merchant. Start with merchant-level settled activity and reconcile the costs to the same period. Processed payment volume explains scale; contribution shows what that activity earns for the provider.

Set the accounting boundary first

Define whether revenue is gross of interchange and network costs or already net of those amounts. This management view must reconcile to finance's approved presentation; it does not decide the accounting policy. The worked example starts with gross merchant fees and subtracts the specified costs once.

Visa explains that merchants negotiate a merchant discount with their financial institution and that processing services may be included in that charge. Do not treat the entire merchant discount as retained provider revenue before accounting for the costs behind it.

Keep settlement principal, reserve movements and fee revenue in separate columns. A reserve withheld from a merchant is not automatically income, and the transaction value processed for a merchant is not the provider's sales. For company-level context beyond this portfolio calculation, see profitability analysis.

Assemble the merchant-month export

Use a stable merchant ID across the processing, billing, servicing and loss exports. Summarize each file to one merchant-month before combining it. Joining transaction rows directly to several support tickets can multiply both revenue and costs.

Capture settled purchase volume, transaction count, earned percentage fees, per-transaction fees and monthly account fees. Keep credits and fee reversals visible. For costs, distinguish interchange, network assessments, processor charges, attributable support effort and losses borne by the provider after recoveries.

Retain segment fields such as sales channel, merchant size and contract type. Card mix also matters: the Federal Reserve's debit interchange tables distinguish transactions covered by the interchange standard from exempt transactions. Use your billed cost records for the calculation rather than applying one public average rate to every merchant.

Set a cutoff for late dispute recoveries. You can report the current month's booked loss or follow a transaction cohort until a stated date, but label the choice. Mixing current fees with unrelated historical losses can make a segment comparison misleading.

Calculate fee revenue from its actual bases

The example covers one month for a US payments provider. Every merchant has the same illustrative pricing: 2.9% of settled volume, $0.10 per settled transaction and the monthly account fee shown below. Internal processing cost is $0.04 per settled transaction. These assumptions illustrate the calculation and are not market benchmarks.

Put the percentage fee in L2 as 2.9%, the per-transaction revenue rate in L3 as 0.10, and the per-transaction processing cost in L4 as 0.04. Place the inputs in columns A through H:

| Merchant, A | Volume (USD), B | Transactions, C | Account fee (USD), D | Interchange (USD), E | Network (USD), F | Service (USD), G | Provider loss (USD), H | |---|---:|---:|---:|---:|---:|---:|---:|---:| | A | 500,000 | 10,000 | 500 | 9,000 | 600 | 100 | 500 | | B | 300,000 | 6,000 | 300 | 6,000 | 450 | 150 | 900 | | C | 200,000 | 2,000 | 200 | 3,400 | 250 | 50 | 100 | | Total | 1,000,000 | 18,000 | 1,000 | 18,400 | 1,300 | 300 | 1,500 |

Service cost is attributable staff effort valued at a documented loaded rate. Provider loss is the net expense remaining after recoveries at the report cutoff. Both are supplied dollar inputs, not an estimate generated from transaction volume.

Use these formulas, filling down from row 2 through row 4:

I2 = B2*$L$2+C2*$L$3+D2
J2 = C2*$L$4
K2 = SUM(E2:H2)+J2
M2 = I2-K2
N2 = IF(I2=0,"",M2/I2)
O2 = IF(B2=0,"",M2/B2*10000)

I is fee revenue, J processing cost, K total attributable cost, M contribution, N contribution margin and O contribution in basis points of processed volume. Columns L2:L4 hold assumptions only. Keep full precision and round displayed percentages and basis points to one decimal place.

Merchant C has less processed volume than B but contributes more dollars. That is the reason to retain both the activity base and the economic result. Volume alone would rank them differently.

Prove the portfolio total

The revenue identity is processed volume times the percentage fee, plus transactions times the fixed fee, plus account fees. Here it is $1,000,000 times 2.9%, plus 18,000 times $0.10, plus $1,000, giving $31,800.

Costs are $18,400 interchange plus $1,300 network, $720 processing, $300 service and $1,500 provider losses: $22,220. Fee revenue less those costs gives the $9,580 portfolio contribution. Calculate the portfolio ratios directly:

=SUM(M2:M4)/SUM(I2:I4)
=SUM(M2:M4)/SUM(B2:B4)*10000

The first formula returns the contribution margin; the second returns contribution basis points. Averaging merchant percentages would give each merchant the same weight regardless of its fee revenue or volume.

Reconcile each cost category to its ledger control and show timing differences separately. If the ledger records merchant fees net of certain pass-through costs, bridge to this gross management presentation explicitly. Do not resolve a difference by burying it in the smallest merchant.

Compare segments and explain the difference

For a segment summary, use a merchant detail sheet with segment in column P and contribution in M. With the segment name in R2, the formula is:

=SUMIFS($M$2:$M$10000,$P$2:$P$10000,R2)

Excel's SUMIFS documentation describes this criteria-based aggregation. Divide the segment's summed contribution by its summed fee revenue or volume, using the denominator that matches the question. Covirage is not affiliated with Visa or Microsoft.

Shared account-management costs need a separate pool and allocation rule. If support effort is already in column G, do not allocate the same payroll again. The cost-to-serve guide covers service drivers, while bank profitability analysis addresses the different economics of bank products.

Where it goes wrong

• Using settlement volume as revenue. It measures activity, not the provider's earned fee income.
• Subtracting interchange twice. A net revenue starting point already excludes that cost.
• Using the wrong transaction count. Settlements, authorizations and refunds can have different fee bases; follow the contract and cost invoice.
• Treating reserve balances as realized losses. Reconcile reserves, recoveries and booked loss expense separately.
• Duplicating service payroll. Attribute direct servicing once, then allocate only the remaining shared pool.

Compare your payment portfolio in Covirage

Bring a sample of your own merchant fee, processing-cost and service exports with the loss treatment documented. Covirage maps the columns, and its tools compute and check contribution; the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own portfolio data.

See customer profitability for the supporting approach.