Life Cycle Spreadsheet - Issue with repeated recurrence?

Hi,

I am working on a lifecycle report that I am required to produce to show the expenditure over a certain time period. I normally work with 5 year lifecycle reports but have been requested to supply a twenty year version.

The problem i am having is that the lowest life expectancy I deal with is five years so in a five year report there is no issue. However, in a twenty year report a five year life expectancy can show up four times.

I normally use an IF formula but this does not take in to account the repetitive nature of a five year repeat.

I have attached a spreadsheet sample with where the repeated should be. The columns of note are E (Life Expectancy), G (Remaining Life) this will indicate which year the cost goes in, and AD (Total Cost) this is the cost figure that should populate the required field.

At present I am having to 'handrolically' add the repeated fields. I have highlighted the cells that should be populated to cover repeated costs in relation to the life expectancy. This is part of a bigger report that pulls in data from other areas. To keep it simple I have just taken it back to the bare bones. I imagine there may need to be a cell added for current year i.e. 2015 and a formula constructed from this?

Many thanks in advance for your help.

Steve

Thanks Siva!!!