Internet amortization answers differ from function

[Howdego]

I have figured the nper for a loan on a number of amortization schedules on
the net. However, when I use the npr function in Excel, I get a different
number of periods. Why would this be? For example, in excel for a 2.9/12 %
loan, 16,472 pv , 0 fv and a $505 per month payment, I get 31.4 months of
payments. However, on the internet programs, i get 34 months. Does Excel
figure this differently?

 

[John Bundy]

I get 34 in excel, did you convert your rate correctly?
Rate cell=0.029/12
PMT cell=-505
PV cell=16472

The formula would look like this
=NPER(0.002417,-505,16472)

 

[Howdego]

Ah! I see. I didn't put the minus sign on the payment line. I get the 34
months as you do when it is in, but 31 months when it is out. Do you know
why that is so? Is that considered an illogical input?

 

[joeu2004]

in excel for a 2.9/12 % loan, 16,472 pv , 0 fv and a $505 per month
payment, I get 31.4 months of payments.

The correct syntax is 2.9%/12. But that cannot be your problem
because 2.9/12% results in a huge monthly rate, and NPER() reports a
computation error.

My guess is: you entered the payment as 550 instead of 505. In that
case, NPER() returns 31.1 -- close to 31.4 (yet-another typo?).

(If you also typed 2.3%/12 or 2.4%/12 instead of 2.9%/12, NPER()
returns 31.4 rounded to one decimal place.)

Suffice it to say: whenever you have a question about function usage,
it is much easier for people to help you if you show exactly how you
use the function. By "exactly", I mean cut-and-paste.

Does Excel figure this differently?

Not really. But note that another program might (correctly) round
certain amounts, and that could account for a small difference. That
is probably not that case here.

Caveat: Some variation might also arise if you use different
assumption in your Excel usage than another program does. For
example, for Canadian loans, the interest is "compounded
semiannually". You can get the correct results using Excel; but it is
not as simply as 2.9%/12.

Again, providing more information might result in more helpful
feedback. For example, what is the URL of the "internet program" --
if by that you mean online calculator?

 

[John Bundy]

It depends on what you are calculating whether or not it matters, but for
safety alway use negative payments. You have (generally) a starting amount
that you are trying to reduce, and you do that with "payments" which is
actually just decreasing the 16472 by 505.

 

[Fred Smith]

Howdego,

When you used Nper(2.9%/12,505,16472) the result you got was -31 months
(that's *minus* 31 months). So that's your clue that you entered something
wrong.

Excel uses the sign to show the direction of cash flow. By convention,
negative numbers are cash flowing out of your pocket, positive numbers are
cash into your pocket. If you borrowed $16,472, this is a positive number,
because you received the cash. Every month, you fork out $505, so this
number is negative.

In practice, as long as you have opposite signs for opposite cash flows, you
will get the correct results. That is Nper(2.9%/12,-505,16472) will yield
the same results as Nper(2.9%/12,505,-16472)

Regards,
Fred.