SheetFX
≡
<< Go back to all functions

NPER

The NPER function in Google Sheets is a powerful tool for calculating the number of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. Whether you're planning for retirement, analyzing loan payments, or evaluating investment opportunities, the NPER function simplifies the calculation. Dive into our comprehensive guide to master its application.

Function Syntax and Parameters

Syntax: NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])

Parameters:

  • rate: The interest rate per period.
  • payment_amount: The payment amount per period.
  • present_value: The present value or the total amount that a series of future payments is worth right now.
  • [future_value]: [Optional] The future value or the cash balance you want to attain after the last payment is made.
  • [end_or_beginning]: [Optional] A logical value that specifies whether payments are due at the end or beginning of the period.

Step-by-Step Tutorial

  1. Calculating the number of payment periods for a loan:

    • Example: =NPER(0.05/12, -200, 8000)
    • Result: 48

  2. Calculating the number of periods for an investment:

    • Example: =NPER(0.07/12, 100, 1000, 0, 1)
    • Result: 9.84

Use Cases and Scenarios

  1. Retirement Planning: Determine the number of years to save for retirement based on regular contributions.
  2. Mortgage Analysis: Calculate the number of monthly payments required to pay off a mortgage.
  3. Loan Evaluation: Evaluate the number of payment periods required to pay off a loan.

Related Functions

  • PV: Calculate the present value of an investment.
  • FV: Calculate the future value of an investment.
  • PMT: Calculate the periodic payment for an annuity.

Related Articles