Calculate Depreciation in Excel: Straight-Line, DB and DDB

Straight line depreciation in Excel takes one short formula: =SLN(cost, salvage, life), or the same maths typed out as =(cost-salvage)/life. It spreads the cost of an asset evenly over its useful life. This guide shows that method first, then the two faster methods Excel has built in, DB and DDB, with a full year-by-year schedule you can copy.

What depreciation means (in one minute)#

Depreciation spreads the cost of a long-lasting asset, like a van or a laptop, across the years you use it. Instead of one big expense in year one, you record a smaller expense each year. Three numbers drive every method:

The amount you can depreciate in total is cost minus salvage: 10,000 - 1,000 = 9,000. The methods only differ in how they split that 9,000 across the years.

Which method you must use is set by your company's accounting policy and your local tax rules. Excel doesn't decide that; it just does the arithmetic.

How to calculate straight-line depreciation in Excel#

Straight-line depreciation gives the same amount every year. Put your inputs in cells so you can change them later:

Then use either formula:

=SLN(B1,B2,B3)
=(B1-B2)/B3

Both return 1,800. SLN stands for "straight line" and works in every version of Excel, on Windows and Mac. The typed version is handy in a job test because it shows you understand the maths. The brackets matter: =B1-B2/B3 would divide first and give 9,800. If you need a refresher on how Excel orders operations, start with your first formulas.

For a monthly figure, give SLN the life in months: =SLN(B1,B2,B3*12) returns 150.

Build a depreciation schedule with absolute references#

A schedule shows each year's depreciation, the running total and the book value (cost minus everything depreciated so far). Put the years 1 to 5 in A6:A10, then:

Select B6:D6 and fill down to row 10 (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the inputs fixed on B1, B2 and B3 while the row numbers change. $B$6:B6 is a growing range: it becomes $B$6:B7, then $B$6:B8, so each row adds up everything so far. The guide to the dollar sign in Excel formulas explains this pattern, and you can practise absolute references in a live sheet.

The result: 1,800 every year, and the book value falls 8,200, 6,400, 4,600, 2,800, 1,000. It ends exactly at salvage, which is a quick check that the schedule is right.

Declining balance with DB#

The DB function uses the fixed-declining balance method: each year it takes the same percentage of whatever book value is left. Early years get large amounts and later years get small ones. The syntax is:

=DB(cost, salvage, life, period, [month])

period is the year you want, so you write one formula per year. For year 1 of our asset, =DB(B1,B2,B3,1) returns 3,690. Excel works out the rate as 1 - (salvage/cost)^(1/life) and rounds it to three decimals, here 0.369.

In a schedule, put the year number in column A and use =DB($B$1,$B$2,$B$3,A6). Because of the rounded rate, the final book value lands a few cents away from salvage (1,000.34). The optional month argument handles a first year that isn't a full 12 months: if you bought the asset in October, use 3.

Double-declining balance with DDB#

DDB, the double-declining balance method, takes an even faster rate: by default 2 divided by the life. For a 5-year life that is 40% of the remaining book value each year. The syntax is:

=DDB(cost, salvage, life, period, [factor])

Year 5 is only 296 because DDB never takes the book value below salvage: 40% of 1,296 would be 518.40, which would overshoot. Change the factor to 1.5 for a 150% declining balance.

DDB does not switch to straight-line in later years by itself. If your policy needs that switch, the VDB function does it by default.

SLN vs DB vs DDB at a glance#

All three methods depreciate the same 9,000 in total. They only change the timing:

Straight-line is the simplest and the one most job tests ask about. Declining methods match assets that lose value quickly, like vehicles and computers. Depreciation is one line on a profit and loss statement in Excel, and the yearly figure often sits next to loan maths like the PMT function for loan payments.

Round depreciation to the cent#

Depreciation often produces long decimals. A 7-year life on our 9,000 gives 1,285.714285... a year. Formatting only hides the extra digits; the cell still holds them, and totals can drift by a cent. Wrap the formula in ROUND to store a clean money value:

=ROUND((B1-B2)/B3,2)

The 2 means two decimal places. See the ROUND function guide for the syntax, the article on ROUND, ROUNDUP and ROUNDDOWN for the differences, and practise rounding in a live sheet. If rounding leaves the last year a cent off, a common fix is to make the final year cost minus salvage minus all earlier years, so the schedule ends exactly at salvage.

Common depreciation mistakes#

Most wrong answers come from one of these:

1. Missing brackets. =B1-B2/B3 divides before it subtracts. Write =(B1-B2)/B3.
2. Unlocked inputs. Without $, filling down shifts B1 to B2, B3 and so on, and the numbers go wild.
3. Wrong period. DB and DDB need the year number; leaving it at 1 in every row repeats year 1.
4. Mixing months and years. If life is in months, the result is per month. Keep units the same.
5. Salvage above zero but ignored. SLN with salvage 0 overstates depreciation if the asset still has value at the end.

Finance roles test exactly this kind of careful setup; see the Excel skills finance jobs expect.

Practise it#

Try the exercise on this page, then practise absolute references in a live spreadsheet: you build formulas that fill down with locked inputs, just like a depreciation schedule, and each answer is checked instantly. Follow it with the rounding lesson so your schedules always add up to the cent.