## Declining Balance method

Like straight line depreciation, the declining balance method is a constant amount each year, but at a higher rate. To calculate it, the worksheet multiplies a rate by the asset's declining book value. The book value is the initial cost, minus accumulated depreciation, and is sometimes called the carrying value. Don't confuse book value with market value, which is how much you can sell the asset for.

What is the rate for declining balance? Excel and Google both use this formula:

The declining balance function (DB) has the same 3 parameters as the straight line method—cost, salvage value, and life—plus two more:

• Period: we run the calculation several times. When we run it this time, which period do we want to see? For example, we run the function for the second year, the third year, etc.
• Month: assuming we're depreciating over several years, how many months are in the first year of depreciation? This value is optional. If we omit it, the function assumes it's 12.

The syntax of this function is:
= DB(cost, salvage, life, period, [month])

Look at the declining balance worksheet of Depreciation Worksheets.xlxs, or in the screenshot below. The initial cost, salvage value, number of years and months in the first year are contained in cells B3, B4, B5 and B6, respectively. The years are listed down column A.

So click in cell B9 and enter the function:
=DB(B3,B4,B5,A9,B6)

Don't press Enter, yet! To save typing, we'll use the AutoFill feature to drag the formula down the column. To avoid errors, we need to make all the cell references, except the years, absolute.

Select each cell reference except A9, then press the F4 key to insert dollar signs. This prevents the reference from changing. The function should now be:
=DB($B$3,$B$4,$B$5,A9,$B$6)

Put the mouse pointer on the AutoFill handle so the mouse pointer becomes a cross-hair:

Drag the AutoFill cursor down to the bottom of the column to get these results:

Note that the Year column goes to 11 because the first year had only 6 months.

## Double-declining balance

When we use the declining balance method to accelerate straight-line depreciation by twice as much as we ordinarily would, this is a special case known as double-declining balance. This function is actually more flexible than the name sounds. By default, it will double the rate, but we can optionally use any rate we want.

The parameters for the double-declining balance function (DDB) are similar to the regular declining balance method:

• Cost: initial cost of the asset
• Salvage: the asset's value when it's fully depreciated
• Life: the number of periods (typically years) over which we depreciate the asset
• Period: we run the calculation several times. When we run it this time, which period do we want to see? For example, we can run the function for the second year, the third year, etc.
• Rate: optional. If we don't specify, the rate is double, i.e. 2. But we can set this parameter to any rate we want.

The syntax of this function is:
=DDB(cost, salvage, life, period, [rate])

Note that unlike the "regular" fixed declining balance method, for double-declining balance, the number of months in the first year doesn't matter.

Look at the double declining balance worksheet of Depreciation Worksheets.xlxs, or in the screenshot below. The values are the same and in the same cells as in the regular declining balance, so click in cell B8 and enter the function:
=DDB(B3,B4,B5,A8)

Also like in the previous example, we want to AutoFill down the column, so select all cell references except A8 and press the F4 key to make them absolute. The formula you enter should now be:
=DDB($B$3,$B$4,$B$5,A8)

Put the mouse pointer on the AutoFill handle so the mouse pointer becomes a cross-hair:

Then drag the AutoFill cursor down to the bottom of the column to get these results:

Let's say we want a rate of 150% instead of the default rate of 200%. To do this, add a 5th parameter of 1.5, as follows.

Click in B21, and enter the function below, remembering to make the first 3 parameters absolute, as before:
=DDB($B$3,$B$4,$B$5,A21,1.5)

AutoFill down to the bottom to get these results:

## Sum of Year's Digits

You might want depreciation to accelerate faster in the early years and slower in later years, perhaps for an asset that loses value quickly, or where you want to take a charge-off sooner. For this, you can use the Sum of Year's Digits method.

It's best to explain this method with the example of an asset that you expect to use for 5 years. In the first year, you add the year's digits: 5 + 4 + 3 + 2 + 1, which is 15. Then you multiply the cost less the salvage by 5/15 (which is 1/3). In the second year, you add the remaining digits 4 + 3 + 2 + 1, which is 10. Then you multiply the cost less the salvage by 4/10 (which is 2/5). And so it goes.

The Sum of Year's function (SYD) parameters are similar to the previous methods:

• Cost: initial cost of the asset
• Salvage: the asset's value when it's fully depreciated
• Life: the number of periods (typically years) over which we depreciate the asset
• Period: we run the calculation several times. When we run it this time, which period do we want to see? For example, we run the function for the second year, the third year, etc.

The syntax of the function is:
=SYD(cost, salvage, life, period)

The function does the calculation using this formula:

Look at the SOYD worksheet of Depreciation Worksheets.xlxs, or at the screenshot below. The same values are in the same cells as in the previous examples, except the life is now 5 years. Click in B8 and enter the formula:
=SYD(B3,B4,B5,A8)

Use the F4 key to make the first 3 parameters absolute, so the formula becomes:
=SYD($B$3,$B$4,$B$5,A8)

AutoFill to the bottom to get these results:

## Conclusion

Now you know how to calculate four types of depreciation for capital purchases: Straight line for general, all-purpose use, declining balance for a faster rate, double declining balance for an even faster or more flexible rate, and sum of year's digits to get more depreciation in earlier years. Best of all, you know how to calculate each of those in any spreadsheet app you have.

So open a new spreadsheet, plug in your own numbers, and see what you get!

Please note: This tutorial is not intended to give you financial advice, but only to explain how to use spreadsheets for depreciation calculations. Please consult a qualified financial advisor before making any financial decisions.