Dealing with Depreciation: SLN, SYD, DB, DDB, and VDB

It is a sad fact of life that, as an engineer, you cannot escape accountancy. Finance gets in everywhere.  Hence, you need to have a grasp, not only of engineering functions, but also of some financial functions –like depreciation.

In this article, we will look at depreciation. You may be buying a new piece of equipment for the site, a new grader (for example) or a front-end loader.   Even a laser printer for the office.

For tax purposes, you must depreciate the item’s value over its useful life.  Or there may be a statutory period such as 3 or 5 years.

The method you use to depreciate the value of the equipment is often up to you. An item like a new car loses much of its book value in the first year. Other machinery may depreciate more slowly.

Here, we look at several Excel formulas to calculate depreciation: SLN, SYD, DB, DDB, and VDB.

All of these calculate the depreciation of an asset for one period per cell. They are not array formulas, hence are not entered as a block. Rather, the formula goes into one cell (usually the first period), and is then copied to the other periods. For the first, straight-line depreciation, the depreciation is the same in all periods. For the rest, it varies per period, being biggest in the first period, and then reducing.

1     SLN – Straight-Line Depreciation

Syntax: SLN(cost, salvage, life)

Arguments:

Cost (required): The purchase price of the item.

Salvage (required): The sale value at the end of the item’s life (also called the salvage value).

Life (required): The number of periods over which the item depreciates (also called the useful life). Typically, this would be the number of years.

See the examples below, where we compare the different methods.

2     SYD – Sum-of-the-Years’ Digits Depreciation

Syntax: SYD(cost, salvage, life, period)

Arguments:

Cost (required): The purchase price of the item.

Salvage (required): The sale value at the end of the item’s life (also called the salvage value). This can be 0.

Life (required): The number of periods over which the item depreciates (also called the useful life).

Period (required): The period number, in the same units as life (typically, years).

3     DB – Declining Balance Depreciation

Syntax: DB(cost, salvage, life, period, [months])

Arguments:

Cost (required): The purchase price of the item.

Salvage (required): The sale value at the end of the item’s life (also called the salvage value). This can be 0.

Life (required): The number of periods over which the item depreciates (also called the useful life).

Period (required): The period number, in the same units as life (typically, years).

Months (optional): The number of months in the first year. If this is omitted, it is assumed to be 12.  If it is not 12, then Excel uses it for the first period, and the value 12-months for the last period.

The DB method calculates depreciation at a fixed rate, rather than a fixed amount as with SLN.  With DB, depreciation will be highest in the first (12-month) period, and be less in later years.  The balance, that is the value after any given period, will be lower than for SLN and SYD.

This is the only method that has an option of partial years for the first and last years.

4     DDB – Double-Declining Balance Depreciation

Syntax: DDB(cost, salvage, life, period, [factor])

Arguments:

Cost (required): The purchase price of the item.

Salvage (required): The sale value at the end of the item’s life (also called the salvage value). This can be 0.

Life (required): The number of periods over which the item depreciates (also called the useful life).

Period (required): The period number, in the same units as life (typically, years).

Factor (optional): The rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method).  Do not use this argument!  Anything other than 2 (or omitted) can leave a residual value at the end of the depreciation that does not equal the salvage value!  We demonstrate this in the examples below.

5     VDB – Variable Declining Balance Depreciation

This is the most versatile (read: “complicated and dangerous”!) of the methods.

It returns the depreciation of an item for any period you specify, including partial periods, using the double-declining balance method or some other method you specify.

Syntax: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])

The VDB function syntax has the following arguments:

Cost (required): The purchase price of the item.

Salvage (required): The sale value at the end of the item’s life (also called the salvage value). This can be 0.

Life (required): The number of periods over which the item depreciates (also called the useful life).

Start_period (required): The starting period for which you want to calculate the depreciation, in the same units as life.

End_period (required): The ending period for which you want to calculate the depreciation, in the same units as life.

Factor (optional): The rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method). Change factor if you do not want to use the double-declining balance method. For a description of the double-declining balance method, see DDB directly above.

No_switch (optional): A logical value to prevent switching to straight-line depreciation when needed.  Omit it if you omitted factor (or it is 2), since DDB will reach the correct salvage value at the end of life.

If no_switch is FALSE or omitted, the formula switches to straight-line depreciation when depreciation is less than the declining balance calculation.  This should make residual value reach the salvage value at the end of life (it is worth checking anyway).

If no_switch is TRUE, Excel does not switch to straight-line depreciation.  It is up to you to make sure that the residual value reaches the correct salvage value at the end of life.

Note: All arguments (if used) except no_switch and salvage must be positive numbers. Salvage may not be negative.

Caution!  The VDB arguments start_period and end_period work differently to the period argument in the other depreciation formulas.  Because you must specify both start_period and end_period, depreciation for the first period would be =VDB(Cost, Salvage, Life, 0,1) (start_period=0, end_period=1).  For the second period it would be =VDB(Cost, Salvage, Life, 1,2) (start_period=1, end_period=2), and so on.

Start_period and end_period also allow you to do what only DB does too (with the optional months argument): to depreciate for part of a year.  You can specify start_period and end_period with decimals (if period is in years), or in months (with period also in months).  You would usually deviate from whole years for only the first and last years.

6     Depreciation Examples:

In this example, we have named cells of Cost = R100,000, Salvage = R10,000, and Life = 5 (yrs).

The Type of depreciation is listed in cells A8:A16.

Period (year) number headings are in B7:F7.

In column G, we SUM the adjacent depreciation for years 1 to 5.  For our values, each of these totals should be equal to Cost – Salvage = R90,000.

In column H, using FORMULATEXT, we document the formula on that row in column B. Those formulas are simply copied across to columns C:F for periods 2 to 5, except for row 16 (VDB & factor).  There, the first period starts at start_period = zero and goes to end_period = B7.  Cell H17 shows the formula in C16, where the second period starts at start_period = B7 and goes to end_period = C7.  This is then copied across to D16:F16.

We avoided that in row 15 (VDB) by using N(A7) in the formula in cell B15.  The N() formula converts A7 to a number –zero, of course, because A7 contains text.

When we chart the Depreciation Amount per Year as shown above, it is clear that the SLN figures (orange line) are the same in each year.  SYD figures also form a straight line, but angled down, decreasing every year by the same amount.  The others start even higher, but the annual difference then decreases per year.

If we look at the totals in column G above, we see that DDB 1.5 using an optional factor of 1.5, and DDB 1 (factor = 1) miss the required figure of R90,000.  As warned above, with a factor other than 2, DDB is no longer a double-declining balance formula.  DDB 1.845 isn’t either, but has been carefully adjusted (with goal seek) to reach the required residual.  Its depreciation figures are in fact identical to those for the DB formula, but the factor will vary depending on the ratio of Salvage to Cost.

VDB with no factor gives results identical to DDB, as expected.

If we use a factor, then omitting the optional no_switch argument means that (unlike DDB with a factor) VDB corrects itself by swapping to straight-line depreciation in later years to still reach the correct residual value.

This can be seen more clearly if we also calculate and graph the residual values:

Here we can clearly see the different paths that the various formulas take to the Salvage value of R10,000, and how DDB 1.5 and DDB 1 (calculated but not graphed) miss it. The brown line of VDB & factor (factor = 1.5) tracks DDB 1.5 initially.  Then it corrects by swapping to straight-line depreciation in later years to reach the correct salvage value.

Conclusion: Do not use DDB with a factor, or VDB with no_switch.

Otherwise, which method you use is up to you and the circumstances.