Forum Discussion

yestodd's avatar
yestodd
Copper Contributor
May 22, 2020

If loan is X, and rate is Y, and I pay Z each payment, how many payments until pd in full?

I have an amortizing loan formula solving the problem, "If my loan amount is X, and my interest rate is Y, and I pay Z each payment, how many payments will I make to pay off the loan?

I solved it by taking the loan balance*rate /12=x, loan balance-x*rate/12 etc until loan balance =<0.  

I also added a column to the left that counted the number of rows that tells me the number of payments.  I need the number in that cell  next to where the loan balance turns negative in a different formula

excel
Formulas and Functions

3 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    May 24, 2020
    You could also try the NPER function:
    =ROUNDUP(NPER(Rate/12,-Payment,Loan Balance),0)

    Note the payment is entered as a negative.

  • mathetes's avatar
    mathetes
    Gold Contributor
    May 22, 2020

    yestodd 

     

    IF I understood you correctly--and it's entirely possible that I didn't--then this formula does what you want.

    =INDEX(U26:U386,MATCH(0,V26:V386,-1))

     

    This retrieves the number 244, and I think that's what you wanted the formula to return. It does it by first finding the spot in the balance column where the numbers is just about to turn negative, and then gives that "index" to Excel to find the corresponding payment number in column "U"

     

    If I that's NOT what you were looking for, come back and try to describe it a bit more carefully.