Depreciation Functions in Google Sheets with Examples

Google Sheets offers four depreciation functions to calculate an asset’s depreciation expense. These functions return the expense for one period, and therefore you should use them in a schedule showing all periods in the asset’s life.

Depreciation is the accounting method used to spread the expense of a fixed asset over time. As GAAP and IFRS follow the matching principle, depreciation matches the expense of an asset to the same period during which the asset provides revenues. Suppose you expect a tangible asset to provide value to an organization for more than one accounting period. In that case, it should be capitalized and expensed over that period.

Straight Line

Companies typically calculate book (not tax) depreciation straight-line as a practical expedient. Straight-line involves a relatively simple calculation that outputs the same expense for each period: days, months, quarters, etc.

SLN function screen capture
SLN Function

Let’s say your company says the useful life of computer equipment is 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

DB depreciation function screen capture
DB Function
DDB depreciation function screen capture
DDB Function
SYD depreciation function screen capture
SYD Function

Some fixed assets lose their value faster at the beginning of their life and slower towards the end. You see this pattern with an accelerated method such as declining balance, double-declining balance, or sum of the year’s digits designed to reflect this pattern of higher expense at the beginning of the asset’s life and lower expense toward the end.

Thumbnail showing a depreciation schedule
Depreciation Schedule Video

Thumbnail showing depreciation in Google Sheets
Depreciation Functions Video

Live Example in Sheets

These templates are live versions of the SYD, DDB, and DB depreciation functions. You can study and use these spreadsheets anywhere you would like. Due to its simplicity, we do not show the SLN calculation in a separate spreadsheet. There is also a comparison of the four methods to see their relative expenses in different periods.

Leave a Comment