NPER Function in Google Sheets

Calculate the number of periods required for an investment to reach a target value using the NPER function.

NPER Function

The NPER function in Google Sheets calculates the number of periods required for an investment to reach a target value based on constant periodic payments and a constant interest rate.

Syntax

NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])
  • rate: The interest rate per period. This is a required parameter.
  • payment_amount: The amount paid each period. This is a required parameter.
  • present_value: The present value, or the total amount that a series of future payments is worth now. This is a required parameter.
  • future_value: (Optional) The cash balance you want to attain after the last payment is made. Default is 0.
  • end_or_beginning: (Optional) When payments are due: 0 for end of period (default), 1 for beginning of period.

Examples

  1. Number of Periods Calculation

Calculate the number of periods needed to pay off a $1000 loan with monthly payments of $50 and an annual interest rate of 5%:

=NPER(5%/12, -50, 1000)

This will output approximately 21.54 months.

Notes

  • NPER can be used to calculate the duration of loans, mortgages, or other types of financial investments.
  • PMT: Calculate the total payment per period.
  • RATE: Calculate the interest rate per period of an annuity.