Depreciation Calculations

Depreciation is an accounting concept whereby the value of an asset is expensed over time. Some expenditures affect only the current period and are expensed fully in that period. Other expenditures, however, affect multiple periods. These expenditures are capitalized (made into an asset) and depreciated (written off a little each period). A forklift, for example, may be useful for five years. Expensing the full cost of the forklift in the year it was purchased would not put the correct cost into the correct years. Instead, the forklift is capitalized and one-fifth of its cost is expensed in each year of its useful life.

Table 12-1 summarizes Excel's depreciation functions and the arguments used by each. For complete details, consult Excel's Help system.

Table 12-1: EXCEL DEPRECIATION FUNCTIONS
Open table as spreadsheet

Function	Depreciation Method	Arguments[*]
SLN	Straight-line. The asset depreciates by the same amount each year of its life.	Cost, Salvage, Life
DB	Declining balance. Computes depreciation at a fixed rate.	Cost, Salvage, Life, Period, [Month]
DDB	Double-declining balance. Computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods.	Cost, Salvage, Life, Period, Month, [Factor]
SYD	Sum of the year's digits. Allocates a larger depreciation in the earlier years of an asset's life.	Cost, Salvage, Life, Period
VDB	Variable-declining balance. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.	Cost, Salvage, Life, Start Period, End Period, [Factor], [No Switch]
[*]Arguments in brackets are optional.

The arguments for the depreciation functions are described as follows:

• Cost: Original cost of the asset.

• Salvage: Salvage cost of the asset after it has fully depreciated.

• Life: Number of periods over which the asset will depreciate.

• Period: Period in the Life for which the calculation is being made.

• Month: Number of months in the first year; if omitted, Excel uses 12.

• Factor: Rate at which the balance declines; if omitted, it is assumed to be 2 (that is, double-declining).

• Rate: Interest rate per period. If you make payments monthly, for example, you must divide the annual interest rate by 12.

• No Switch: True or False. Specifies whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation.

Figure 12-21 shows depreciation calculations using the SLN, DB, DDB, and SYD functions. The asset's original cost, $10,000, is assumed to have a useful life of ten years, with a salvage value of $1,000. The range labeled Depreciation Amount shows the annual depreciation of the asset. The range labeled Value of Asset shows the asset's depreciated value over its life.

Figure 12-21: A comparison of four depreciation functions.

On the CD 

The companion CD-ROM contains  depreciation.xlsx, the workbook shown in Figure 12-21.

Figure 12-22 shows a chart that graphs the asset's value. As you can see, the SLN function produces a straight line; the other functions produce curved lines because the depreciation is greater in the earlier years of the asset's life.

Figure 12-22: This chart shows an asset's value over time, using four depreciation functions.

The VDB (variable declining balance) function is useful if you need to calculate depreciation for multiple periods, such as when you need to figure accumulated depreciation on an asset that has been sold. Figure 12-23 shows a worksheet set up to calculate the gain or loss on the sale of some office furniture. The formula in cell B12 is

 =VDB(B2,B4,B3,0,DATEDIF(B5,B6,"y"),B7,B8) 

Figure 12-23: Using the VDB function to calculate accumulated depreciation.

The formula computes the depreciation taken on the asset from the date it was purchased until the date it was sold. The DATEDIF function is used to determine how many years the asset has been in service.