Spreadsheets

DB function: declining balance depreciation in Excel

Use Excel's DB function for declining balance depreciation: see what each argument does, work through five years of an example, and handle a part-year purchase.

Editorial illustration for DB function: declining balance depreciation in Excel

The DB function in Excel calculates declining balance depreciation, which is the idea behind written down value: each year's charge is a fixed percentage of the book value left. Its syntax is =DB(cost, salvage, life, period, [month]). For a ₹80,000 asset with ₹8,000 salvage and a five-year life, DB gives ₹29,520 in year 1 and smaller amounts after that.

What do the arguments mean?

  • cost: the amount you paid, ₹80,000
  • salvage: the value at the end of the asset's life, ₹8,000. Use a small positive number, not zero.
  • life: the number of years over which you depreciate, 5
  • period: which year you want the charge for, 1 to 5
  • month: optional, the number of months the asset was in use in the first year. If you leave it out, Excel uses 12.

Behind the scenes, Excel works out a rate as 1 - (salvage/cost)^(1/life), rounded to three decimal places. Here that is 1 - (8,000/80,000)^(0.2), which comes to 0.369, or 36.9 percent a year. Each year's charge is that rate times the book value at the start of the year.

Why not zero salvage?

With a salvage of zero the formula's rate becomes 1, so the whole cost is charged in the first period. That is mathematically right for the function but useless for a real schedule. Use a small positive salvage, such as ₹1, if you want the machinery to run.

What are the five years of charges?

Fill a year column 1 to 5 and write =DB($B$1, $B$2, $B$3, A6) next to each, where B1 to B3 hold cost, salvage and life. Figures rounded to the nearest rupee, so a rupee may not tie:

YearDepreciationBook value at year end
129,52050,480
218,62731,853
311,75420,099
47,41712,683
54,6808,003

The year 1 charge is 80,000 x 0.369 = 29,520. Year 2 is 50,480 x 0.369 = 18,627. The book value ends at about ₹8,003, close to the ₹8,000 salvage because the rate is rounded to three places.

How does the month argument work?

Say the asset was bought with six months of the first year left. Then =DB(80000, 8000, 5, 1, 6) returns 80,000 x 0.369 x 6/12 = ₹14,760 for the first, partial year. Year 2 is a full year on the remaining value: (80,000 - 14,760) x 0.369 = ₹24,073.56. Because the first year was short, Excel's schedule runs to a sixth period to finish the life, with a part-year charge in that last period.

The function also exists in Google Sheets with the same argument order. A practical tip is to build the schedule one row at a time, using the period number in column A, so you can see how each year's charge relates to the book value before it. If the numbers look odd, check that cost is larger than salvage and that period does not exceed life, because Excel may show an error otherwise.

When would you choose DB rather than SLN?

Straight line charges the same each year. Declining balance charges more early and less later, which fits assets that lose value fastest when new, such as laptops and phones. Put both in adjacent columns for the same asset and compare the totals after year 2. Neither is right for every purpose, so choose the one that matches how you want to report.

Checks before you trust the schedule

  1. Confirm the total of all years is close to cost minus salvage, within rounding.
  2. Check that the life you typed matches the number of rows.
  3. Test the month argument by entering 12 and comparing with the version that omits it.
  4. Write down the method and assumptions beside the sheet.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports