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

Updated 2014.01.09

# Objective:

·
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.

# NPER Arguments:

·
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.

## Example: How long
will it take to pay off a loan?

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?

#### Solution

·
**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: