# calculate number of payments (using NPER)

#### alexaronson

##### Active Member
Hello,

I am trying to use the function NPER to calculate the number of payments it would take to eliminate some debt using a few constants.

In the NPER formula, I have the following
RATE = 12%/12 (12% APR, 12 months in a year)
PMT = 60
Pv = 3000
Fv = 0

The results of the formula = -40.7

However, when evaluating it by creating an amortization table, the number of periods requires 70 months to pay the debt off. The way I created the amortization table is by taking:
Debt = A1
Rate = B1
Payment = C1
Cell A2 = A1+A1*B\$1\$/12-C\$1\$
Cell A3 = A2+A2*B\$1\$/12-C\$1\$
Cell A3 is copied down to A4, A5, A6, A7, and so forth until the last cell has a starting balance of 0 or negative.

Is my amortization table correct, or is NPER populated wrong?

Alex in Memphis, TN

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### tusharm

##### MrExcel MVP
Being told that you would pay off the debt in negative 40.7 months should have raised a alarm!

In using the various finance functions, you have to be careful with the signs of the inflows and outflows. Excel treats inflows as positive numbers and outflows as negative numbers. So, if you use =NPER(B3/12,-B4,B2) where B3 is the rate, B4 is the payment and B2 in the present value, the result would be 69.66071689 months.
Hello,

I am trying to use the function NPER to calculate the number of payments it would take to eliminate some debt using a few constants.

In the NPER formula, I have the following
RATE = 12%/12 (12% APR, 12 months in a year)
PMT = 60
Pv = 3000
Fv = 0

The results of the formula = -40.7

However, when evaluating it by creating an amortization table, the number of periods requires 70 months to pay the debt off. The way I created the amortization table is by taking:
Debt = A1
Rate = B1
Payment = C1
Cell A2 = A1+A1*B\$1\$/12-C\$1\$
Cell A3 = A2+A2*B\$1\$/12-C\$1\$
Cell A3 is copied down to A4, A5, A6, A7, and so forth until the last cell has a starting balance of 0 or negative.

Is my amortization table correct, or is NPER populated wrong?

Alex in Memphis, TN

#### alexaronson

##### Active Member
Thanks for the help. I am satisfied with the results I am now getting.

Replies
1
Views
174
Replies
13
Views
283
Replies
5
Views
113
Replies
9
Views
775
Replies
4
Views
474

1,191,219
Messages
5,985,328
Members
439,958
Latest member
qb0000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back