Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Depreciation Formulas

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

    Default

    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

  2. #2
    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

    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 ]

  3. #3
    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'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.

  4. #4
    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

    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.

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

    Default

    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.

  6. #6
    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

    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 ]

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

    Default

    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.

  8. #8
    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

    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)

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

    Default

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

  10. #10
    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

    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.

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
  •