Depreciation spreads the cost of an asset, such as a vehicle, a machine or a computer, over the years in which it is used, instead of treating the whole cost as an expense in the year of purchase. Accountants use it to match costs to the income that an asset helps to earn, and it affects the profit shown in the accounts. Excel has built-in functions for the main methods: SLN for straight line, DB and DDB for declining balance, and SYD for the sum of the years digits. This guide explains the inputs, shows each function with a worked example using one asset, builds a schedule table, compares the methods, and lists the common mistakes. The tax rules differ by country, so check with an accountant for official reporting.
Updated November 2026
In this guide
- The inputs you need
- Method 1: straight line with SLN
- Method 2: double declining balance with DDB
- Method 3: fixed declining balance with DB
- Method 4: sum of the years digits with SYD
- Compare the methods
- Build a depreciation schedule
- Accumulated depreciation and book value
- Partial years and the first period
- Units of production method
- Which method should you use?
- Depreciation and your profit
- Common mistakes
- Practice problems
- Quick recap
The inputs you need
Every method needs the same three inputs. The cost is the price paid for the asset and anything needed to get it ready for use. The salvage value is the estimated value at the end of its useful life, which can be zero. The life is the number of periods over which the asset is depreciated, usually years. The depreciable amount is the cost minus the salvage value. In our example, a machine costs 10,000, has a salvage value of 1,000 and a life of 5 years, so the depreciable amount is 9,000.
Method 1: straight line with SLN
Straight line spreads the depreciable amount equally. The function is SLN(cost, salvage, life). For the example, =SLN(10000,1000,5) returns 1,800, which is 9,000 divided by 5. The machine loses 1,800 of book value each year: 10,000 at the start, then 8,200, 6,400, 4,600, 2,800 and finally 1,000. This is the simplest and the most common method, and it suits assets that give the same benefit each year, such as buildings and office furniture.
Method 2: double declining balance with DDB
Declining balance methods charge more depreciation in the early years. DDB(cost, salvage, life, period, factor) uses a factor of 2 by default, so the rate is 2 divided by the life, which is 40 percent for 5 years. Year 1 is =DDB(10000,1000,5,1), which is 10,000 times 40 percent, or 4,000. Year 2 is 6,000 times 40 percent, which is 2,400. Year 3 is 3,600 times 40 percent, which is 1,440. Year 4 is 2,160 times 40 percent, which is 864. Year 5 is limited to 296, so that the book value ends at the salvage value of 1,000.
Method 3: fixed declining balance with DB
DB(cost, salvage, life, period) uses a fixed rate that Excel calculates as 1 minus the salvage divided by the cost, raised to the power of 1 divided by the life, rounded to three decimals. For our asset, the rate is 0.369. Year 1 is =DB(10000,1000,5,1), which is 3,690. Year 2 is 6,310 times 0.369, which is 2,328.39. Year 3 is 3,981.61 times 0.369, which is 1,469.21. The method is used in some tax systems, and it ends exactly at the salvage value without the cap that DDB needs.
Method 4: sum of the years digits with SYD
SYD(cost, salvage, life, per) gives a decreasing charge that falls by the same amount each year. For a life of 5, the sum of the years digits is 5 plus 4 plus 3 plus 2 plus 1, which is 15. Year 1 is 5 divided by 15 of the depreciable amount, which is 3,000, and the formula is =SYD(10000,1000,5,1). Year 2 is 4 divided by 15, which is 2,400. Year 3 is 1,800, year 4 is 1,200 and year 5 is 600. The total is 9,000, as it should be.
Compare the methods
The table shows the yearly depreciation for the same machine.
| Year | SLN | DDB | SYD |
|---|---|---|---|
| 1 | 1,800 | 4,000 | 3,000 |
| 2 | 1,800 | 2,400 | 2,400 |
| 3 | 1,800 | 1,440 | 1,800 |
| 4 | 1,800 | 864 | 1,200 |
| 5 | 1,800 | 296 | 600 |
| Total | 9,000 | 9,000 | 9,000 |
All the methods write off 9,000 in total. They differ in the timing. The accelerated methods give higher charges early, which lowers the profit in the first years and lowers it less later.
Build a depreciation schedule
Type the inputs in cells: cost in B1, salvage in B2 and life in B3. In A6 to A10 type the years 1 to 5. In B6 use =SLN($B$1,$B$2,$B$3) for straight line, in C6 =DDB($B$1,$B$2,$B$3,A6) and in D6 =SYD($B$1,$B$2,$B$3,A6). Fill down. Add a book value column: for straight line, the book value after year 1 is =$B$1-SUM(B$6:B6), which is 8,200. A schedule keeps the numbers organized for the accounts. Our table guide shows how to format it.
Accumulated depreciation and book value
Accumulated depreciation is the total depreciation charged so far. After year 3 with straight line, it is 3 times 1,800, which is 5,400. The book value, or net book value, is the cost minus the accumulated depreciation, which is 10,000 minus 5,400, or 4,600. The book value never goes below the salvage value. On the balance sheet, the asset appears at its book value, and the depreciation of the year appears as an expense on the income statement. Keep a column for each in the schedule.
Partial years and the first period
If you buy the asset in the middle of the year, the first year has only part of a full year of depreciation. For straight line, if you buy on 1 July, the first year charge is half of 1,800, which is 900. The final year then also has a half-year charge, so the asset is fully depreciated after 5.5 calendar years. DB has an optional month argument for the number of months in the first year. Follow the policy of your company or your tax rules, since methods for part years differ.
Units of production method
Some assets wear out with use, not time, such as a machine that can make 100,000 units. Excel has no special function, but the formula is simple. The depreciation per unit is the depreciable amount divided by the total units, which is 9,000 divided by 100,000, or 0.09. If the machine makes 18,000 units in a year, the depreciation is 18,000 times 0.09, which is 1,620. Use =(B1-B2)/units*units_this_year. This method links the cost to the actual use of the asset.
Which method should you use?
The choice depends on the accounting rules, the tax rules and the way the asset gives benefits. Straight line is the simplest and the default for many companies. Accelerated methods suit assets that lose value or usefulness quickly, such as computers and vehicles. Tax authorities often specify their own methods and rates, which may differ from the accounts. The important point is to be consistent and to document the method. An accountant can tell you which method is allowed in your country. Our compound interest guide covers a related finance topic.
Depreciation and your profit
Depreciation is a non-cash expense, so it reduces the profit shown in the accounts without reducing the cash in the bank. If a business has revenue of 50,000 and cash expenses of 38,000, and it charges 1,800 of straight line depreciation, the profit is 50,000 minus 38,000 minus 1,800, which is 10,200. With the DDB method in year 1, the charge is 4,000 and the profit is 8,000. The cash is the same in both cases, but the reported profit differs. This is why the method must be disclosed and applied consistently.
Common mistakes
The most common mistake is to depreciate the cost instead of the cost minus the salvage value. Another is to use the wrong period, such as months with a life in years. A third is to depreciate land, which is normally not depreciated. A fourth is to continue depreciation after the book value reaches the salvage value. A fifth is to forget that DDB needs a cap in the last years, which Excel handles automatically. A sixth is to mix up the methods in the same schedule. Check that the total equals the depreciable amount.
Practice problems
Try these. Problem 1: cost 5,000, salvage 500 and life 3 give SLN of 1,500 per year. Problem 2: the DDB of the first year for a cost of 5,000 and a life of 5 is 5,000 times 40 percent, which is 2,000. Problem 3: the SYD for year 1 of a life of 4, with a depreciable amount of 10,000, is 4 divided by 10, which is 4,000. Problem 4: the book value after 2 years of straight line for our machine is 6,400. Problem 5: build the schedule and check the totals.
Quick recap
Depreciation spreads the cost of an asset over its life. In Excel use SLN for equal charges, DDB or DB for higher charges in the early years and SYD for a falling pattern. Enter the cost, the salvage value and the life, and the period for the accelerated methods. Build a schedule with the charge, the accumulated depreciation and the book value, and check that the total equals the cost minus the salvage value. Choose the method with your accountant, and keep it consistent from year to year.
Frequently asked questions
What is the straight line depreciation formula in Excel?
=SLN(cost, salvage, life).
What is DDB in Excel?
It is the double declining balance method, which gives higher depreciation in the early years.
What is the salvage value?
The estimated value of the asset at the end of its useful life.
What is book value?
Cost minus accumulated depreciation.
Which depreciation method is best?
It depends on accounting and tax rules, so ask an accountant.


