Asset Depreciation

[syedadnan]

Regards,

I need asset to be depreciate as below;

Like i have a field in query which is showing the date of purchase, months of purchase from date of purchase, count of year from purchase date what i want is;

suppose if LCD is in category of Electrical Equipment and the depreciaition of Electrical equp is 33% per year so the query should calculate the depreciaition like;

cost is 100 so first year would be 33
then second year it should start from 67 because of first year depreciation
then so on

 

[gemma-the-husky]

what is the problem?

store cost, and a table of depreciation that has been applied. store a field that indicates the depreciation method to be applied. (eg your example indicates reducing balance, but there are many other depreciation methods)

Many systems might well de-normalise by storing total depreciation, monthly deprecation amount and so on.

 

[smig]

TotalDepreciation = (1 - (((100 - depreciation) / 100) ^ Years)) * 100

 

[syedadnan]

TotalDepreciation = (1 - (((100 - depreciation) / 100) ^ Years)) * 100

Could you please elaborate this in detail i shall be thankfull

 

[syedadnan]

Could you please elaborate this in detail i shall be thankfull

I am googling here and there but finding nothing.. here is my problem..

I have a field Book value (cost of asset), then purchased date, then a field count of months from the purchased date, then a field count of year from purchase date.

What i want is to see the current depreciated value of asset as of today but i want this to be calculated on year basis like;

If LCD is book value (cost) is 1000 and the rate apply with category of asset is 10% so this should be depreciated 100 first year then start depreciation on 900 for the second year and so on... i have salvage "0" for all assets

 

[smig]

why don't you put it in Excel and see if it's work ?

if you take your example:
After 1 year the value will be 900 - Total depreciation of 10%
After 2 years the value will be 810 - Total depreciation of 19%
After 3 years the value will be 729 - Total depreciation of 27.1%

not sure what salvage "0" is for. Value will never be 0.

If you want to calculate the Daily depreciation based on the Yearly one it will be:
Dx = 365 Root of Yx - 1
don't forget that 10% = 0.1


try to google and learn about Accrued Interest. It's the same idea

 

[syedadnan]

why don't you put it in Excel and see if it's work ?

if you take your example:
After 1 year the value will be 900 - Total depreciation of 10%
After 2 years the value will be 810 - Total depreciation of 19%
After 3 years the value will be 729 - Total depreciation of 27.1%

not sure what salvage "0" is for. Value will never be 0.

If you want to calculate the Daily depreciation based on the Yearly one it will be:
Dx = 365 Root of Yx - 1
don't forget that 10% = 0.1


try to google and learn about Accrued Interest. It's the same idea

Thankyou very much for your time..

Things are clearing to me now but i want to know how to use this formula TotalDepreciation = (1 - (((100 - depreciation) / 100) ^ Years)) * 100

in query what is depreciation in your formula as i am using a field dep in query or simply guide me the way to use this formula in query with field names

 

[smig]

if you use the query builder:
TotalDepreciation: (1 - (((100 - [dep]) / 100) ^ [Years])) * 100

if you set the dep in percents then (100% = 1):
TotalDepreciation: 1 - ((1 - [dep]) ^ [Years])


The idea is that you don't calculate the Total Depreciation directly.
You calculate the final price first and calculate the Total Depreciation from this

 

[syedadnan]

if you use the query builder:
TotalDepreciation: (1 - (((100 - [dep]) / 100) ^ [Years])) * 100

if you set the dep in percents then (100% = 1):
TotalDepreciation: 1 - ((1 - [dep]) ^ [Years])


The idea is that you don't calculate the Total Depreciation directly.
You calculate the final price first and calculate the Total Depreciation from this

Thanks a billion...


WORKING PERFECT !!!!!!

 

[gemma-the-husky]

as I said there are several methods of calculating depreciation. The two simplest are

straight line:
asset cost 1000
life of asset 5 years
depreciation 20% per annum, 200/year (based on 1000 total)

(alternative with a residual value)
asset cost 1000
life of asset 5 years
value after five years - 200
depreciation 20% per annum, 160/year (based on cost to business of 1000 less 200)

reducing balance
asset cost 1000
depreciation 20% per annum,
value at end of year 1 - 800
value at end of year 2 - 640
value at end of year 3 - 512 etc


there is no right or wrong method. Depreciation is not a REAL cost anyway. All you need to be is consistent. What depreciation does is applies a notional charge to a business for the use of an asset.

The mechanics of calculating a depreciation value are trivial.

 

[syedadnan]

Thanks a billion...


WORKING PERFECT !!!!!!

Could you please tel me one thing that so far the total of depreciation in percent is coming accurate if i want to view in other field as monthly depreciation or todate so what could be the further step as i have mentioned the criteria as below and working fine and generating me the total depreciation in years as i have a field of year count in numeric like 1 for first year 2 and then so on

TotalDepreciation: (1-(((100-[deppercent])/100)^[purchased_years]))*100

 

[smig]

If you want to calculate the Daily depreciation based on the Yearly one it will be:
Dx = 365th. Root of Yx - 1
don't forget that 10% = 0.1

If you want to calculate the Monthly depreciation based on the Yearly one it will be:
Mx = 12th. Root of Yx - 1
Yx = (1+Mx)^12

don't forget that 100% = 1 and 10% = 0.1

 

[syedadnan]

If you want to calculate the Monthly depreciation based on the Yearly one it will be:
Mx = 12th. Root of Yx - 1
Yx = (1+Mx)^12

don't forget that 100% = 1 and 10% = 0.1

One more please !!!

I am getting every thing in the query as per the formula given by you

TotalDepreciation: (1-(((100-[deppercent])/100)^[purchased_years]))*100

working perfect, what now facing is when iam creating report based on this query and trying to sum deprecited amount with this formula

Total dep: nz([curdepvalue],0)*nz([count],0)

i am getiing message from report data mismatch and same with this to calculate the book value after depreciaiton

bv: nz([currentassetvalue],0)*nz([count],0)

how to get totals via sum at report ??