Excel 2013 displaying of monthly, quarterly and semi annually payment date

Hi,

Maybe I am missing something in this discussion...

In B3: =EDATE(B2,$B$1)

You are correct that the 19 stands for the day of the month, and that 3 stands for the interval: 3 for quarterly payments, 6 for semi-annual payments.

For monthly payments, use interval 1 instead of 3 or 6:

=DATE(YEAR(TODAY()-19),MONTH(TODAY()-19)+1-MOD(MONTH(TODAY()-19)-1,1),19)

But since MOD(MONTH(TODAY()-19)-1,1) = 0 regardless of today's month, we can omit this part, and the formula becomes shorter:

=DATE(YEAR(TODAY()-19),MONTH(TODAY()-19)+1,19)

which is the formula that I posted. So in fact, it uses the same layout as the other formulas, but omitting the part that always evaluates to 0.

The -1 after the month is because we start at the first month (January), If you want to start in February, use -2, and if you want to start in March, use -3, etc.

So for example, for quarterly payments on the 13th of February, May, August and November, use

=DATE(YEAR(TODAY()-13),MONTH(TODAY()-13)+3-MOD(MONTH(TODAY()-13)-2,3),13)

13 = payment day

3 = interval (quarterly = every 3 months)

2 = start month (February)

And for semi-annual payments on the 7th of April and October, use

=DATE(YEAR(TODAY()-7),MONTH(TODAY()-7)+6-MOD(MONTH(TODAY()-7)-4,6),7)

7 = payment day

6 = interval (semi-annual = every 6 months)

4 = start month (April)

For monthly payments on the 5th of each month:

=DATE(YEAR(TODAY()-5),MONTH(TODAY()-5)+1,5)

5 = payment day

1 = interval

start month is irrelevant here.

I have uploaded a small sample workbook to DropBox where you can specify the payment date, frequency and starting month. The formula will calculate the next payment date:

See https://www.dropbox.com/s/9qyzoy6m286vr1u/Payment.xlsx?dl=1

Hi HansV

Your reply is very helpful! 

Can I have some follow up questions?

Since I have many bonds to calculate and those bonds have different payment date, month and frequency, i want to have a single universal formula. So that I can link to other variable cells for calculation.

For example, in your formula

=DATE(YEAR(TODAY()-19),MONTH(TODAY()-19)+3-MOD(MONTH(TODAY()-19)-1,3),19)

Please correct me if i am wrong.

I think "-19" should be the date of payment of a specific month.

"+3" and "3" mean quarterly.

So why i need to -1? And what will be the formula if the quarterly payments are on 19th of Mar, Jun, Sep and Dec? Also 19th of Feb, May, Aug and Nov?

Can the monthly payment be arranged like the quarterly and semiannually one?

Any variables that i need to consider?

Many Thanks for your help!

Stephen

For monthly payments on the 19th of the month:

=DATE(YEAR(TODAY()-19),MONTH(TODAY()-19)+1,19)

For quarterly payments on the 19th of January, April, July and October:

=DATE(YEAR(TODAY()-19),MONTH(TODAY()-19)+3-MOD(MONTH(TODAY()-19)-1,3),19)

For semi-annual payments on the 19th of January and July:

=DATE(YEAR(TODAY()-19),MONTH(TODAY()-19)+6-MOD(MONTH(TODAY()-19)-1,6),19)