logo-v-light

Our Service

NPER

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)

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.