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

depreciation schedule

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:

Period

Calculation

Result

1

$2,000

(5/15) × 6,000

2

(4/15) × 6,000

$1,600

3

(3/15) × 6,000

$1,200

4

(2/15) × 6,000

$800

5

(1/15) × 6,000

$400

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.