Asset Monthly Depreciation

[Benjamin_4]

i want to calculate the depreciation of an asset and i want my accumulated value to automatically add the previous monthly depreciation value with the current one any time the user calculates for the monthly depreciation, the accumulated value should pick the results from monthly depreciation and add it to the one it has and output the results.
example:monthly depreciation is 40 and accumulated value is 0 the result for accumulated value should be 40
when the value for monthly depreciation is 60 the accumulated value should add 40+60=100.

this is my code in MySql(i got stuck at the accumulated part)

drop trigger if exists update_purchases;# MySQL returned an empty result set (i.e. zero rows).

drop trigger if exists insert_purchases;# MySQL returned an empty result set (i.e. zero rows).

drop procedure if exists process_purchase;# MySQL returned an empty result set (i.e. zero rows).

delimiter |

create procedure process_asset_update (
in Cost_Of_Acquisition double,
in Estimated_Useful_Life double,
inout Monthly_Depreciation double,
inout Estimated_Residual_Value double,
inout Accumulated_Depreciation double,

in Asset_ID integer
)

BEGIN
set monthly_Depreciation = (Cost_Of_Acquisition / Estimated_Useful_Life)/12;
set Estimated_Residual_Value = 10 *(Cost_Of_Acquisition);
set Accumulated_Depreciation = monthly_Depreciation ;

END|# MySQL returned an empty result set (i.e. zero rows).

CREATE TRIGGER `update_asset_update`
before update ON `asset_update`
for each row
BEGIN
call process_asset_update(new.Cost_Of_Acquisition,new.Estimated_Useful_Life,new.Monthly_Depreciation,new.Estimated_Residual_Value,new.Accumulated_Depreciation,new.Asset_ID);
END|# MySQL returned an empty result set (i.e. zero rows).

CREATE TRIGGER `insert_asset_update`
before insert ON `asset_update`
for each row
BEGIN
call process_asset_update(new.Cost_Of_Acquisition,new.Estimated_Useful_Life,new.Monthly_Depreciation,new.Estimated_Residual_Value,new.Accumulated_Depreciation,new.Asset_ID);
END|

delimiter ;

-- insert some sample data

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(2500,5);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(2000,4);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(3000,6);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(1500,3);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(5000,9);# 1 row affected.

-- update asset_update set Total = 0;

Please seriouse help will be appreciated since am working with time THANK YOU

[mmcdonald]

haha I have fun with calculating depreciation in an Excel spreadsheet - good luck getting it done in SQL :)

I'm sorry as I can't actually help, but I have to say, if you get this finished please do share. I'd even be willing to buy it if it was accurate and reliable. I'll have 100's of deprecating assets this time next year :'(

[cereal]

example:monthly depreciation is 40 and accumulated value is 0 the result for accumulated value should be 40 when the value for monthly depreciation is 60 the accumulated value should add 40+60=100.

So when the new monthly depreciation is 20 the new accumulated depreciation becomes 120 because the previous accumulated value was 100? Am I correct?

Can you show the structure of the table? I've done few tests but I would like to understand more the structure to use, maybe I'm missing something. At the moment this is what I'm using:

CREATE TABLE `asset_update` (
  `Asset_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Cost_Of_Acquisition` double(4,0) DEFAULT NULL,
  `Estimated_Useful_Life` double(1,0) DEFAULT NULL,
  `Monthly_Depreciation` double(10,2) DEFAULT NULL,
  `Estimated_Residual_Value` double(10,2) DEFAULT NULL,
  `Accumulated_Depreciation` double(20,2) DEFAULT NULL,
  PRIMARY KEY (`Asset_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And this is the procedure:

create procedure process_asset_update (in Cost_Of_Acquisition double, in Estimated_Useful_Life double, inout Monthly_Depreciation double, inout Estimated_Residual_Value double, inout Accumulated_Depreciation double, in Asset_ID integer)
BEGIN
    declare result double(10,2) default '0.00';
    select au.Accumulated_Depreciation into result from asset_update as au order by Asset_ID desc limit 1;
    set Monthly_Depreciation = (Cost_Of_Acquisition / Estimated_Useful_Life)/12;
    set Estimated_Residual_Value = 10 * (Cost_Of_Acquisition);
    if result is null then set Accumulated_Depreciation = Monthly_Depreciation;
    else set Accumulated_Depreciation = Monthly_Depreciation + result;
    end if;
END|

Right now it does not work correctly because it just sums the previous Monthly_Depreciation instead of the Accumulated_Depreciation, I'm probably doing something terribly wrong, but at the moment I really don't understand what, could be the cache, because it seems it registers the value of the first row when performing the second insert and from then it doesn't change anymore.

The output is:

mysql> select Asset_ID as ID, Cost_Of_Acquisition as CoA, Estimated_Useful_Life as EUL, Monthly_Depreciation as MD, Accumulated_Depreciation as AD from asset_update;
+----+------+------+-------+-------+
| ID | CoA  | EUL  | MD    | AD    |
+----+------+------+-------+-------+
|  1 | 2500 |    5 | 41.67 | 41.67 |
|  2 | 2000 |    4 | 41.67 | 83.33 |
|  3 | 3000 |    6 | 41.67 | 83.33 |
|  4 | 1500 |    3 | 41.67 | 83.33 |
|  5 | 5000 |    9 | 46.30 | 87.96 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

Live example: http://sqlfiddle.com/#!2/7be77/1/0

I will try to fix it, but I hope, in the meaning time, it helps to find a solution.

[mmcdonald]

So when the new monthly depreciation is 20 the new accumulated depreciation becomes 120 because the previous accumulated value was 100? Am I correct?

Correcticus!

If an asset has the value of £1000 and depreciates over 24 months then it has an annual depreciation value of £500 and a monthly depreciation rate of £41.67. The Accumulated depreciation of the asset between months 1 and 10 is £416.70. The value of the asset at month end 12 is £500.

My assets all have fixed depriciation, thankfully! :)

[cereal]

Solved:

create procedure process_asset_update (in Cost_Of_Acquisition double, in Estimated_Useful_Life double, inout Monthly_Depreciation double, inout Estimated_Residual_Value double, inout Accumulated_Depreciation double, in Asset_ID integer)
BEGIN
    declare result double(10,2) default '0.00';
    select au.Accumulated_Depreciation into result from asset_update as au order by au.Asset_ID desc limit 1;
    set Monthly_Depreciation = (Cost_Of_Acquisition / Estimated_Useful_Life)/12;
    set Estimated_Residual_Value = 10 * (Cost_Of_Acquisition);
    if result is null then set Accumulated_Depreciation = Monthly_Depreciation;
    else set Accumulated_Depreciation = Monthly_Depreciation + result;
    end if;
END|

It was the order by statement of the select query, it was referring to Asset_ID variable of the procedure not to the field name of the table, just change it to au.Asset_ID. Now it outputs:

+----+------+------+-------+--------+
| ID | CoA  | EUL  | MD    | AD     |
+----+------+------+-------+--------+
|  1 | 2500 |    5 | 41.67 |  41.67 |
|  2 | 2000 |    4 | 41.67 |  83.33 |
|  3 | 3000 |    6 | 41.67 | 125.00 |
|  4 | 1500 |    3 | 41.67 | 166.67 |
|  5 | 5000 |    9 | 46.30 | 212.96 |
+----+------+------+-------+--------+
5 rows in set (0.00 sec)

Live example: http://sqlfiddle.com/#!2/af493/1/0
Full code is attached.

As reference, the issue in my previous code is due to a naming conflict:

• http://dev.mysql.com/doc/refman/5.5/en/stored-program-restrictions.html#stored-routine-name-conflicts

In addition, since float and double numbers are not accurate you should switch to decimal, read this for more information:

• http://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

Bye!

[Benjamin_4]

@cereal WOW!!!! man you have reaally made my day thanks thanks thanks:)
@mmcdonald programmers never say never

[mmcdonald]

@Benjamin

@mmcdonald programmers never say never

You'll actually find that I haven't used 'never' once on this page :) I actually said good luck!

Michael

[Benjamin_4]

sorry for the bother but i reall need help with this too. How can i add this code to the one you just answered?
this tells when the depreciation should start:

SELECT  Cost_Of_Acquisition AS originalCost, Date_Acquired, Estimated_Useful_Life,
       IF (monthsDepreciated >= Estimated_Useful_Life * 12, Estimated_Useful_Life*12, monthsDepreciated) AS monthsDepreciated, 
       monthlyDepreciation, 
       IF (monthsDepreciated >= Estimated_Useful_Life * 12, Cost_Of_Acquisition, monthsDepreciated * monthlyDepreciation) AS totalDepreciation,
       IF (monthsDepreciated >= Estimated_Useful_Life * 12, 0, Cost_Of_Acquisition - monthsDepreciated * monthlyDepreciation) AS depreciatedValue
FROM (
    SELECT
           Cost_Of_Acquisition,
           Estimated_Useful_Life,
           Date_Acquired, 
           (YEAR(NOW())*12 + Month(NOW())) - ( YEAR(Date_Acquired)*12 + Month(Date_Acquired)) AS monthsDepreciated,
           (Cost_Of_Acquisition - Estimated_Residual_Value)/Estimated_Useful_Life AS monthlyDepreciation
    FROM asset_update
    ) AS X
ORDER BY Date_Acquired;

I will appreciate the help.

[cereal]

Can you explain me more? You want to add it to the procedure or you want to use as separate query to analyze the rows?

Note: my previous example is fine for inserts, but it is not good for updates queries because it should loop every following row with the new values. I'm not sure how to deal with that. Probably the update trigger needs a separate procedure, or the accumulated depreciations should be calculated on the fly with a query, saving for example everything in a temporary table.

[Benjamin_4]

use as seperate procedure but if it can be added to this procedure it will be great

[cereal]

Sorry for my late reply,

the problem with your above query is that it will return multiple rows, in general if you want to get results through the variables of a procedure, then you can return only single rows. Your query returns these kind of results: http://sqlfiddle.com/#!2/44386/1 But I do not understand how you want to apply that.

I need to see the table structure, right now I'm going blindly, so I need the output of this command:

show create table asset_update;

In your first code there was no Date_Acquired column, so I didn't considered this in the insert of values and I do not know how this is set. Can you provide a sample data?

From what I understand you want to set a range limit basing on the Estimated_Useful_Life or/and on the Date_Acquired column, correct? What it should happen when the data inserted is out of range? It is not considered?

At the moment the procedure gets the monthly depreciation of the previous row and adds it to the current, and that's all; you want to use some IF statements to fill or not some columns? In practice, try to explain what you want to achieve with the above query, that way me or someone else will be able to help. Bye!

[Benjamin_4]

Asset_ID Date_Acquired Item_Code Serial_Number Cost_Of_Acquisition Monthly_Depreciation Months_Depreciated Estimated_Useful_Life Estimated_Residual_Value Accumulated_Depreciation Depreciation_Start_Date User Status Disposal_Date

[Benjamin_4]

that is my asset_update table

[Benjamin_4]

what i want really is to calculate the monthly derpreciation of an asset based on these conditions:the Depreciation_Start_Date.that is when it should start calculating,the asset should stop calculating when the Disposal_Date is selected. IT the system should also depreciate the asset every month till it is disposed or the disposal date is selected. This are my main challenges right now.
I Hope this makes sense now

[cereal]

Hi, this is not enough, at least for me, to solve the problem:

• Is the Asset_ID a foreing key that points to another table or this is an auto increment key?
• If there is another table involved, can you provide all the schemas?
• Is Asset_ID unique in the asset_update table?
• Is this table temporary or are you using an engine different from myisam?
• When and how the Depreciation_Start_Date and the Disposal_Date are inserted and selected?

Your insert queries do not consider the date colums, and your last query does not consider the Disposal_Date. I'm asking for the structure of the tables from my first post in this thread, a list of the column names does not help me, so:

show create table asset_update;

And any other table involved. If the date colums, for example, are stored into a foreing table assets, then you can use the asset_update to generate the monthly depreciations, basing:

• on the given range date set in the assets table,
• or on the current month

Currently it seems these dates needs to be inserted sooner or later, but it's not clear how: as update, as insert?

Anyway, if you're using a schema with two or more tables, please provide as much information as possible. Otherwise me or any other user will continue to suggest wrong solutions. I've started trying with a schema as I'm supposing above, but at the moment I'm really busy, so I cannot test. In fact, excuse me if I'm not very present right now, I'm moving to a new home and in the next few weeks I will probably not have an internet access. In the meaning time I hope you solve it, if not, as soon as I can, I will try to help, bye!