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 is 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. You'll use these values 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; a company could 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. You use a certain amount of guesswork when you're 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's depreciating. You can use any of 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:
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])
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 $2,613.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 10-5 shows an example.
In Figure 10-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. You can use the double-declining balance approach only 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.
Note: When using double-declining balance depreciation, it's up to you to spot when the method no longer applies. Excel doesn't tell you; instead, it cheerily continues calculating depreciation values, even if the depreciation exceeds the total value of the asset. In Figure 10-5, the #N/A values were typed in by hand.