Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
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.
| 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.
| On the CD | The companion CD-ROM contains |
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.
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)
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.
Категории