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.
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.