Depreciation Calculation.

[Sarfraz Khan]

Dear Sir,

I have attached my FA register and I wanted to calculate depreciation on the same.

1) Description is the Name of Assets

2) F column is date of purchase

3) G column is Cost of Assests

4) H column is Rate of Dep

5) Column I to P is Years from Mar 2007 to Mar 2014.


1) Result Wanted:



*) My Financial Year is April to Mar

*) If Date of purchase is Oct 2007 then Dep Should Calculate in year Mar 2008 as per dep rate

*) If Date of purchase is Dec 2009 then Dep Should calculate in year Mar 2010 as per dep rate

*) If date of purchase is April 2011 then Dep should calculate in year MR 2012. as per dep rate


2) Result Wanted :

*) If Date of purchase is Oct 2007 then Dep Should Calculate in year Mar 2008 as per dep rate and for Mar 2009 it should calculate from 1st April 2008 to 31 mar 2009 so the total days would one year = 365 days.

Please advice right formula.

 

[Chihiro]

To calculate depreciation value/rate. It normally requires following values. Original Cost/Value, Salvage value, life expectancy.
IE. How much did it originally cost, and how much of cost can you recuperate after number of years.

Lets say, you bought a server for $3,500.00. Typically life of server is 5 years (though you can stretch it quite a bit, but in that case Salvage value will likely be $0). Lets say at end of 5 years, you can salvage 10% of it's value.

If it's linear depreciation then.
Depreciation value = (3500-350)/5 = 630
Excel formula is =SLN(Orig Cost, Salvage Value, Life)

There are other depreciation calculation with few differences. But all of them will place higher depreciation value at 1st period (as often is the case in real life).
In your case you may want to use DB() function. This function uses fixed rate to calculate depreciation values.

=DB(Orig Cost, Salvage Value, Life, Period, Month)

Life is in years; Period indicates age (Ex. year 2); Month is optional but is used to indicate specific number of month in first period/year. If left out it will use 12.

However, if you must use predetermined rate of 4.75% depreciation rate... can you verify that depreciation rate is applied each period to adjusted value and not original value?

 

[Sarfraz Khan]

To calculate depreciation value/rate. It normally requires following values. Original Cost/Value, Salvage value, life expectancy.
IE. How much did it originally cost, and how much of cost can you recuperate after number of years.

Lets say, you bought a server for $3,500.00. Typically life of server is 5 years (though you can stretch it quite a bit, but in that case Salvage value will likely be $0). Lets say at end of 5 years, you can salvage 10% of it's value.

If it's linear depreciation then.
Depreciation value = (3500-350)/5 = 630
Excel formula is =SLN(Orig Cost, Salvage Value, Life)

There are other depreciation calculation with few differences. But all of them will place higher depreciation value at 1st period (as often is the case in real life).
In your case you may want to use DB() function. This function uses fixed rate to calculate depreciation values.

=DB(Orig Cost, Salvage Value, Life, Period, Month)

Life is in years; Period indicates age (Ex. year 2); Month is optional but is used to indicate specific number of month in first period/year. If left out it will use 12.

However, if you must use predetermined rate of 4.75% depreciation rate... can you verify that depreciation rate is applied each period to adjusted value and not original value?

Dear Sir,
In my case dep value will remain same on each year eg : 4.75 %, life of assets is not required in this case.
The only formula would be (cost of assets * 4.75% /365* number of day) from date of purchase to 31 mar of that particular year.

If I calculate I need to check the financial year manually then put the formula for each assets that's why I wanted to write a formula which will put dep amount for that specific year.

eg : if I purchase assets on 5th May 2009 I need to put formula manually at 31 Mar 2010 column, else amount will show in mar 2007, mar 2008 as well,

 

[Chihiro]

In that case.
In I3: =IF($G3*(I$1-MIN($F3,I$1))*(0.0475/365)=0,"",$G3*(I$1-MIN($F3,I$1))*(0.0475/365))

In J3: =IF(I3="",IF($G3*(J$1-MIN($F3,J$1))*(0.0475/365)=0,"",$G3*(J$1-MIN($F3,J$1))*(0.0475/365)),$G3*(0.0475/356)*365)

Copy down.

See attached.

Edit: Realized there is typo in J3 formula. replace /356 with /365.