Formula – Use of Match formula without helpers for replacement of assets

HI All

Please see the attached example spreadsheet.

I have an air-conditioning unit with an estimated useful life of 10 years, but an actual remaining useful life of only 2 years and a replacement value of $20K in year 0.

As you can see by the example spreadsheet the air-conditioning unit gets replaced in year 2 (actual remaining life) and year 12. The other replacement years are outside the scope of the 20 year period.

In year 12 there is indexation of 11 years added to the original $20K replacement costs and give the result of $27.982K.

It is only 11 years as there is no indexation in year 1.

Currently I use multiple rows to calculate the replacement year and replacement value plus indexation as shown in the attached spreadsheet using the match formula.

Is there any way to consolidate these multiple row formulas into a one row formula line ie without the helper rows?

So instead of requiring 11 rows per asset of helper rows I will only require 1 row per asset.

Thanks in advance

Please try at G7 drag to Z7
=IF(MOD(COLUMNS($F7:G7),2),0,$F7*(1+'assumptions '!$C$4)^(COLUMNS($F7:G7)-2))

I'm not sure why you skip Year 4, this formula didn't.

Thank you so much!

HI Bo_Ry

I have been testing the formula you provided which works perfectly if the year of replacement is in years 1 and useful life is 1 year. As it populates year 1 and then repeats for every year after that.


The problem I have is that I they replacement year is say year 3 and useful life is 10 years it should only populate in year 3 and 13.

Similarly if the replacement is in year 15 and the useful life is 10 years it should only populate in 15.

Is there anyway of modifying your formula so this occurs???


Thanks again for your assistance.

Andrew

Hi Andrew,

I not quit get what you want.
where will you put replacement year and useful life in the table?
Please add this data to your table and populates year for each different replacement year and useful life.

HI BO_Ry

I have attached the following spreadsheet to help you see what I mean.

In this one actual remaining life is 15 years so the first population occurs at year 15 indexed to $30,666 , it has a useful life of 2 years therefore it will populate in year 17 $ 32,596 and in year 19 at $34,649.

The actual life and useful life can change dependent on the unit , so the formula has to be flexible enough to handle this.

I hope this helps you understand the issue.

thanks again

Andrew

Hi Andrew

F8 drag to Z8
=IF(AND(F$6>=$C8,MOD(F$6,$B8)=MOD($C8,$B8)),$E8*(1+Assumptions!$C$4)^(F$6-1),0)

Please change Estimated Overall Life (yrs) and Actual Remaining Life (yrs) to see if it work for other no.

HI BO_Ry

Thanks for that it does seem to be working for all variations ! You have made my life so much better.

I will double check this over the weekend.

But thanks again!.

Andrew

Hi Andrew, Happy to help.