Check Sheet Logo
Check Sheet

NPER

The NPER function calculates the total number of payment periods required to pay off an investment based on a constant payment amount, a fixed interest rate, and the present value of the investment. It is commonly used in financial planning and investment analysis.

Usage

Use the NPER formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:

=NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])
Parameters:
  1. rate (required):
    The interest rate per payment period.
  2. payment_amount (required):
    The constant payment amount made each period.
  3. present_value (required):
    The present value of the investment.
  4. future_value (optional):
    Optional. The future value of the investment, if any.
  5. end_or_beginning (optional):
    Optional. Specifies whether payments are due at the end or beginning of each period. Use 0 or omitted for payments at the end of each period, or 1 for payments at the beginning of each period.

Examples

Here are a few example use cases that explain how to use the NPER formula in Google Sheets.

Calculate Loan Repayments

By using NPER, you can calculate the total number of payments needed to pay off a loan given a constant payment amount, a fixed interest rate, and the present value of the loan.

Determine Investment Duration

You can use NPER to determine the total number of payment periods required to reach a specific investment goal based on a constant payment amount, a fixed interest rate, and the present value of the investment.

Plan for Retirement

By using NPER, you can calculate how many payment periods you need to save for retirement based on a constant payment amount, a fixed interest rate, and the present value of your retirement savings.

Common Mistakes

NPER not working? Here are some common mistakes people make when using the NPER Google Sheets Formula:

Incorrect order of arguments

One common mistake is to provide the arguments in the wrong order. Make sure to provide the arguments in the correct order as specified in the syntax.

Missing required arguments

If you forget to provide one or more of the required arguments, the formula will return an error. Make sure to provide all required arguments.

Invalid value for rate

If the rate argument is negative or zero, the formula will return an error. Make sure to provide a positive value for rate.

Invalid value for payment_amount

If the payment_amount argument is negative or zero, the formula will return an error. Make sure to provide a positive value for payment_amount.

Invalid value for present_value

If the present_value argument is negative, the formula will return an error. Make sure to provide a non-negative value for present_value.

The following functions are similar to NPER or are often used with it in a formula:

  • PV

    The PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.

  • FV

    The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.

  • PMT

    The PMT formula in Google Sheets is a financial function that calculates the periodic payment required to fully pay off a loan or investment based on a constant interest rate and a fixed number of payments. It is commonly used to determine loan payments, mortgage payments, and annuity payments.

  • RATE

    The RATE formula returns the interest rate per period of an annuity. This formula is often used in financial analyses to calculate the rate of return on an investment. It assumes that payments are made at regular intervals and that the interest rate remains constant throughout the duration of the annuity.

Learn More

You can learn more about the NPER Google Sheets function on Google Support.