Bank customer profitability across products and costs

Bank customer profitability is the contribution a banking relationship earns after its agreed funding, credit and servicing costs. Compute it across the relationship's products for one period, then reconcile every component before ranking customers. A large loan balance or a high fee total alone does not answer the question.

Set the boundary before joining the files

Use a relationship identifier that groups the products finance has approved for comparison. Keep legal customer and account identifiers underneath it so a reviewer can trace a total back to the original records. Do not combine unrelated accounts because their names look similar.

The OCC's funds transfer pricing guidance explains how funding costs and benefits can be allocated to products and activities. That guidance addresses large banking firms and explicitly excludes community banks from its applicability; the example here applies supplied management allocations, not a universal regulatory formula.

Agree whether the report includes credit charges, directly assigned operating costs and activity-based service costs. Label excluded corporate overhead, taxes and capital charges. The OCC's Earnings handbook concerns bank earnings and the quality of risk management; a relationship contribution report is only one input to that broader assessment.

Relationship contribution = Interest income + Fee income − Net funding charge − Credit charge − Direct operating cost − Allocated service cost

Allocated service cost = Recorded service hours × Approved hourly rate

A negative net funding charge represents a benefit. Interest expense, if relevant, must appear either in the agreed net funding figure or separately in the calculation, with no double counting. For the general distinction between contribution and final profit, see gross margin, contribution margin and net margin.

Start with product and activity exports

Take a quarterly product-income file, the approved funding and credit allocation files, and service activity for the same quarter. Required fields are relationship ID, product ID, period, income category, amount and currency. The activity file also needs hours, and the rate file needs its effective period.

The illustrative US bank below assigns a service rate of $100 per hour. All amounts cover the same quarter. Funding is signed: loan charges are positive and deposit benefits are negative. Direct operating costs exclude the service pool.

Preserve missing amounts as exceptions. Zero credit charge on a deposit is an approved classification here; a blank charge on a loan would require review. Reject duplicated product-period records before aggregating them.

Calculate the product rows, then the relationships

Put the table in columns A:H, with the first product on row 2, and the hourly rate in M2. Calculate service cost in I2 and contribution in J2:

=H2*$M$2
=C2+D2-E2-F2-G2-I2

Fill both down through row 7. For A's loan, service cost is 40 × 100 = $4,000. Contribution is 90,000 + 6,000 − 45,000 − 9,000 − 6,000 − 4,000 = $32,000. Its deposit adds 0 + 4,000 − (−18,000) − 0 − 2,000 − 2,000 = $18,000.

List relationship IDs in N2:N4. Sum contribution in O2 using Microsoft's documented SUMIFS function, which adds values meeting the specified criteria:

=SUMIFS($J$2:$J$7,$A$2:$A$7,N2)

Covirage is not affiliated with Microsoft.

Amounts are whole dollars; no intermediate rounding is needed. B leads on income but trails on contribution. Review its funding, credit and service components before proposing changes to pricing or coverage. The general customer profitability report explains how to present that review without turning a ranking into an automatic exit list.

Prove the totals before using the ranking

Interest income sums to $260,000 and fees to $22,000, giving $282,000 of income. Gross loan funding charges are $135,000; deposit benefits are $40,000, leaving $95,000 net. Those figures must match the corresponding source-file control totals.

Recorded hours sum to 200. At $100 per hour, $20,000 is assigned from the service pool. If the actual pool is larger, show the unassigned remainder separately rather than silently increasing every relationship's cost.

The final identity is 282,000 − 95,000 − 31,000 − 22,000 − 20,000 = $114,000. Independently, 50,000 + 28,000 + 36,000 = $114,000. Both must agree, but neither proves that all bank expenses are included. Use bank profitability analysis for the wider institution-level boundary, and keep future relationship expectations separate through customer profitability and lifetime value.

Where it goes wrong

• Counting funding twice. Deducting interest expense after using a funding allocation that already includes it understates contribution.
• Mixing credit measures. An expected charge for one relationship and an actual loss for another create an inconsistent ranking.
• Allocating costs by revenue without review. The assignment may conceal the service work that makes relationships different.
• Joining mismatched periods. Current product income with outdated rates or incomplete activity records changes the answer without a business change.

Compare your own relationships in Covirage

Bring your product-income export, approved funding and credit allocations, and service activity with its rates. Covirage maps the columns, and its tools compute and check relationship contributions from the supplied values; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See Covirage for commercial banking for the supporting industry context.