# Next payment due

#### drallely

##### New Member
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))

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

##### Well-known Member
Hi DRallely,

Does this do what you want?

DRallely.xlsx
ABCDEF
1StartTodayMonthlyAnnualFortnightlyQuarterly
201-Jan-2116-Apr-2101-May-2101-Jan-2223-Apr-2101-Jul-21
301-Jan-2101-Jan-2201-Feb-2201-Jan-2314-Jan-2201-Apr-22
401-Jan-2101-Mar-2101-Apr-2101-Jan-2212-Mar-2101-Apr-21
501-Jan-2101-Apr-2101-May-2101-Jan-2209-Apr-2101-Jul-21
601-Jan-2101-May-2101-Jun-2101-Jan-2207-May-2101-Jul-21
701-Jan-2101-Jun-2101-Jul-2101-Jan-2204-Jun-2101-Jul-21
801-Jan-2107-Jul-2101-Aug-2101-Jan-2216-Jul-2101-Oct-21
901-Jan-2114-Jan-2101-Feb-2101-Jan-2215-Jan-2101-Apr-21
1001-Jan-2101-Jan-2101-Feb-2101-Jan-2215-Jan-2101-Apr-21
1104-Apr-2205-Apr-2504-May-2504-Apr-2614-Apr-2504-Jul-25
Sheet1
Cell Formulas
RangeFormula
C2:C11C2=EDATE(A2,DATEDIF(A2,B2,"M")+1)
D2:D11D2=EDATE(A2,(DATEDIF(A2,B2,"Y")*12)+12)
E2:E11E2=B2+(14-MOD((B2-A2),14))
F2:F11F2=EDATE(A2,(DATEDIF(A2,B2,"M")+(3-MOD(DATEDIF(A2,B2,"M"),3))))

#### drallely

##### New Member
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))

Replies
3
Views
67
Replies
8
Views
214
Replies
0
Views
64
Replies
5
Views
278
Replies
1
Views
79

### Forum statistics

1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back