RATE / NPER
Two of the pieces PMT normally takes as known inputs, solved for instead: the interest rate itself, or how many payments it takes to pay something off.
RATE and NPER both turn the usual PMT setup around. Instead of knowing the rate and solving for the payment, they treat the payment as known and solve for whichever piece is actually missing. They're the natural pair for "which of these two loan offers is actually cheaper" (RATE, once a quoted payment is known) or "how long until this is paid off at this payment amount" (NPER) questions, the kind that come up constantly when comparing real financing offers against each other.
Syntax
| Argument | Description |
|---|---|
| nper | RATE: Required. The number of periods. NPER: this is what NPER itself solves for. |
| pmt | Required for both. The fixed payment each period. |
| pv | Required for both. The loan amount or starting balance. |
| fv | Optional for both. The balance left over at the end: 0 if the loan is fully paid off. |
| rate | NPER: Required. The interest rate per period, matching nper's units. |
| type | Optional for both. 0 (end of period) or 1 (start of period). |
| guess | RATE: Optional. A starting estimate for the solver. Rarely needed. |
A real example: uncovering the real rate behind a quoted payment
A $15,000 loan with a known $460 monthly payment over 36 months, but no stated interest rate:
RATE returns the rate per period, here per month, so multiplying by 12 converts it to an annual figure that can actually be compared against a competing offer's advertised APR.
Worked examples
| A | B | C | |
|---|---|---|---|
| 1 | Loan amount | Monthly payment | Months |
| 2 | 15000 | -460 | 36 |
(multiplying by 12 converts the period rate back to an annual one for comparison)
| A | B | C | |
|---|---|---|---|
| 1 | Balance | Annual rate | Monthly payment |
| 2 | 8000 | 0.18 | -300 |
Common mistakes
- Forgetting to convert RATE's result back to an annual figure before comparing it to an advertised annual rate: RATE returns a per-period rate, matching whatever period nper was counted in.
- Feeding NPER a payment too small to ever cover the interest. The balance never shrinks, and NPER has no finite answer to give.
- Using RATE/NPER with a rate and nper from different periods (an annual rate with a monthly nper), the same mismatch that trips up PMT.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice RATE / NPER →