Excel SYD Function

by Obaidullah Jan, ACA, CFA

SYD is an Excel function that calculates the depreciation expense under the sum of the years digits method.

Sum of years digits is an accelerated depreciation method, i.e. it charges higher depreciation in initial years of the asset and the depreciation expense declines over time. Depreciation expense is charged in proportion of the remaining useful life of the asset at the start of the period to the sum of digits of all years in the useful life. For example, if an asset has useful life of 5, the sum of years digits equals 15 (=5 + 4 + 3 + 2+ 1) and in the first year, 33% of the depreciable amount is written off as depreciaiton.

Under the sume of years digits method, depeciation expense for a period is calculated based on the following formula:

$$ SYD\ Depreciation \\=Depreciable\ Amount \times \frac{Remaining\ Useful\ Life}{N} $$

Depreciable amount equals cost minus the salvage vale. Remaining useful life shows the remaining number of periods of the asset's life at the start of the peirod for which we intend to calculate depreciaton. N is the sum of years digits.

Instead of manually calculating the sum of the years digits, we can use the following formula

$$ N=\frac{n\times(n+1)}{2} $$

Where n is the useful life of the asset.

Syntax

SYD’s syntax is:

SYD(cost, salvage, life, per)

Cost refers to the historical cost of the asset being depreciated.

Salvage value is the value of the asset at the end of its useful life. It is also called residual value.

Life refers to the total useful life of the asset, expressed in the same time unit for which we intend to calculate depreciation expense

Per refers to the number of period for which we want to determine depreciation expense.

Example

The following screenshot illustrates the use of SYD function to calculate depreciation for an asset:

Excel SYD Function

You can see that total depreciation expense under all the methods is same except for a little rounding-off in the declining balance method. Different methods just allocates total cost differently over different periods.