Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Straight line depreciation (2000 SR1)

    I need help with a straight line deprection formula. Any assistance would be appreciated

    For example a piece of machinery costs 512,556 and has a life expectancy of 18 years and at the end of 18 years the value will be 0. I need to find out what the value of the machinery will worth at any specified year, let say at 15.

    My math is incredibly poor, that is why I use Excel <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Straight line depreciation (2000 SR1)

    In this case, it is (1 - 15/18) * 512,556

    More general:
    Original worth in cell B1 (in your example: 512,556)
    Remaining worth in cell B2 (in your example: 0)
    Life expectancy in cell B3 (in your example: 18)
    Age in cell B4 (in your example: 15)
    Worth at specified age: =(1-B4/B3)*(B1-B2)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Straight line depreciation (2000 SR1)

    Thank you, Hans

    The request that was presented to me was for a function and I was struggling with the VDB and DB functions. Your solution seems straightforward and relatively easy for me to understand, but is there a function for those die-hard function fans?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Straight line depreciation (2000 SR1)

    Directly you can use:
    =TREND({512556,0},{0,18},15)

    Or if values in cells:
    If the starting time is in A1 = 0
    Life expectancy in cell A2 = 18

    Starting worth in B1 = 512,556
    Remaining worth in cell B2 = 0
    Age in cell B3 =15

    You can use trend:
    =TREND(B1:B2,A1:A2,B3)

    You can use Slope and Intercept
    =SLOPE(B1:B2,A1:A2)*B3+INTERCEPT(B1:B2,A1:A2)

    Or direct interpolation:
    =+B1+(B1-B2)/(A1-A2)*(B3-A1)

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Straight line depreciation (2000 SR1)

    Another happy customer, they liked good old fashioned trend.

    Thank you

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Straight line depreciation (2000 SR1)

    I struggled for years with solutions like you suggest until I stumbled across this in the Online Help:
    <hr>SLN
    See Also

    Returns the straight-line depreciation of an asset for one period.

    Syntax

    SLN(cost,salvage,life)

    Cost is the initial cost of the asset.

    Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).

    Life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).

    Example

    Suppose you've bought a truck for $30,000 that has a useful life of 10 years and a salvage value of $7,500. The depreciation allowance for each year is:

    SLN(30000, 7500, 10) equals $2,250<hr>
    Anyone ever had problems with this built-in function?
    Gre

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Straight line depreciation (2000 SR1)

    In principal (whether XL calcs it this way or not) isn't the SLN value just:

    =(cost-salvage)/life

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Straight line depreciation (2000 SR1)

    Sure is. <img src=/S/yep.gif border=0 alt=yep width=15 height=15> I suspect that the SLN function has been around since at least Excel 95. AFAIK every user I have encountered goes back and reinvents the wheel; rather than using the built-in function - although they'll turn to Online Help for Double Declining Depreciation. It is just a mixture of wondering whether there is an obscure "known issue" and wondering at the temptation to reinvent the wheel.
    Gre

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Straight line depreciation (2000 SR1)

    Personally, I would reinvent the calc and use
    =(cost-salvage)/life

    Since, to me, it is more understandable, than to use a builtin function. I usually save the built-in ones for the more complicated ones. But, that's me: I would rather derive the formula to make sure I understand the principles.

    Steve

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Straight line depreciation (2000 SR1)

    Hi unkamunka,

    SLN won't give Hetty the answer she was looking for. All SLN gives is the depeciation for one period. To get the residual value, you'd need something like:
    =cost -SLN(cost,salvage,life)*term
    where term is the number of periods over which the asset has been/is being depreciated. This doesn't seem to be much simpler than building the whole lot from scratch as Hans & Steve did.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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