Showing number of payments left if start date and total number of payments are known

[Jim Deans]

Hi,
I'm banging my head against the wall trying to work out what I'm missing here, I get the distinct feeling its something obvious!:

First payment date: 1st Jan
Number of monthly payments that go out on the same day of the month as the first payment: 12
Last payment date:
Number of payments left:

Todays Date: today()

Working on the first payment going out on the 1st Jan, the 12th payment would go out on the 1st December.
Using the EDate, Year Frac etc and similar brings the last payment date up as the 1st Jan (this being 12 months from 1st Jan although this is in fact the start of the 13th month), this is similar if I try and work out the number of payments left based on today's date. e.g it's the 13th October today, there should be 2 payments left (1st Nov and 1st Dec) but if i adjust the EDate by

Has anyone any suggestions? Just -1 from the number of payments doesn't work when it comes to trying to work out the number of payments left based on today's date

Cheers

dates.xlsx
	A	B	C	D	E	F	G
1	First Payment	Last Payment Date	Total Payments	Payments Left		Today	13/10/2023
2	20/02/2023	20/02/2028	60	52
Sheet1

Cell Formulas
Range	Formula
G1	G1	=TODAY()
B2	B2	=EDATE(A2,(C2))
D2	D2	=DATEDIF(G1,B2,"M")

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D
1	First Payment	Last Payment Date	Total Payments	Payments Left
2	44,977	46,803	60	52
3
4				52
Sheet1

Cell Formulas
Range	Formula
B2	B2	=EDATE(A2,(C2))
D2	D2	=DATEDIF(G1,B2,"M")
D4	D4	=IF(DAY(TODAY())<20,DATEDIF(EOMONTH(TODAY(),-1)+20,$B$2,"M"),DATEDIF(EOMONTH(TODAY(),0)+20,$B$2,"M"))