# How to calculate straight-line depreciation in Excel

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

**Excel:** `=(B2-B3)/B4` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Delivery van | Value |
| Cost | 38500 |
| Salvage value | 6000 |
| Useful life (years) | 6 |
| Yearly depreciation |  |

Formula in B5: `=(B2-B3)/B4`

Result: $5,416.67

## 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)`.

## Other ways

- `=(B2-B3)/B4/12`: Monthly depreciation, for monthly books. (Per month: $451.39.)
- `=B2-(B2-B3)/B4*2`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/straight-line-depreciation