+ Reply to Thread
Results 1 to 3 of 3

Formulas to show all previous depreciation combined, and also current depreciation.

  1. 11-16-2012, 02:06 PM #1
    Morisk
    Morisk is offline
    Registered User
    Join Date
    11-16-2012
    Location
    Grand Haven, MI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Formulas to show all previous depreciation combined, and also current depreciation.

    What happens when you ask a production manager to help out with creating an accounting excel sheet?? You find a forum! Appreciate any help in advance!

    I need to create a formula that will calculate straight line depreciation. I need it to show a combined total of all previous years/months depreciation for an item, and also the current years depreciation. I have included a layout of how they would like it to look. I have come real close with some formulas I have found online, but not quite there yet.

    Thank you again,

    Mo
    Attached Files Attached Files
    Last edited by Morisk; 11-16-2012 at 03:11 PM.
    Reply With QuoteRegister To Reply
  2. 11-20-2012, 05:55 AM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Formulas to show all previous depreciation combined, and also current depreciation.

    Is this solved?

    If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 11-20-2012, 09:05 AM #3
    Morisk
    Morisk is offline
    Registered User
    Join Date
    11-16-2012
    Location
    Grand Haven, MI
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Formulas to show all previous depreciation combined, and also current depreciation.

    The formula for all prior years depreciation is:

    =IF(F11=0,"",SLN(H11,0,F11*12)*MIN(F11*12,((N$1-YEAR(D11))*12+12-MONTH(D11)+1)))

    N1 is where I entered the the "up to" year.
    F11 is the useful life.
    H11 is the cost.
    D11 is purchase date.

    I had to add the "if" function in there to get rid of the div/0 error. I wanted to be able to have extra lines for accounting to be able add lines and that was unsightly.

    The formula for the current year is:

    =IF(F11=0,"",MIN(H11-N11,SLN(H11,L11,F11)))

    This formula will now not depreciate further than 0.

    So far I have not found any issues. If when I get to actually filling the form all in with the data and I come by something I will post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1