If a record is more than 5 years old write it down by 20% per year until £0, (but by months) (1 Viewer)

[InstructionWhich7142]

So I used to do this by year but it was "bumpy" because if some high value thing suddenly loses 20% of it's value in one month of the year it looks higher than the other 12, it was suggested to do it by 1.66% per month instead, I ended up coming up with this abomination (i'd already written my own MAX and MIN for using in queries (happy to have a better suggestion here, I was just sick of having to nest iif just to get max/min)

I basically convert dates to months in the epoch, subtract the old date from today's date, push it back 60 months to get the "more than 5 years old" then divide that remainder by 60, the MIN and MAX just solve the negative bit that occurs within 5 years and the greater than 60 bit after 10 years

(1-Round(xMin(xMax((((CLng(Format(Now(),"yyyy"))*12+CLng(Format(Now(),"mm")))-(CLng(Format([everyday],"yyyy"))*12+CLng(Format([everyday],"mm")))-60)/60),0),1),3))

Hope there's an elegant way i've missed

 

[The_Doc_Man]

Do you keep "original value" as well as "effective value"? Because if you don't, you will not reach 0 for a LOT longer than you think. You'll run into a Zeno's paradox situation except that when the value drops below 1 penny or whatever is your smallest coin, then you can finish the drop.

Think of this as a compound interest problem in reverse. If you want a linear drop in value, you must remember the original value in order to take your 1.66% of that original amount each time away from the effective value.

As to computing age, use DateDiff, perhaps twice, because you will have two dates to consider... one is the date of reference for the overall record; the other is the date on which you last performed a particular adjustment. Then run a quick pair of queries.

UPDATE TheRecord SET EffectiveValue = INT( EffectiveValue - (OriginalValue * 0.0166) *100 ) / 100, EffectiveDate = Date
WHERE DateDiff( "m", OriginalDate, Date ) > 60 AND DateDiff("d", EffectiveDate, Date ) > 30  AND EffectiveValue >0 ;

UPDATE TheRecord SET EffectiveValue = 0 WHERE EffectiveValue < 0 ;

Here is a link to DateDiff, which should make your work a little easier. Note carefully in my example that the two DateDiff calls use different interval units. So that first WHERE clause says the record is more than 60 months old, was last changed more than 30 days ago, and hasn't dropped to 0 value yet. The second update catches the case where round-off issues caused you to lose more than the effective value in the first update, thus going negative. And of course you can pick another interval than 30 days or 60 months.

DateDiff function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[gemma-the-husky]

I would have thought the calculation could be rather slicker.

If you know the purchase date, then you can probably use elapsed_days(today-purchasedate) > 365*5 (or something similar) to determine whether the depreciation is cost*annual_depn_rate/12 or something different.

 

[Solo712]

This OOP is puzzling. There is no situation recognized by the accounting world in which an item would start depreciating after holding its acquisition value for 5 years. Also I am not sure about the "bumpiness", i.e. the need to re-valuate the remaining value of the asset monthly. At any rate, in case this inquiry rejects the established ways altogether, there is a built-in VBA function to calculate depreciation which avoids any of the perceived difficulties.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ddb-function

Best,
Jiri

 

[Isaac]

Hope there's an elegant way i've missed

VBA.

I doubt many will agree with me on this, but I find that when you have a really complex expression like that, it's cleaner to make a vba function because you can clearly write out the comments, the justification, the explanation - in other words, it's more easy to document, troubleshoot and maintain. This is just a thought - and this case is by no means the best example of it, just a thought I had.

 

[pbaldy]

I also don't understand the 5 year bit, but the bumpiness makes sense. If you just record depreciation once a year, your monthly financials will understate the expense for 11 months and overstate it for 1.

FWIW, I would probably record each month's depreciation into a table, rather than just manipulate a field. That gives you a history in case something goes awry. Your "net" value is the original value less any depreciation recorded.

 

[InstructionWhich7142]

Datediff is a good point, I've used that elsewhere but it didn't occur to me here as I was modifying existing code that used format

You sound like our accountants lol

Yes we do keep the original value and this is just a written down value that's reported monthly so that simplifies things, no recursive decreases etc

"Your "net" value is the original value less any depreciation recorded." yep this is what we do, we recalculate every month, I didn't think the bumpiness was a big issue but the change due to depreciation does vary month on month because i've currently got something like "if more than 72 months old * 0.6, if more than 84 * 0.4 so if some expensive items were bought either a year apart or just more one month than other months in general that "bump" comes round once per year as that item crosses the next year 20% whereas this monthly 1.6% does smooth that

 

[InstructionWhich7142]

(1-Round(xMin(xMax(((DateDiff("m",[everyday],Now())-60)/60),0),1),3))

perfection

 

[DickyP]

This sort of depreciation was a fairly normal approach for weapons systems when I was involved in procurement in the army.

Secondly have you thought of using the VBA SYD function which basically enables you to do all this..

 

[plog]

How's 'smooth' do you want it? Just pick the smallest time interval, determine how many of them are in 5 years, determine how many of them have expired since asset acquisition and use it to do multiplication and division.

 

[DickyP]

Secondly have you thought of using the VBA SYD function which basically enables you to do all this..

As a humorous aside having advised looking at SYD I went to my 'go-to' reference for VBA - VB & VBA in a Nutshell to just check up on my advice about financial functions (it was correct). At the end of each section in the book there is always a 'See Also', recommending related functions, methods etc. to view.

However the 'See also' for DDB depreciation just says 'A psychiatrist!'.

 

[CJ_London]

There is no situation recognized by the accounting world in which an item would start depreciating after holding its acquisition value for 5 years.

In terms of capital assets I would agree (not had dealings with weapon systems!). But might use the method for saleable stock - e.g. the scenario is an anticipated 'shelf life' of 10 years, start reducing it's 'cost' after 5 years so reduced profits are spread over a period rather than in the final year.

I used a similar technique many years ago, but in the context of warehouse management - (written down sale price less written down cost of stock) v (cost of storing stock plus cost of disposal) - when the latter exceeded the former it was disposed of pronto.

 

[InstructionWhich7142]

In terms of capital assets I would agree (not had dealings with weapon systems!). But might use the method for saleable stock - e.g. the scenario is an anticipated 'shelf life' of 10 years, start reducing it's 'cost' after 5 years so reduced profits are spread over a period rather than in the final year.

I used a similar technique many years ago, but in the context of warehouse management - (written down sale price less written down cost of stock) v (cost of storing stock plus cost of disposal) - when the latter exceeded the former it was disposed of pronto.

This is pretty close to the mark, the industry we're in has a long tail of spares and repairs orders so although stock is a few years old it's still likely to be used and sold at value or indeed at a marked up price based on current market availability and MOQs etc, after 5 years the likelihood of use drops, on average, so we start to write down, historically we never wrote down, finally our accountants put their foot down about it, lol

 

[CJ_London]

I can see that happening in the computer parts industry - who buys DDR2 memory these days?

although I remember the price of dot matrix printers shooting up in value due to rarity value as the one thing they could do which laser printers couldn’t is print a continuous sheet of paper and for a while that was a requirement in some businesses