Recurring annual events using a specific date as a trigger date

[Bamboozled]

I am building a cash flow. I have a start date for an income stream, and on
each anniversary of that date I need to trigger an increase to that income,
reoccurring about 4 times.
Using an if function, asking excel to return a value if the cash flow date
is greater than the income start date, and then asking it to return a value
if the cash flow date is equal to the anniversary (start date +365 days,
second anniversary = start date + 730 days.. etc), and doing this for 4
repeats, excel tells me I have entered too many arguments! I thought it could
take 7....

 

[Duke Carey]

Bam -

Don't use a test that goes by # of days, as that will get upset by leap
years. Try testing for >= trigger dates. If the trigger dates are known,
use them. If they must be flexible, use a construct such as

=EDATE(start date,48)

Which will give you the 4th anniversary of the start date. You have to have
the Analysis Toolpak add-in installed to use EDATE().

Test for the LAST trigger date first, then move to the next to the last, etc

=IF(cash flow date>=EDATE(start date,48), final increment, if(cash flow
date>=EDATE(start date,36), next-to-final increment,if(cash flow
date>=EDATE(start date,24), second increment,if(cash flow date>=EDATE(start
date,12), first increment))))