How much does the monthly payment move if rates rise half a point? Lay rates across a row, compute PMT for each, and you have a sensitivity table that shows your exposure at a glance.
The example
Payment on $25,000 / 60 months across rates.
| A | B | C | |
|---|---|---|---|
| 1 | Rate | 5.5% | 6.0% |
| 2 | Payment | $477.53 | $483.32 |
The formula
One PMT per rate:
How it works
A simple formula row beats a Data Table here:
- Put the rates across a row (5.0%, 5.5%, 6.0%…).
- Below each, compute
=PMT(rate/12, term, -principal), locking principal and term with$. - Copy across — the payment recalculates for each rate.
- Add a row for total interest (
payment × term − principal) to see the full cost impact, not just the payment.
Show the delta: a row of =thisPayment − basePayment highlights how many dollars each rate step adds — the number that actually matters for budgeting against a rate rise.
Try it: interactive demo
Payment vs rate ($, term below).
Variations
Total interest row
Full cost impact:
Delta vs base
Dollars per rate step:
As a 2-way grid
Add term variation → data table.
Pitfalls & errors
Lock principal & term. Use $ so only the rate varies as you copy across.
Annual vs monthly. Divide the annual rate by 12 and use months for the term, consistently.
Sign of principal. Enter it negative (or negate PMT) for a positive payment.
Practice workbook
Frequently asked questions
How do I see how a loan payment changes with the rate?
How do I show the total interest impact too?
How do I vary rate and term together?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class