Next payment due

drallely

New Member
Joined
Mar 16, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
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))))
 
Solution

drallely

New Member
Joined
Mar 16, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top