01 / 14Five Machines, Three Short Years

DepreciationExcelFinancial Functions

Depreciation in Excel: SLN, DB, DDB and VDB Explained

The Tele-handler Left the Yard at £16,720.95 Against a Book Value of £22,387.06, Because DB Charged a Full Year of Depreciation on Four Months of Ownership

19 Oct 202615 min read

UsesDBROUNDMIN, MAXSUM

Brackenhall Plant Hire sells its machines to the operators who drive them, at net book value. The MT-625 tele-handler went in August for £16,720.95. The policy the register is meant to run put it at £22,387.06.

Nothing errored. Every row of the schedule called the same function against the same four cells, the columns footed, and the asset wound down to something close enough to the £9,500 residual in the policy that nobody opened it.

DB takes a fifth argument, and it is the only thing in the function that knows when you bought the machine. Leave it out and DB charges twelve months of depreciation on an asset you owned for four.

MeasureAs the register had itWith the month filled in
Year 1 charge, 2022£24,938.00£8,312.67
Accumulated at 31/08/2025£57,279.05£51,612.94
Net book value at sale£16,720.95£22,387.06
Periods in the schedule56
Book value at the end£9,479.81£9,840.65

The tele-handler was one of three machines on the register bought partway through a year, and the first of them to be sold.

Scope. DB, DDB, SLN and VDB in Excel for Microsoft 365, 2021, 2019 and Excel for Mac. The argument lists and the rounding are the same in every version, and in Google Sheets.

The policy here is reducing balance over five years to a stated residual, on a December year end. Your own conventions — monthly charges, mid-month rules, pooled assets — change the arithmetic around the function, not the argument that goes missing inside it.

Plant hire is only the example. Vehicles, laptops, fit-outs and leasehold improvements all leave at the wrong number for the same reason.

01Five Machines, Three Short Years

Five Machines and the Argument Nobody Filled In

Brackenhall Plant Hire's fixed-asset register, in A2:G9. Every machine is depreciated on reducing balance over five years to a stated residual, and every row of every schedule calls DB with four arguments instead of five. Column D is the first-year charge the register booked; column E is what the same function returns once it is told how many months of that first year the machine was owned. The two rows that agree are the two machines bought on 1 January, where twelve months happens to be right. Nothing on this sheet errors, no total disagrees with its own column, and the error only surfaces when a machine is sold at a book value three years of compounding has pulled £5,666.11 too low.

ABCDEFG
1
Asset
Bought
Months owned in year 1
As the register had it
With the month filled in
Difference
What happened
2
Tele-handler MT-625
01/09/2022
4
24938
8312.67
16625.33
The machine the operator bought. Owned for four months of 2022 and depreciated for twelve, so £16,625.33 of its value came off the books in a year it spent mostly on a forecourt in Preston. Everything after that period compounds the error, because DB applies its rate to whatever the previous period left.
3
Site dumper D-900
01/01/2023
12
6197.5
6197.5
0
The row that reconciles, and the reason the register looked sound. Bought on the first day of the financial year, so twelve months is the correct answer and the missing argument costs nothing at all.
4
Excavator EX-14
01/05/2023
8
14444
9629.33
4814.67
Eight months owned, twelve charged. A third of a year of depreciation, £4,814.67, booked before the machine existed as far as this company was concerned.
5
Road sweeper RS-7
01/11/2023
2
10688
1781.33
8906.67
The worst of the five in proportion: two months of ownership carrying a full year's charge, which is six times the depreciation the policy asks for.
6
Generator G-60
01/01/2022
12
4166.4
4166.4
0
The second January purchase, and the second row that agrees. Two rows out of five reconciling is what a spot check finds when it stops at two rows.
7
First-year charge, all five
60433.9
30087.23
30346.67
The register booked £60,433.90 of first-year depreciation where the policy asks for £30,087.23. The £30,346.67 difference is not lost — it is charged early, which is why no total ever looks wrong.
8
MT-625 book value at sale
31/08/2025
16720.95
22387.06
5666.11
Three years of compounding a four-month head start. The operator paid the register's number, £16,720.95, for a machine the company's own policy valued at £22,387.06.

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

The sheet above is the register's first-year charge, one row per machine. Column D is what the schedule booked, column E is what the same function returns once it is told how long the machine was owned, and column F is the gap.

The dumper and the generator reconcile exactly. Both were bought on 1 January, so twelve months is the right answer and the missing argument costs nothing.

The tele-handler, the excavator and the sweeper were bought in September, May and November. Between them the register charged £30,346.67 of depreciation in a year the machines were not there for.

Charging too much early is not a wash that comes out later. It moves value out of the years that earned it, and it prices anything sold off the register too low.

Scenario: Open your own register and put =DB($B$2,$B$3,$B$4,1) beside =DB($B$2,$B$3,$B$4,1,$B$5) for your newest asset, with B5 holding the months you owned it. Subtract one from the other. Anything but nought is sitting in this year's profit.

Try it in the grid

02What DB Does With Your Four Numbers

DB derives one rate and applies it to whatever the last period left behind. The rate is =ROUND(1-($B$3/$B$2)^(1/$B$4),3): one minus the fifth root of residual over cost, rounded to three decimal places.

For the tele-handler that is one minus the fifth root of 9,500 ÷ 74,000, which is 0.33671 and which DB rounds to 0.337. Every later period takes 33.7% of the book value standing at the start of it.

The inputs live in four cells and nowhere else: B2 cost, B3 residual, B4 life in years, B5 months owned in the first year. Where a policy sets lives by asset class, B4 is itself a lookup: =XLOOKUP($B$1,Policy[Class],Policy[Life]).

The schedule starts at row 10 with an opening row — period 0, accumulated nought, closing book value =$B$2 — and runs one row per period beneath it.

Scenario: Put =ROUND(1-(9500/74000)^(1/5),3) in a spare cell, then put =DB(74000,9500,5,2)/(74000-DB(74000,9500,5,1)) beside it. The two should return the same 0.337, which is DB's rate shown twice.

Try it in the grid

03The Fifth Argument Is Months, Not a Date

DB(cost, salvage, life, period, [month]) takes its fifth argument as the number of months in the first year, not the date you bought the asset. It defaults to 12, which is the whole bug.

With it supplied, period 1 is cost × rate × month ÷ 12. The tele-handler's four months are 74,000 × 0.337 × 4 ÷ 12, or £8,312.67, against the £24,938.00 the register booked.

BoughtMonths in year 1Period 1
1 January12£24,938.00
1 May8£16,625.33
1 September4£8,312.67
1 November2£4,156.33

Every period after the first compounds the difference, because DB applies its rate to the book value the previous period left. A first year charged twice over hands every later year a smaller base.

Scenario: In an empty sheet, fill A1:A6 with 1 to 6 and put =DB(74000,9500,5,A1,4) in B1, filled down. Compare the total with =DB(74000,9500,5,A1) filled down the same way.

Try it in the grid

04A Part Year Buys You a Sixth Period

A five-year asset bought in September is on your books across six accounting years, and DB knows it. When month is less than 12, the schedule runs to life + 1 periods, not life.

The extra period is the mirror of the first: (cost − accumulated) × rate × (12 − month) ÷ 12. For the tele-handler that is period 6, and it is £2,851.51 of depreciation.

PeriodYearChargeClosing book value
12022£8,312.67£65,687.33
22023£22,136.63£43,550.70
32024£14,676.59£28,874.11
42025£9,730.58£19,143.53
52026£6,451.37£12,692.16
62027£2,851.51£9,840.65

A schedule built with five rows because the life says five years strands that last charge. The asset sits on the books above its residual for ever, or somebody writes the balance off in one line nobody can explain.

The row count is a formula, not a judgement. =IF($B$5<12,$B$4+1,$B$4) returns the number of periods the schedule needs.

Scenario: Add =DB(74000,9500,5,6,4) under your five-row schedule. If it returns a number rather than an error, your schedule is a row short of the asset's life.

Try it in the grid

05SLN, DB and DDB on the Same Machine

Three functions, one machine, three different answers. Which one is right is a policy question you answer once and then implement, not a choice you make per asset.

SLN(cost, salvage, life) spreads the loss evenly: =SLN(74000,9500,5) is £12,900.00 a year, every year. It takes no period argument because every period is the same.

YearSLNDB, full yearDDB
1£12,900.00£24,938.00£29,600.00
2£12,900.00£16,533.89£17,760.00
3£12,900.00£10,961.97£10,656.00
4£12,900.00£7,267.79£6,393.60
5£12,900.00£4,818.54£90.40
Total£64,500.00£64,520.19£64,500.00

Straight line is simplest and reads best on a board pack. Reducing balance matches how plant actually loses value: a tele-handler is worth far less after its first year than after its fifth.

Here, SLN and DDB both land exactly on cost minus residual. DB's total is £20.19 adrift of the £64,500 it is supposed to write off, and section 8 is about that number.

Scenario: Put =SLN(74000,9500,5), =DB(74000,9500,5,1) and =DDB(74000,9500,5,1) in three cells side by side, then read them against your own written policy. One of the three is the one you are supposed to be using.

Try it in the grid

06DDB Is Faster Still and Stops at the Residual

DDB(cost, salvage, life, period, [factor]) takes a fixed share of the remaining book value each period. The factor defaults to 2, which on a five-year life is 2 ÷ 5, or 40%.

That makes year one £29,600.00 on the tele-handler, which is nearly half the depreciable amount in the first twelve months. Year two takes 40% of what is left, and so on down.

The last year is the one that surprises people: £90.40, not £3,836.16. DDB refuses to take book value below the residual, so once the four earlier years have written off £64,409.60 of a £64,500 allowance, £90.40 is all there is left to take.

DDB has no month argument. A part year under DDB is either VDB's job or a manual proration, which is the next section.

Scenario: Fill A1:A5 with 1 to 5 and put =DDB(74000,9500,5,A1) beside it. Add =SUM(B1:B5) underneath and check it against =74000-9500. The two match exactly, which is the thing DB cannot do.

Try it in the grid

07VDB Takes Fractions of a Year

VDB(cost, salvage, life, start, end, [factor], [no_switch]) is the declining-balance function that measures between two points on an asset's life, and those points may be fractions.

Four months from the day of purchase is =VDB(74000,9500,5,0,4/12), which returns £9,866.67 — four months at DDB's 40% rate. The year after it is =VDB(74000,9500,5,4/12,16/12).

The factor argument is the rate multiplied by the life, so DB's 33.7% is a factor of 1.685. =VDB(74000,9500,5,0,4/12,1.685) returns £8,312.67, exactly what DB returns for its first period.

The last argument is the one to read twice. Left out or FALSE, VDB switches to straight line over the remaining life as soon as straight line gives the bigger charge. TRUE keeps it declining to the end.

Scenario: Put =VDB(74000,9500,5,0,5) and =VDB(74000,9500,5,0,5,2,TRUE) in two cells. The first writes the asset down to its residual; the second shows you what the switch is worth.

Try it in the grid

08A Rounded Rate Never Lands on the Residual

DB rounds its rate to three decimal places, and 0.337 is not 0.33671. Over six periods that rounding leaves the tele-handler at £9,840.65 against a policy residual of £9,500 — £340.65 that never gets written off.

It cuts the other way too. The register's own five-period schedule, with no month argument, ended at £9,479.81: £20.19 below the residual the policy set.

So the schedule needs two guards rather than one. Cap every period at what is left above the residual, and make the final period the balancing figure:

B11: =LET(open, D10,
          raw,  DB($B$2,$B$3,$B$4,A11,$B$5),
          MIN(raw, MAX(0, open - $B$3)))
B16: =D15-$B$3

The last period is a plug, and it should be. The policy says the asset reaches £9,500, so the closing book value is the input and the final charge is whatever gets you there: £3,192.16 rather than DB's £2,851.51.

Scenario: Put =SUM($B$11:$B$16) under your schedule and =$B$2-$B$3 beside it. Before the plug they differ by the rounding; after it they agree to the penny.

Try it in the grid

09Work the Month Out, Do Not Type It

A typed 4 is a 4 for ever, including on the asset somebody copies this block to next March. The month belongs in a formula, from the purchase date and the year end.

On a December year end, months owned in the first year is =13-MONTH($B$6). September gives 4, November gives 2, January gives 12.

For any other year end, put the year end in B7 and use =12-MOD(MONTH($B$6)-MONTH($B$7)-1,12). On a 31 March year end that returns 7 for a September purchase and 1 for a March one.

Where the register holds the year end as a date, =DATE(YEAR($B$6),12,31) builds a December one from the purchase date, and =EOMONTH($B$6,0) gives the end of the month the asset arrived in.

Scenario: Replace the typed month in your newest asset's schedule with =13-MONTH($B$6), then change the purchase date to 1 November and watch period 1 fall to a sixth of a full year.

Try it in the grid

10A Disposal Is a Date, Not a Row

The MT-625 was sold on 31 August 2025, eight months into its fourth period. The charge to the date of sale is that period's charge pro-rated: =DB($B$2,$B$3,$B$4,4,$B$5)*8/12.

Where the convention is to charge to the end of the month of disposal, the months come from the sale date the same way the first year's did: =MONTH(EOMONTH($B$8,0)) on a December year end.

At 31/08/2025As the register had itWith the month filled in
Accumulated to 31/12/2024£52,433.86£45,125.89
Charge to 31/08/2025£4,845.19£6,487.05
Accumulated at sale£57,279.05£51,612.94
Net book value£16,720.95£22,387.06

Both columns pro-rate the final year identically. The whole £5,666.11 gap was created in 2022 and compounded by three periods of applying 33.7% to a base that was already too low.

A sale priced at net book value is a formula with your register inside it. The machine left at the number the sheet held, not the number the policy described.

Scenario: Put =$B$9-D14 beside your next disposal, with B9 the proceeds and D14 the book value at the sale date. A profit on every disposal in a row is a register depreciating faster than its assets do.

Try it in the grid

11Five Checks Before the Register Goes Out

Each of these is one cell, and each catches a different failure. Put them above the schedule where somebody has to scroll past them.

  1. The schedule writes off what it should. =SUM($B$11:$B$16) against =$B$2-$B$3: equal to the penny once the final period is a plug.
  2. Book value never goes under. =MIN($D$11:$D$16) against =$B$3. Anything lower is depreciation the policy never authorised.
  3. The period count matches the life. =COUNT($A$11:$A$16) against =IF($B$5<12,$B$4+1,$B$4). A part year needs the extra row.
  4. The first year is not a full year. =ROUND(B11/$B$2,3) returns 0.337 — the rate itself — when the month argument is missing, and 0.112 when it is there.
  5. The register agrees with the ledger. =SUMIFS(Ledger[Amount],Ledger[Code],"DEPN") against the register's total for the same year.

Scenario: Add check 4 to every asset block you have, then sort the column. Every row returning the bare rate is an asset that was charged a full year for a part year.

Try it in the grid

12Ten Traps

  1. The fifth argument left empty. Twelve months on an asset you owned for two, and no error anywhere.
  2. A schedule one row short. With month under 12 there are life + 1 periods, and the last one is real depreciation.
  3. A residual of nought. The rate becomes exactly 1, so DB writes the whole asset off in its first period. Use SLN, DDB or VDB for an asset depreciated to nothing.
  4. Treating the rate as exact. It is rounded to three decimals, so the schedule lands near the residual rather than on it.
  5. A month argument above 12 or below 1. DB returns #NUM!, which at least is loud — until somebody wraps it in =IFERROR(DB(...),0) and turns it into a silent nought.
  6. DDB's factor assumed to be 2. A policy that says 150% declining balance needs 1.5 passed explicitly.
  7. DDB read as reaching the residual evenly. It front-loads, then collapses to whatever is left in the final year.
  8. VDB's last argument ignored. Left out, it switches to straight line partway through and changes every later figure.
  9. A typed month copied to the next asset. The block is copied, the date changes, the 4 does not.
  10. Pro-rating the first year by hand as well as passing month. The four months come off twice, and the charge drops to a third of what it should be.

13Five Exercises

  1. Reproduce it. Build the tele-handler's schedule twice, with and without a month of 4, and read the two book values at 31 August 2025.
  2. Add the missing row. Take the six-period schedule, delete period 6, and find where the £2,851.51 ends up.
  3. Plug the last period. Write period 6 as =D15-$B$3 and check =SUM($B$11:$B$16) against =$B$2-$B$3.
  4. Switch methods. Put SLN, DB and DDB side by side for the same machine, then total each column against cost minus residual.
  5. Break it with a zero. Set the residual to nought and watch DB take the whole £74,000 in period 1 while SLN and DDB behave.

What to Take Away

Nothing in this register was broken. DB returned exactly what it was asked for, every column footed, two of the five machines reconciled on a spot check, and the schedules ended within a few pounds of their stated residuals.

What nobody had written down is that DB assumes a full year unless you tell it otherwise, and that an asset bought partway through a year lives across one more accounting period than its life has years.

Two cells say it before a machine is priced: the first-year charge divided by cost, which equals the bare rate when the month is missing, and the schedule's total against cost minus residual.

Two cells, against £30,346.67 of depreciation taken in years the plant was not there for, and a tele-handler that left the yard £5,666.11 under the value its owner's own policy had put on it.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge