ExcelFunctions.net

Search Site:

Excel provides several built-in functions for calculating depreciation and amortization, using various methods.

The different depreciation methods, and the associated Excel functions, are discussed below.

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.

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:

=SLN( 1000, 100, 5 )

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.

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.

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:

=DB( 1000, 100, 5, 1 )

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.

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.

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:

=DDB( 1000, 100, 5, 1 )

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.

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:

=VDB( 1000, 100, 5, 1, 3 )

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.

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.

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:

=SYD( 1000, 100, 5, 1 )

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.

If you use the French accounting system, you may prefer to use the Excel Amordegrc or Amorlinc functions, both of which calculate the prorated linear depreciation of an asset.