Blog · Board and management reporting · Retail banking
Churn in banking separates closed products, lost relationships and inactive accounts. Reconcile all three to a fixed opening cohort and compare the relationship revenue attached to each outcome.
Churn in banking needs separate measures for closed accounts, lost relationships and inactivity. A customer who closes a savings account but keeps a checking account has reduced the product relationship without leaving the bank. Build the comparison from stable relationship IDs before interpreting any closure rate as customer churn.
Start with a month-opening file of eligible accounts and relationship IDs. Add the closing status file, qualifying customer activity and relationship revenue for the comparison periods. Keep product type, opening date, closure date and closure reason so operations can review the exceptions.
Set the product scope explicitly. If the scope contains only deposit products, call the outcome deposit-relationship churn. A customer with a retained loan may still have a wider bank relationship. The general churn definition explains the fixed opening base; banking adds the account-to-relationship mapping.
For inactivity, document which events count. This example uses no qualifying customer-initiated transaction for at least 90 days. It excludes bank-generated interest postings and fees from qualifying activity. This is an internal analytic rule, separate from legal dormancy or unclaimed-property obligations: OCC guidance explains that abandonment periods depend on state law.
Keep missing activity dates in an unknown group. Do not turn missing history into a claim that the customer has never transacted. The distinction between a review signal and a confirmed loss also matters in churn versus dormancy.
On an account sheet, use A for relationship ID, B for account ID, C for opening-open flag, D for ending-open flag and E for last qualifying activity date. Put the cutoff date in L1 and the inactivity threshold in L2. With complete status data, the closed-opening-account flag in F2 is:
=--AND(C2=1,D2=0)
The inactive-open-account flag in G2 is:
=IF(D2<>1,0,IF(E2="","",--($L$1-E2>=$L$2)))
An account last used on June 15, 2026 is 107 days without qualifying activity at September 30, 2026, so it meets this internal threshold if still open. Confirm that a closed flag represents a real closure rather than an account migration or an unmatched record.
Aggregate by relationship, keeping one row per relationship. The following hypothetical US bank cohort has no new accounts, migrations or unknown dates during September. Earlier revenue is August relationship revenue; current revenue is September relationship revenue, both from the bank's management reporting file.
| Relationship | Opening accounts | Closed opening accounts | Ending open accounts | Inactive ending accounts | Earlier monthly revenue (USD) | Current monthly revenue (USD) |
|---|---|---|---|---|---|---|
| R1 | 2 | 1 | 1 | 0 | 400 | 350 |
| R2 | 1 | 1 | 0 | 0 | 100 | 0 |
| R3 | 1 | 0 | 1 | 1 | 80 | 10 |
| R4 | 2 | 0 | 2 | 0 | 300 | 330 |
| R5 | 1 | 1 | 0 | 0 | 120 | 0 |
| R6 | 1 | 0 | 1 | 0 | 100 | 100 |
| Total | 8 | 3 | 5 | 1 | 1,100 | 790 |
For this summary in A1:G7, put a lost-relationship flag in H2 and fill down:
=--AND(B2>0,D2=0)
Check every eligible product before marking the relationship lost, including products opened after the starting snapshot. A new replacement product can preserve a relationship even though an opening account closed.
| Measure | Spreadsheet formula | Result |
|---|---|---|
| Opening-account closure rate | =SUM(C2:C7)/SUM(B2:B7) |
37.5% |
| Opening-relationship loss rate | =SUM(H2:H7)/COUNTIFS(B2:B7,">0") |
33.3% |
| Inactivity among ending open accounts | =SUM(E2:E7)/SUM(D2:D7) |
20.0% |
Microsoft documents COUNTIFS as counting observations that meet the specified conditions. Here it counts relationships with opening accounts, rather than counting products.
The account proof is 8 opening accounts minus 3 closures equals 5 ending accounts. The relationship proof is 6 opening relationships minus 2 lost relationships equals 4 retained relationships. The inactivity denominator is the ending open-account population, so that rate is a status measure, not another opening-cohort churn rate. Percentages are rounded to one decimal place.
R2 and R5 lost their eligible relationships. Their earlier revenue totals $220. R1 and R3 remain but show contractions of $50 and $70; R4 expands by $30. These movements reconcile the monthly revenue snapshots:
Current cohort revenue = Earlier revenue − Lost-relationship revenue − Retained contraction + Retained expansion
| Revenue bridge | Amount (USD) |
|---|---|
| Earlier monthly cohort revenue | 1,100 |
| Lost-relationship revenue | −220 |
| Retained-relationship contraction | −120 |
| Retained-relationship expansion | 30 |
| Current monthly cohort revenue | 790 |
The lost-relationship revenue formula is =SUMIFS(F2:F7,H2:H7,1). Microsoft's SUMIFS documentation describes summing values subject to criteria. For retained contraction, put =IF(H2=1,0,MAX(F2-G2,0)) in I2; for expansion, put =IF(H2=1,0,MAX(G2-F2,0)) in J2, then fill down and sum.
This is a revenue movement bridge, not proof that churn caused every dollar of decline. Usage, rates, pricing and calendar differences can change revenue while a relationship remains. Product operations owns closure validation; finance owns the revenue tie; customer analytics owns the cohort comparison. Use products per customer alongside it to track relationship depth.
Covirage is not affiliated with Microsoft.
Upload account-status, activity and relationship-revenue files. Covirage maps the columns, its tools compute and check the separate cohort measures, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for retail banking for the supporting workflow.
It counts as an account closure under the bank's reporting definition. It becomes relationship churn only when the customer no longer holds any eligible products at the cutoff. Transfers between products and administrative account replacements need separate treatment.
No. An account can remain open while activity declines, and a customer can use other products. An internal inactivity threshold identifies a review population. It does not establish customer intent, legal dormancy or the end of the entire relationship.
The customer analytics owner should maintain the measurement rules with product operations and finance. Operations validates status and activity, finance validates revenue, and the relevant compliance team owns legal dormancy and unclaimed-property treatment separately from the analytic metric.