# Thread: Straight line depreciation (2000 SR1)

1. ## 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. ## 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. ## 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. ## 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. ## Re: Straight line depreciation (2000 SR1)

Another happy customer, they liked good old fashioned trend.

Thank you

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

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?

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

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

#### Posting Permissions

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