NPER( ) function

Returns the number of periods required to pay off a loan.

Syntax

NPER(rate, payment, amount <,type>)

Parameters

Name Type Description
rate numeric

The interest rate per period.
payment numeric

The payment per period.
amount numeric

The principal amount of the loan.

type

optional

 numeric

The timing of payments:

  • 0 – payment at the end of a period
  • 1 – payment at the beginning of a period

If omitted, the default value of 0 is used.

Output

Numeric.

Examples

Basic examples

Returns 300.00, the number of months required to pay off a $275,000 loan at 6.5% per annum, with payments of $1,856.82 due at the end of each month:

NPER(0.065/12, 1856.82, 275000, 0)

Returns 252.81, the number of months required to pay off the same loan, with payments of $2,000 due at the end of each month:

NPER(0.065/12, 2000, 275000, 0)

Returns 249.92, the number of months required to pay off the same loan, with payments of $2,000 due at the beginning of each month:

NPER(0.065/12, 2000, 275000, 1)

Advanced examples

Annuity calculations

Annuity calculations involve four variables:

  • present value, or future value $21,243.39 and $ 26,973.46 in the examples below
  • payment amount per period $1,000.00 in the examples below
  • interest rate per period 1% per month in the examples below
  • number of periods 24 months in the examples below

If you know the value of three of the variables, you can use an Analytics function to calculate the fourth.

I want to find: Analytics function to use:
Present value

PVANNUITY( )

Returns 21243.39:

PVANNUITY(0.01, 24, 1000)
Future value

FVANNUITY( )

Returns 26973.46:

FVANNUITY(0.01, 24, 1000)
Payment amount per period

PMT( )

Returns 1000:

PMT(0.01, 24, 21243.39)
Interest rate per period

RATE( )

Returns 0.00999999 (1%):

RATE(24, 1000, 21243.39)
Number of periods

NPER( )

Returns 24.00:

NPER(0.01, 1000, 21243.39)

Annuity formulas

The formula for calculating the present value of an ordinary annuity (payment at the end of a period):

The formula for calculating the future value of an ordinary annuity (payment at the end of a period):

[ Back to top ]

ACL Scripting Guide 14.1