IF function to find depreciation
Results 1 to 9 of 9

Thread: IF function to find depreciation
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF function to find depreciation

    panie
    View Public Profile
    Send a private message to panie
    Find More Posts by panie
    Add panie to Your Contacts

    i have to find the depreciation value for a particular project, in order to find the NPV.

    The depreciation method to be used is 'sum of digits method'
    Life of the project= 5
    Cost of investment is = USD 100, 000

    Depreciation for 5 years using IF function?

    NOTE: FOR THOSE WHO DOSENT KNOW WHAT 'SUM OF DIGITS' method is.

    Year 0 1 2 3 4 5
    sum 5 4 3 2 1 0 = 15

    thus, year 01 depreciation= (100,000/15)* 5
    year 02 = (100,000/15)*4

    Logic: Higher depreciation is allocated to the first few years.

  2. #2
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function to find depriciation

    i guess there is no need to use the if funtion, jus use the foll formula in cell B1(or 1st row of any column and drag it down)

    =100000*(row()/15)

    if the initial investment/useful life is a variable, u may suitably give cell reference instead of the constants that i have used.

    hope this helps,
    Regards,
    Nachiket

  3. #3
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function to find depreciation

    I confess I'm not quite sure I know what you're looking to do ... perhaps something like the attached - ie est. accum. depreciate as at given point in time in life of asset ?

    Sheet1

     AB
    1Years5
    2Cost100,000.00
    3  
    4Dep.6,666.67
    5Year2
    6Accum.20,000.00

    Spreadsheet Formulas
    CellFormula
    B4=(B2/((B1*(2+(B1-1)))/2))
    B6=($B$5*(2*$B$4+(($B$5-1)*$B$4)))/2


    Excel tables to the web >> Excel Jeanie HTML 4
    Does my a$$ look big in this picture ?

  4. #4
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function to find depreciation

    edit: scrap the above - I missed the point re: front loading...

    Front Loaded Version would be:

    B6: =$B$2-((($B$1-($B$5))*(2*$B$4+((($B$1-($B$5))-1)*$B$4)))/2)
    Last edited by DonkeyOte; Dec 7th, 2009 at 02:03 PM.
    Does my a$$ look big in this picture ?

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Location
    St. Louis, MO
    Posts
    1,561
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function to find depreciation

    Without a helper for the depreciate amount:


    AB
    1Cost$ 100,000.00
    2Years to Depreciate5
    3Years Passed1
    4Accum$ 33,333.33

    Spreadsheet Formulas
    CellFormula
    B4=B1-((B2-B3)*((B2-B3)+1))/2*(B1/((B2*(B2+1))/2))


    Excel tables to the web >> Excel Jeanie HTML 4
    Never stop learning

    Excel 2013 Windows 7 (work)
    Excel 2007 Windows XP (home)

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function to find depreciation

    If only I were good at math .... thanks for the clean up c_m_s_jr
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Mar 2009
    Location
    St. Louis, MO
    Posts
    1,561
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: IF function to find depreciation

    Oh please Donkey. I've seen many of your post and you've cleaned up far more of my formulas. Besides I am an accountant by trade so you could argue I "cheated" since I deal with this kind of stuff all the time.

    Now if I were only good at VBA.
    Never stop learning

    Excel 2013 Windows 7 (work)
    Excel 2007 Windows XP (home)

  8. #8
    New Member
    Join Date
    Dec 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: IF function to find depriciation

    thankss nachiket.. i knw the answer can be derived pretty easily with a simple formula. bt my teacher is complicating my life by asking to incorporate an if function to the sum. lol

    BDW thanks for the tip!!


    Quote Originally Posted by nachiketdp View Post
    i guess there is no need to use the if funtion, jus use the foll formula in cell B1(or 1st row of any column and drag it down)

    =100000*(row()/15)

    if the initial investment/useful life is a variable, u may suitably give cell reference instead of the constants that i have used.

    hope this helps,
    Regards,
    Nachiket

  9. #9
    New Member
    Join Date
    Dec 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF function to find depreciation

    woow! this is so great! this is my first visit to site and it is soo awasome!!!
    and thankss for the great illustration too.
    I wonder whether why does my teacher wants me to incorporate an if function here!!

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
  •