Explanation

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the NPER function is to calculate the number of periodic payments for loan.

For this example, we want to calculate the number of payments for a $5000 loan, with a 4.5% interest rate, and fixed payments of $93.22. The NPER function is configured as follows:

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest:

=C6/12

pmt - The payment made each period. This is the known amount $93.22, which comes from cell C7.

pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in front of C5:

-C5

fv - the future value, or desired balance after last payment. We do not provide a value since the default is zero and makes sense for a loan.

type - When payments are due, where 0 = end of period and 1 = beginning of period. Default is 0 so we do not provide a value.

With these inputs, the NPER function returns 59.996, which is rounded to 60 in the example, representing 60 months = 5 years. The monthly payment is rounded to the nearest penny, which causes NPER to return a number slightly less than 60.

Explanation

The NPER function returns the number of periods for loan or investment. You can NPER to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. An annuity is a series of equal cash flows, spaced equally in time.

The goal in this example is to calculate the years required to save 100,000 by making annual payments of $5,000 where the interest rate is 5% and the starting amount is zero. Payments are made annually, at the end of each year.

To solve for periods, the NPER function is configured like this:

=NPER(C6,-C7,-C4,C5,0)

where:

  • rate - from cell C6, 5%.
  • pmt - from C7, $5,000 (entered as negative value)
  • pv - from cell C4, 0.
  • fv - from cell C5, 100000.
  • type - 0, payment at end of period (regular annuity).

With this information, the NPER function returns 14.20669908 years. Note both payment and present value are entered as negative values, since these are cash outflows.