Rate volume mix analysis for net interest income

Rate volume mix analysis separates a bank's change in net interest income into total balance growth, portfolio composition and realized rate effects. Use average earning-asset balances and average interest-bearing funding balances, with income and expense from the same periods. The finished bridge must add to the actual change in interest income less interest expense.

Define the bridge before opening the spreadsheet

Keep assets and funding in separate groups. Within earning assets, mix means the share held in loans, securities or other defined portfolios. Within funding, it means the share in the funding categories you include. Never calculate one common portfolio share across both sides of the balance sheet.

Period interest = Average balance × Annualized realized rate × Days / Annualization basis

Net interest income = Interest income − Interest expense

The worked example uses two illustrative 90-day periods and a 360-day annualization basis throughout. It derives each realized rate from reported interest and average balance. It does not apply a contractual coupon to a closing balance.

This is a dollar bridge. Net interest margin owns the separate question of income relative to average earning assets. For product-unit sales rather than bank balances, use the price volume mix method. If adapting a price volume mix analysis template, replace unit fields with average balances and document the bank-specific annualization basis.

Assemble the export and prove the endpoints

A US bank's finance team provides one row per portfolio, with the same category definitions in both periods. Interest below is a positive amount before the funding sign is applied:

Base earning assets total 40,000,000 + 20,000,000 = 60,000,000. Current earning assets total 54,000,000 + 18,000,000 = 72,000,000. Noninterest-bearing funding and equity do not generate an interest-expense row in this simplified example.

Base interest income is 600,000 + 200,000 = 800,000. Subtract funding expense of 250,000 to obtain net interest income of 550,000. Current income is 877,500 + 202,500 = 1,080,000; less expense of 375,000, current net interest income is 705,000. The bridge therefore has to explain an increase of 155,000.

Tie each endpoint to the general ledger. Keep accrual adjustments, nonaccrual treatment and included fee income consistent. If the management report uses tax-equivalent income, either use that basis throughout or add a separately labeled reconciliation to reported income.

Calculate realized rates and scaled balances

Put the seven source columns above in A:G, with headers in row 1 and data in rows 2:4. Use H for the base annualized rate and I for the current annualized rate:

H2 = E2/D2*360/90
I2 = G2/F2*360/90

Fill down. Format H:I as percentages, retaining their unrounded values in calculations:

Next calculate total average balances for each side. J holds the base group total and K the current group total. L is the balance each portfolio would have if its base share were applied to the current group total:

J2 = SUMIF($A$2:$A$4,A2,$D$2:$D$4)
K2 = SUMIF($A$2:$A$4,A2,$F$2:$F$4)
L2 = D2*K2/J2

Fill down again. Loans scale to 40,000,000 × 72,000,000 / 60,000,000 = 48,000,000. Securities scale to 24,000,000. Deposits scale to 60,000,000 because they are the only funding category in this example.

The scaled asset balances must sum to 72,000,000, the actual current asset total. Their differences from actual portfolio balances must net to zero within the asset group. This check proves that the mix step redistributes balances without introducing additional growth.

Allocate volume, mix and rate effects

Use a fixed sequence: grow total balances at base mix and base rates, change the mix at base rates, then change rates on actual current balances. Columns M:P contain signed contributions and the actual signed interest change:

M2 = C2*(L2-D2)*H2*90/360
N2 = C2*(F2-L2)*H2*90/360
O2 = C2*F2*(I2-H2)*90/360
P2 = C2*(G2-E2)
Q2 = P2-SUM(M2:O2)

Fill down to row 4. Q is the row residual and must be zero apart from spreadsheet precision. Funding effects are negative when they increase expense because column C contains −1.

For loans, volume contributes (48,000,000 − 40,000,000) × 6% × 90/360 = 120,000. Mix contributes (54,000,000 − 48,000,000) × 6% × 90/360 = 90,000. Rate contributes 54,000,000 × (6.5% − 6%) × 90/360 = 67,500.

The company check is 550,000 + 110,000 + 30,000 + 15,000 = 705,000. Display dollars rounded to whole dollars; retain unrounded rates and effects for the residual.

Interpret the result and its limits

Balance growth supplied most of the increase. Asset mix also helped because the bank shifted balances toward the higher-yielding loan portfolio. Higher asset rates contributed 90,000, but increased deposit rates absorbed 75,000, leaving a net rate benefit of 15,000.

Finance should agree portfolio definitions with treasury before circulating the bridge. The OCC's earnings handbook overview connects analysis of bank earnings with assessment of risk management. The bridge supports that discussion, but a realized-rate movement can reflect repricing, fees, nonaccruals or changes within a portfolio.

It also does not measure future interest-rate risk. The FDIC's interest-rate-risk examination guidance calls for evaluating assumptions such as deposit behavior and loan prepayments. Those prospective assumptions require their own review beyond a historical accounting bridge.

Where it goes wrong

• Unequal period lengths enter the same bridge. Normalize them or add a separate calendar effect. Otherwise a longer quarter looks like improved rates or balances.
• Interactions are counted twice. In this convention, current balances enter the rate term. Adding another rate-times-volume interaction would overstate the change.
• Closing balances replace average balances. Month-end loan growth may earn little interest during the month, so endpoint balances distort realized yields.
• Funding signs reverse. Increased deposit expense reduces net interest income. Apply one explicit sign convention to every row and check the ledger endpoints.
• Missing portfolios are treated as zero rates. A new or discontinued category needs a documented rate convention. Keep the resulting effect visible rather than allowing division errors to disappear into totals.

Reconcile your own bank portfolio exports

Bring matching average-balance and interest exports with the day-count basis and portfolio definitions. Covirage maps the columns, and its tools compute and check the bridge; the external AI model explains the contributions with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See revenue driver analysis for the supporting approach to bridges that reconcile.