big table depreciation - what about items sold?

[rprovideo]

Hi all

I have added depreciation calculations into out main inventory worksheet. Its a big sheet containing several thousand lines (however I only need depreciation for about 1200) and quite a few columns. I started creating a formula that worked pretty well, although I'm sure its overcomplicated.

However as my accountant pointed out, that items we sell or lose before depreciation runs out are calculated wrongly. I am trying to find a way to solve this with AMORLINC but it seems it just cannot handle this situation?

what I have:
-date of buying
-date of selling (if any)
-buying price
-depreciation rate

what I need
-a depreciation value for each year (I have a column for each since 1993) that:
-shows zero before the item was bought (so for an item bought in 2002, columns for 1993-2001 show zero)
-shows a calculated depreciation value if the item is active in the given year
-shows zero if the depreviation reached zero OR if the item was sold in that or any previous year.
-a sum of depreciation column for the actual year (I'm going to update this formula end of each year), showing the sum of all years if the item is active, and zero if the item has been sold
-a price column showing the buying price if the item is active, and zero if the item was sold (so summing all this would give me the total of all active items)

-if there rate is 100%, the depreciation is the full value for the year of buying
-if the rate is not 100%, the first and last year is a partial year, in between, the depreciation is buying price x rate (so same for all full years).
-if the item is sold before the depreciation hits zero, it should ONLY calculate depreciation till the selling date..so this will be a partial year, and there will be a value outstanding.

I did almost everything except this last thing. I cannot handle sold items. Can someone help me?

I have attached a spreadsheet with two sample lines. in the first case, the car is depreciated before its sold, so the program handles it fine.
However in the second case its bought in 2010, sold in the end of 2011, but AMORLINC is still running up till 2015. the sum depreciation (corrected) shows a wrong number for the second car.

It seems to me this is a very common exercise and I there must be an easy solution for it.

Any help appreciated
Balazs

[Richard Buttrey]

Hi,

Maybe in AI3 copied across

Please Login or Register  to view this content.