Cell Referencing (Event occuring every X years)

I am building a Cash Flow Model from year 2002 (base year) to year 2015. Every X years (this is a sensitivity variable) from 2002, interest rates will increase by Y amount (this is a sensitivity variable).

Years are in a row (i.e. H33:W33)

Interest rate increase (variable)is hardcoded in cell M21 (ex. 0.5%)

Period of Occurance in years (variable)is hardcoded in cell M22 (ex. 5 "for every 5 years")

Interest rates are in a row (i.e.H68:W68)

The starting intersest rate is essentially harcoded in cell H68.

My problem is how to write the formula for cells I68:W68 that will allow the event to occur every X years based on cell M22 and increase the interest rate based on cell M21?



Is there a simple look-up and reference function that can do this using the variables (maybe in combination w/ logical function using my row containing the years?

Thanks for your help.

[Hayesk]

This doesn't make use of lookup or reference, but uses logical and pure arithmatical. The MOD function returns the remainder of MOD(number, divisor)

Try this:

=IF(MOD(M33-$H$33,$M$22)=0,$M$21+L68,L68)

[Chris Davison]

Nice one Hayesk....

may I augment it slightly so it counts the first cell as 1 and carries on for "x" number of years :

in I68 try :

=IF(MOD(COLUMN()-8,$M$22)=0,H68+$M$21,H68)

and copy the formula accross to the right