SLN, DB and DDB Depreciation Functions in Python

Excel has five depreciation functions, and finance models use all of them: SLN for straight line, SYD for sum-of-years' digits, DB for fixed-declining balance, DDB for double-declining balance and VDB for declining balance with a switch to straight line. They look interchangeable, but each has quirks that a Python translation must copy exactly to produce the same numbers: DB rounds its rate to three decimal places and prorates the first and last years by a month count; DDB never switches to straight line, so it stops short of the salvage value unless the last periods happen to catch up; VDB does switch. A naive "multiply book value by a rate" gets close and is wrong in the last years — exactly where auditors look. This page implements each function, lays the results out as a schedule with pandas, and checks every period against the spreadsheet's own calculation. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

The functions use only standard Python; pandas builds the schedules. The check writes the same formulas into a workbook and recalculates it with LibreOffice (soffice), whose depreciation functions follow Excel's definitions. The example asset costs 30,000, has a salvage value of 3,000 and a useful life of 5 years. For month-by-month schedules with disposals and a full asset register, see Build a Fixed Asset Depreciation Schedule.

Step 1 — SLN and SYD

Straight line spreads the depreciable amount evenly; sum-of-years' digits weights early years more heavily, using the remaining life over the sum of the years' digits:

def sln(cost, salvage, life):
    return (cost - salvage) / life

def syd(cost, salvage, life, per):
    return (cost - salvage) * (life - per + 1) * 2 / (life * (life + 1))

COST, SALVAGE, LIFE = 30_000, 3_000, 5
print("SLN:", sln(COST, SALVAGE, LIFE))
print("SYD:", [round(syd(COST, SALVAGE, LIFE, p), 2) for p in range(1, LIFE + 1)])

Straight line charges 5,400 a year. Sum-of-years' digits divides the 27,000 depreciable amount in the proportions 5:4:3:2:1 out of 15, so year 1 carries 9,000 and year 5 only 1,800. Both total exactly 27,000 over the life, ending at the salvage value. These two are the easy cases; the declining-balance functions are where the details live.

Step 2 — DB: fixed-declining balance with a rounded rate

DB(cost, salvage, life, period, [month]) applies a fixed rate to the book value each year. Excel computes the rate as 1 − (salvage/cost)^(1/life) and rounds it to three decimals; the optional month argument is the number of months in the first year, which prorates the first year and adds a stub period at the end:

def db(cost, salvage, life, period, month=12):
    rate = round(1 - (salvage / cost) ** (1 / life), 3)
    book = cost
    for p in range(1, period + 1):
        if p == 1:
            dep = cost * rate * month / 12
        elif p == life + 1:
            dep = book * rate * (12 - month) / 12
        else:
            dep = book * rate
        book -= dep
    return dep

print("DB rate:", round(1 - (SALVAGE / COST) ** (1 / LIFE), 3))
print("DB full years:", [round(db(COST, SALVAGE, LIFE, p), 2) for p in range(1, LIFE + 1)])
print("DB, 7 months in year 1:", [round(db(COST, SALVAGE, LIFE, p, month=7), 2) for p in range(1, LIFE + 2)])

The exact rate would be 0.36904…; Excel uses 0.369. That rounding is why a reimplementation with the unrounded rate drifts a few units per year from the spreadsheet. With month=7 — an asset bought in June of a calendar year — the first year carries only 7/12 of a year's charge, and a sixth period picks up the remaining 5/12 at the end. Because the rate is rounded, the book value after the last period lands near but not exactly on the salvage value; DB does not force it.

Step 3 — DDB: double-declining, without a switch

DDB(cost, salvage, life, period, [factor]) charges factor / life of the remaining book value each period (factor 2 by default), but never takes book value below salvage. It does not switch to straight line when that would give a larger charge:

def ddb(cost, salvage, life, period, factor=2):
    book = cost
    for p in range(1, period + 1):
        dep = min(book * factor / life, max(book - salvage, 0))
        book -= dep
    return dep

charges = [round(ddb(COST, SALVAGE, LIFE, p), 2) for p in range(1, LIFE + 1)]
print("DDB:", charges, "| total:", round(sum(charges), 2), "of", COST - SALVAGE)

Year 1 charges 40% of 30,000 — 12,000 — and each following year 40% of what remains. With these figures the cap bites in the last year — book value is not allowed below 3,000 — and the total happens to reach the full 27,000. With a lower salvage value, or a longer life, DDB leaves part of the depreciable amount undepreciated at the end of the life, because the declining charge shrinks faster than the remaining amount. That is the behaviour to reproduce if a workbook uses DDB; it is usually not the accounting treatment people intend, which is what VDB is for.

Step 4 — VDB: declining balance that switches to straight line

VDB(cost, salvage, life, start, end, [factor], [no_switch]) returns the depreciation between two periods. By default it switches to straight line when the straight-line charge over the remaining life exceeds the declining charge, so the asset always reaches its salvage value:

def vdb_schedule(cost, salvage, life, factor=2, switch=True):
    book, out = cost, []
    for p in range(1, life + 1):
        declining = book * factor / life
        straight = (book - salvage) / (life - p + 1)
        dep = max(declining, straight) if switch else declining
        dep = min(dep, max(book - salvage, 0))
        out.append(dep)
        book -= dep
    return out

def vdb(cost, salvage, life, start, end, factor=2):
    return sum(vdb_schedule(cost, salvage, life, factor)[int(start):int(end)])

low_salvage = 1_000
print("DDB, salvage 1,000:", [round(ddb(COST, low_salvage, LIFE, p), 2) for p in range(1, LIFE + 1)])
print("VDB, salvage 1,000:", [round(vdb(COST, low_salvage, LIFE, p - 1, p), 2) for p in range(1, LIFE + 1)])

With a salvage value of 1,000, DDB charges 1,555.20 in year 5 and ends with a book value of 2,332.80 — 1,332.80 short of salvage. VDB switches to straight line in year 4, when the remaining amount spread over the two remaining years is larger than the declining charge, and depreciates exactly to 1,000. This implementation covers whole periods, which is how VDB is nearly always used; Excel's version also accepts fractional start and end periods, which a model using them would need to add.

Step 5 — Schedules in pandas, checked against the spreadsheet

Lay all five methods out side by side, then write the equivalent formulas into a workbook, recalculate, and compare every period:

import subprocess
import pandas as pd
from openpyxl import Workbook, load_workbook

years = range(1, LIFE + 1)
sched = pd.DataFrame({
    "Year": list(years),
    "SLN": [sln(COST, SALVAGE, LIFE)] * LIFE,
    "SYD": [syd(COST, SALVAGE, LIFE, p) for p in years],
    "DB": [db(COST, SALVAGE, LIFE, p) for p in years],
    "DDB": [ddb(COST, SALVAGE, LIFE, p) for p in years],
    "VDB": [vdb(COST, SALVAGE, LIFE, p - 1, p) for p in years],
}).round(2)
print(sched.to_string(index=False))

wb = Workbook()
ws = wb.active
for i, p in enumerate(years, start=1):
    ws.append([f"=SLN({COST},{SALVAGE},{LIFE})", f"=SYD({COST},{SALVAGE},{LIFE},{p})", f"=DB({COST},{SALVAGE},{LIFE},{p})",
               f"=DDB({COST},{SALVAGE},{LIFE},{p})", f"=VDB({COST},{SALVAGE},{LIFE},{p - 1},{p})",
               f"=DB({COST},{SALVAGE},{LIFE},{p},7)", f"=DDB({COST},{low_salvage},{LIFE},{p})"])
wb.save("dep.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "dep.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/dep.xlsx", data_only=True).active
ours = [[sln(COST, SALVAGE, LIFE), syd(COST, SALVAGE, LIFE, p), db(COST, SALVAGE, LIFE, p), ddb(COST, SALVAGE, LIFE, p),
         vdb(COST, SALVAGE, LIFE, p - 1, p), db(COST, SALVAGE, LIFE, p, 7), ddb(COST, low_salvage, LIFE, p)] for p in years]
diffs = [abs(calc.cell(r, c).value - ours[r - 1][c - 1]) for r in range(1, LIFE + 1) for c in range(1, 8)]
print(f"{len(diffs)} values compared, largest difference: {max(diffs):.10f}")

The schedule shows the five methods' very different shapes for the same asset: level for SLN, steep then shallow for SYD, DB and DDB, and VDB tracking DDB until it switches. All 35 spreadsheet values — five methods, plus DB with a partial first year and DDB with the lower salvage, for five years each — match the Python functions to rounding error. Keeping this comparison as a test protects the quirks (the rounded DB rate, the missing DDB switch) from being "fixed" by someone who assumes they are bugs.

Common pitfalls

• Using the exact DB rate. Excel rounds it to three decimals; use the rounded rate to match.
• Forgetting DB's month argument. A partial first year adds a stub period after the life.
• Expecting DDB to reach salvage. It does not switch to straight line; use VDB for that.
• Rounding each period before carrying the book value. Keep full precision, round only for display.
• No comparison with the spreadsheet. Test every method, period by period.

Conclusion

Excel's depreciation functions are short to implement and easy to get subtly wrong: SLN divides the depreciable amount evenly, SYD weights it by the remaining years' digits, DB applies a fixed rate rounded to three decimals with an optional partial first year, DDB applies factor ÷ life to the book value without ever switching to straight line, and VDB switches when straight line gives more. Implement each separately, build schedules with pandas, and check every period against the spreadsheet functions before relying on the Python version.

Frequently asked questions

How do I calculate straight-line depreciation in Python? Subtract the salvage value from the cost and divide by the useful life, which is what Excel's SLN function returns for each period.

Why doesn't my Python DB calculation match Excel? Excel rounds the fixed-declining rate to three decimal places. Calculate the rate as 1 minus salvage divided by cost to the power of one over life, round it to three decimals, then apply it.

Does Excel's DDB depreciate down to the salvage value? Not necessarily. DDB never switches to straight line, so with some inputs the book value stays above salvage at the end of the life. VDB switches and reaches the salvage value.

What is the difference between DDB and VDB? DDB returns the double-declining charge for one period without switching method. VDB returns the depreciation between two periods and by default switches to straight line when that gives a larger charge.

Related

• Up one level: Excel Formula Equivalents in pandas — spreadsheet functions in Python.
• Build a Fixed Asset Depreciation Schedule — a full monthly register.
• PMT, NPV and IRR in Python — other finance functions.
• IPMT, PPMT, EFFECT and NOMINAL Equivalents in Python — loan and rate functions.