Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Depreciation Formulas

  1. #11
    New Member
    Join Date
    Apr 2002
    Location
    PCC
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #13
    New Member
    Join Date
    Apr 2002
    Location
    PCC
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #14
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...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. #15
    New Member
    Join Date
    Apr 2002
    Location
    PCC
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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