How Can I calculate next payment date

[hetterbr]

My spreadsheet has the following:

A = Start date
B = Freqeuncy (monthly, quarterly, semi-annual, annual)
c = Current date

I want to be able to calculate when the next payment date is going to be. And it must take into account the various payment frequencies. and it must land on the same day (if started on the 16th of the month, then next payment must be on the 16th).
Oh and everytime I go back into the spreadsheet, it should update itself automatically.


thanks

 

[barry houdini]

Try this formula in D2 copied down

=EDATE(A2,CEILING(DATEDIF(A2,C2,"m")+1,VLOOKUP(B2,{"monthly",1;"quarterly",3;"semi-annual",6;"annual",12},2,0)))

 

[hetterbr]

I forgot to mention that Column A would have the full start date in it: 01-Nov-11.

I am getting an error and I think it is due to the year being in that cell.

thanks

 

[hetterbr]

Ok, weird.

but my computer screen just hiccupped and now the formula seems to work fine.

Maybe time to stop working and go home.

thanks for the help.

 

[barry houdini]

My assumption is that A2 and c2 will contain dates - the formula won't work otherwise - the only requirement beyond that is that C2 is a later date than A2 - if that isn't always the case then the formula can be adjusted to cater for that......

 

[hetterbr]

I am finding that A2 is sometimes before or after C2. You mentioned that the formula could be adjusted to allow for this. How would I do that?

thanks

 

[hetterbr]

Bump, Anyone know what needs to change?

thanks