Thanks:  0
Likes:  0

1. 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!

2. 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,1,9.5) produces \$791.67 (1st month)

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

=VDB(10000,500,120,0,DATEDIF("12/1/01","7/1/02","M")+12,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 ]

3. 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?

4. ...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,12,24,0) or \$950.00

...remember, the 1st period begins at 0

[ This Message was edited by: Mark W. on 2002-04-26 12:11 ]

5. 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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•