# 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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

##### 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
103
Replies
8
Views
311
Replies
0
Views
144
Replies
6
Views
96
Replies
4
Views
80

1,133,244
Messages
5,657,584
Members
418,401
Latest member
B_A_M155

### 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