SYD

Returns the depreciation of an asset for a given year using the sum-of-years'-digits method.

Syntax:

SYD(originalcost, salvagevalue, lifetime, year)

originalcost: the initial cost of the asset.

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

lifetime: the number of years over which the asset is being depreciated.

year: the year number for which the depreciation is calculated.

To calculate depreciation, SYD adds the year numbers during the asset’s life; the year numbers taken backward and divided by that sum give the proportion of the total loss of value to assign to depreciation that year.

For example with a 4 year depreciation, where the asset loses 5000 in value: the sum of the year numbers is 1 + 2 + 3 + 4 = 10, and the depreciation each year (in order) is 4/10ths, 3/10ths, 2/10ths then 1/10th of 5000.

The formula for a year's depreciation is

(originalcost - salvagevalue)*(lifetime+1 - year)*2 / [(lifetime+1)*lifetime]

Example:

SYD(10000, 2000, 4, 1)

returns 3200 in currency units, which is the depreciation in the first year for an asset which cost 10000 and is written down to 2000 over 4 years. The total loss of value is 8000; 3200 is 4/10ths of this.

Application:

Depreciating a Delivery Truck

Let's say a company, "Fresh Goods Inc.," purchases a new delivery truck to expand its business. The company needs to calculate the depreciation expense for this truck over its useful life.

Asset Details:

Cost of Asset (Initial Cost): $60,000
Salvage Value (Residual Value): $5,000 (The estimated value of the truck at the end of its useful life)
Useful Life: 5 years (The period over which the asset is expected to be used)

The company wants to determine the depreciation expense for each of the five years using the sum-of-years' digits method. The formula for the SYD function is:

SYD(cost, salvage, life, per)

Where:

cost is the initial cost of the asset.
salvage is the salvage value at the end of the depreciation.
life is the number of periods over which the asset is being depreciated.
per is the period for which you want to calculate the depreciation.

Calculation Breakdown

First, let's calculate the sum of the years' digits. The formula is: Sum = n∗(n+1)/2, where n is the useful life. Sum = 5∗(5+1)/2=5∗6/2=15

The depreciable base is the cost minus the salvage value: Depreciable Base = $60,000 - $5,000 = $55,000

Now, we can create a table to show the SYD function calculation for each year.

Depreciation Schedule for the Delivery Truck

	Year	SYD Function	Calculation	Annual Depreciation	Accumulated Depreciation	Book Value (at end of year)
	A	B	C	D	E	F
1	0	-	-	-	-	$60,000.00
2	1	SYD(60000, 5000, 5, 1)	($55,000∗5/15)	$18,333.33	$18,333.33	$41,666.67
3	2	SYD(60000, 5000, 5, 2)	($55,000∗4/15)	$14,666.67	$33,000.00	$27,000.00
4	3	SYD(60000, 5000, 5, 3)	($55,000∗3/15)	$11,000.00	$44,000.00	$16,000.00
5	4	SYD(60000, 5000, 5, 4)	($55,000∗2/15)	$7,333.33	$51,333.33	$8,666.67
6	5	SYD(60000, 5000, 5, 5)	($55,000∗1/15)	$3,666.67	$55,000.00	$5,000.00

Explanation of the Table

Year (per): This is the period for which the depreciation is calculated.
SYD Function: This column shows the Excel function that would be used to get the depreciation for that specific year.
Calculation: This shows how the SYD method works. The depreciation expense for each year is calculated by multiplying the depreciable base ($55,000) by a fraction. The numerator of the fraction is the number of remaining years of the asset's life, and the denominator is the sum of the years' digits (15).
Annual Depreciation: This is the depreciation expense for the specific year. You can see it's highest in year 1 and decreases over time.
Accumulated Depreciation: This is the total depreciation accumulated up to that year.
Book Value: This is the remaining value of the asset on the company's books. It is calculated by subtracting the accumulated depreciation from the initial cost of the asset. Notice that at the end of year 5, the book value is equal to the salvage value ($5,000), which confirms the calculation is correct.

This example demonstrates how the SYD function simplifies the calculation of depreciation using the sum-of-years' digits method, providing clear and decreasing depreciation expenses over the life of the asset.