Use the NPER function in Google Sheets to calculate the number of periods (payment periods) for a loan or investment. Needless to say, the payment must be constant periodic and also the interest rate must be fixed.

That means, with the help of the NPER financial function, you can calculate the number of months/quarter/years required to pay off a loan in full or reach an investment goal.

To return the number of payment periods using the NPER function, you must input the following values in Google Sheets.

See the Syntax of the NPER function and its arguments to understand the said input values.

Syntax:

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

Arguments:

1. rate – The annual interest rate.
2. payment_amount – The amount of each (periodic) payment (PMT) which includes principal payment (PPMT) and interest payment (IPMT).
3. present_value – The present value (PV) (sum of all the payments).
4. future_value – The future value (FV) or the cash balance that you want to attain after the final payment. This is optional and default to 0.
5. end_or_beginning – payment is due at the end or beginning of each period. This is also optional. The default value is 0 (payment at the end of the period).

Now let’s see how to calculate the number of payment periods using the NPER function in Google Sheets.

Examples to the Use of NPER Function in Google Sheets

Please try to input the annual interest rate. We can use the annual interest rate as it is in the formula if you are making the payment on a yearly basis.

If the payment is made quarterly, then within the formula, divide the annual interest rate by 4 and for the monthly payments, divide the interest by 12.

In the below formula examples, I have included the detail. Let’s calculate the number of periods required to pay off a loan.

Calculate the Number of Periods Required to Pay Off a Loan in Google Sheets

NPER Yearly

Assume you are planning to get a loan amounting to $30,000.00. The annual interest rate is 4.5%. You are willing to pay $6,000.00 at the end of each year.

You can calculate the number of periods required to pay off the loan in full using the NPER function in Google Sheets. See that formula.

Payment is at the end of each year. So only included the first three arguments.

=NPER(4.5%,-6000,30000)

If you make the payment at the beginning of each year, modify the formula as below.

=NPER(4.5%,-6000,30000,0,1)

The 0 in the formula indicates the future value and 1 indicates the payment is made at the beginning of each period. Future value is set to 0 since you are paying off the loan.

Even if the future value is 0, when you want to specify the “end_or_beginning” argument as above, it must be included. Because future value is the fourth argument and “end_or_beginning” is the fifth.

The payment has shown as negative as it’s outgoing. Please see the “Tips” at the end of this post.

NPER Quarterly

When you want to pay off the loan in Quarterly basis, use the NPER formula as below.

Changes Required: Since you have the annual interest rate, you must divide it by 4 to get the quarterly rate.

I am willing to pay $2,000.00 every quarter to pay off a loan amount of $30,000.00. See the formula below that returns the number of quarterly payments to be made.

=nper(4.5%/4,-2000,30000)

NPER Monthly

In monthly NPER calculation just divide the annual interest rate by 12. So I am skipping the example here.

Calculate the Number of Periods Required to Reach an Investment Goal in Google Sheets

Here we can include the future value (FV argument).

You can easily calculate how many months/quarter/year you need to pay to reach an investment goal @ 4.5% annual interest rate.

Suppose you would like to save (deposit) an amount of $500.00 at the beginning of every month. Your bank interest rate is 4.5% (annual) and your goal to reach is $20,000.

If so, you can calculate the total number of the period required to reach that goal using the NPER function in Google Sheets.

As per the formula, you need 37 months to reach that goal.

Tips:

In all my formulas above, I have shown the payments (PMT) as negative values. All the other parameters are positive in the formulas. But you may have seen some of the users showing positive values as payment (PMT) in the formula. In such cases, you must show the PV in negative as well as the FV value, if any, in negative.

Example:

In D7:

=nper(D2/12,D3,D4,D5,D6)

In H7:

=nper(H2/12,H3,H4,H5,H6)

That’s all. Enjoy.