What is the NPER Function?
The NPER function is categorized under Financial functions. 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 analysis, 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.
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:
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
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.
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: