Blog · Finance metrics and formulas · Banking
Compare loan balances, past-due amounts, maturities and product concentration on one reporting date. Use a worked portfolio summary, exact spreadsheet formulas and reconciliation checks.
Loan portfolio analysis in banks starts with a consistent reporting date and a reconciled principal balance. Compare product concentration, past-due installments, balances on past-due loans and upcoming maturities using separate, clearly labeled measures. This is a descriptive review of the supplied loan book for a bank's business reporting team.
The word delinquency can conceal two different amounts. One is principal installments already overdue. The other is the full outstanding principal of loans that meet a stated days-past-due threshold. A loan with a missed installment can have a much larger remaining balance, so the two totals answer different questions.
For this example, outstanding principal excludes accrued interest and undrawn commitments. Overdue installments contain principal only. The threshold field contains outstanding principal on loans reported as at least 30 days past due, under the bank's supplied classification. It is not an estimate of what will be lost.
Maturing principal means the outstanding balance on loans whose contractual maturity falls within the next 12 months. It is a snapshot of those loans, rather than a schedule of all payments expected during that window. Scheduled amortization would need its own export.
Keep relationship coverage separate from this book review. Lending-only relationships examines the broader customer relationship, while facility utilization compares drawings with committed limits.
Ask the reporting owner for a September 30, 2026, aggregate export. Group it by product and the bank's supplied borrower segments. Every loan belongs to one product-and-segment row, and the classification is unchanged throughout the comparison.
Put the following fields in columns A:F, with data in rows 2:5. The maturity window runs from October 1, 2026, through September 30, 2027.
| Product | Segment | Outstanding principal (USD) | Overdue installments (USD) | Principal on 30+ day past-due loans (USD) | Principal maturing in window (USD) |
|---|---|---|---|---|---|
| Business term | Established | 4,800,000 | 72,000 | 240,000 | 600,000 |
| Business term | Growing | 1,200,000 | 36,000 | 120,000 | 300,000 |
| Equipment | Established | 2,500,000 | 25,000 | 125,000 | 500,000 |
| Commercial real estate | Established | 3,500,000 | 35,000 | 175,000 | 350,000 |
| Total | 12,000,000 | 168,000 | 660,000 | 1,750,000 |
These are illustrative balances for a US bank, all in USD. The file contains business summaries; individual borrower information is unnecessary for the calculations shown here. Retain the source report's balance basis and status definitions alongside the summary so another reviewer can reproduce it.
Concentration share = Product outstanding principal / Total outstanding principal
30+ day past-due loan share = Principal on qualifying loans / Outstanding principal
Overdue installment share = Overdue principal installments / Outstanding principal
For the total principal in C6, use the first formula below. Fill it across through F6 to obtain the other control totals. The remaining formulas return the bankwide overdue installment share, past-due loan share and maturity share:
=SUM(C2:C5)
=SUM(D2:D5)/SUM(C2:C5)
=SUM(E2:E5)/SUM(C2:C5)
=SUM(F2:F5)/SUM(C2:C5)
The results are $12,000,000 outstanding principal, a 1.40% overdue installment share, a 5.50% balance share on 30+ day past-due loans, and a 14.58% maturity share. Display percentages to two decimal places and retain full precision in the workbook.
To summarize products, put a product label in H2. Enter these formulas in I2, J2, K2 and L2 respectively:
=SUMIFS($C$2:$C$5,$A$2:$A$5,H2)
=SUMIFS($E$2:$E$5,$A$2:$A$5,H2)
=I2/SUM($C$2:$C$5)
=IF(I2=0,"",J2/I2)
The results use one denominator for concentration and a different denominator for each product's past-due share. Microsoft's SUMIFS documentation explains summing amounts that meet specified criteria; add a segment criterion when the question requires both classifications.
| Product | Principal (USD) | Concentration | Principal on 30+ day past-due loans (USD) | Past-due loan share |
|---|---|---|---|---|
| Business term | 6,000,000 | 50.00% | 360,000 | 6.00% |
| Equipment | 2,500,000 | 20.83% | 125,000 | 5.00% |
| Commercial real estate | 3,500,000 | 29.17% | 175,000 | 5.00% |
| Total | 12,000,000 | 100.00% | 660,000 | 5.50% |
First tie the $12,000,000 principal balance to the dated portfolio control report. Reconcile differences in accrued interest, excluded products or balance adjustments before reading the ratios. Then confirm the product summary equals the original export: $6,000,000 + $2,500,000 + $3,500,000 = $12,000,000.
The threshold balance also reconciles: $360,000 + $125,000 + $175,000 = $660,000. Dividing $660,000 by $12,000,000 gives 5.50%.
As an independent spreadsheet check, put each source row's threshold balance share in G2 using =E2/C2, filled through G5. Then calculate:
=SUMPRODUCT(C2:C5,G2:G5)/SUM(C2:C5)
It must return 5.50% as well. Microsoft documents SUMPRODUCT as summing products of corresponding ranges. This check weights each row by its principal, which preserves the portfolio denominator.
Covirage is not affiliated with Microsoft.
Business term loans account for half the principal and $360,000 of the threshold balance. Within that product, the growing segment has a 10.00% threshold share, compared with 5.00% for established borrowers. The reporting team can verify the segment assignments and investigate which recorded balance movements explain the difference.
The $1,750,000 maturity total identifies a separate operational review: confirm the contractual dates and responsible relationship teams. It does not predict renewals or repayments. Keep this snapshot alongside, rather than inside, the loan-to-deposit ratio, which answers a funding question using a different denominator.
Bring a dated aggregate portfolio export with principal, product, segment, past-due definitions and maturity fields. Covirage maps the columns; its tools compute and check the comparisons, and the external AI model explains them 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 overview.
Use a reporting date, product, approved borrower segment, outstanding principal, overdue principal installments and maturity information. Include a separately defined balance on loans meeting the chosen past-due threshold. Aggregate summaries work when the underlying definitions and control totals are supplied.
No. Past-due principal installments are the overdue scheduled amounts. The outstanding principal of loans meeting a past-due threshold includes their remaining principal as well. Keep these fields separate and label both clearly before calculating their shares of the portfolio.
Divide the outstanding balance in a defined product or segment by total outstanding portfolio balance on the same date. Each classification must be mutually exclusive for its shares to sum to the whole. Report the classification and balance basis alongside the percentage.