DB Function?

[cjwenngatz]

I have a list of assets in column A.
Column B contains a historical date (Jan, 2008)
Column C contains current months date (Nov, 2009).
Column D shows the period difference (B-C) = 21

Now here comes the fun part, I need to know what the NBV of the asset
is using 20% declining balance). This formula will be copied to
multiple assets, so it needs to be dynamic.

I've tried everything I can think of :S

Thanks!

 

[Bernie Deitrick]

cjwenngatz,

NBV should be simply the price you paid reduced by the decline multiplied by
the number of years that you have had it, limited to the life of the asset
(since you have age in months, you would use months). For a 20% annual
decline, that would be a 5 year life, so perhaps

=E2*(1-0.2*(MIN(D2,60)/12))

where E2 has the initial book value, and D has the age of the asset in
months. 60 is five years in months.

Of course, you should look at what this returns versus what you expect....

HTH,
Bernie
MS Excel MVP