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

 

[kenmar1]

The fiscal year is 7/1 to 6/30 any year.
For assets over a year the vdb seems to work fine.
FY 7/1/01 - 6/30/02
Cost $10,000
Salvage value (5%) $500
Life 120 months (10 years)
Annual Depreciation $9,500 (cost-salvage*life)
Purchase date 12/1/01 (mid year)
Monthly Deprec. $791.67
So the amount that should be depreciated for this asset for this year should equal $5541.69
I am trying to find a formula that will or course choose between a full and part year when needed.
Thanks for your help with this - it's driving me nuts cause I know it's there!

 

[Mark W.]

Okay, maybe when this is over I'll know a bit more about accounting and you'll know more about Excel.

=VDB(10000,500,120,0,<font color=red>1</font>,9.5) produces $791.67 (1st month)

=VDB(10000,500,120,0,<font color=red>DATEDIF("12/1/01","7/1/02","M")</font>,9.5) produces $4386.07 (the partial year)

=VDB(10000,500,120,0,<font color=red>DATEDIF("12/1/01","7/1/02","M")+12</font>,9.5) produces $7913.40 (at end of 2nd FY)

These values don't quite agree with yours. Why?
This message was edited by Mark W. on 2002-04-25 17:36

 

[kenmar1]

I'm no formula guru, but I believe my orginal numbers were wrong because if you use VDB as it stands, it uses the double-declining balance method for depreciation and we need to use the straightline method. So if you set the factor to zero, you get the correct depreciation amount.
On the example I used above the annual amnount should be $950.00 and the monthly should be $79.17 so the amount of depreciation on a $10,000 asset with a salvage value of $500 and a life of 120 months for one year should be $950 and the amount of depreciation for 7 months (12/1/01 to 7/1/02) would be $554.17. which is what you get if you set the factor to 0.
But even this does not calulate correctly if the asset is purchased in the prior fiscal year and is over 12 months old. It calulates all the depreciation not just the current fiscal year. Or am I just missing something?

 

[Mark W.]

...But even this does not calulate correctly if the asset is purchased in the prior fiscal year and is over 12 months old. It calulates all the depreciation not just the current fiscal year. Or am I just missing something?

I think you're missing something. Here's the function with its argument (optional ones italicized)...

=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)


...so, the depreciation of the 2nd complete year is...

=VDB(10000,500,120,<font color=red>12,24</font>,0) or $950.00

...remember, the 1st period begins at 0
This message was edited by Mark W. on 2002-04-26 12:11

 

[kenmar1]

Thanks Mark!
That works perfectly and if I use cell that references the month automatically in the formula, it calcs it out from whatever period I want. I just have to remember to change periods when I run the reports - very doable.
Preciate your time and effort on this.
Ken