calculate number of payments (using NPER)

[alexaronson]

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?

Thanks for any help you can give me.

Alex in Memphis, TN

 

[tusharm]

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?

Thanks for any help you can give me.

Alex in Memphis, TN

 

[alexaronson]

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