Next quarter (period) formula

[deltaone]

Hi guys

Here is my first post

I am trying to develop a function that can retrieve a certain date in regards to certain criteria.

Let me explain: I have a client who needs to pay me.

The start date of the contract is 02/17/2012.
The end date of the contract is 02/18/2017.

During this period the client has to pay me every quarter. Therefore, he needs to pay me on 05/17/2012, 08/17/2012, 11/17/2012 and so on until the end date of the contract.

I would like to have in a certain cell that would have the nearest quarter payment. In other word, considering we are the 2/22/2013, the cell should show me 05/17/2013. If we were on 07/28/2012, the cell should show me 08/17/2012.

The formula should actually be generalized so that I can apply it to other customers that have a different payment frequency (ie: the client needs to pay me every 2 months, etc.)

The purpose of this would be to run a report at the end of each month that will have all the customers that need to pay me this month.

I have been trying to use MOD, DATE, MONTH, YEAR and DATES functions without any success…

It’s a real headache for me and I have been stuck on this for the past week, any help would be greatly appreciated!

Thanks a lot for the hand!


Cheers

 

[barry houdini]

One other possible approach here is to use COUPNCD function which gives the next payment date (after today) based on a future maturity date, so you can use EDATE to create that future date from your start date, e.g.

=COUPNCD(TODAY(),EDATE(A2,400*12),4)

COUPNCD only allows you to set payments to be 1, 2, 3 or 4 times a year and I noticed that it gives slightly different result to the first formula I suggested in that if A2 is the last day of the month then all payment dates are deemed to be last day of the month (which you may or may not prefer), for example if A2 is 28th Feb 2011 the future payments are not deemed to fall on 28th of each month but on 31st May, 31st August and 30th November