Calculating Depreciation

Depreciation has an enormous effect on the bottom line of any business, and accurately calculating depreciation is crucial if you want to avoid triggering a detailed scrutiny of your records by the IRS. These functions help you precisely determine the depreciation of an asset for a specific period. Table 16-2 lists the common arguments used in these functions.

Table 16-2: Depreciation Function Arguments
Open table as spreadsheet

Argument	Description
Cost	Initial cost of the asset
Life	Length of time the asset will be depreciated
Period	Individual time period to be computed
Salvage	Asset's remaining value after it has been fully depreciated

The SLN Function

The SLN function determines the straight-line depreciation for an asset for a single period. This depreciation method assumes that the depreciation is uniform throughout the useful life of the asset. The cost or basis of the asset, less its estimated salvage value, is deductible in equal amounts over the life of the asset. This function takes the arguments (cost, salvage, life). (For definitions of these arguments, see Table 16-2.)

Suppose you want to determine the depreciation for each year of a machine that costs $8,000 new; has a life of 10 years; and has a salvage value of $500. The formula =SLN(8000, 500, 10) tells you that each year's straight-line depreciation is $750.

The DDB and DB Functions

The DDB (double declining balance) function computes an asset's depreciation at an accelerated rate-more in the early periods and less later. Using this method, depreciation is computed as a percentage of the net book value of the asset (the cost of the asset less any prior years' depreciation).

The function takes the arguments (cost, salvage, life, period, factor). All DDB arguments must be positive numbers, and you must use the same time units for life and period; that is, if you express life in months, period must also be in months. The factor argument is optional and has a default value of 2, which indicates the normal double declining balance method. Using 3 for the factor argument specifies the triple declining balance method. For other argument definitions, see Table 16-2.

Suppose you want to calculate the depreciation of a machine that costs $5,000 new and that has a life of five years (60 months) and a salvage value of $100. The formula =DDB(5000, 100, 60, 1) tells you that the double declining balance depreciation for the first month is $166.67. (Note that life is expressed in months.) The formula =DDB(5000, 100, 5, 1) tells you that the double declining balance depreciation for the first year is $2,000.00. (Note that life is expressed in years.)

The DB (declining balance) function is similar to the DDB function except it uses the fixed declining balance method of depreciation and can calculate depreciation for a particular period in the asset's life. It takes the arguments (cost, salvage, life, period, month).

The life and period arguments must use the same units. The optional month argument is the number of months depreciated in the first year, which, if omitted, is 12-a full year. For example, to calculate the real depreciation for the first period on a $1,000,000 item with a salvage value of $100,000; a life of six years; and seven months in the first year; use the formula =DB(1000000, 100000, 6, 1, 7), which returns $186,083.33.

The VDB Function

The VDB (variable declining balance) function calculates the depreciation of an asset for any complete or partial period, using either the double declining balance or another accelerated-depreciation factor you specify.

This function takes the arguments (cost, salvage, life, start, end, factor, no switch). The start argument is the period after which depreciation will be calculated, and end is the last period for which depreciation will be calculated. These arguments determine the depreciation for any length of time during the life of the asset. The life, start, and end arguments must all use the same units (days, months, or years). The optional factor argument is the rate at which the balance declines. If you omit factor, Excel assumes that the argument is 2 and uses the double declining balance method. The optional no switch argument is a value that specifies whether to switch to straight-line depreciation when the straight-line depreciation is greater than the declining balance. If you omit no switch or type 0 (FALSE), Excel switches to straight-line depreciation; to prevent the switch, type 1 (TRUE). For other argument definitions, see Table 16-2.

Suppose you purchased a $15,000 asset at the end of the first quarter of the current year and that this asset will have a salvage value of $2,000 after five years. To determine the depreciation of this asset next year (the fourth to seventh quarters of its use), use the formula =VDB(15000, 2000, 20, 3, 7). The depreciation for this period is $3,760.55. The units used here are quarters. Notice that the start argument is 3, not 4, because we are jumping over the first three periods to start in the fourth.

The SYD Function

The SYD function computes an asset's depreciation for a specific time with the sum-of-the-years'-digits method. The SYD function takes the arguments (cost, salvage, life, period). (For definitions of these arguments, see Table 16-2.) You must use the same units for life and period. Using the sum-of-the-years'-digits method, Excel calculates depreciation on the cost of the item less its salvage value. Like the double declining balance method, the sum-of-the-years'-digits method is an accelerated depreciation method.

Suppose you want to determine the depreciation of a machine that costs $15,000; has a life of three years; and a salvage value of $1,250. The formula =SYD(15000, 1250, 3, 3) tells you that the sum-of-the-years'-digits depreciation for the third year is $2,291.67.