Straight line depreciation (2000 SR1)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Another happy customer, they liked good old fashioned trend.

Thank you

Reply | Quote

Another happy customer, they liked good old fashioned trend.

Thank you

Reply | Quote

I struggled for years with solutions like you suggest until I stumbled across this in the Online Help: [indent]

---

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

---

[/indent]Anyone ever had problems with this built-in function?

Reply | Quote

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

=(cost-salvage)/life

Steve

Reply | Quote

Sure is. 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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Sure is. 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.

Reply | Quote

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

=(cost-salvage)/life

Steve

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

I struggled for years with solutions like you suggest until I stumbled across this in the Online Help: [indent]

---

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

---

[/indent]Anyone ever had problems with this built-in function?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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)

Reply | Quote