Excel 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: 