Depreciation Formulas

[kenmar1]

are there some simple formulas that will allow me to calculate depreciation of assets (by month life) so that if the asset was placed in service mid-year it would calculate the partial year rather than the full year minus salvage value and return the yearly accumulated depreciattion.
Example:
truck cost $110,000 with a salvage value of $10,000 bought in 12/1/01 with a life of 120 months (10 years). I've got all the formulas for all the values yearly depreciation (cost - salvage value/life)but I need to find a way to calculate only the current fiscal year accumulated depreciation (7/1/01 to 6/30/02).
Am I making this too difficult?
Thanks - Ken

[Mark W.]

See the Excel Help Index topics for...

"DB worksheet function"
"DDB worksheet function"
"SLN worksheet function"
"SYD worksheet function"
"VDB worksheet function"

Perhaps VDB is your best bet.

[ This Message was edited by: Mark W. on 2002-04-24 17:09 ]

[kenmar1]

Thanks Mark - That's very helpful.
using VDB I can calulate the months easily.
Question, how do I calcualte by months between dates?
I want to calculate the depreciation from an asset that was placed in service in mid year (say december) and only record the six months depreciation this year but record the full year for other assets...and do it all with one or two formulas.

[Mark W.]

On 2002-04-25 10:09, kenmar1 wrote:
Thanks Mark - That's very helpful.
using VDB I can calulate the months easily.
Question, how do I calcualte by months between dates?
I want to calculate the depreciation from an asset that was placed in service in mid year (say december) and only record the six months depreciation this year but record the full year for other assets...and do it all with one or two formulas.

Don't these VDB examples cover this?


VDB(2400, 300, 10, 0, 1) equals $480.00, the first year's depreciation.

VDB(2400, 300, 120, 6, 18) equals $396.31, the depreciation between the sixth month and the eighteenth month.

If you need to calculate the months between 2 dates you can use...

=DATEDIF(A1,B1,"M")

...where A1 and B1 contain date values.

[kenmar1]

Yes...that part I got, thanks.
In order to calculate the depreciation for this fiscal year only (ignore the previous years depreciation).

Purchased 3/1/00
This fiscal year starts 7/1/01
=VDB(C3,D3,E3,0,K3,FALSE) K3 is the field that I need to calculate only the amount of months in this fiscal year only, say 7/1/01 to now() or whatever day I run the report and disregard the rest.
So, if I can find a formula that will take the dates prior to 7/1/01 and only calculate foward, I can put that number in as K3 and it should work.

[Mark W.]

On 2002-04-25 12:51, kenmar1 wrote:
Yes...that part I got, thanks.
In order to calculate the depreciation for this fiscal year only (ignore the previous years depreciation).

Purchased 3/1/00
This fiscal year starts 7/1/01
=VDB(C3,D3,E3,0,K3,FALSE) K3 is the field that I need to calculate only the amount of months in this fiscal year only, say 7/1/01 to now() or whatever day I run the report and disregard the rest.
So, if I can find a formula that will take the dates prior to 7/1/01 and only calculate foward, I can put that number in as K3 and it should work.

=DATEDIF("7/1/01",TODAY(),"M")

July 1st 2001 was just over 9, but not quite 10 months ago.

[ This Message was edited by: Mark W. on 2002-04-25 13:01 ]

[kenmar1]

Thanks! That works great for assets over a year or more....one final (I sure hope!)
How would you not count the months from 7/1/01 to 12/1/01 if an asset was place in service on 12/1/01?
In otherwords depreciate partial year life based on purchase date but not go over 12 months.

[Mark W.]

On 2002-04-25 13:27, kenmar1 wrote:
Thanks! That works great for assets over a year or more....one final (I sure hope!)
How would you not count the months from 7/1/01 to 12/1/01 if an asset was place in service on 12/1/01?
In otherwords depreciate partial year life based on purchase date but not go over 12 months.

Take a look at the DB worksheet function. Wouldn't this be...

=DB(cost,salvage,life,period,1)

[kenmar1]

I couldn't seem to get that to work.
Seems to me I have to have a formula that ties to the purchase date.
If the purchase date is less than a full year i.e. 12/1/01, calc the time from 12/1/01 to today()otherwise use the =DATEDIF("7/1/01",TODAY(),"M")
statement....

[Mark W.]

On 2002-04-25 15:45, kenmar1 wrote:
I couldn't seem to get that to work.
Seems to me I have to have a formula that ties to the purchase date.
If the purchase date is less than a full year i.e. 12/1/01, calc the time from 12/1/01 to today()otherwise use the =DATEDIF("7/1/01",TODAY(),"M")
statement....

You need to think in terms of "periods" which I presume for you are months. Perhaps it would help if you gave me the values for...

cost,
salvage,
life,
start_period,
end_period

...and what you believe to be the resulting depreciation. Then I could help you with a specific formula.