Years required to pay back a loan

[UBComma]

I am trying to calculate how many years it will take to pay back a loan if I have a given amount paid per year. For example, If I borrow $100,000 at 3.75% annual and I pay $3,000 per year, how long will it take before the loan amount is paid off? I have tried using NPER but the result doesn't seem right. I entered it as =NPER(0.0375,3000,100000) and get 22 Years, which can't be correct since 22 payments * $3,000 is only $66,083. What am I doing wrong?

Any help will be appreciated.

[#2]

@UBComma 

The payment should be negative (amounts you receive are positive, amounts you pay are negative). However, your problem has no solution.

3.75% interest of $100,000 is $3,750, so you won't be able to pay back the loan with a yearly payment of $3,000.

But with a yearly payment of for example $5,000 it would be possible:

=NPER(3.75%,-5000,100000)

[#3 · Accepted solution]

@UBComma  wrote: ``I entered it as =NPER(0.0375,3000,100000) and get 22 Years``

No.  That returns about -22.  That is your clue that something is wrong with your use of NPER.

In fact, it should be =NPER(0.0375,3000,-100000).

But that returns #NUM because 3000 is not sufficient to cover even just interest for the first period.  That is, 100000*3.75% = 3750.

Your payment should be more than 3750 in order to reduce the loan periodically.

For example, for a 30-year loan, the annual(!) payment would be
=PMT(3.75%,30,-100000), which returns about 5609, much more than 3000 that you expect.

OTOH, if you are making monthly payments,
=ROUNDUP(NPER(3.75%/12, 3000, -100000),0) returns 36 months, which is 3 years.

-----

PS....  I notice that HansV uses negative pmt and positive pv, whereas I use positive pmt and negative pv.  Both are equally valid.

[#4]

Hans,
Thank you for your reply. I recall that the payment needs to be a negative number although I didn't show it that way in my example. When I use this formula and I get a negative number for the number of periods, that means the payment is too low and the principle amount will never be paid off?

[#5]

@UBComma 

I get a positive number: