May 22 2020 01:06 PM
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
May 22 2020 02:40 PM
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.
May 24 2020 10:39 AM
@mathetes Let me give it a shot!
May 24 2020 01:14 PM