Blog · Board and management reporting · Banking
Compare collections cash, workload, costs and aging movements on a consistent opening portfolio. Build a reconciled monthly review from aggregate operational exports without using borrower records.
Collection analytics in banking compares recorded collection activity, cash receipts, aging movements and operating cost for a defined portfolio. The useful question is whether the same opening workload produced more cash or better payment status at a comparable cost. A lower closing balance alone cannot answer it.
This is an operations review using aggregate summaries. It does not choose borrowers to contact or prescribe collection actions. Keep the broader business context in banking analytics, and use this method to explain what the collection team's records actually show.
Choose an opening assignment date and follow those same accounts to the closing date. Group them by their opening aging bucket, product and servicing team as appropriate. Freeze the opening classification: accounts that age later remain members of their original cohort for this review.
Specify whether balances and cash include principal, interest or fees. The example below uses principal only. Its cash column is principal cash posted to the opening cohort during September 2026; its noncash column contains approved principal reductions that did not produce cash.
An operational cash recovery is not automatically a recovery of a previously charged-off loan. Keep those populations separate in the source report. Likewise, an account moving to an earlier aging bucket is not necessarily paid off.
The OCC's Retail Lending handbook defines roll rate as movement of accounts or balances between payment statuses. That distinction matters: a balance-based movement report and an account-based movement report answer different questions. Label the unit on every table.
Ask operations for one row per opening cohort, with opening accounts and balances, cash, noncash reductions and closing principal. Ask finance for the corresponding collection cost and operations for distinct cases worked. Do not export names, contact details or individual payment histories for this summary.
Use these columns in a worksheet named Summary:
| Column | Field | Definition |
|---|---|---|
| A | Opening aging cohort | Fixed September 1 classification |
| B | Opening accounts | Distinct accounts assigned at opening |
| C | Opening principal | Principal assigned at opening, USD |
| D | Principal cash | Cash reducing that cohort's principal, USD |
| E | Noncash reductions | Approved reductions without cash, USD |
| F | Closing principal | Remaining principal on September 30, USD |
| G | Collection cost | Supplied operating cost allocation, USD |
| H | Cases worked | Distinct opening-cohort accounts worked during the month |
For workload, count each account once within the month even if it generated several activities. Preserve a separate activity count if needed, but do not label it distinct cases. Assign shared costs under a documented rule and keep that rule constant across comparable months.
An illustrative US bank supplies the following principal-only summary. There are no new assignments or transfers within this fixed-cohort view. All dollar amounts are exact.
| Opening days past due | Opening accounts | Opening principal (USD) | Principal cash (USD) | Noncash reductions (USD) | Closing principal (USD) | Cost (USD) | Cases worked |
|---|---|---|---|---|---|---|---|
| 1–29 | 100 | 400,000 | 60,000 | 5,000 | 335,000 | 9,000 | 80 |
| 30–59 | 60 | 300,000 | 30,000 | 10,000 | 260,000 | 9,000 | 50 |
| 60–89 | 40 | 200,000 | 10,000 | 0 | 190,000 | 7,000 | 30 |
| Total | 200 | 900,000 | 100,000 | 15,000 | 785,000 | 25,000 | 160 |
Enter the expression after each cell label in row 2 and fill through row 4. These formulas work in Excel and Google Sheets. Percentages below are rounded to two decimal places; cost per case is rounded to cents.
I2 =IF(C2=0,"",D2/C2)
J2 =IF(D2=0,"",G2/D2)
K2 =IF(H2=0,"",G2/H2)
L2 =C2-D2-E2-F2
| Opening cohort | Cash / opening principal | Cost / principal cash | Cost per case worked (USD) | Reconciliation residual (USD) |
|---|---|---|---|---|
| 1–29 | 15.00% | 15.00% | 112.50 | 0 |
| 30–59 | 10.00% | 30.00% | 180.00 | 0 |
| 60–89 | 5.00% | 70.00% | 233.33 | 0 |
| Total | 11.11% | 25.00% | 156.25 | 0 |
Calculate total measures from total dollars and cases, rather than averaging cohort percentages:
=SUM(D2:D4)/SUM(C2:C4)
=SUM(G2:G4)/SUM(D2:D4)
=SUM(G2:G4)/SUM(H2:H4)
The older cohort has lower recorded principal cash relative to its opening balance and higher cost per case worked. That is a descriptive result, not evidence that its staff performed worse. Assignment complexity, work recorded elsewhere and allocation changes all need review before drawing that conclusion.
For the same opening cohorts, classify the remaining closing principal into an earlier, unchanged or later aging bucket. Here, earlier includes current status; it does not mean fully collected.
| Opening cohort | Earlier bucket at close (USD) | Same bucket at close (USD) | Later bucket at close (USD) | Total remaining principal (USD) |
|---|---|---|---|---|
| 1–29 | 60,000 | 150,000 | 125,000 | 335,000 |
| 30–59 | 20,000 | 100,000 | 140,000 | 260,000 |
| 60–89 | 10,000 | 70,000 | 110,000 | 190,000 |
| Total | 90,000 | 320,000 | 375,000 | 785,000 |
The remaining principal moving later is 375,000 / 900,000 = 41.67% of opening principal. State that denominator. Dividing by remaining principal answers a different question about the closing population.
If an aggregate worksheet named Movements has month in A, opening cohort in B, closing category in C and remaining principal in D, summarize later-bucket balances with:
=SUMIFS(Movements!$D$2:$D$500,Movements!$A$2:$A$500,$N$1,Movements!$B$2:$B$500,$A2,Movements!$C$2:$C$500,"Later")
Use the same month label in N1 and the export. Google's SUMIFS documentation explains summing a range against multiple criteria. Covirage is not affiliated with Microsoft or Google.
The principal bridge is 900,000 − 100,000 − 15,000 = $785,000. Independently, the aging movement table totals 90,000 + 320,000 + 375,000 = $785,000. Both must equal the closing operational control total for this opening cohort.
Tie the cash column to posted principal receipts after reversals. Tie noncash reductions to their approved adjustment summary and costs to the finance allocation. These checks explain balance movement without treating every reduction as recovered cash.
Keep customer analytics in banking and bank marketing analytics separate from the collection operations review. For wider credit-quality measures, use bank asset quality ratios; collection workload and cash results do not replace them.
Bring an aggregate cohort export, aging movement summary and matching cost workbook; Covirage maps the columns, and its tools compute and check the reconciliations and 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 self-service analytics for the supporting business reporting context.
Include opening assigned balances, principal cash collected, noncash reductions, closing balances, distinct cases worked and operating cost. Add an aging movement table for the same opening cohort. Show the report dates and balance definition so the team can reconcile the review to operational control totals.
No. A recovery rate compares defined cash receipts with a specified balance base. Aging improvement describes movement into an earlier payment status. A balance can move earlier without being fully repaid, and an accounting adjustment can reduce it without any cash arriving.
Keep new assignments outside the fixed opening cohort and report them separately. For a whole-workload reconciliation, add an explicit new-assignment column and any transfers. Do not divide cash from both old and new assignments by only the opening cohort's balance.