NPER

Returns the number of payment periods for an annuity.

Syntax:

NPER(rate, payment, presentvalue, futurevalue, type)

rate: the (fixed) interest rate per period.

payment: the payment made each period.

presentvalue: the lump sum payment at the start of the term.

futurevalue: the cash balance paid at the end of the term (optional - defaults to 0).

type: when payments are made (optional - defaults to 0):

0 - at the end of each period.

1 - at the start of each period (including a payment at the start of the term).

NPER returns the number of payment periods implied by a lump sum (presentvalue) at the start of the term, a payment being made each period for numperiods periods, at fixed rate interest, compounded each period, and a lump sum (futurevalue) at the end of the term.

Example:

NPER(5%, -100, 0, 1000, 0)

returns approximately 8.31, the number of periods to realise this scenario.

Application:

Scenario:

Imagine you have a car loan of $20,000. The annual interest rate is 6% (compounded monthly), and you can afford to make monthly payments of $300. You want to know how many months it will take to pay off the entire loan.

Here's how you can use the NPER function to find the answer:

Variables:

Rate (Interest Rate): The interest rate per period. Since the annual rate is 6% and payments are monthly, the monthly rate is 6% / 12 = 0.5% or 0.005.
PMT (Payment): The payment made each period. In this case, it's -$300. The payment is entered as a negative value because it's an outflow of cash.
PV (Present Value): The current value of the loan. This is $20,000.
FV (Future Value): The desired future value of the loan. Since you want to pay it off completely, the future value is $0.
Type: This specifies when payments are due. We'll assume payments are made at the end of the period, so this is 0.

NPER Function Formula:

NPER(rate, pmt, pv, [fv], [type])

Applying the values:

NPER(0.005, -300, 20000, 0, 0)

Result:

The NPER function would return approximately 81.3 months. This means it will take about 82 months (or 6 years and 10 months) to fully pay off the car loan.

	Variable	Description	Value
	A	B	C
1	Rate	Monthly interest rate (6% / 12)	0.005
2	PMT	Monthly payment	-300
3	PV	Present value of the loan	20000
4	FV	Future value (target)	0
5	Type	Payment at the end of the period	0
6	NPER	Number of periods (months)	81.3