What is the NPER Function?
The NPER function is categorized under Financial functionsFunctionsThe NPER function is categorized under Financial functions. The function helps calculate the number of periods that are required to pay off a loan through. The function helps calculate the number of periods that are required to pay off a loan through regular periodic payments and a fixed interest rate.
In financial analysisFinancial Analyst Job DescriptionSee a real Financial Analyst Job Description including all the skills, experience, and education required to be the successful candidate for the job. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models, we often wish to build a corporate fund. The NPER function will help us know the number of periods required to reach our target amount. It can also be used to get the number of payment periods for a loan that we wish to take.
Formula
=NPER(rate,pmt,pv,[fv],[type])
The NPER function uses the following arguments:
- Rate (required argument) – It is the interest rate per period.
- Pmt (required argument) – It is the payment made each year. Generally, it contains principal and interest but no other fees and taxes. It cannot be changed over the life of the annuity.
- Pv (required argument) – It is the present value, or the lump-sum amount that a series of future payments is worth right now.
- Fv (optional argument) – It is the future vale or the cash balance which we want at the end that is after the last payment is made. When omitted, it shall take the value as zero.
- Type (optional argument) – It indicates when payments are due. If type is set to 0 or omitted payments are due at the end of the project. If set to 0, payments are due at the project’s start.
How to use the NPER Function in Excel?
As a worksheet function, NPER can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let’s consider an example:
Example 1
Let’s assume we need $50,000 and the loan will be given to us at a 5% interest rate with a monthly payment of $500. Let’s now calculate the number of periods required to repay the loan.
The formula to be used would be:
We get the result below:
In the above example:
- We input the payment for the loan as a negative value, as it represents an outgoing payment.
- Payments are made monthly, so we needed to convert the annual interest rate of 5% into a monthly rate (=5%/12).
- We projected the value as zero, and that the payment is to be made at the end of the month. Hence, the [fv] and [type] arguments can be omitted from the function call.
- The value returned by the function is in months. We then rounded the result to the nearest whole month, that is 130 months or 10 years, 10
Example 2
Let’s assume we wish to make an investment of $10,000 and want to earn $500,000. The annual interest rate is 5%. We will make a monthly payment of $5,000. Let’s now calculate the number of monthly investment required to earn $500,000.
The formula to be used is:
We get the result below:
Few things to remember about the NPER Function:
- Remember that, in line with the cash flow convention, outgoing payments are represented by negative numbers and incoming cash flows by positive numbers.
- #NUM! error – Occurs if the stated future value will never be met or the supplied periodic interest rate and payments are insufficient. In such scenario, we will need to increase the payment amount or reduce the interest rate to achieve a valid result.
- #VALUE! error – Occurs if any of the given arguments are non-numeric values.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts