Summation of Pro-Rated Values and Replacement

[Jrub]

Good Morning All,

Thank you for your interest in my massive headache, any comments or help is greatly appreciated.

I have attached an example sheet of my pains.

Rows contain data for a particular item, where it was originally constructed, a useful life of the item, and assessed an adjustment to it's useful life.

I am trying to tally costs per year based on the item being prorated against it's useful life, with the incorporation of an adjustment at the first iteration, and then the standard life taking over until the end of a pre-selected time frame.

This seems to be an impossible task, being able to control calculate the lifetime of an item with an adjustment, and then having it repeated in multiples of it's standard useful life.

The formula I am using per year is the following untill useful life+ life adjustment= current column year:

((current year-year constructed)/(useful life + life adjustment))*cost of item

After that it is just:

((current year-year constructed)/(useful life))*cost of item

Life adjustment only adjusts the life of the item for the first time it needs to be replaced, then after that it appears in multiple of it's standard life. Achieving this seems frankly impossible.

Has anyone done anything similar to this, or could provide some basic principles I should follow as I strive for a solution?

I have noted to correct summed values in red at the bottom of the excel file, these are calculated by hand.

Thank you to all for reading.