NPER
Returns the number of periods needed to pay off a loan, or to reach a target, with equal payments at a fixed rate.
NPER(<rate>, <pmt>, <pv> [, <fv>] [, <type>])Returns: A number of periods. A fraction means the last payment is smaller or comes at a different point.
What it does
NPER answers "how many payments will it take?" If a company borrows an amount and can pay a fixed amount each period, NPER returns the number of periods to repay the loan. It can also show how long it takes to reach a savings target with regular deposits. Finance teams use it to test payoff plans, to size debt repayment by month, and to check the term on a loan statement.
Syntax and arguments
- rate: the interest rate per period. Match it to the payment period. Required.
- pmt: the payment per period. Cash you pay is negative. Required.
- pv: the amount borrowed, received as a positive number. Required.
- fv: a balance left after the payments. Optional, and 0 is the default.
- type: 0 for end-of-period payments (the default), or 1 for start-of-period payments. Optional.
Example
A company borrows 20,000 at 6% a year. It can pay 500 a month. The question is how many months the loan will take.
Months to Repay = NPER(0.06 / 12, -500, 20000)
The result is about 44.7 months. That means 44 full payments of 500 and a smaller final payment in month 45. The minus sign on the payment is the Excel convention for cash paid out.
Example: a second, different use
The number of months to build a reserve of 30,000 from a monthly deposit of 400, earning 0.25 percent a month. The future value is the target, and the present value is 0.
Months to Reserve Target = NPER(0.0025, -400, 0, 30000)
Things to watch
- Use the rate per period, matching the payment. An annual rate with monthly payments gives a result that looks far too short.
- The result can be a fraction. Round up for a count of payments, and say how the last payment is handled.
- Keep the sign convention consistent. Cash in and cash out must have opposite signs.
- NPER does not show the interest cost. Use IPMT or the amortization schedule for that.
In business terms
NPER supports payoff planning for debt, the timing of savings targets, the payback period on a loan, and the term check on a financing offer. Treasurers use it to show how long a repayment plan will run.