Depreciation in Excel

Straight Line Depreciation

Straight line depreciation is the most basic type of depreciation. This method depreciates an asset by a fixed amount per period, over the asset's useful life.

The Sln function can be used to calculate straight line depreciation in Excel during a single period of an asset's useful life. The depreciation of the asset over a specified number of periods can then easily be calculated by multiplying the calculated single period depreciation by the number of periods.

Excel SLN Function Example

If you have an asset that cost $1,000 and has a residual value of $100 after 5 years, you can calculate the annual straight line depreciation of the asset as follows:

which gives the result 180.00.

I.e. using the straight line method, the annual depreciation of the asset is $180.00.

For further details of this function, see the Excel Sln function page.

Declining Balance Depreciation

The declining balance method of depreciation is an accelerated depreciation method in which, for each period of an asset's useful lifetime, the calculated value of the is reduced by a fixed percentage of the asset's value at the start of the current period.

Excel's Db function uses the declining balance method to calculate the depreciation of an asset during a specified period.

Excel DB Function Example

If you have an asset that cost $1,000 and has a residual value of $100 after 5 years, you can calculate the declining balance depreciation of the asset during year 1 as follows:

which gives the result 369.00.

I.e. using the declining balance method, the depreciation of the asset during year 1 is $369.00.

See the Excel Db function page for further details and examples of this function.

Double Declining Balance Depreciation

The double declining balance method of depreciation charges the cost of an asset at a rate that is double that of straight line depreciation. Therefore, the depreciation is the greatest during the first period and it reduces in each successive period.

Excel's Ddb and Vdb functions both calculate depreciation, using the double declining balance method. These functions differ in that the Vdb function can calculate depreciation over multiple periods at once, whereas the Ddb only calculates depreciation over a single specified period.

Although the default factor is two (for double declining balance depreciation), both functions allow you to specify the factor that the rate of depreciation is multiplied by.

Excel DDB Function Example

If you have an asset that cost $1,000 and has a residual value of $100 after 5 years, you can calculate the double declining balance depreciation of the asset during year 1 as follows:

which gives the result 400.00.

I.e. using the double declining balance method, the depreciation of the asset during year 1 is $400.00.

See the Excel Ddb function page for further details of this function.

Excel VDB Function Example

If you have an asset that cost $1,000 and has a residual value of $100 after 5 years, you can calculate the double declining balance depreciation of the asset during years 2 and 3 as follows:

which gives the result 144.00.

I.e. using the double declining balance method, the depreciation of the asset during years 2 and 3 is $144.00.

See the Excel Vdb function page for a full description of this function, with additional examples.

Sum-of-Years' Digits Depreciation

The sum-of-years digits method of depreciation is another accelerated depreciation method, in which the annual depreciation is determined by multiplying the depreciable cost by a schedule of fractions.

Excel's Syd function calculates the depreciation of an asset over a specified period, using the sum-of-years digits method. This function uses the following equation:

where,

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

Excel SYD Function Example

If you have an asset that cost $1,000 and has a residual value of $100 after 5 years, you can calculate the sum-of-years digits depreciation of the asset during year 1 as follows:

which gives the result 300.

I.e. using the sum-of-years digits method, the depreciation of the asset during year 1 is $300.

See the Excel Syd function page for further details of this function.