Depreciation Schedule in Excel

A depreciation schedule shows how much of an asset's cost becomes expense each year, and what the asset is still worth on the books. Excel has a function for each of the common methods, so the whole schedule comes down to a few formulas filled down a column. This guide builds one step by step and explains the mistakes that throw the numbers off.

What depreciation is, in plain terms

When a business buys equipment that will last several years, it doesn't count the whole price as an expense in the year of purchase. Instead, it spreads the cost over the years the equipment is used. Each year's share is called depreciation.

Three numbers drive every calculation:

• Cost: what the business paid for the asset, ready to use.
• Salvage value (also called residual value): what the business expects the asset to be worth at the end of its useful life.
• Useful life: how many years the business expects to use it.

The amount that gets depreciated over the whole life is cost minus salvage value. The asset is not expected to lose its salvage value, so that part is never expensed.

Two more terms appear in every schedule. Accumulated depreciation is the running total of all depreciation recorded so far. Book value is cost minus accumulated depreciation: what the asset is carried at on the balance sheet.

The three methods and their Excel functions

The methods differ in timing. Each one spreads the same cost, but some put more of it in the early years.

• Straight-line charges the same amount every year: (cost − salvage) ÷ life. The function is SLN(cost, salvage, life).
• Double-declining balance charges twice the straight-line rate on the book value at the start of each year. The early years get the most. The function is DDB(cost, salvage, life, period).
• Sum-of-the-years' digits multiplies cost minus salvage by a falling fraction. For a five-year life the digits add up to 1+2+3+4+5 = 15, so year 1 gets 5/15, year 2 gets 4/15, and so on down to 1/15. The function is SYD(cost, salvage, life, period).

Notice that DDB and SYD need a period: the year you want. Straight-line doesn't, because every year is the same.

Worked example: Harbor Bakery's riding mower

Harbor Bakery bought a riding mower for $31,750. It expects to use it for 5 years and sell it for $2,850 at the end. The sheet holds the inputs in column B:

Step 1: straight-line depreciation

In B7, refer to the three input cells:

=SLN(B3,B4,B5)

The depreciable amount is $31,750 − $2,850 = $28,900. Spread over 5 years, that is $5,780.00 a year.

Step 2: first-year sum-of-the-years' digits

In B8, add the period as the fourth argument. Year 1 is the year you want:

=SYD(B3,B4,B5,1)

Year 1 gets 5/15 of $28,900, which is $9,633.33. That is noticeably more than straight-line, because SYD front-loads the expense.

Step 3: the full five-year comparison

Extending the same mower to a full schedule shows how the three methods differ. With the year number in column A, each method needs one formula that can be filled down. Here are the results for Harbor Bakery, worked out with the same functions:

All three methods depreciate the same $28,900 in total; only the timing changes. Double-declining starts at 40% of the full cost in year 1 (twice the 20% straight-line rate). In year 5, a full 40% of the remaining $4,114.80 would push the book value below $2,850, so DDB stops at $1,264.80 and the mower ends at exactly its salvage value. Excel's DDB function applies that floor for you.

Building a schedule you can fill down

In the second exercise, Summit Bike Repair has a $24,500 server rack with a $1,250 salvage value and a 5-year life. Years 1 to 5 sit in A8:A12, and each method has its own column. The trick is to lock the input cells and leave the year free:

• B8: =SLN($B$3,$B$4,$B$5)
• C8: =DDB($B$3,$B$4,$B$5,A8)
• D8: =SYD($B$3,$B$4,$B$5,A8)

The dollar signs make $B$3 an absolute reference, so it still points at the cost when you copy the formula down. A8 is relative, so in row 9 it becomes A9, the year 2 cell. One formula per column, filled down to row 12, gives the whole schedule. Totals go in row 13 with =SUM(B8:B12) and the same for C and D.

For the server rack, straight-line is $4,650 a year and SYD runs from $7,750 down to $1,550; both total $23,250. The DDB column totals only $22,594.88, leaving a book value of $1,905.12, above salvage. That is how the DDB function works: it never switches to straight-line by itself, so in some schedules it doesn't reach salvage value by the final year.

Accumulated depreciation and book value

The third exercise adds the balance-sheet view. Juniper Landscaping's riding mower cost $15,000, with a $1,200 salvage value and a 5-year life. Column B holds the DDB depreciation, column C the accumulated depreciation and column D the book value at the end of each year.

The formulas: C8 is =B8, because the first year's total is just that year. C9 is =C8+B9, last year's total plus this year's, filled down. D8 is =$B$3-C8, cost minus everything to date, filled down. The final book value equals the $1,200 salvage value, which is a good sign the schedule is right.

Choosing between the methods

The method is an accounting policy choice, made when the asset is bought and then applied consistently. Straight-line is the simplest and suits assets that give about the same service every year, such as furniture or a building. The accelerated methods, DDB and SYD, suit assets that do most of their work, or lose most of their value, in the early years, such as vehicles and computer equipment. Whatever the method, the total over the life is the same: cost minus salvage. Tax depreciation follows its own rules, which this lesson does not cover.

How to check a depreciation schedule

• Totals: straight-line and SYD columns should both total cost minus salvage. For Harbor Bakery, $28,900.
• Direction: straight-line is flat; DDB and SYD fall every year. A rising column means the period argument is wrong.
• Floor: book value should never go below salvage value.
• Link: accumulated depreciation plus book value should equal cost on every row.

Common mistakes

These are the mistakes the exercises check for, with the wrong numbers they produce.

Leaving out the salvage value

Dividing the cost by the life (=B3/B5) depreciates the full price, including the part the business expects to get back. For Harbor Bakery that gives $6,350 a year instead of $5,780, and SYD year 1 becomes $10,583.33 instead of $9,633.33. For Summit Bike Repair, straight-line becomes $4,900 instead of $4,650.

Using the last year instead of the first

SYD's period argument is the year you want. Passing the life (5) instead of 1 returns the smallest share, $1,926.67, instead of year 1's $9,633.33. Under SYD, year 1 is always the largest.

Every row using year 1

Typing 1 as the period, or locking it as $A$8, makes every row repeat the first year. Summit's DDB column would show $9,800 five times and its SYD column $7,750 five times. Point the period at the year in column A with a relative reference.

Accumulated depreciation that doesn't accumulate

If C9 is =B9, the "accumulated" column just copies each year's depreciation. It has to be a running total: the row above plus this year.

Book value from this year's depreciation only

Book value is cost minus all depreciation to date. Using =$B$3-B9 gives Juniper a year 2 book value of $11,400 instead of $5,400, and $14,256 in year 5 instead of $1,200.

Depreciation schedules and MO-220 practice

Depreciation schedules are part of the MO-220 objective "Perform routine accounting activities", which covers calculating periodic depreciation and projecting future depreciation. MO-220 task instructions describe the purpose rather than naming the function. You will see "calculate the first year's depreciation using the sum-of-the-years'-digits method", and you have to know that means SYD. These exercises are worded the same way, as MO-220 practice for Microsoft® Excel® skills, done in a spreadsheet in your browser. Accounting Exercises is not affiliated with Microsoft or Certiport.

To record the depreciation you have calculated, see the depreciation journal entry, which uses the same Harbor Bakery mower. A loan amortization schedule uses the same fill-down layout with locked inputs, and the MO-220 practice page lists every topic.