Hardware Depreciation

[IanNicholls]

Hello

I am creating an asset register and would like a column to display the current value of a piece of hardware where the purchase date and purchase value are both known.
Assuming the lifecyle period would need to be known this would be 3 years, at which point the item would be rated at zero value.
Which function should be used to give the desired results?

Cheers

[Special-K]

This is more of a maths question than a specific Excel function.

"Assuming the lifecycle period...would be 3 years"
Based on that the result doesn't need to be specific, an approximation would suffice.
So 3 years = 365 * 3 = 1095 days.

The calculation would be:

(1095 - (Today - purchase date)) * 100% * value of hardware

so

(1095 - (TODAY() - PURCHASEDATE)) * 100% * PURCHASEVALUE

This will produce 100% of purchase price assuming bought today and 0% when purchase date is 3 years old.
Beyond 3 years the result will be negative so you may want to modify this to

IF(1095 - (TODAY() - PURCHASEDATE) < 0,0,1095 - (TODAY() - PURCHASEDATE)) * 100% * PURCHASEVALUE

[IanNicholls]

Thanks for the quick reply.

The above offers a bespoke solution. Is there not a function that would give an industry recognised result?

Regards