The NPER() function calculates the duration of a compound interest process, annuity calculation, or repayment plan. It is based on regular payments of the same amount and/or one-time payments at the beginning or end of the period, following the financial mathematical benefit principle:
Payment of the creditor+Payment of the debtor=0Payment of the creditor+Payment of the debtor=0
Syntax:
NPER(Rate, Pmt; Pv; Fv; Type)
Arguments:
- Rate (required) – The (constant) periodic interest rate, expressed as an arrears rate.
- Pmt (required/optional, see Note) – The amount of regular payments (e.g., an annuity).
- Pv (required/optional, see Note) – The present (starting) value of one payment direction.
- For disbursement plans, this is the initial account balance.
- For repayment plans, this is the loan amount.
- Fv (optional/required, see Note) – The desired future value (e.g., a residual balance or final repayment amount).
- Type (optional) – Specifies payment timing:
- 0 or omitted: Payments at the end of each period (default).
- 1: Payments at the beginning of each period.
Background:
The five financial functions—
- PV() (present value),
- FV() (future value),
- PMT() (regular payment),
- NPER() (number of periods),
- RATE() (interest rate)
—are interrelated through the benefit principle equation, where M represents the payment timing (Type).
The present value and periodic payments are compounded, and the sum is compared to the future value. Each function solves for one variable in this equation when the others are known. For RATE(), an approximation method is used.
Examples:
The following examples illustrate common financial applications.
- Compound Interest Calculation
An investor deposits $10,000 at a 4.5% annual interest rate, aiming for $25,000. How long must the money remain invested?
=NPER(4.5%, , -10000, 25000)
Result: 20.82 years (the target is exceeded after 21 years).
- Verify using FV(4.5%, 20, , -10000) → $24,117.14 (not reached).
- FV(4.5%, 21, , -10000) → $25,202.41 (achieved).
Key Notes:
- For Pmt, Pv, and Fv, ensure at least one is provided (or the calculation is trivial).
- Negative/positive values indicate cash flow direction (outflow vs. inflow).
- Type adjusts the compounding timing for accuracy.