Results 1 to 10 of 10

20031014, 13:01 #1
 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>

20031014, 13:08 #2
 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: =(1B4/B3)*(B1B2)

20031014, 13:27 #3
 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 diehard function fans?

20031014, 14:08 #4
 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+(B1B2)/(A1A2)*(B3A1)
Steve

20031014, 14:53 #5
 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

20031014, 18:08 #6
 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>SLNAnyone ever had problems with this builtin function?
See Also
Returns the straightline 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>Grüße

20031014, 18:21 #7
 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:
=(costsalvage)/life
Steve

20031014, 18:40 #8
 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 builtin 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.
Grüße

20031014, 19:24 #9
 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
=(costsalvage)/life
Since, to me, it is more understandable, than to use a builtin function. I usually save the builtin ones for the more complicated ones. But, that's me: I would rather derive the formula to make sure I understand the principles.
Steve

20031015, 03:24 #10
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]