Straight-line vs declining balance depreciation in Excel (SLN, DB, DDB)

Straight-line depreciation with SLN

The syntax is =SLN(cost, salvage, life). It has no period argument because every full year gets the same amount: ($50,000 − $5,000) ÷ 5 = $9,000. The book value falls in a straight line from $50,000 to $5,000, which is where the name comes from.

Straight-line is the simplest method and suits assets that are used about evenly over their life, such as office furniture or buildings. In a fixed asset register, the accumulated depreciation is the sum of the yearly charges, and the net book value is cost minus accumulated depreciation. For an asset bought during the year, many companies charge only the months in service in the first year: =SLN(cost,salvage,life)*months/12.

Declining balance with DB: the rate and the rounding

The syntax is =DB(cost, salvage, life, period, [month]). Excel first calculates a fixed rate, 1 − (salvage / cost)^(1 / life), and rounds it to three decimals: 1 − 0.1^(0.2) = 0.36904, rounded to 0.369. Every year's depreciation is the opening book value times that rate:

1. Year 1: $50,000 × 36.9% = $18,450. Book value $31,550.
2. Year 2: $31,550 × 36.9% = $11,641.95. Book value $19,908.05.
3. Year 3: $7,346.07, year 4: $4,635.37, year 5: $2,924.92.

Because of the rounded rate, the book value after five years is $5,001.69, not exactly the $5,000 salvage value. That is how DB works in Excel, not a mistake in your sheet. If the schedule must end exactly at salvage, put the difference into the last year or use DDB or VDB.

The optional month argument handles a partial first year. With month = 3 (the asset was bought in October), year 1 gets only 3/12 of a full year: $50,000 × 36.9% × 3 ÷ 12 = $4,612.50. The schedule then runs one year longer, and the sixth period takes the remaining 9 months, $1,991.32 here.

Double-declining balance with DDB and VDB

The syntax is =DDB(cost, salvage, life, period, [factor]). The rate is factor ÷ life, with a default factor of 2, so 2 ÷ 5 = 40% of the opening book value: $20,000, $12,000, $7,200 and $4,320. In year 5, 40% of $6,480 would be $2,592, but DDB never depreciates below the salvage value, so it charges only $1,480 and the book value ends at exactly $5,000. For the 150% declining balance method, set the factor to 1.5.

Note that DB is not double-declining: DB derives its rate from the salvage value (36.9% here), DDB from the life and the factor (40%). The two are easy to confuse because both are called declining balance.

A common rule with declining balance is to switch to straight-line once that gives a higher charge on the remaining book value. =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) does this automatically and also accepts partial periods, for example =VDB(B2,B3,B4,0,0.25) for the first quarter. In this example the switch never pays off, so VDB over years 0 to 5 returns the same $45,000 as DDB.

Worked example: a $50,000 machine with five methods

Cost, salvage value, life and the months in the first year are inputs (yellow). Each method column is filled down from year 1 to 5.

Worked example: a $50,000 machine with five methods

	A	B	C	D	E	F	G
1	Input	Value
2	Cost ($)	50,000
3	Salvage value ($)	5,000
4	Useful life (years)	5
5	Months in the first year (DB variant)	3
6
7	Year	SLN	DB	DDB	SYD	Book value after DB	DB, 3 months in year 1
8	1	9,000.00	18,450.00	20,000.00	15,000.00	31,550.00	4,612.50
9	2	9,000.00	11,641.95	12,000.00	12,000.00	19,908.05	16,747.99
10	3	9,000.00	7,346.07	7,200.00	9,000.00	12,561.98	10,567.98
11	4	9,000.00	4,635.37	4,320.00	6,000.00	7,926.61	6,668.40
12	5	9,000.00	2,924.92	1,480.00	3,000.00	5,001.69	4,207.76
13	6						1,991.32
14	Total	45,000.00	44,998.31	45,000.00	45,000.00		44,795.94
15	Book value at the end	5,000.00	5,001.69	5,000.00	5,000.00		5,204.06
16
17	Check						Value
18	DB rate, rounded to three decimals as Excel does						36.9%
19	DDB rate (factor 2 ÷ life)						40.0%
20	VDB years 0–5, declining balance with switch to straight line						45,000.00

Fictional asset and teaching numbers. Accounting and tax depreciation rules differ between countries and companies; this is not tax or accounting advice.

Sum-of-years' digits with SYD

=SYD(cost, salvage, life, period) is another accelerated method. The years' digits of a 5-year life add up to 1 + 2 + 3 + 4 + 5 = 15. Year 1 gets 5/15 of the depreciable $45,000 ($15,000), year 2 gets 4/15 ($12,000), and so on down to $3,000 in year 5. The charge falls by the same amount every year, and the total is exactly $45,000.

Which method to use

In financial reporting, the method should reflect how the asset's benefits are used up: straight-line for even use, declining balance or SYD for assets that lose most of their value or usefulness early, such as vehicles, computers or machines with heavy early use. The total depreciation is the same; only the timing of the expense, and therefore of reported profit, changes.

For taxes, many countries prescribe their own methods, rates and first-year rules, which can differ from the accounting books. The resulting differences between book and tax values are what deferred taxes are about. Check the rules that apply to you or ask a tax adviser; this guide only shows how the Excel functions calculate. For how depreciation flows through a full model, see the three-statement model example.

Common mistakes

• Expecting DB to end exactly at salvage. Excel rounds the DB rate to three decimals, so the final book value is usually a little off, $5,001.69 instead of $5,000 here.
• Using DB when double-declining is meant. DB is a fixed-rate method based on the salvage value. Double-declining balance is DDB with the default factor 2.
• Forgetting the partial first year. An asset bought in October does not get a full year of depreciation. Use the month argument of DB, VDB with fractional periods, or SLN × months ÷ 12.
• Relative references when filling down. Cost, salvage and life must be absolute ($B$2), only the period changes from row to row.
• Depreciating below salvage. A hand-made declining balance formula keeps going below the salvage value. DDB and VDB stop at salvage; a manual schedule needs a MIN cap.
• Using a date as the period. The period argument is a number (1, 2, 3 …), not the date of the year end.