Comparison of Depreciation Functions available in Google Sheets

Basic information about the fixed asset:

  • Asset Cost - 36,000
  • Salvage Value - 3,000
  • Life - 5 years
  • Highlighted - Highest depreciation expense that year

Year SLN – Straight Line DB – Declining Balance DDB – Double Declining Balance SYD – Sum of the Years Digits
1 6,600 14,112 14,400 11,000
2 6,600 8,580 8,640 8,800
3 6,600 5,217 5,184 6,600
4 6,600 3,172 3,110 4,400
5 6,600 1,928 1,666 2,200
Total 33,000 33,009 33,000 33,000

The three accelerated methods (DB, DDB, and SYD) calculate more depreciation in the early years, and straight line (SL) returns more depreciation expense in the later years. DDB is slightly faster than DB in this table. However, that difference between the two can be increased or decreased by adjusting the factor used in the DDB formula.

Notice that the declining balance method does not properly sum to the total cost less salvage value of the asset. This could create bookkeeping issues when balancing to your depreciation expense schedules.

 Live examples in Sheets 

Go to this spreadsheeet for a live version of the above table that you can study and use anywhere you would like.

DDB – Double Declining Balance depreciation function Google Sheets

The double declining balance depreciation method is one of the techniques used to depreciate an asset more quickly during the beginning of its life than the end. Unlike under the straight line method, the depreciation expense is not the same for every full period of depreciation. Rather, like its name, the amount of depreciation declines in each successive period.

See a table comparing the different depreciation amounts using the different methods.

Videos

Purpose

To return the value of one period of double declining balance depreciation expense.

Syntax

=DDB(cost,salvage,life,period,[factor])

  • cost – Acquisition cost of the asset. Includes purchase price and costs associated with its acquisition such as freight and sales tax.
  • salvage – Amount that you expect to receive in exchange for the asset at the end of its useful life. Typically, this is zero. However, an example of a case where this not zero is the expected trade-in value of an automobile.
  • life – Length of time that the asset is expect to be in service given in number of periods.
  • period – The period for which you are calculating depreciation expense.
  • [factor] – OPTIONAL. Factor used to increase depreciation instead of the default value of 2. Use this to increase or decrease the rate of depreciation with a higher number making the earlier amounts larger. If you do not specify a factor, it is the same as entering a 2.
  • Note: Be sure that life and period are in the same units (months or years).

Formulas

DDB formula

Examples

Example 1

An automobile is purchased for $40,000 that is expected to last 3 years and be traded-in for $4,000.

A B C
1 Data Argument Description
2 $40,000 cost acquisition cost
3 $4,000 salvage money back at end of life
4 3 life number of periods for useful life
5 1 period which period the expense is for

 

Formula Description Result
=DDB(A2,A3,A4,A5) Depreciation expense for first period $26,667
=DDB(A2,A3,A4,2) Depreciation expense for second period $8,889
=DDB(A2,A3,A4,3) Depreciation expense for final period $444

Notice that the three results add up to 36,004 instead of 36,000. Although this difference would be immaterial to a financial statement user, it could cause problems for bookkeepers trying to balance the accounts.

Example 2

A laptop computer is purchased for €3,000 that is expected to last 36 months and is expected to be worth €150 at the end of the three years.

FormulaDescriptionResult

=DDB(3000,150,36,11) Depreciation expense for month 11 €94

Note that months were used in the above table instead of years. The DDB function works with any type of time in input but it would typically be months or years.

 Live examples in Sheets

Go to this spreadsheeet for the examples of the DDB function shown above that you can study and use anywhere you would like.

SYD – Sum of the Years Digits depreciation function – Google Sheets

The sum of the years’ digits depreciation method is one of the techniques used to depreciate and asset more quickly during the beginning of its life than the end. Unlike under the straight line method, the depreciation expense is not the same for every full period of depreciation. Rather the amount of depreciation is greater in the early periods and declines in each successive period.

See a table comparing the different depreciation amounts using the different depreciation methods.

Videos

Purpose

To return the value of one period of sum of the years’ digits depreciation expense.

Syntax

=SYD(cost,salvage,life,period)

  • cost – Acquisition cost of the asset. Includes purchase price and costs associated with its acquisition such as freight and sales tax.
  • salvage – Amount that you expect to receive in exchange for the asset at the end of its useful life. Typically, this is zero. However, an example of a case where this not zero is the expected trade-in value of an automobile.
  • life – Length of time that the asset is expect to be in service given in number of periods.
  • period – The period for which you are calculating depreciation expense.

Formulas

SYD formula

Examples

Example 1

An automobile is purchased for 36,000 that is expected to last 3 years and be traded-in for 3,000.

A B C
1 Data Argument Description
2 $36,000 cost acquisition cost
3 $3,000 salvage money back at end of life
4 3 life number of periods for useful life
5 1 period which period the expense is for

 

Formula Description Result
=SYD(A2,A3,A4,A5) Depreciation expense for first period $16,500
=SYD(A2,A3,A4,2) Depreciation expense for second period $11,000
=SYD(A2,A3,A4,3) Depreciation expense for final period $5,500

Example 2

A laptop computer is purchased for €3,000 that is expected to last 36 months and is expected to be worth €150 at the end of the two years.

Formula Description Result
=SYD(3000,150,36,11) Depreciation expense for month 11 €111

 Live example in Sheets

Go to this spreadsheeet for a live version of the SYD function that you can study and use anywhere you would like.

SLN – Straight Line depreciation function in Google Sheets

Straight line depreciation is the most straight forward and the most common method of depreciation. Most accounting departments choose this method due to its simplicity and the fact that most fixed assets’ usefulness are consumed at a fairly even rate. If you buy a car, even though the fair market value declines most sharply in the first year, you probably are not planning on selling it anyway. The car still serves its function of getting you from point A to point B in the same way in year 5 as it does in year 1.

See a table comparing the different depreciation amounts using different depreciation methods.

Videos

Purpose

To return the value of one period of straight line depreciation expense.

Syntax

=SLN(cost,salvage,life)

  • cost – Acquistion cost of the asset. Includes purchase price and costs associated with its acquisition such as freight and sales tax.
  • salvage – Amount that you expect to receive in exchange for the asset at the end of its useful life. Typically, this is zero. However, an example of a case where this not zero is the expected trade-in value of an automobile.
  • life – Length of time that the asset is expect to be in service given in number of periods.

Formula

SLN formula

Examples

Example 1

An automobile is purchased for $40,000 that is expected to last 36 months and be traded-in for $4,000.

A B C
1 Data Argument Description
2 $40,000 cost acquisition cost
3 $4,000 salvage money back at end of life
4 36 life number of periods for useful life

 

Formula Description Result
=SLN(A2,A3,A4) Depreciation expense each period $1,000

Example 2

A laptop computer is purchased for €3,000 that is expected to last 24 months and have no value after the end of the two years.

Formula Description Result
=SLN(3000,0,36) Depreciation expense each period €83.33

DB – Declining Balance depreciation function in Google Sheets

The fixed declining balance depreciation method is one of the techniques used to depreciate an asset more quickly during the beginning of its life than the end. Unlike under the straight line method, the depreciation expense is not the same for every full period of depreciation. Rather, like its name, the amount of depreciation declines in each successive period.

See a table comparing the different depreciation amounts using different depreciation methods.

Related Videos

Purpose

To return the value of one period of declining balance depreciation expense.

Syntax

=DB(cost,salvage,life,period,[month])

  • cost – Acquisition cost of the asset. Includes purchase price and costs associated with its acquisition such as freight and sales tax.
  • salvage – Amount that you expect to receive in exchange for the asset at the end of its useful life. This formula will not work with a zero in this field.
  • life – Length of time that the asset is expect to be in service given in number of periods.
  • period – The period for which you are calculating depreciation expense.
  • [month] – OPTIONAL. The number of months in the first year of depreciation if it is not a full year.
  • Note: Be sure that life and period are in the same units (months or years). Only use the [month] option if you are using months for the period and life.

Formulas

DB formula

Examples

Example 1

An automobile is purchased for $40,000 that is expected to last 3 years and be traded-in for $4,000.

A B C
1 Data Argument Description
2 $40,000 cost acquisition cost
3 $4,000 salvage money back at end of life
4 3 life number of periods for useful life
5 1 period which period the expense is for

 

Formula Description Result
=DB(A2,A3,A4,A5 Depreciation expense for first period $21,440
=DB(A2,A3,A4,2) Depreciation expense for second period $9,948
=DB(A2,A3,A4,3) Depreciation expense for final period $4,616

Notice that the three results add up to 36,004 instead of 36,000. Although this difference would be immaterial to a financial statement user, it could cause problems for bookkeepers trying to balance the accounts.

Example 2

A laptop computer is purchased for €3,000 that is expected to last 36 months and is expected to be worth €150 at the end of the three years.

Formula Description Result
=DB(3000,150,36,11) Depreciation expense for month 11 €104

 Live example in Sheets

Go to this spreadsheeet for a live version of the DB function that you can study and use anywhere you would like.

Depreciation functions available in Google Sheets with examples

Depreciation is the accounting method used to spread the expense of purchasing a fixed asset over the time it is used. As GAAP and IFRS try to follow the matching principle, depreciation matches the expense of an asset to the same time period during which the asset is providing revenues. If a tangible asset is expected to provide value to an organization for more than one accounting period, then it should be capitalized and expensed over that period of time using a depreciation method.

Straight Line

As a practical expedient, book (not tax) depreciation is typically calculated on the straight line basis. This involves a relatively simple calculation which outputs an expense amount for each period which can be days, months, quarters, etc.

If you had a company policy of computer equipment having a useful life of three years, the calculation would be to divide the acquisition cost by 36 months and multiply the result by the number of months in the period.

Accelerated methods

Some fixed assets may lose their value at a faster rate in the beginning of their life and a slower rate towards the end. This pattern can be shown with an accelerated method such as declining balance, double declining balance, or sum of the years digits.

 Live examples in Sheets 

Live versions of the SYD, DDB, and DB functions that you can study and use anywhere you would like. SLN didn’t make the cut due to its simplicity. There is also a comparison of the four methods.