Microsoft Office Tutorials and References
In Depth Information
Depreciating the Finer Things in Life
5. Click the cell that has the salvage amount, or enter its address.
6. Enter a comma ( ,).
7. Click the cell that has the number of periods, or enter its address.
8. Type a ), and press the Enter key.
The returned value is the amount of depreciation per period. Each period has
the same depreciation amount. The same formula, referencing the same cells
(using $ for absolute referencing), is in each cell in the D9:D20 range.
Creating an accelerated
The SYD function creates an accelerated depreciation schedule (that is, more
depreciation is applied in the earlier periods of the asset’s life). The method
uses an interesting technique of first summing up the years’ digits. So for a
depreciation schedule that covers five years, a value of 15 is first calculated
as 1 + 2+ 3 + 4 + 5 = 15. If the schedule is for ten years, then the first step of
the method is to calculate the sum of the digits 1 through 10, like this: 1 + 2 +
3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 = 55.
The years’ digit sum is then used as the denominator in calculations with the
actual digits themselves to determine a percentage per period. The digits
in the calculations are the reverse of the actual periods. In other words, in
a five-year depreciation schedule, the depreciation for the first period is
calculated as (5 ÷ 15) × Depreciable Cost. The second period depreciation is
calculated as (4 ÷ 15) × Depreciable Cost. The following table makes it clear,
with an assumed five-year depreciation on a depreciable cost of $6,000, and a
salvage value of $0:
(5/15) × 6,000
(4/15) × 6,000
(3/15) × 6,000
(2/15) × 6,000
(1/15) × 6,000
Guess what? You don’t even need to know how this works! Excel does all the
figuring for you. The SYD function takes four arguments: the cost, the
salvage, the life (the number of periods), and the period to be calculated.