Formula to Calculate when fixed assets are replaced every X years

**edphill** · 07-23-2014, 06:08 AM

Hi Everyone. I'm sure this is quite simple but I've been staring at the screen for a while without any luck.

I am building a model that includes a Cap Ex schedule showing when machinery is bought.

The information is:

Unit Cost - How much each unit costs
Useful Life - The piece of equipment must be replaced every X years.

Quantity - This differs each year as more equipment is required as yields increase or planted area increases.

The table is set up with years along the top and a list of equipment down the side.

I need a formula that calculates how many pieces of equipment are required in each year and then minuses equipment that is already in operation and puts the total expenditure for that year into the cell.

E.g. I need 4 Bulldozers in year 1 at a cost of $50k each and they have a useful life of 3 years. SO $200k goes in there.

In year 2 I need 5 Bulldozers so but there are already 4 in operation, so I only need to add 1, so $50k is entered into there.

Year 3 needs 5 bulldozers so no change

In year 4 I need 6 bulldozers but 4 have reached the end of their useful life and need to be replaced and one more bought, so $250,000 will show in that year.

Hopefully this makes sense. Would very much appreciate any help.

Thank you.