Formula to work out how many payments left on a loan?

[tonywatsonhelp]

Hi Everyone,

So I'm trying to work out how many payments are left on a loan?

I have a start Date in D4
End Date in F4
Number of months in B4

So all i want to do is look at the End date, and todays date and work out how many payments are left?
the dates are shown as DD/MM/YY so for example last Payment date = "26/12/21" so it should say i have 4 payments left, but 13/12/21 would be 3

please help if you can

Thanks
Tony

 

[etaf]

=DATEDIF(TODAY(),F4,"M")
would give 3 and 13/12/21 would give 2
Why 13th Dec ?
is that because its before today's date of 23 sep
"M" is complete months
so the 22nd would be 2 and 23/24 would be 3


if so then -
=DATEDIF(TODAY(),F4,"M")+1

 

[tonywatsonhelp]

Thanks etaf,
yes it was just an example as i'm having trouble with if the payment is due tomorrow so there are 3 payments but only two months,
so what i was trying to do is count how many "23rd" there are between the two dates but it wasn't working,
I'll give you idea a go see what happens
thanks
Tony

 

[etaf]

that should work ,

Book6
	B	C	D	E	F
3	Month				End date
4	3				12/21/21
5	3				12/22/21
6	4				12/23/21
7	4				12/24/21
8	4				12/25/21
Sheet1

Cell Formulas
Range	Formula
B4:B8	B4	=DATEDIF(TODAY(),F4,"M")+1

 

[tonywatsonhelp]

Hi, sorry but the above is correct, it works perfectly,
thanks
Tony

 

[etaf]

you are welcome

 

[kvsrinivasamurthy]

This will count numbers of 23's between today and Date in F4. Change 23 as required.

=SUMPRODUCT(1*(DAY(ROW(INDIRECT(TODAY()&":"&$F$4)))=23))

To count 26

=SUMPRODUCT(1*(DAY(ROW(INDIRECT(Today()&":"&EndDate)))=26))