Section 9.3. Depreciation | Excel 2010: The Missing Manual

9.3. Depreciation

Another common calculation in the world of finance is depreciation . Simply stated, depreciation is how much the value of an asset decreases over time. All assets that depreciate begin with a certain value (which you determine) and then depreciate over the course of a lifetime (which you specify). At the end of an asset's life, from an accounting perspective, the asset's deemed to be useless and without value.

Excel offers four basic depreciation functions (explained below), which can help you determine how much value your asset has lost at any given point in time. These values are useful if you want to sell the asset, or if you're attempting to calculate the current net worth of a business. Depreciation can also figure into tax calculations and business losses; for example, a company might write off a loss based on the value an asset has lost.

Note: The depreciation of an asset isn't as straightforward as interest calculations. That's because there's a certain amount of guesswork involved in deciding what an asset is worth and how rapidly its value declines. An asset can include almost anything, from a piece of equipment or property to a patented technology. It may depreciate due to wear and tear, obsolescence, or market conditions (like decreased demand and increased supply).

In order to assign a value to a depreciated asset, Excel makes a logical guess about the way in which the asset is depreciating. There are a number of accepted ways to make this guess (or estimate, as financial types prefer to call it). The easiest way of depreciating an asset is the straight-line depreciation method, where the value of the asset decreases regularly from the starting value to the final value. However, this approach isn't necessarily realistic for all assets.

Excel supports four basic depreciation functions, each of which figures depreciation in a slightly different way:

SLN( ) . SLN( ) uses simple straight-line depreciation , where the cost of the asset minus its scrap value (the value of the asset if sold purely as raw material) is simply divided by the life span of the asset. In other words, if the life span is 10 years , the book value of the asset is depreciated by 10% of the original value each year. SLN( ) is the only form of depreciation that proceeds regularly. All other types of depreciation are known as accelerated depreciation functions because they assume that the asset's greatest loss in value occurs early on, rather than evenly over several years. As a result, these accelerated depreciation functions are often more realistic.
SYD( ) . SYD( ) uses the sum-of-years-digits depreciation method. This method starts with a larger depreciation rate, which is gradually lowered as the asset becomes less valuable . SYD( ) is a good all-around choice for most depreciation calculations.
DDB( ) . DDB( ) uses the double declining balance depreciation method, which is like straight-line depreciation on steroids. It reduces an asset by double the percentage of the SLN( ) method, which makes for a fast reduction in value (and a hefty tax writeoff). In other words, if the life span is 10 years, the book value of the asset is depreciated by 20% of the original value each year.
There's a clear problem with this approach, however: eventually the asset will dip below its final value (and even become a negative number). The way accountants handle this is by imposing an additional rule. As soon as the amount of depreciation in any given year is less than the straight-line depreciation amount, you must immediately abandon the DDB( ) method and start calculating depreciation with SLN( ). But it's still up to you to be aware of this and check for this condition. If this sounds messywell, it is.
DB( ) . DB( ) uses the declining balance method, where an asset declines in value by a fixed percentage of its book value every year. Like the SYD( ) and the DDB( ) approaches, this method is an accelerated depreciation function. However, it has a different limitation: you can't specify the final value the asset should have after its lifetime is over (the salvage value).

These functions work with more of less the same arguments, but they produce different results. Here's a look at the syntax of all four functions:

 SLN(cost, salvage, life) SYD(cost, salvage, life, period) DB(cost, salvage, life, period, [month]) DDB(cost, salvage, life, period, [factor])

The cost is the initial value of the asset.
The salvage argument is the value of the asset at the end of its depreciation. If it's lost all value, this will be 0.
The life is the number of periods over which the asset is depreciating. It's also known as the useful life of the asset.
The period is a number that indicates the period in which you want to calculate depreciation; it must use the same unit as the life. For example, if you're depreciating an asset over three years, a period of 3 will get you the amount of money that the asset depreciated in the third year. The SLN( ) function doesn't require a period argument because depreciation is the same in every period.
The month is an optional argument supported by the DB( ) function. It specifies the number of months that the asset is in use for the first year. If you leave this out, Excel assumes that there are 12 months of service.
The factor is an optional argument supported by the DDB( ) function. You multiply the factor by the expected straight-line depreciation to get the actual depreciation. If you omit this argument, Excel assumes a value of 2 (which is why it's known as double declining depreciation).

As an example, imagine a company purchases a top-of-the-line computer for $10,000. The computer has a salvage value of $200 after its useful life of five years. To compute its second-year depreciation using the popular SYD( ) method, you'd use the following calculation:

 =SYD(10000, 200, 5, 2)

This calculation indicates that the computer loses $2,613.33 of its value in the second year. However, to calculate the total depreciation so far, you need to add the depreciation in both the first and second year, and subtract that from the initial value, as shown here:

 =10000-SYD(10000, 200, 5, 1)-SYD(10000, 200, 5, 2)

This formula takes the $10,000 total, and subtracts the first- and second-year depreciation ($3,266.67 and $26,133.33, respectively), which gives you a current value of $4,120. You'll notice that with the SYD( ) function, most of the depreciation takes place in the first year, and then each following year the asset loses a smaller amount of its value.

The easiest way to see depreciation at work is to build a worksheet that compares these four different functions. Figure 9-5 shows an example.

In Figure 9-5, the straight line depreciation is the slowest of all, which makes it less realistic for dealing with the plummeting values of aging high-tech parts . The sum-of-years-digits depreciation provides faster depreciation, yet still ends up with the desired salvage value of $3,000. The last two methods are a little trickier. The double-declining balance approach is only usable for the first two years. In the third year, the depreciation it calculates ($2,044.8) is less than the sum-of-years depreciation value ($2,240), so it's time to switch methods . To underscore that fact, the following cells display the #N/A code. The declining-balance approach works well initially, but then tops out in the last year. Probably, you'll ignore the last calculated value and just use the salvage value of $3,000.

Figure 9-5. This worksheet shows depreciation on a sample investment. Once you set the initial value, lifetime value, and salvage value, you can calculate the depreciation. But as you can see, some methods produce faster depreciation than others.

Note: When using double-declining balance depreciation, it's up to you to spot when the method no longer applies. Excel won't tell you; instead, it cheerily continues calculating depreciation values, even if the depreciation exceeds the total value of the asset. In Figure 9-5, the #N/A values were typed in by hand.