Blog · Finance metrics and formulas · Financial services
Calculate relationship value across financial products using contribution, retention and funding assumptions. Follow a worked example with spreadsheet formulas, credit-cost checks and a clear bridge to source exports.
Customer lifetime value in financial services is the discounted contribution expected from a customer relationship across its products, less acquisition spending. Calculate it after funding, servicing and expected credit costs. Keep the relationship definition, future product mix and retention assumptions visible beside the answer.
A balance is not customer value. A large relationship can generate little contribution once its funding cost and service demands are included. Start with a de-identified relationship summary that finance, treasury and credit risk can each reconcile.
Use a stable anonymous relationship key to connect products. Decide whether the reporting unit is a business customer, an agreed commercial group or another defined relationship. Avoid bringing names, account numbers or transaction descriptions when an approved summary can answer the question.
The general LTV calculation owns the basic formula. The extra work here is combining products without duplicating internal funding benefits and matching credit costs to the periods being valued.
Relationship CLV = Sum of discounted expected relationship contribution over the stated horizon − Acquisition spending
Relationship contribution = Revenue and funding benefits − Funding charges − Servicing costs − Expected credit costs
Use either a treasury-supplied net funding result or its separate components. Do not combine both. The OCC's explanation of funds transfer pricing describes allocating funding and liquidity costs and benefits across activities. That provides context for the economic allocation; use your own institution's approved method for this calculation.
Request a common reporting date and period across the files. Keep product balances as supporting measures rather than summing them into income. The financial services segmentation guide covers grouping relationships; retain the same contribution definition when comparing those segments.
| Business summary | Required fields | Check |
|---|---|---|
| Relationship register | Anonymous key, opening cohort, active status, closure date | One relationship definition across products |
| Product profitability | Relationship key, product, period, revenue, funding allocation | No duplicate internal transfers |
| Service costs | Relationship key or allocation group, period, attributed cost | Allocation total agrees with the cost summary |
| Credit-cost summary | Product or risk group, forecast period, approved expected cost | Same horizon and basis as contribution |
| Acquisition summary | Relationship key or cohort, acquisition spending | Deducted once |
Keep a separate list of unmapped or excluded rows. A profitable-looking segment with missing service costs needs correction before anyone changes an acquisition budget.
An illustrative US commercial financial institution is evaluating a relationship containing deposits, a term loan and payments. The table uses annual USD amounts supplied by the relevant business owners. Deposit revenue is a net funding benefit after customer interest; the term-loan revenue includes interest and fees. Payments revenue consists of fees.
Put these columns in a worksheet named Products, with the product rows in rows 2–4. Contribution in F2, filled down, is:
=B2-C2-D2-E2
| Product | Revenue or net funding benefit (USD) | Funding charge (USD) | Servicing cost (USD) | Expected credit cost (USD) | Contribution (USD) |
|---|---|---|---|---|---|
| Deposits | 600 | 0 | 180 | 0 | 420 |
| Term loan | 1,660 | 600 | 140 | 200 | 720 |
| Payments | 390 | 0 | 120 | 40 | 230 |
| Total | 2,650 | 600 | 440 | 240 | 1,370 |
The annual relationship contribution is $1,370: $2,650 − $600 − $440 − $240. The product contributions also sum to $1,370. A zero funding charge on deposits means its supplied benefit is already net on the stated basis, not that deposits have no economic cost.
Credit risk supplies the expected economic costs for this management projection. Do not subtract an existing allowance balance every year. The FDIC's CECL resource page describes a separate accounting framework for expected credit losses; a management contribution schedule must explain how its period charges relate to that framework rather than silently substituting one for the other.
Assume this relationship's conditional annual contribution remains $1,370 for three years. For this example, contribution is received at each year-end only if the relationship is active then. The supplied probabilities are 100%, 90% and 80%; they are cumulative survival probabilities, not successive annual retention rates. Assume no value after year three.
On an Inputs sheet, put acquisition spending of $250 in B2 and an annual discount rate of 10% in B3. On a Projection sheet, use columns A–F in the order below. In C2 enter =SUM(Products!$F$2:$F$4) and fill down to each year. For expected contribution in D2, enter:
=B2*C2
For the discount divisor in E2, enter:
=(1+Inputs!$B$3)^A2
For present value in F2, enter the following, then fill D2:F2 down to row 4:
=D2/E2
| Year | Survival probability | Conditional contribution (USD) | Expected contribution (USD) | Discount divisor | Present value (USD) |
|---|---|---|---|---|---|
| 1 | 100% | 1,370 | 1,370 | 1.100 | 1,245.45 |
| 2 | 90% | 1,370 | 1,233 | 1.210 | 1,019.01 |
| 3 | 80% | 1,370 | 1,096 | 1.331 | 823.44 |
| Total | 3,087.90 |
Relationship CLV is $3,087.90 − $250 = $2,837.90. Calculate the result from unrounded cells and round only the displayed amounts to cents:
=SUM(F2:F4)-Inputs!B2
This is a three-year estimate under explicit assumptions. It does not prove that the product mix remains unchanged or that customer retention causes revenue retention. Product runoff, repricing and changes in service needs should change the conditional contributions when supported by the forecast.
Suppose the term loan's expected annual credit cost rises from $200 to $400, with all other assumptions unchanged. Apply that single change to the product table and rerun the same projection:
| Calculation step | Baseline (USD) | Higher credit cost (USD) |
|---|---|---|
| Annual term-loan credit cost | 200 | 400 |
| Annual relationship contribution | 1,370 | 1,170 |
| Year 1 expected contribution | 1,370 | 1,170 |
| Year 2 expected contribution | 1,233 | 1,053 |
| Year 3 expected contribution | 1,096 | 936 |
| Present value using the same divisors | 3,087.90 | 2,637.11 |
| Less acquisition spending | 250 | 250 |
| Relationship CLV | 2,837.90 | 2,387.11 |
The reduction is $450.79, calculated from unrounded values. Show this sensitivity beside the baseline so the commercial team understands the dependence on credit assumptions. A customer lifetime value dashboard can organize the discussion; it cannot remove the need for a reconciled contribution basis.
Covirage is not affiliated with Microsoft.
Bring a sample of your own de-identified relationship, product contribution and retention summaries. Covirage maps the columns, and its tools compute and check the supplied figures; the external AI model explains the comparisons with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.
See Covirage for financial services for the supporting industry context.
A customer can hold deposits, borrowing and payment products at once. Product results show where contribution comes from; the relationship total shows the economics of keeping or acquiring that customer. Shared costs and internal funding transfers need consistent treatment across the products.
A deposit balance is an input to funding economics, not revenue by itself. Use the funding benefit supplied by treasury under the institution's methodology, after the applicable customer interest cost. Do not add the balance to fees or count its internal funding benefit twice.
Not necessarily. The customer may retain a deposit or payments relationship. Define relationship activity before calculating retention, and measure product runoff separately. Otherwise normal repayment can look like a lost customer even when the broader relationship continues.
This calculation is a management estimate for comparing relationship economics under stated assumptions. It is not automatically a recognized asset or a substitute for accounting credit-loss calculations. Finance should reconcile source amounts and explain differences in timing, allocations and horizon.