• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

NPER in Excel

Home » Excel » Blog » Financial Functions in Excel » NPER in Excel

NPER in Excel

NPER in Excel (Table of Content)

  • Introduction to NPER in Excel
  • How to Use NPER in Excel?

Introduction to NPER in Excel

NPER in excel is a financial function which calculates a number of periods which are required to completely pay off the loan amount with an assumption of a fixed monthly rate of interest along with fixed payments/EMI. It simply allows you to build a model where you wish to find out a number of periods required to completely reach out the final loan amount.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Syntax

NPER syntax

Argument in NPER Formula

  • rate – is a required argument that specifies interest rate per period.
  • pmt – is a required argument that specifies the amount paid at each period which contains principle amount and interest rate (excluding taxes and other fees).
  • pv – is a required argument that is nothing but the actual loan amount.
  • fv – optional argument which specifies the future value of the loan amount.
  • type – optional argument that specifies whether the payment is to be made at the start of the period or at the end of the period: 0 – payment made at the end of the period, 1 – payment made at the start of the period.

How to Use Excel NPER in Excel?

NPER in Excel is very simple and easy. Let’s understand how to use the NPER in Excel with a few examples.

You can download this NPER Formula Excel Template here – NPER Formula Excel Template

Example #1 – NPER Simple Example in Excel

Mr. Akshay has taken a loan of 1,50,000 for higher studies. He agreed to pay the amount with a rate of interest as 10.28% per annum and is able to make a payment of 10,000 per month. However, he is not so sure when he can clear the loan amount. We will help him out to find this out with the help of the NPER function in Microsoft Excel.

In cell B1, put the value of the Loan Amount.

NPER Formula in excel example 1-1

In cell B2, Input the Rate of Interest per Year Value (Which is 10.28% in case of Mr. Akshay).

NPER Formula in excel example 1-2

In cell B3, put the value of Monthly EMI which Mr. Akshay can pay on behalf of his Loan Amount.

Popular Course in this category
Cyber Week Sale
Excel for Finance Training (15 Courses, 5+ Projects) 15 Online Courses | 5 Hands-on Projects | 74+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (1,078 ratings)
Course Price

View Course

Related Courses
Excel Data Analysis Training (12 Courses, 6+ Projects)Excel for Marketing Training (5 Courses, 12+ Projects)

NPER Formula in excel example 1-3

We need to find out the period under which Mr. Akshay can pay this amount with monthly EMI as 10,000 and rate of interest as 10.28% per annum. This value, we need to calculate in cell B4.

In cell B4, start typing the formula of NPER.

NPER Formula in excel example 1-4

Mention B2/12 as the first argument for NPER formula. As a given Rate of Interest is per year, we need to break it down to the interest rate per month because we are paying the amount per month.

NPER Formula in excel example 1-5

Now, next argument is the amount to be paid at each period, since Mr. Akshay is ready to pay the EMI amount of 10,000 per month (Cell B3), you will mention it as the next argument in the formula. However, please note that this amount is a cash outflow (amount which is going to be deducted from Mr. Akshay’s account). Therefore, you need to mention it as a negative amount.

excel example 1-6

Input the loan amount Mr. Akshay has taken from bank i.e. 1,50,000 (which is stored in cell B1).

excel example 1-7

Press Enter key.

excel example 1-8

You can see that with the rate of interest as 10.28% per annum and monthly EMI of 10,000, Mr. Akshay can clear/repay his loan amount of 1,50,000 within 16.12 months.

Please note that in this formula we have not included fv (future value of loan) and type (whether the EMI is being paid at the start of the month or at the end of the month) as these are the optional arguments.

Example #2 – Calculate Period for Future Value Increment

Mr. Sandeep a 25-year-old engineer, wants to make some investment for his retirement. He wishes to have a lumpsum amount of 20,00,000 at the time he retires and ready to invest 1,00,000 today (present value). The yearly rate of interest is 5% and Mr. Sandeep is ready to pay a monthly EMI of 5,000. We want to calculate the number of months Mr. Sandeep requires to earn 20,00,000.

In cell B1 of the Excel sheet, input the Annual Rate of Interest.

Calculate Period for Future Value Increment 1

In cell B2, mention the payment amount Mr. Sandeep is ready to pay every month.

Calculate Period for Future Value Increment -2

In cell B3, add the present value of the investment which Mr. Sandeep is about to make.

Calculate Period for Future Value Increment -3

In cell B4, the future value will take place. The value which Mr. Sandeep wants as a lumpsum amount at the time of his retirement.

Calculate Period for Future Value Increment -4

In cell B5, start typing the NPER Formula.

Calculate Period for Future Value Increment - 5

Put B1/12 as a first argument under NPER Formula. As the given rate of interest is per annum, you need to divide it by 12 in order to get a monthly rate of interest (because Mr. Sandeep is paying installment monthly).

Calculate Period for Future Value Increment - 6

Mention -B2 as next argument in this formula. As the value 5,000 is going to be debited from Mr. Sandeep’s account (outgoing amount), we need to mention the negative sign for the same.

Calculate Period for Future Value Increment - 7

Next argument would be the Present Value of the Investment which is in cell B3.

NPER example 2-8

Lastly, mention B4 as an argument that represents the Future Value of Investment.

NPER example 2-9

Press the Enter key and you will get the value of the number of months, Mr. Sandeep has to make an investment in order to get 20,00,000 as a final amount when he retires.

NPER example 2-10

Here, if Mr. Sandeep makes a payment of 5,000 per month for the investment amount of 1,00,000, it will require 256.82 months (21.40 years) to get matured up to the lumpsum amount of 20,00,000.

Things to Remember About NPER in Excel

  • PMT under NPER function generally includes interest amount. But not the taxes and additional processing fees.
  • Rate of interest should be homogeneous throughout the period. For example, in the above examples, we have divided the yearly interest rate by 12 to make it homogeneous through the year for each month.
  • The payments which are outgoing, all need to be considered as debts and marked as negative payments.
  • #VALUE! error occurs when one of the required arguments from the formula is non-numeric.
  • #NUM! error occurs when the future value mentioned will not meet over the period of time with the mentioned rate of interest and EMI (investment amount). In that case, we need to increase the monthly investment amount. Also, this error occurs when outgoing payments are not marked as negative.

Recommended Articles

This is a guide to NPER in Excel. Here we discuss how to use NPER in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Calculating IRR Formula in Excel
  2. Pivot Table Formula in Excel
  3. Creating Chart Wizard in Excel
  4. OR Formula in Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

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

Primary Sidebar
Excel Functions Tutorials
  • Statistical Functions in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • VBA Function in Excel
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • VBA (180+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

© 2019 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download NPER Formula Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Limited Period Offer - Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More