Build a Depreciation Schedule in Excel: Fixed Asset Register, NBV, and the Monthly Journal

How-to · Updated July 2026 · ~13 min read

The auditor asks for the fixed asset register. You send the workbook, and the accumulated depreciation on it is 1,240 out from the general ledger — not because of one big mistake, but because a van was sold in April and the row was deleted, a laptop was added in June with no in-service date, and two assets have quietly kept depreciating past the end of their useful life because nothing in the sheet ever told them to stop.

A depreciation schedule is not hard Excel. It is a schedule that has to survive twelve months of additions, disposals and someone else's edits and still tie to three GL accounts. This post builds one: the net book value equation, the register layout, the per-period formulas for straight line and declining balance, a walk-down that terminates cleanly, disposals with gain or loss, and the monthly journal entry that falls out of it.

Net book value: the equation and what it is not

NBV = Cost − Accumulated depreciation

That is the whole equation. With cost in column E and accumulated depreciation in column L, it is one cell: =E2-L2.

One extension matters. Where an asset has been written down, NBV is cost less accumulated depreciation less accumulated impairment, and impairment gets its own column rather than being folded into accumulated depreciation — the two move differently and the disclosure note splits them.

What NBV is not:

NBV is an allocation of past cost across periods, not a valuation. A five-year-old delivery van sitting at 3,000 NBV may be worth 7,000 or 900, and neither number changes the schedule. That is precisely why a gain or loss appears on disposal: the market answers a question the schedule never asked. The total of the NBV column is the net fixed assets line on your balance sheet.

The fixed asset register layout

One row per asset, sixteen columns, no merged cells, no blank rows. Everything downstream — the schedule, the journal, the disclosure note — is a SUMIFS against this table, and every one of them breaks on a merged cell.

Four conventions to fix before you type a formula, because changing them later means restating:

1. In-service date, not invoice date. Depreciation starts when the asset is available for use — a machine that sat in a crate for six weeks starts when it was commissioned.
2. Full month in the month of acquisition. An asset in service on 28 March takes a full March charge. Half-month and pro-rata-by-days are equally defensible; pick one and apply it to every asset.
3. Charge in the month of disposal, none after. Again, a policy, not a rule.
4. This is the book register. Tax depreciation — capital allowances, MACRS — runs on different lives and conventions and belongs in a separate schedule. Making one register serve both is how deferred tax gets lost.

Put the period end date in B1 and refer to it as $B$1 everywhere, so one cell change rolls the schedule forward. With sixteen columns you will also lose track of which asset you are on, so freeze the header row and the ID column: cursor in B2, View > Freeze Panes > Freeze Panes (how freeze panes actually anchors).

The per-period formulas: SLN, DDB, SYD and VDB

Excel has four depreciation functions worth knowing, and each returns the charge for one period — the period unit is whatever unit you express life in. Pass life in months and you get a monthly charge, which is what a monthly close needs.

For the van — cost 30,000, residual 3,000, five years:

Annual straight line:   =SLN(30000,3000,5)        →  5,400.00
Monthly straight line:  =SLN(30000,3000,5*12)     →    450.00
Month 1 double-declining: =DDB(30000,3000,5*12,1,2) →  1,000.00
Month 1 sum-of-digits:  =SYD(30000,3000,5*12,1)   →    885.25

Two traps in that list.

DB is not DDB. DB derives its own rate as 1 − (salvage/cost)^(1/life) rounded to three decimals — 36.9% for the van. With a residual value of zero that rate becomes 100%, and DB writes the whole asset off in period one. DDB has no such failure mode. Unless you specifically need DB's rounded-rate behaviour, use DDB.

DDB has a floor but a long tail. It never takes book value below salvage, but with a nil residual it approaches zero without arriving: 30,000 over five years leaves 2,332.80 unrelieved at the end of year five. VDB fixes that by switching to straight line on the remaining book value once that gives the larger charge — =VDB(30000,0,5,3,4) returns 3,240.00 where =DDB(30000,0,5,4) returns 2,592.00, and VDB lands on nil on schedule. The alternative, and what most book policies do, is to plug the final period, which the register formula below handles.

The charge formula that stops on its own

This is the column that separates a schedule from a spreadsheet. Column K, filled down the register:

=IF(OR($I2<1, AND($N2<>"", EOMONTH($N2,0)<$B$1)), 0,
   IF($I2>=$G2*12,
      MAX(0, $E2-$F2-$J2),
      MIN(IFS($H2="SL",  SLN($E2,$F2,$G2*12),
              $H2="DDB", DDB($E2,$F2,$G2*12,$I2,2),
              $H2="SYD", SYD($E2,$F2,$G2*12,$I2)),
          MAX(0, $E2-$F2-$J2))))

With the period number in column I:

=(YEAR($B$1)-YEAR($D2))*12+MONTH($B$1)-MONTH($D2)+1

Read it in four clauses:

• $I2<1 — not in service yet at this period end, so nothing.
• EOMONTH($N2,0)<$B$1 — disposed of in an earlier month, so nothing. The month of disposal itself still charges.
• $I2>=$G2*12 — final scheduled month or later, so charge whatever depreciable base remains. That one clause mops up the rounding stub (a 4,999 asset over three years leaves 0.04 unrelieved if you simply repeat 138.86 thirty-six times) and forces DDB to land exactly on residual.
• MIN(charge, MAX(0, cost − residual − accumulated)) — the ordinary case, capped at the remaining base. This is what stops fully depreciated assets carrying on, and it is the clause most home-built registers are missing.

IFS needs Excel 2019 or Microsoft 365; nest three IFs on older builds. If the whole register is straight line — most small-company registers are — the middle block collapses to SLN($E2,$F2,$G2*12).

Then the roll-forward: L2 =J2+K2 and M2 =E2-L2. Next month, paste column L into column J as values, change B1, done. Save each month's register as its own copy — a register that overwrites itself has no audit trail.

A sample depreciation schedule, walked down

The van again, annual view, so you can total it by hand. Cost 30,000, residual 3,000, five years, straight line at 5,400 a year:

Total charge 27,000 = cost 30,000 less residual 3,000. Closing NBV in year five equals the residual, not zero — a schedule that runs to nil on an asset carrying a residual value is wrong, and that is a five-second check on anyone's workbook.

The same asset on DDB front-loads it: 12,000 / 7,200 / 4,320 / 2,592, then 888 in year five rather than the arithmetic 1,555.20, because DDB caps the charge so book value cannot fall below the 3,000 residual. Same 27,000 total, different shape.

To spread this across every asset at once, copy the register's asset columns onto a second sheet — in-service date in D, cost in E, residual in F, life in years in G — put the accumulated brought-forward figure in column I, month-end dates across row 4 from column J onward, and fill one formula across and down from J5:

=IF(J$4<EOMONTH($D5,0), 0, MIN(SLN($E5,$F5,$G5*12), MAX(0, $E5-$F5-SUM($I5:I5))))

The expanding reference SUM($I5:I5) is the trick: anchored on the left, open on the right, so as it fills rightwards it accumulates every month already charged. Fill it across 60 columns and each asset stops itself at the right month with no manual edit. That version is straight line throughout; if the register mixes methods, drop the IFS block from the charge formula above in place of SLN($E5,$F5,$G5*12) and point its period argument at the column count rather than column I.

Disposals: gain, loss, and the row you must not delete

Gain or (loss) = Proceeds − NBV at the disposal date, which in column P is:

=IF($N2="","",$O2-($E2-$L2))

Because column K stops charging after the disposal month, column L freezes at the accumulated figure as at disposal, so this stays correct for the rest of the year. Sell the van in year four for 9,500 when accumulated is 21,600: NBV 8,400, gain 1,100. Scrap it for nothing and it is an 8,400 loss.

Do not delete the row. A disposed asset stays in the register with its disposal date and proceeds filled in — that is the evidence for the disposal journal, and next year's opening reconciliation needs it. Instead, exclude it from the balance sheet totals by date, so an asset disposed of after the period end still counts as held:

Cost held at period end:  =SUMIFS($E$2:$E$500,$N$2:$N$500,"")
                          +SUMIFS($E$2:$E$500,$N$2:$N$500,">"&$B$1)

The same pair over column L gives accumulated depreciation, and over column M gives NBV — the three figures that have to agree with the ledger.

The disposal journal, for the van at 9,500:

The credit to cost is the full original cost, not the NBV, and the debit is the full accumulated depreciation. Posting only the NBV against the cost account is the commonest disposal error, and it leaves an orphan in accumulated depreciation that nobody can explain three years later.

The monthly depreciation journal

One entry a month, built from column K by class so it posts to the same accounts every time. On a Journal sheet with the class in column A:

=SUMIFS(Register!$K$2:$K$500, Register!$C$2:$C$500, $A2)

Two checks below the entry, both visible without scrolling:

Balanced:  =IF(ROUND(SUM(C2:C20)-SUM(D2:D20),2)=0,"OK - balanced",
              "OUT BY "&TEXT(SUM(C2:C20)-SUM(D2:D20),"#,##0.00"))
Agrees:    =IF(ROUND(SUM(D2:D20)-SUM(Register!$K$2:$K$500),2)=0,"OK",
              "JE does not agree to register")

The second is the useful one: it catches a new asset class added to the register with no matching journal line, which otherwise understates the charge quietly for months. The debit side hits the depreciation line of the profit and loss statement; the credit is a contra-asset netting against cost on the balance sheet. If the entry goes into QuickBooks Online rather than being keyed by hand, the layout above is already close to the import format — see importing journal entries into QuickBooks Online from Excel for the column names and the gotchas.

How HISAB 360 helps

The register above is forty minutes of work the first time and five minutes a month after — the cost is that every one of those formulas has to be right, and nothing in Excel tells you when one is not. HISAB 360 is a paid Excel add-in with an AI chat panel docked inside the workbook. Describe the register — "columns for cost, in-service date, residual, life and method, a monthly straight-line charge that stops at residual, and an NBV column" — and it writes the columns and formulas into the live sheet rather than handing you text to paste, including the DDB variant and the disposal gain calculation.

The reconciliation half is where it earns its place. It connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting, so it can pull the fixed asset cost, accumulated depreciation and depreciation expense account activity into a sheet beside your register and match them line by line, with fuzzy matching where the asset names differ. Those connectors also write, so the monthly journal built from column K can be created directly in the ERP rather than retyped. Asset invoices that arrive as PDFs can be extracted into table form for the additions log.

Honest limits: Windows desktop Excel only — Microsoft 365 or 2016+, 64-bit, not Mac and not Excel on the web — and it is paid, with a 15-day trial that includes 50 AI credits and needs no card. Every generated formula and every posted journal deserves the review you would give a junior's work.

Tying out at month end

Four figures, checked every month:

The first two drift, and for predictable reasons: an addition posted to the GL that nobody added to the register, an asset expensed below the capitalisation threshold but registered anyway, a disposal journalled but not dated in column N. Reconcile the movement, not just the balance — opening cost, plus additions, less disposals, equals closing cost — because a balance that agrees by coincidence is not a reconciliation.

Update the register before you run depreciation: capitalise the month's additions, set their in-service dates, then change B1 and let the schedule calculate. The other order means catch-up charges, and catch-up charges are where prior-period errors hide. Depreciation belongs on the close calendar as a dated, owned task alongside accruals and prepayments — the month-end close checklist has the sequencing.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.