When you buy a capital asset for your business—say, a machine, a vehicle, or property—you'll probably want to depreciate it over time. This will make your financial statements reflect that the asset is being used up gradually, rather than all at once. You might even be required to depreciate certain assets, depending on how you do your accounting and applicable tax regulations.
There are several common ways of calculating depreciation:
- Straight-line
- Declining balance
- A special case of declining balance isDouble-declining balance
- Sum of year's digits (SOYD)
The difference between all these methods is the speed of depreciation. Depreciation should generally match the rate at which an asset gets used—and thus declines in value—and some assets might decline faster than others. Just think of how quickly new computers become obsolete!
In this tutorial, we'll show you how to calculate depreciation in each of these methods, using any spreadsheet app. You can follow along with this tutorial using any version of Excel (for Windows, Mac, or theExcel Web App), Google Sheets, Numbers,and most other spreadsheet apps. Each of the functions will work the exact same way in any of these apps.
Screencast
For each method in this tutorial, assume we're buying an asset for half a million dollars, Euros, or any other currency. The fully depreciated value will be 20,000, and we're going to depreciate it over 10 years. You can also follow along with the spreadsheet download, available in the top right corner of this post.
Straight line method
Straight line depreciation is the easiest to calculate and most commonly used, since the amount is the same each year. It's the initial cost minus the fully depreciated value, divided by the number of years.
The straight line depreciation function (SLN)thus gets three parameters:
- Cost: how much we paid for the asset
- Salvage value: the value of the asset when depreciation is finished
- Life: how many periods (typically years) we depreciate it
The syntax of this function is:
=SLN(cost, salvage, life)
Look at the straight-line worksheet of Depreciation Worksheets.xlxs, or in your own spreadsheet,type in info from the example below:
The initial cost is in B3, the salvage value is in B4, and the life is in B5.So click in cell A8 and enter the function:=SLN(B3,B4,B5)
This gives us a result of $48,000 a year.
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 columngoes 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:
Specify your own rate
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 theSOYDworksheet ofDepreciation 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.
