How to Use the Nper Function in Excel

What is the NPER Function?

The NPER function is categorized under Excel Financial functions Functions List of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst . The function helps calculate the number of periods that are required to pay off a loan or reach an investment goal through regular periodic payments and at a fixed interest rate.

In financial analysis Financial Analyst Job Description The financial analyst job description below gives a typical example of all the skills, education, and experience required to be hired for an analyst job at a bank, institution, or corporation. 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:

1. Rate (required argument) – This is the interest rate per period.
2. Pmt (required argument) – The payment made each period. Generally, it contains principal and interest but no other fees and taxes.
3. Pv (required argument) – The present value, or the lump-sum amount that a series of future payments is worth right now.
4. Fv (optional argument) – This is the future vale or the cash balance which we want at the end after the last payment is made. When omitted, it takes the value as zero.
5. Type (optional argument) – Indicates when payments are due. If type is set to 0 or omitted, then payments are due at the end of the period. If set to 0, payments are due at the start of the period.

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 a 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 future 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 months.

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 additional monthly contributions of $5,000. Let's now calculate the number of monthly investments required to earn $500,000.

The formula to be used is:

We get the result below:

A few things to remember about the NPER Function:

1. Remember that, in line with the cash flow convention, outgoing payments are represented by negative numbers and incoming cash flows by positive numbers.
2. #NUM! error – Occurs if the stated future value will never be met or the supplied periodic interest rate and payments are insufficient. In such a scenario, we will need to increase the payment amount or increase the interest rate to achieve a valid result.
3. #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 CFI resources:

• Excel Functions for Finance Excel for Finance This Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel.
• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must Know Advanced Excel Formulas Must Know These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Download our free Excel ebook!
• Excel Shortcuts for PC and Mac Excel Shortcuts PC Mac Excel 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

How to Use the Nper Function in Excel

Source: https://corporatefinanceinstitute.com/resources/excel/functions/nper-function/

Share :