The Excel SYD Function

Sum-of-Years' Digits Depreciation

When calculating the depreciation of an asset, it is common to use an accelerated depreciation calculation, in which the calculated value of an asset is reduced by a larger amount during the first period of its lifetime, and smaller amounts during subsequent periods.

One popular accelerated depreciation method is the sum-of-years' digits depreciation method, which calculates depreciation using the following equation:

SYD Function Equation

where,

  • cost = initial cost of the asset (at start of period 1);
  • salvage = final value of the asset at the end of its lifetime;
  • life = number of periods over which the depreciation occurs;
  • per = the period for which the depreciation is being calculated.

Function Description

The Excel SYD function calculates the sum-of-years' digits depreciation for a specified period in the lifetime of an asset.

The syntax of the function is:

SYD( cost, salvage, life, per )

where the function arguments are as follows:

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


SYD Function Example

In the example below, the SYD function uses the sum-of-years' digits depreciation method to calculate the yearly depreciation of an asset that costs $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.

 Formulas:
 A
1Depreciation
2=SYD( 10000, 1000, 5, 1 )
3=SYD( 10000, 1000, 5, 2 )
4=SYD( 10000, 1000, 5, 3 )
5=SYD( 10000, 1000, 5, 4 )
6=SYD( 10000, 1000, 5, 5 )
 Results:
 AB
1DepreciationAsset Value At End Of Period
23000.00- Value at end of Yr 1 is $10,000 - $3,000 = $7,000
32400.00- Value at end of Yr 2 is $7,000 - $2,400 = $4,600
41800.00- Value at end of Yr 3 is $4,600 - $1,800 = $2,800
51200.00- Value at end of Yr 4 is $2,800 - $1,200 = $1,600
6600.00- Value at end of Yr 5 is $1,600 - $600 = $1,000

As expected, subtracting the five yearly depreciation figures from the initial cost of $10,000 gives the required salvage value of $1,000.


Further examples of the Excel SYD function are provided on the Microsoft Office website.


Excel SYD Function Errors

If you get an error from the Excel SYD Function, this is likely to be one of the following:

Common Errors
#NUM!-

Occurs if either:

  • The supplied salvage argument is < 0;
  • The supplied life or the supplied per argument is ≤ 0;
  • The supplied per is greater than the supplied life argument.
#VALUE!-Occurs if any of the supplied values are non-numeric.