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