Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Build a Fixed Asset Depreciation Schedule with Python

Turn a fixed asset register into a monthly depreciation schedule: straight-line and double-declining balance with the switch to straight line, a start-of-depreciation convention, disposals part way through the year, net book values, a cost and depreciation roll-forward by category, and a check against Excel's SLN and VDB functions.
Report layouts & templatesExcel functions in pandas

The fixed asset register is a list; the depreciation schedule is what turns it into numbers for the accounts: how much each asset depreciates each month, what it is worth at the year end, and how the totals moved from opening to closing. Spreadsheets that do this by hand grow a column per month and a formula per cell, and break when an asset is sold mid-year or someone copies a formula one row too far. Python computes every asset's monthly charge from a few rules, and the output is a schedule the auditors can follow. This page is part of Building Finance and Operations Reports.

Prerequisites

Bash
pip install pandas openpyxl

asset_register.xlsx lists eight assets with their in-service date, cost, salvage (residual) value, useful life in months and method: SL for straight line, DDB for double-declining balance. One asset, the company car, was sold on 31 August 2026. The schedule is for the financial year January to December 2026.

The rules

Three decisions shape every number, and they belong at the top of the script where an accountant can read them:

  • When depreciation starts. Here: the month after the asset is put into service (full-month convention). Other common choices are the month of purchase or a half-month in the first month.
  • Declining balance. Double-declining balance charges 2 / life of the current book value each period and switches to straight line over the remaining life once that gives the larger charge, so the asset reaches its salvage value exactly at the end of its life.
  • Disposals. Depreciation runs up to and including the month of disposal; after that the asset leaves the register, and the gain or loss is the proceeds minus the book value at that point.
Book value of a 21,900 server rack under each method A line chart of the net book value of a server rack costing 21,900 with 1,500 salvage over 48 months, at yearly points. Straight line falls evenly, 5,100 a year, to 1,500. Double-declining balance falls faster in the first two years, switches to straight line in month 31, and reaches the same 1,500 at the end of year four. Straight line Double-declining 0k 10k 20k 30k Start Year 1 Year 2 Year 3 Year 4 21.9 16.8 11.7 6.6 1.5 21.9 13.1 7.9 4.6 1.5

Step 1 — One function for any asset and month

Compute each asset's full life, month by month, then keep the months of the year being reported. Computing the full life, rather than only this year, is what makes declining balance correct: this year's charge depends on every charge before it.

Python
import pandas as pd

register = pd.read_excel("asset_register.xlsx", parse_dates=["InService", "Disposed"])
YEAR_START, YEAR_END = pd.Period("2026-01", "M"), pd.Period("2026-12", "M")

def monthly_schedule(asset):
    """Depreciation for every month of the asset's life, as a DataFrame."""
    first = asset.InService.to_period("M") + 1            # full-month convention
    cost, salvage, life = asset.Cost, asset.Salvage, int(asset.LifeMonths)
    rows, book = [], cost
    for i in range(life):
        remaining = life - i
        straight = (book - salvage) / remaining
        if asset.Method == "DDB":
            charge = max(book * 2 / life, straight)          # switch when straight line is larger
            charge = min(charge, book - salvage)             # never below salvage
        else:
            charge = (cost - salvage) / life
        book -= charge
        rows.append((first + i, charge, book))
    out = pd.DataFrame(rows, columns=["Month", "Depreciation", "BookValue"])
    out.insert(0, "AssetID", asset.AssetID)
    if pd.notna(asset.Disposed):
        out = out[out["Month"] <= asset.Disposed.to_period("M")]
    return out

life = pd.concat([monthly_schedule(a) for a in register.itertuples()], ignore_index=True)
print(life[life["AssetID"] == "FA-104"].head(3).round({"Depreciation": 2, "BookValue": 2}).to_string(index=False))

The DDB switch is the line max(book * 2 / life, straight): early on the declining charge is larger; later, as book value falls, the straight-line charge over the remaining months overtakes it, and from then on the asset depreciates evenly to its salvage value. For the server rack that happens in month 31. An asset with a high salvage value may never switch: the delivery van, with 6,000 salvage on 38,500, reaches its salvage value in month 56 while the declining charge is still the larger one, and the min(..., book - salvage) cap stops it there.

Step 2 — The year's schedule, one column per month

Keep the months of 2026 and pivot to the familiar layout: assets down, months across, a total column:

Python
year = life[life["Month"].between(YEAR_START, YEAR_END)]
schedule = (year.pivot_table(index="AssetID", columns="Month", values="Depreciation", aggfunc="sum", fill_value=0)
                .reindex(register["AssetID"], fill_value=0))
schedule.columns = [m.strftime("%b") for m in schedule.columns]
schedule["FY2026"] = schedule.sum(axis=1)
print(schedule.round(0).astype(int).iloc[:, [0, 1, 6, 7, 8, 11, 12]].to_string())
Part of the FY2026 schedule A sheet with assets down and months across. The packing machine starts in March and the workstations in August; the company car stops after August, the month it was sold. Declining balance assets show a falling charge each month. Asset Jan Feb Jul Aug Sep Dec FY2026 FA-101 van 419 405 342 331 320 289 4,204 FA-104 server 874 838 677 649 622 548 8,394 FA-106 packer 0 0 974 958 942 895 9,669 FA-107 car 483 483 483 483 0 0 3,867 FA-108 workstations 0 0 0 267 267 267 1,333

The packing machine (FA-106) starts in March because it went into service in February; the workstations (FA-108) start in August; the car (FA-107) stops after August, the month it was sold.

Step 3 — Net book value and disposals

Book value at the year end, or at disposal, comes from the last computed month. Assets whose depreciation had not started by the year end are carried at cost:

Python
last = life[life["Month"] <= YEAR_END].groupby("AssetID").tail(1).set_index("AssetID")
reg = register.set_index("AssetID")
reg["BookValue"] = last["BookValue"].reindex(reg.index).fillna(reg["Cost"])
reg["AccumDep"] = reg["Cost"] - reg["BookValue"]

sold = reg[reg["Disposed"].notna()].copy()
sold["GainLoss"] = sold["Proceeds"] - sold["BookValue"]
print(sold[["Description", "Cost", "AccumDep", "BookValue", "Proceeds", "GainLoss"]].round(2).to_string())

A negative figure is a loss on disposal: the car sold for 716.67 less than its book value of 16,216.67. Gains and losses go to the P&L as other income or expense, separately from the depreciation charge.

Step 4 — The roll-forward by category

Auditors and the balance sheet want movements, not just balances: cost and accumulated depreciation at the start of the year, additions, disposals, the year's charge, and the closing figures. Every column is derived from the register and the life schedule:

Python
start = YEAR_START - 1                          # December 2025
dep_before = life[life["Month"] <= start].groupby("AssetID")["Depreciation"].sum()
dep_year = year.groupby("AssetID")["Depreciation"].sum()
reg["OpeningAccum"] = dep_before.reindex(reg.index).fillna(0)
reg["Charge"] = dep_year.reindex(reg.index).fillna(0)
added = reg["InService"].dt.year == 2026
gone = reg["Disposed"].dt.year == 2026

roll = pd.DataFrame({
    "Opening cost": reg["Cost"].where(~added, 0),
    "Additions": reg["Cost"].where(added, 0),
    "Disposals": -reg["Cost"].where(gone, 0),
    "Opening dep": reg["OpeningAccum"],
    "Charge": reg["Charge"],
    "Dep on disposals": -(reg["OpeningAccum"] + reg["Charge"]).where(gone, 0),
}).groupby(reg["Category"]).sum()
roll["Closing cost"] = roll["Opening cost"] + roll["Additions"] + roll["Disposals"]
roll["Closing dep"] = roll["Opening dep"] + roll["Charge"] + roll["Dep on disposals"]
roll["Closing NBV"] = roll["Closing cost"] - roll["Closing dep"]
roll.loc["Total"] = roll.sum()
print(roll.round(0).astype(int).to_string())

check = reg.loc[~gone, "BookValue"].sum()
print(f"closing NBV from the roll-forward {roll.loc['Total', 'Closing NBV']:.2f}; "
      f"sum of assets still held {check:.2f}")

The last line is the check that matters: the roll-forward's closing net book value must equal the sum of the book values of the assets still held. If they differ, an asset is being counted twice or not at all.

How the roll-forward moves Opening net book value plus additions, less disposals at book value, less the year's depreciation charge, equals closing net book value, which must match the sum of the assets still held. Opening NBV 1 Jan 2026 + Additions at cost − Disposals at book value − Charge this year Closing NBV = assets held

Step 5 — Check against Excel's SLN and VDB

Excel has the same calculations built in: SLN(cost, salvage, life) for the straight-line charge and VDB(cost, salvage, life, start, end, factor) for the declining balance total between two periods, switching to straight line by default. Using months as the periods, VDB(cost, salvage, 60, 0, k, 2) is the accumulated depreciation after month k. Writing those formulas next to the Python numbers and recalculating with LibreOffice is a quick independent check:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["AssetID", "Months to Dec 2026", "Python accumulated", "Excel formula"])
for a in register.itertuples():
    months = life[(life["AssetID"] == a.AssetID) & (life["Month"] <= YEAR_END)]
    k = len(months)
    python_acc = round(months["Depreciation"].sum(), 2)
    if a.Method == "DDB":
        formula = f"=VDB({a.Cost},{a.Salvage},{a.LifeMonths},0,{k},2)"
    else:
        formula = f"=SLN({a.Cost},{a.Salvage},{a.LifeMonths})*{k}"
    ws.append([a.AssetID, k, python_acc, formula])
wb.save("depreciation_check.xlsx")
Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated depreciation_check.xlsx > /dev/null 2>&1
ls recalculated
Python
from openpyxl import load_workbook

rows = list(load_workbook("recalculated/depreciation_check.xlsx", data_only=True).active.iter_rows(min_row=2, values_only=True))
for asset_id, k, py, xl in rows:
    print(f"{asset_id}: {k:3} months  python {py:10,.2f}  excel {xl:10,.2f}  diff {abs(py - xl):.4f}")

Every asset agrees to the cent (the small differences are the Python totals' rounding to two decimals). Straight line agrees trivially; the declining balance assets agree too, which confirms the switch rule matches Excel's VDB with its default switch. (Excel's DDB function does not switch to straight line, so its totals never quite reach the salvage value — use VDB when comparing.)

Writing the report

The workbook has three sheets: the monthly schedule, the roll-forward, and the register with book values and disposal results. The pattern is the same as in Build a Budget vs Actual Variance Report: numbers written as values from pandas, totals as SUM formulas, a number format with thousands separators and negatives in brackets:

Python
with pd.ExcelWriter("fixed_assets_fy2026.xlsx", engine="xlsxwriter") as xw:
    schedule.round(2).to_excel(xw, sheet_name="Schedule")
    roll.round(2).to_excel(xw, sheet_name="Roll-forward")
    reg[["Description", "Category", "InService", "Cost", "Method", "LifeMonths", "AccumDep", "BookValue", "Disposed"]] \
        .round({"AccumDep": 2, "BookValue": 2}).to_excel(xw, sheet_name="Register")
    fmt = xw.book.add_format({"num_format": "#,##0.00;(#,##0.00)"})
    for name in ("Schedule", "Roll-forward"):
        xw.sheets[name].set_column(1, 14, 12, fmt)
        xw.sheets[name].set_column(0, 0, 14)
print("wrote fixed_assets_fy2026.xlsx")

Common pitfalls

  • Computing only the current year for declining balance. The charge depends on every previous period; compute from the in-service date.
  • Using Excel's DDB for comparison. It does not switch to straight line; VDB does.
  • Depreciating below salvage. Cap each charge at book value minus salvage.
  • Forgetting disposals in the roll-forward. Remove both the cost and the accumulated depreciation of sold assets, or the closing figures carry assets that are gone.
  • An undocumented convention. Full-month, mid-month and half-year conventions all give different numbers; state which one the schedule uses.

Conclusion

Compute each asset's depreciation for its whole life month by month — straight line, or double-declining balance with the switch to straight line and a salvage floor — then keep the year being reported. Pivot to a monthly schedule, take book values from the last month to date, calculate gains and losses on disposals, and build a roll-forward whose closing net book value matches the assets still held. Check the totals once against Excel's SLN and VDB to confirm the rules match.

Frequently asked questions

How do I calculate double-declining balance depreciation in Python? Each period, charge 2 divided by the life in periods times the current book value, but no less than the straight-line charge over the remaining periods and no more than book value minus salvage. Compute from the start of the asset's life, not just the current year.

Which Excel function matches declining balance with a switch to straight line? VDB. With the factor set to 2 and no_switch left as FALSE, VDB switches to straight line when that gives a larger charge, which is the usual accounting treatment. DDB does not switch.

How are disposals handled in a depreciation schedule? Depreciate up to the disposal month, then remove the asset's cost and accumulated depreciation. The gain or loss is the proceeds minus the book value at disposal.

Should depreciation start in the month of purchase? It depends on the policy: the month after, the month of purchase, or half a month are all used. Pick one, apply it to every asset and state it on the report.

More on report layouts & templates

More on report layouts & templates →