July 1, 2011, 3:30 p.m.
posted by tactics
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. Figure lists the common arguments used in these functions.
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 Figure.)
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 Figure.
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 Figure.
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 Figure.) 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.
- Comment