Bank customer lifetime value: contribution by cohort

Bank customer lifetime value estimates future contribution from the whole banking relationship, after agreed funding and customer costs. Calculate historical contribution by joining product rows to a unique relationship ID, then apply clearly labeled retention and discount assumptions. A deposit balance, loan balance or product count is not the value estimate.

1. Agree the contribution definition before joining exports

Bring the relationship master, product income export, approved funding allocation file, servicing costs and credit-cost allocation for the same period. Include closed products and relationships so the historical cohort does not quietly become a list of survivors.

Relationship contribution = Interest income + Fee income + Signed net funding allocation − Servicing costs − Credit costs

The signed funding allocation can be a credit for deposit funding or a charge for lending. In this example, deposit allocations are already net of interest paid to depositors. Do not subtract that expense again.

The Federal Reserve's funds transfer pricing guidance for large financial institutions explains how allocating funding and liquidity risks to business lines, products and activities supports risk-adjusted profitability measurement. It does not supply a universal customer funding rate. Use treasury's approved allocations and document their scope.

These are management contribution figures. Reconcile the underlying income and cost exports to their approved management totals, with a bridge for allocations. The general LTV calculation guide covers the broader formula choices; this method addresses banking relationships.

2. Calculate contribution at the product row

The following illustrative US bank cohort had five relationships at the start of a completed year. Relationship E closed during the year. All other relationships remain active under the bank's documented definition.

Put relationship ID in column A, product in B, interest income in C, fees in D, signed net funding allocation in E, servicing cost in F and credit cost in G. Contribution in H2 is:

=SUM(C2:E2)-SUM(F2:G2)

Fill down through H8. All money is USD; negative funding amounts are charges.

The check is 3,800 + 480 − 680 − 970 − 730 = $1,900. A funding charge already incorporated in another source column must be removed from that column before applying this layout.

3. Aggregate accounts to relationships and measure retention

Create one row for each original relationship, including E. With its ID in K2, calculate annual relationship contribution in L2:

=SUMIFS($H$2:$H$8,$A$2:$A$8,K2)

Microsoft documents SUMIFS as adding values that meet specified criteria. If the export contains several periods, add the period criterion too; otherwise the same customer ID can pull another year's contribution into the result.

Covirage is not affiliated with Microsoft.

The relationship total ties to the product total. Retention is 4 / 5 = 80%, counting relationships rather than product holdings. A customer who closes a loan but keeps an active deposit relationship has not necessarily left the bank.

Historical contribution per starting relationship is 1,900 / 5 = $380. Contribution per currently active relationship is 1,900 / 4 = $475. Label both denominators: they answer different questions, and the second excludes the closed relationship.

Assign cohorts from the original relationship start date, not the latest product opening. Repeat the starting-count, retained-count and contribution summary for each cohort over equally mature windows before comparing their results.

4. Keep the future estimate separate from the history

For an illustrative projection of the four currently active relationships, assume annual contribution of $475 conditional on remaining active, annual survival of 80%, a 10% annual discount rate, and a two-year horizon. Contribution arrives at each year end only if the relationship is still active. No terminal value is included.

These assumptions are imposed for the example. A small historical cohort cannot establish that they will hold, and using the survivor average needs review before applying it elsewhere.

Two-year value per current relationship = 475 × 0.80 / 1.10 + 475 × 0.80^2 / 1.10^2

Place conditional contribution in P2, annual survival in P3 and annual discount rate in P4:

=P2*P3/(1+P4)+P2*P3^2/(1+P4)^2

Year-two survival is 0.80 × 0.80 = 64%. Displayed amounts round to cents; the four-relationship total uses unrounded present values. This is a limited-horizon estimate, not an assertion that relationships end after two years.

For portfolio aggregation beyond this cohort, see customer lifetime value and customer equity. Keep the ledger-based lifetime value method beside the projection so observed contribution remains distinguishable from assumed future value.

Where it goes wrong

• Counting products as customers. Multiple holdings inflate the denominator and can make a product closure look like relationship churn.
• Double-counting funding costs. Combining income already net of funding with another full funding charge understates contribution.
• Removing closed relationships. A survivor-only historical export hides lost relationships and changes the retention denominator.
• Mixing loss measures. Do not subtract both an allocated credit-loss expense and the same charge-offs as another cost. Agree the historical and prospective definitions with finance.
• Treating assumptions as findings. Historical retention and contribution do not guarantee future survival, margins or credit costs. Retain the valuation date and assumption owner alongside the result.

Work from your relationship files in Covirage

Bring product income, relationship IDs, approved funding and cost allocations, and a sheet of agreed lifetime assumptions. Covirage maps the columns, and its tools compute and check contribution and cohort comparisons; 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 Covirage for retail banking for the supporting industry context.