Excel 2016: Using NPER to determine how long it will take to pay off a loan

Updated 2014.01.09

· Learn how to use the NPER function to determine how many periods it will take to reach an investment goal when making regular constant payments at a regular interest rate.

·
RATE: rate __per period__, not the annual
rate

· PMT: Payment—always a negative number

· PV: Present value. The amount you have in the bank right now (negative) if you are doing a savings problem, or the amount you owe (positive) if you are doing a loan problem.

· FV: Future value. The amount you will have in the bank at the end of the number of periods if you are doing a savings problem, or the amount you will owe at the end of the number of periods if you are doing a loan problem (usually 0). This number is always positive (or 0).

· TYPE: Type of payments. 1=payments at the beginning of the period, 0=payments at the end.

You need to borrow $20,000. You know
that you can borrow the money at 6.00% (annual rate) and make monthly payments
at the __end__ of each month. You can afford to pay off $250 a month to
repay the loan. How long will it take to pay off the loan?

·
**Rate Per Period**: 6.00% annual rate,
divided by 12 periods per year = .06/12 = .005.

·
**Payment**: $250 (this will be negative,
since it represents money that you are paying out).

·
**Present value**: $20,000 (this will be
positive, since it represents money that you have right now).

·
**Future value**:** **$0.

·
**Type**: 0=end of period, 1=beginning of
period. We are paying at the end, so our type is: 0.

The NPER arguments are: NPER (**rate,
pmt, pv, **fv, type)

So the formula is:

=NPER(.06/12, -250, 20000, 0, 0)

Again, I used constants for clarity, but you should use cell references for arguments (see the example below).

The **pmt** is negative because it
represents money that is being "paid out" to the bank (even though it
is going into *your* account).

The **pv** argument is negative
because it represents the sum of all money that has previously been "paid
out" to the bank.

If you use cell references, you get: