Excel guide · 5 min read

How to calculate depreciation in Excel

Q: What is the formula for straight-line depreciation in Excel?

=SLN(cost,salvage,life), or the same thing written out, =(cost-salvage)/life. A $38,000 asset with $6,000 salvage over 5 years gives $6,400 a year.

Q: How do I calculate monthly depreciation?

For straight-line, divide the yearly amount by 12, or use =SLN(cost,salvage,life_in_months). With DDB you can also give the life in months and ask for a month as the period.

Quick answerFor straight-line depreciation use =SLN(cost,salvage,life): a $38,000 van with a $6,000 salvage value over 5 years depreciates $6,400 a year. For faster write-offs early on, =DDB(cost,salvage,life,year) gives double-declining balance ($15,200 in year 1) and =DB(cost,salvage,life,year) gives fixed declining balance. Build a schedule with one row per year and a running book value.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	D	E	F	G
1	Input	Value	Year	Straight-line	Double-declining	Book value (DDB)
2	Cost	38000	1	6400	15200	22800
3	Salvage value	6000	2	6400	9120	13680
4	Useful life (years)	5	3	6400	5472	8208
5			4	6400	2208	6000
6			5	6400	0	6000

Download this sheet as an Excel practice file (.xlsx)

Straight-line: SLN or (cost - salvage) / life

Straight-line spreads the cost evenly. Take what you paid, subtract what you expect to sell it for at the end (the salvage or residual value), and divide by the useful life in years. Excel's function is =SLN(B2,B3,B4), in the order cost, salvage, life. It gives the same number as the plain formula below, which is what column E of the schedule uses.

Use absolute references, $B$2, in the schedule so the formula can be copied down every year without the inputs shifting. After five years the van has been depreciated by $32,000 and is on the books at its $6,000 salvage value.

=(B2-B3)/B4

Result: 6400

6400, the same depreciation every year for 5 years.

Double-declining balance: DDB

Double-declining balance writes off more in the early years, when a vehicle loses value fastest. Each year it takes twice the straight-line rate, 2/5 = 40%, of the remaining book value, not of the original cost. Excel's =DDB(B2,B3,B4,1) gives year 1, $15,200; change the last argument for later years. An optional fifth argument, factor, replaces the 2: use 1.5 for 150% declining balance.

DDB never takes the book value below salvage. That is what the MIN in column F does: each year it takes the smaller of 40% of the book value and whatever is left above salvage. Here, year 4 would be $3,283.20 at 40%, but only $2,208 is left above the $6,000 salvage, so year 4 is $2,208 and year 5 is 0.

=MIN(G4*2/$B$4,G4-$B$3)

Result: 2208

2208, the year 4 DDB amount, capped by the salvage value. Years 1 to 3 are 15200, 9120 and 5472.

The DDB gotcha: it does not always reach salvage

With a low salvage value, the opposite happens: 40% of a shrinking balance never gets all the way down. If the van's salvage value were $1,000, the book value after 5 years of DDB would still be $2,954.88, so $1,954.88 would never be depreciated by DDB within the life.

This is why many schedules use VDB instead. =VDB(cost,salvage,life,start,end) uses declining balance but switches to straight-line for the remaining years as soon as straight-line gives the larger amount, so the asset lands on its salvage value at the end of its life. For year 1 use start 0 and end 1, for year 2 start 1 and end 2, and so on.

=38000*(1-2/5)^5

Result: 2954.88

2954.88, the DDB book value after 5 years if salvage were $1,000. It stays well above the salvage value.

Fixed declining balance: DB

DB works out a single rate that would take the cost down to the salvage value over the life, 1 - (salvage/cost)^(1/life), and Excel rounds that rate to three decimal places. For the van it is 0.309, or 30.9% of the remaining book value each year. =DB(B2,B3,B4,1) returns $11,742 for year 1.

Because of the rounding, DB does not land exactly on salvage: this schedule ends at $5,986.51, not $6,000. DB also takes an optional fifth argument, month, for an asset bought partway through its first year. With month set to 7, year 1 gets 7/12 of a full year and DB adds an extra, partial period at the end.

The formula below builds the whole DB schedule in Excel 365 or 2021: year, depreciation and book value, each rounded to cents.

=LET(r,ROUND(1-(B3/B2)^(1/B4),3),y,SEQUENCE(B4),HSTACK(y,ROUND(B2*(1-r)^(y-1)*r,2),ROUND(B2*(1-r)^y,2)))

Result: spills 5 rows × 3 columns

1	11742	26258
2	8113.72	18144.28
3	5606.58	12537.7
4	3874.15	8663.55
5	2677.04	5986.51

Five rows: 11742, 8113.72, 5606.58, 3874.15 and 2677.04 of depreciation, ending at a book value of 5986.51.

Build the schedule and check it

Put cost, salvage value and life in input cells (B2:B4), never typed into formulas.
List the years 1 to 5 down column D.
Straight-line in E2: =($B$2-$B$3)/$B$4, or =SLN($B$2,$B$3,$B$4), copied down.
Year 1 DDB in F2: =MIN($B$2*2/$B$4,$B$2-$B$3), or =DDB($B$2,$B$3,$B$4,D2).
Book value in G2: =$B$2-F2. From row 3 on, F3 is =MIN(G2*2/$B$4,G2-$B$3) and G3 is =G2-F3, copied down.
Check: the total of each depreciation column must equal cost minus salvage, and the last book value must equal salvage.

=SUM(F2:F6)

Result: 32000

32000, exactly cost minus salvage, so the DDB column is complete.

Which method to pick, and a word on tax

Straight-line is the simplest and the most common for company accounts, and the right choice when an asset is used evenly over its life, such as office furniture. Declining balance (DDB, or VDB so it reaches salvage) suits assets that lose value or usefulness fastest when new, such as vehicles and computers. DB is less common; use it if your accounting policy specifies a fixed-rate declining balance.

For a part-year in straight-line, multiply the first year by the months owned divided by 12, then add the leftover to a final extra year. Bought in June with 7 months owned, the van's first year is $3,733.33.

Tax depreciation follows its own rules in each country, with set lives, rates and first-year allowances (for example MACRS in the US), so the book schedule you build here may not match your tax return. Check the method and numbers with an accountant before you file.

Common mistakes

Calculating declining balance on the original cost every year

Declining balance applies the rate to the remaining book value, not the cost. Each row must use the previous row's book value.

Letting book value drop below salvage

Cap each year at book value minus salvage with MIN, or use DDB or VDB, which do it for you.

Relative references to the inputs

Copying =(B2-B3)/B4 down one row turns it into =(B3-B4)/B5. Use $B$2, $B$3 and $B$4.

Mixing up the argument order

All of SLN, DB, DDB and VDB take cost, then salvage, then life. Swap cost and salvage and you get negative depreciation.

FAQ

What is the formula for straight-line depreciation in Excel?

=SLN(cost,salvage,life), or the same thing written out, =(cost-salvage)/life. A $38,000 asset with $6,000 salvage over 5 years gives $6,400 a year.

What is the difference between DB and DDB in Excel?

DB uses a fixed rate, worked out from cost, salvage and life and rounded to three decimals. DDB uses twice the straight-line rate (or another factor you give it). Both apply the rate to the remaining book value each year.

How do I calculate monthly depreciation?

For straight-line, divide the yearly amount by 12, or use =SLN(cost,salvage,life_in_months). With DDB you can also give the life in months and ask for a month as the period.

Why does my DDB schedule show 0 in the last years?

The book value reached the salvage value early, so there was nothing left to depreciate. That is correct. Use straight-line or VDB if you want the cost spread over every year.

Can I use this schedule for my taxes?

Not without checking. Tax rules set their own methods, lives and allowances, which vary by country and change over time. An accountant can tell you which applies to your asset.

Functions used

Related guides

Updated November 3, 2026