NPER Function in Google Sheets

What is NPER Function in Google sheets?

The NPER function in Google Sheets calculates the number of payment periods for an investment or loan based on constant, periodic payments and at a constant interest rate. It is a handy function for financiers or anyone who wishes to manage their loans and investments with efficiency. The function has five parameters, which include the interest rate, the amount to be paid per period, the total investment amount, the future value, and whether the amount is paid at the beginning or end of each period.

For instance, you have a loan of $1,000 and are paying $50 per month with an annual interest rate of 5%. Use the following formula to find out how long it will take to pay off the loan. Here, since we pay monthly, the interest per month is 5%/12 = 0.416%. As it’s an outflow, we write -50. The future value is zero as we pay off the entire loan. Let us look at the result.

=NPER(0.416%, -50, 1000, 0)

NPER Function in Google sheets Definition

The loan can be paid off in 20.9 months.

Key Takeaways
  1. The NPER function in Google Sheets is used to calculate the number of periods required to pay off a loan or reach a financial goal, given constant periodic payments and a constant interest rate.
  2. The syntax of NPER is as follows: =NPER(rate, payment, present_value, [future_value], [end_or_beginning])
    • rate: interest rate per period
    • payment: The amount paid each period
    • present_value: the principal amount
    • future_value (optional): Amount of money at the end of the loan/investment
    • end_or_beginning (optional): Specifies whether payments are made at the end or the beginning of each period.
  3. The NPER function is commonly used for loan repayment and investment planning to find the duration for which the amount should be paid.

Syntax

Let us look at the syntax of the NPER function and its parameters to understand the function in detail.

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

  • rate – The interest rate calculated per period.
  • payment_amount – The payment amount for each period.
  • present_value – The current value of the annuity or loan amount.
  • future_value – (optional) – The future value remaining after the final payment has been made.
  • end_or_beginning – (optional)] – Specifies whether the payments are due at the end (0) or beginning (1) of each period. The default is 0.

The Google Sheets function NPER returns the number of periods required to repay the loan or investment when we supply the following inputs.

Note:

  • When paying on a yearly basis, we input the annual interest rate.
  • If the payment is made quarterly, we must divide the annual interest rate by 4
  • If the payment is monthly, divide the interest by 12.

How to Use NPER Function in Google sheets?

The NPER function in Google Sheets allows you to calculate the number of months/years/quarters required to pay off a loan or reach an investment goal. So, is the 4 formula as simple as it looks? Let’s look at the steps to calculate the NPER value in Google Sheets.  

There are two ways to enter the NPER function in Google Sheets.

#1 – Entering the NPER Function Manually

Step 1: Open the Google Sheet where you have your investment/loan data. The details to be input include the interest rate, payment amount, present value, future value(optional), and whether payments are at the beginning or end of the period (optional).

Entering NPER Function manually 1

Step 2: In cell B5, we enter the NPER function with the necessary parameters. Since we have already entered the arguments in a sheet, we just use their cell references.

=NPER(B1/12, -B2, B3, B4)

Here, B2 is negative since it is an outflow. Since the payment is monthly, we divide the annual interest rate by 12.

Entering NPER Function manually 1-1

Step 3: Press Enter. We get the number of payment periods for the same.

Entering NPER Function manually 1-2

#2 – Using the Google Menu Bar

  • We can also enter the same function using the Google Menu bar.
  • Go to the menu bar and click on “Insert” “Function” “Financial” “NPER.”
  • Enter the required arguments. Close the bracket and press the “Enter” key.
Using the Google Menu Bar

Examples

Let’s look at a few examples to gain clarity on using the NPER function in Google Sheets. We use the different interest rates based on the payment periods and find how to calculate the time taken for payments.

Example #1 – NPER Yearly – Calculate the Number of Periods Required to Pay Off a Loan

Suppose X has a $25,000 loan at an annual interest rate of 5%. He plans to make yearly payments of $2,500. Let’s try to find out how many years it will take to pay off the loan completely.

Step 1: Enter the details in a Google sheet

Annual Interest Rate: 5%

Loan Amount: $25,000

Yearly Payment Amount: -$2,500 (negative because it’s an outflow)

NPER Function in GS Example 1

Step 2: Enter the following function in cell B4 of your sheet.

=NPER(B1, B2, B3)

Since it is a yearly payment, we do not make any changes to the annual interest rate.

NPER Function in GS Example 1-1

Step 3: Press Enter. The formula will return the total number of periods (months) needed to pay off the loan.

NPER Function in GS Example 1-2

You can pay off the loan in 14.2 years. If you make the payment at the beginning of each year, you must modify the formula as shown.

=NPER(B1, B2, B3,0,1)

The 0 indicates the future value, and 1 means the payment is made at the beginning of each period.

Example #2 – NPER Quarterly – Calculate the Number of Periods Required to Pay Off a Loan

Now, let us look at how to pay off a loan quarterly. To do this, you must use the NPER formula, as shown below.

Step 1: Enter the details in a Google Sheet.

Annual Interest Rate: 6%

Loan Amount: $15,000

Quarterly Payment Amount: -$1,000 (negative because it’s an outflow)

NPER Function in GS Example 2

Step 2: Since we are given the annual interest rate, divide it by 4 to get the quarterly rate.

Here, we are paying $1000 every quarter to pay off a $15,000 loan. Let us see how many quarters it takes to pay off the loan amount. We pay it at the end of each quarterly period.

=NPER(B1/4,B2,B3)

NPER Function in GS Example 2-1

Step 3: Press Enter. You get the number of quarterly periods you need to pay off this loan amount.

NPER Function in GS Example 2-2

It is around 17.1 quarters or 4 years approximately.

Example #3 – NPER Monthly – Calculate the Number of Periods Required to Pay Off a Loan

Let us use the same data as above for the monthly NPER calculation, except for the monthly payment. You must just divide the annual interest rate by 12. Also,, let us calculate NPER for payments made at the beginning of each period.

Annual Interest Rate: 6%

Monthly Payment Amount: -$300 (negative because it’s an outflow)

Loan Amount: $15,000

Step 1: Using the NPER function in Google Sheets, you can calculate the total period required to repay the loan.

Enter the NPER formula Google Sheets.

=NPER(B1/12,B2,B3,0,1)

NPER Function in GS Example 3

Step 2: Press Enter. You will repay the loan in 57.3 months or just before 5 years.

Note that we always make the periodic payment amount a negative value as it is an outflow. If you use a positive value for it, you must show the PV and FV values in negative.

Important Things to Note

  1. Always remember to adjust your interest rate for different frequencies. If your payments are monthly, and you have the annual interest rate, adjust the rate accordingly. For annual payments, use the annual rate and for quarterly, you must divide the annual rate by 4.
  2. Ensure that the rate and amounts are in the right format to avoid errors.
  3. Remember that the NPER function assumes regular, fixed payments throughout the period of the. Do not use the function for varying amounts as it could lead to erroneous calculations.

Frequently Asked Questions (FAQs)

What are the benefits of using the NPER function in Google Sheets?

The NPER function is very useful for financial calculations in Google Sheets when used for loan and investment planning.

1. It is simple and easy to calculate the number of periods and can be done by anyone.
2. It helps in financial planning by calculating the duration of loan and investment payments.
3. It helps you plan investments and set goals for future savings. Based on your payment amount helps you determine the required time to achieve your target.
4. Users can calculate the payoff period for their loan amounts, which makes it extremely useful for loan planning.
5. It helps ease understanding of different payment schedules, be they quarterly or monthly.

Why do we use a negative sign for the payment argument in NPER?

The payment value is usually written with a negative sign in the formula because it represents an outflow of money. In this function, outflow of money is treated as negative, while inflow is considered positive. So, the loan payment or amount of investment is input as a negative number.

How to calculate the number of periods for an investment goal?

In case of an investment, where you actually save a certain amount during every period to reach a particular future value, the payment argument is entered in the NPER function and the future value argument should also be entered. For instance,

=NPER(0.06/12, -200, 0, 5000)
0.06/12 is the monthly interest rate.
-200 is the monthly saving amount (a negative cash flow).
0 is the present value as you are starting from zero.
5000 is the target future value.

Download Template

This article must help understand NPER Function in Google Sheets with its formula and examples. You can download the template here to use it instantly.

Guide to What is the NPER Function in Google Sheets. We explain how to use NPER function in Google Sheets with examples. You can learn more from the following articles.

Types of Charts in Google Sheets

COSH Function in Google Sheets

Word Count in Google Sheets

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *