Next payment due

[drallely]

I am trying to calculate the next due date for a periodic payment based on a starting date and a the current date.

e.g.:
Start date 1/1/2021 Today's date is 16/4/2021 when is the next monthly payment due
Start date 1/1/2021 Today's date is 16/4/2021 when is the next annual payment due
Start date 1/1/2021 Today's date is 16/4/2021 when is the next fortnightly payment due
Start date 1/1/2021 Today's date is 16/4/2021 when is the next quarterly payment due

I have a solution for monthly payments but cannot translate that to annual, fortnight or quarterly.

A1: =TODAY()
A2: 1/1/2021

A3: =DATE(YEAR(A1),IF(DAY(A2)>DAY(A1),MONTH(A2)+1,MONTH(A2)),DAY(A1))

 

[Toadstool]

Hi DRallely,

Does this do what you want?

DRallely.xlsx
	A	B	C	D	E	F
1	Start	Today	Monthly	Annual	Fortnightly	Quarterly
2	01-Jan-21	16-Apr-21	01-May-21	01-Jan-22	23-Apr-21	01-Jul-21
3	01-Jan-21	01-Jan-22	01-Feb-22	01-Jan-23	14-Jan-22	01-Apr-22
4	01-Jan-21	01-Mar-21	01-Apr-21	01-Jan-22	12-Mar-21	01-Apr-21
5	01-Jan-21	01-Apr-21	01-May-21	01-Jan-22	09-Apr-21	01-Jul-21
6	01-Jan-21	01-May-21	01-Jun-21	01-Jan-22	07-May-21	01-Jul-21
7	01-Jan-21	01-Jun-21	01-Jul-21	01-Jan-22	04-Jun-21	01-Jul-21
8	01-Jan-21	07-Jul-21	01-Aug-21	01-Jan-22	16-Jul-21	01-Oct-21
9	01-Jan-21	14-Jan-21	01-Feb-21	01-Jan-22	15-Jan-21	01-Apr-21
10	01-Jan-21	01-Jan-21	01-Feb-21	01-Jan-22	15-Jan-21	01-Apr-21
11	04-Apr-22	05-Apr-25	04-May-25	04-Apr-26	14-Apr-25	04-Jul-25
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=EDATE(A2,DATEDIF(A2,B2,"M")+1)
D2:D11	D2	=EDATE(A2,(DATEDIF(A2,B2,"Y")*12)+12)
E2:E11	E2	=B2+(14-MOD((B2-A2),14))
F2:F11	F2	=EDATE(A2,(DATEDIF(A2,B2,"M")+(3-MOD(DATEDIF(A2,B2,"M"),3))))

 

[drallely]

Thanks Toadstool this is exactly what I was trying to do.
I added one more for four weekly payments based on your fortnight formula.

=B2+(28-MOD((B2-A2),28))