Depreciation - Sum of Years Digits  



Sum-of-Years' Digits is a depreciation method that results in a more accelerated write-off than straight line, but less than the declining-balance method or double-declining-balance method. Under this method annual depreciation is determined by multiplying the Depreciable Cost by a fraction. The numerator is the current year. The denominator is the sum of the years of the life of the asset. If the asset has a life of 5 years the denominator would be 15 computed by 1+2+3+4+5. The first year’s depreciation would be 5/15 times the cost of the asset. The second year would be 4/15 times the cost of the asset. The fifth year would be 1/15 times the cost of the asset. The sum of all the fractions equals 5/15 this gives us a 100% depreciation. The Excel SYD function performs these computations for us.

The Syntax for the SYD function is:
= SYD(cost, salvage, life, period)


Argument    Description
Cost The original cost of the asset
Salvage The value of the asset at the end of its expected useful life
Life The useful life of the asset.  It is the number of periods over which the asset is being depreciated
Period The period for which you want to calculate the depreciation

The following formula would compute the Sum of the Years depreciation for the first year for an item that cost 10,000, has a salvage value of 1000, and a useful life of 8 years.

=SYD(10000, 1000, 8, 1)

The following formula would compute the Sum of the Years depreciation for the third year for an item that cost 19,000 has no salvage value, and a useful life of 10 years.

=SYD(19000,0,10, 3) 

Practice for Sum of Years Digits

  1. Create a new worksheet named SYD
    2. .
  2. Enter the worksheet data as shown below:

    3. Sum of Years Digits starting spreadsheet

  3. Enter =SYD( in cell B7.  Excel’s intellisense displays the list of arguments.  
    Click on cell B2 then press F4 to create an absolute address. Enter a comma.
    Click on cell B3 then press F4. Enter a comma.
    Click on cell B4 then press F4. Enter a comma.
    Click on cell A7. Type a right parenthesis. Press Ctrl + Enter.
    Using the AutoFill handle drag down through cell B11.

  4. The Accumulated Depreciation for the first year is the same as the depreciation for the first year.
    Enter =B7 in cell C7 then press Enter.
    The Accumulated Depreciation for the following years is computed as the Accumulated Depreciation up to this time, plus the Depreciation for the current year. 
    Enter =C7 + B8 in cell C8.
    Using the AutoFill handle, drag down through cell C11.

  5. The Book Value is the Cost of the Asset minus the Accumulated Depreciation.
    Enter =$B$2-C7 in cell D7 then press Ctrl + Enter.
    Using the AutoFill handle, drag down through cell D11.

  6. Press Ctrl + ~ to view your formulas.  They should appear as those below.

    Sum of Years Depreciation formulas

  7. Press Ctrl + ~ to return to normal view. Your results should be as shown below.

    8. Depreciation- Sum of Years' Digits results