How to calculate straight-line depreciation in Excel

Q: How do I make a depreciation schedule?

List years 1 to 6 in A9:A14 and put =$B$2-($B$2-$B$3)/$B$4*A9 in B9 for the book value at each year end.

Formula (Excel and Google Sheets)

=(B2-B3)/B4

Divide the cost minus the salvage value by the useful life: =(B2-B3)/B4, the same result as Excel's SLN function.

Result on the example: $5,416.67 in B5
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in B5. Change any input and the result updates, or edit the formula to experiment.

How it works

Straight-line depreciation spreads the part of the cost you expect to lose, cost minus salvage value, evenly over the useful life. $32,500 over 6 years is $5,416.67 a year, every year. Excel's SLN function does exactly this division: SLN(B2,B3,B4).

=(B2-B3)/B4

Result5416.666667

B2$38,500
The value in B2.
B3$6,000
The value in B3.
B2-B332500
The - operator subtracts.
B46
The value in B4.
B2-B3)/B45416.666667
The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =(B2-B3)/B4. We ran it in Google Sheets mode on the same data and it returns $5,416.67.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=(B2-B3)/B4/12	$451.39Different result	Monthly depreciation, for monthly books. Per month: $451.39.
=B2-(B2-B3)/B4*2	$27,666.67Different result	Book value after 2 years: cost minus two years of depreciation. Book value after two years: $27,666.67.

Common mistakes

Leaving out the salvage value overstates depreciation and leaves the book value at 0 instead of the expected resale value.
An asset bought mid-year usually gets a partial first year. Multiply the first year by the months owned / 12.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate straight-line depreciation

In B5, calculate the yearly straight-line depreciation for the asset: cost in B2, salvage value in B3, useful life in B4.

Questions

How do I make a depreciation schedule?

List years 1 to 6 in A9:A14 and put =$B$2-($B$2-$B$3)/$B$4*A9 in B9 for the book value at each year end.

What about declining-balance depreciation?

Excel has DB and DDB for that; they front-load the depreciation instead of spreading it evenly.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.