Interest rate sensitivity analysis in Excel

Interest rate sensitivity analysis in Excel compares corporate interest expense before and after a finance-supplied change in borrowing rates. Calculate the change facility by facility, using drawn balances and the days each balance can reprice. Reset timing matters: the same rate increase costs less within the budget year when it takes effect later.

Start with a reconciled borrowing export

Bring the treasury debt schedule and the corresponding ledger balances into the workbook. Use one row per facility when principal is constant; use separate dated rows when a balance changes. Keep the facility ID so finance can trace each calculated amount to the source schedule.

The minimum inputs are drawn principal, currency, floating or fixed status, current all-in annual rate, next applicable reset date, and contractual day-count basis. Record maturity and planned repayments too. An undrawn commitment is not outstanding principal; any commitment fees need their own schedule.

Treasury supplies the shock and confirms which facilities it affects. FP&A supplies the budget window. The controller checks that the principal total agrees with the debt ledger at the starting date. Record the export date and the person who confirmed the rate assumptions alongside the workbook.

This is a sensitivity analysis: change one assumption while holding the others constant. Use scenario analysis in Excel when balances, revenue, and several cost assumptions move together.

Set the assumptions before writing formulas

Consider a US distributor planning January 1 through December 31, 2027. All four facilities below remain outstanding throughout the year. Every agreement in this example uses actual days divided by 365; there are no floors, caps, changing spreads, or fees in this calculation.

The baseline holds current all-in rates constant. A permanent rate shock begins at each floating facility's listed reset, then stays in effect through year-end. These are explicit planning assumptions, not a prediction of future rates.

Put the start date in M2, the end date in M3, and the shock of 1.00% in M4. That is a 100-basis-point increase. Enter dates as actual date values, rather than labels stored as text.

Principal sums to $15,000,000. Floating balances total $12,000,000, or 80.0% of the total. That percentage describes the balance mix; it does not account for when the rates reset.

Calculate eligible days and expense

Incremental interest = Drawn principal × Rate shock × Eligible days / Day-count base

Use columns G through J for eligible days, baseline interest, incremental interest, and stressed interest. With the first facility in row 2, enter:

=IF(C2="Floating",MAX(0,DAYS($M$3,MAX($M$2,E2))+1),0)
=B2*D2*DAYS($M$3+1,$M$2)/F2
=B2*$M$4*G2/F2
=H2+I2

Fill the formulas through row 5. The inclusive end date requires the additional day. Microsoft's DAYS documentation confirms that the function returns the days between two dates. The eligible-day formula produces zero when the next reset falls after the planning window.

The term loan's increase is 4,000,000 × 1.00% × 275 / 365. Keep full precision in the formulas and round displayed USD amounts to cents. Summing the displayed facility amounts can differ from the unrounded total by a cent.

For facilities that mature, amortize, or change their day-count basis, split the schedule into the appropriate intervals before using this method. A reset-only formula assumes the balance remains drawn for the whole budget window.

Prove the total and compare shocks

The sum of stressed interest must equal baseline interest plus the incremental amount. This check must return zero, allowing only rounding tolerance:

=SUM(J2:J5)-SUM(H2:H5)-SUM(I2:I5)

Independently calculate the increase from principal and eligible days. Microsoft's SUMPRODUCT documentation describes multiplying corresponding array entries and adding the products:

=$M$4*SUMPRODUCT(B2:B5,G2:G5)/365

That check uses the common 365 denominator in this example. With different contractual bases, sum the individually calculated facility amounts instead.

Replace M4 with each approved shock, leaving every other input unchanged:

For this deliberately linear schedule, doubling the positive shock doubles the increase. Treasury can see which facilities drive the exposure, while FP&A can carry the approved expense change into the financial KPI pack. Payment timing still needs the actual interest payment schedule; an expense sensitivity alone does not establish cash payment dates.

Where it goes wrong

• Applying the shock before reset. A later reset shortens the affected period. Check the effective date against the agreement.
• Using committed capacity as principal. Unused borrowing capacity inflates the expense calculation.
• Overlooking balance changes. Repayments, new draws, and maturity dates require dated balance intervals.
• Ignoring rate floors or changing margins. The simple linear result holds only when the whole supplied shock reaches the borrowing rate.
• Replacing contractual day counts. Use each facility's actual convention; the example's common denominator is an assumption.

Covirage is not affiliated with Microsoft.

Compare your borrowing schedule in Covirage

Bring your debt schedule and finance-approved rate assumptions in a file or Google Sheet; Covirage maps the columns, and its tools compute and check the facility 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 Covirage for FP&A reporting for the wider planning context.