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

%3CLINGO-SUB%20id%3D%22lingo-sub-1412574%22%20slang%3D%22en-US%22%3EIf%20loan%20is%20X%2C%20and%20rate%20is%20Y%2C%20and%20I%20pay%20Z%20each%20payment%2C%20how%20many%20payments%20until%20pd%20in%20full%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412574%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20amortizing%20loan%20formula%20solving%20the%20problem%2C%20%22If%20my%20loan%20amount%20is%20X%2C%20and%20my%20interest%20rate%20is%20Y%2C%20and%20I%20pay%20Z%20each%20payment%2C%20how%20many%20payments%20will%20I%20make%20to%20pay%20off%20the%20loan%3F%3C%2FP%3E%3CP%3EI%20solved%20it%20by%20taking%20the%20loan%20balance*rate%26nbsp%3B%2F12%3Dx%2C%20loan%20balance-x*rate%2F12%20etc%20until%20loan%20balance%20%3D%26lt%3B0.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20added%20a%20column%20to%20the%20left%20that%20counted%20the%20number%20of%20rows%20that%20tells%20me%20the%20number%20of%20payments.%26nbsp%3B%20I%20need%20the%20number%20in%20that%20cell%26nbsp%3B%20next%20to%20where%20the%20loan%20balance%20turns%20negative%20in%20a%20different%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1412574%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412760%22%20slang%3D%22en-US%22%3ERe%3A%20If%20loan%20is%20X%2C%20and%20rate%20is%20Y%2C%20and%20I%20pay%20Z%20each%20payment%2C%20how%20many%20payments%20until%20pd%20in%20full%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677160%22%20target%3D%22_blank%22%3E%40yestodd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20I%20understood%20you%20correctly--and%20it's%20entirely%20possible%20that%20I%20didn't--then%20this%20formula%20does%20what%20you%20want.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(U26%3AU386%2CMATCH(0%2CV26%3AV386%2C-1))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20retrieves%20the%20number%20244%2C%20and%20I%20think%20that's%20what%20you%20wanted%20the%20formula%20to%20return.%20It%20does%20it%20by%20first%20finding%20the%20spot%20in%20the%20balance%20column%20where%20the%20numbers%20is%20just%20about%20to%20turn%20negative%2C%20and%20then%20gives%20that%20%22index%22%20to%20Excel%20to%20find%20the%20corresponding%20payment%20number%20in%20column%20%22U%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20that's%20NOT%20what%20you%20were%20looking%20for%2C%20come%20back%20and%20try%20to%20describe%20it%20a%20bit%20more%20carefully.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1414977%22%20slang%3D%22en-US%22%3ERe%3A%20If%20loan%20is%20X%2C%20and%20rate%20is%20Y%2C%20and%20I%20pay%20Z%20each%20payment%2C%20how%20many%20payments%20until%20pd%20in%20full%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BLet%20me%20give%20it%20a%20shot!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415110%22%20slang%3D%22en-US%22%3ERe%3A%20If%20loan%20is%20X%2C%20and%20rate%20is%20Y%2C%20and%20I%20pay%20Z%20each%20payment%2C%20how%20many%20payments%20until%20pd%20in%20full%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415110%22%20slang%3D%22en-US%22%3EYou%20could%20also%20try%20the%20NPER%20function%3A%3CBR%20%2F%3E%3DROUNDUP(NPER(Rate%2F12%2C-Payment%2CLoan%20Balance)%2C0)%3CBR%20%2F%3E%3CBR%20%2F%3ENote%20the%20payment%20is%20entered%20as%20a%20negative.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

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

You could also try the NPER function:
=ROUNDUP(NPER(Rate/12,-Payment,Loan Balance),0)

Note the payment is entered as a negative.