The NPER function in Excel is used to calculate the number of periods required to pay off a loan or investment based on a constant payment and a constant interest rate. This function is useful for financial analysis and planning, as it allows users to determine how long it will take to pay off a loan or investment, and how much interest will be paid over the life of the loan or investment.
NPER Syntax
The syntax for the NPER function is as follows:
=NPER(rate, payment, present value, [future value], [type])
- rate: The interest rate per period.
- payment: The payment made each period.
- present value: The present value of the loan or investment.
- future value: (Optional) The future value of the loan or investment. If omitted, the future value is assumed to be 0.
- type: (Optional) The type of payment made each period. If omitted, the type is assumed to be 0 (payments made at the end of each period).
NPER Examples
Suppose you take out a loan of $10,000 at an annual interest rate of 5%, to be paid back in monthly installments of $200. Using the NPER function, you can calculate how many months it will take to pay off the loan:
=NPER(5%/12, -$200, $10,000)
The result is 63.85, which means it will take approximately 64 months (or 5 years and 4 months) to pay off the loan.
Another example is if you want to know how much you need to save each month to reach a savings goal of $50,000 in 10 years, assuming an annual interest rate of 3%. Using the NPER function, you can calculate the monthly savings required:
=NPER(3%/12, -$50,000, 0)
The result is 120.27, which means you need to save approximately $417.67 per month for 10 years to reach your savings goal.
NPER Tips & Tricks
- The NPER function assumes that payments are made at regular intervals (e.g. monthly, quarterly, annually).
- The NPER function returns the number of periods required to pay off a loan or investment, rounded up to the nearest whole period.
- The NPER function can be used in conjunction with other financial functions, such as PV, FV, and PMT, to perform more complex financial calculations.
Common Mistakes When Using NPER
- Forgetting to convert the interest rate to the appropriate periodic rate (e.g. monthly, quarterly, annually).
- Using the wrong sign for the payment and present value arguments. Payments should be entered as negative values, while present values should be entered as positive values.
- Forgetting to include the future value argument, which can result in an inaccurate calculation.
Why Isn’t My NPER Working?
- Check that the interest rate, payment, and present value arguments are entered correctly and in the correct order.
- Make sure that the future value argument is included if necessary.
- Check that the type argument is entered correctly (0 for payments made at the end of each period, 1 for payments made at the beginning of each period).
- Ensure that the payment and present value arguments have opposite signs.
NPER: Related Formulae
- PV (Present Value)
- FV (Future Value)
- PMT (Payment)
- IPMT (Interest Payment)
- PPMT (Principal Payment)
