Forum Discussion
Years required to pay back a loan
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.
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)
- 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?