Depreciation and Fixed Asset Tie-Out
Fixed assets accumulate errors quietly because the schedule is rarely rebuilt — it is rolled
forward, year after year, often through a change of software or preparer. Each roll carries
forward whatever was already wrong.
Four failures account for most of what this tie-out finds:
- A disposed asset still depreciating. The asset was sold, the gain was recorded, and the
register was never updated. Depreciation continues on something the client no longer owns.
- Beginning accumulated depreciation that does not agree to the prior-year return. Almost
always introduced by a software conversion, and it silently misstates every subsequent year.
- An asset depreciated past its cost. Arithmetically impossible and surprisingly common,
usually from a life change applied retroactively.
- Additions never added. Capital expenditure expensed or sitting in construction in
progress, so depreciation is understated and the balance sheet is wrong.
None of these show up by reading the depreciation expense figure. They show up in a rollforward,
which is why this skill starts there.
The gate
No clean workpaper unless all of these hold:
- The cost rollforward foots. Beginning cost + additions − disposals = ending cost, in total
and by asset class.
- The accumulated depreciation rollforward foots. Beginning accumulated + current-year
expense − accumulated on disposals = ending accumulated.
- Beginning balances equal prior-year ending balances. Supplied from the prior-year return
or financial statements — not from the software's current state of the file, which is what
introduced the error in the first place.
- Register totals agree to the return or trial balance for cost, accumulated depreciation,
and current-year depreciation expense.
- No asset has accumulated depreciation exceeding its depreciable basis.
Standing rule on tax figures
Do not state a recovery period, MACRS percentage, bonus depreciation rate, Section 179 limit,
threshold, or phase-out from memory. These change, and several have changed more than once
recently.
What the script does instead:
- Recomputes straight-line depreciation exactly where the schedule states a straight-line
method, using the cost, salvage, life, and convention on the schedule. This is arithmetic and
is checked precisely.
- For MACRS and other accelerated methods, tests internal consistency only — that
accumulated depreciation does not exceed basis, that prior accumulated plus current equals
ending, that the asset is in service, that the life and class are populated and consistent
across years — and flags each asset for verification of the rate against current
instructions. It does not reproduce percentage tables.
- Flags every asset whose life or method changed from the prior year, because that is either
an error or a method change requiring proper procedure rather than a schedule edit.
Where a figure must be confirmed, the workpaper says "verify recovery period and convention for
[asset class] placed in service [date] against current instructions for Form 4562." That is more
useful than a number recalled from an earlier year, and it cannot go stale.
Inputs
- Fixed asset register, current year, per asset: description, class, acquisition date, in-service
date, cost, salvage, method, life, convention, prior accumulated depreciation, current-year
depreciation, ending accumulated, disposal date and proceeds where applicable.
- Prior-year ending balances — total cost and total accumulated depreciation, by class, per
the prior-year return or financial statements as filed.
- The return or trial balance figures to tie to: cost, accumulated depreciation, current-year
depreciation expense. Form 4562 totals if available.
- Book and tax registers separately if the client maintains both. Do not reconcile a book
register to a tax return — the difference is a deferred tax item, not an error, and mixing
them produces a meaningless variance.
Step 1 — Roll forward and tie
python3 scripts/depr_tieout.py \
--register fa_register.csv \
--prior-year prior_balances.csv \
--tb-cost 4820115.00 --tb-accum 2140880.00 --tb-depreciation 412655.00 \
--basis tax --year 2025 \
--client "Kestrel Fabrication LLC" \
--out "Kestrel - 2025 Fixed Asset Tie-Out.xlsx"
Seven tests run:
- Test 1 — Cost rollforward foots, in total and by class
- Test 2 — Accumulated depreciation rollforward foots
- Test 3 — Beginning balances agree to prior-year ending
- Test 4 — Register totals agree to the trial balance or return
- Test 5 — No asset over-depreciated; accumulated ≤ depreciable basis for every asset
- Test 6 — Straight-line assets recomputed and agreed
- Test 7 — Asset-level integrity (see below)
Step 2 — Asset-level review
Test 7 checks each asset for the conditions that indicate a stale or broken register:
- Disposed but still depreciating — a disposal date with current-year depreciation running
past it. The most common real finding.
- Disposed but not removed — a disposal date with cost still in ending balances.
- Gain or loss not computed — a disposal with proceeds but no gain or loss, or a gain
computed on the wrong accumulated depreciation figure. The script recomputes proceeds less net
book value.
- In service before acquisition, or an in-service date after year end.
- Fully depreciated but still held — legitimate, and it should remain on the register.
Depreciation still running on it is not legitimate.
- Zero or negative cost, or depreciation with no cost.
- Missing method, life, or convention — the schedule cannot be recomputed or reviewed, which
is itself a finding.
- Life or method changed from the prior year — flagged for every affected asset.
- An addition dated in a prior year appearing for the first time — either a late-recorded
addition needing prior-year consideration, or a duplicate.
- Duplicate assets — same description, cost, and in-service date under different asset IDs.
A double-recorded asset doubles depreciation and is invisible in totals.
Step 3 — Deliver
Workbook tabs:
- Tie-Out Summary — the seven tests with amounts and differences, the verdict, and the
figures requiring confirmation against current authority. The signable page.
- Rollforward — cost and accumulated depreciation, beginning through ending, in total and by
asset class, with the prior-year agreement shown.
- Asset Detail — every asset with recomputed depreciation, difference, net book value, and
flags.
- Exceptions — every asset-level finding with the asset, the condition, and the dollar
effect where quantifiable.
- Additions — current-year additions with in-service dates, method, life, and the note to
verify recovery period and any bonus or Section 179 election against current instructions.
- Disposals — each disposal with proceeds, cost, accumulated depreciation removed, net book
value, and recomputed gain or loss, plus whether depreciation correctly stopped.
- Book vs Tax — where both registers are supplied, the difference by class, which is the
deferred tax input rather than an error.
Then, in chat: whether the rollforwards foot, whether beginning balances agree to the prior
year, the current-year depreciation figure and whether it ties, and the asset-level exceptions
worth acting on. Lead with any beginning-balance disagreement — it affects every year going
forward and is the hardest to unwind later.
What to escalate
- Beginning accumulated depreciation not agreeing to the prior-year return. Determine whether
the prior year, the current year, or both are wrong before proceeding. This can require an
amended return or a method-change filing, and it does not resolve itself.
- A retroactive life or method change made by editing the schedule. A change in depreciation
method or life generally requires a prescribed procedure, not a spreadsheet edit. Report what
changed and route the treatment question to the signer.
- Assets on the register that no longer physically exist. If no physical inventory has been
performed in years, say so — the register is unverified as to existence, which is a scope point
as much as a control one.
- Construction in progress carried for multiple years without being placed in service.
Depreciation may be understated and the classification may be wrong.
- Repairs and maintenance containing capital items, or additions that look like repairs. Worth
scanning the expense account; this is a frequent examination adjustment in both directions.
- Bonus or Section 179 elections that appear inconsistent across assets placed in service in
the same year, or state treatment applied without regard to state decoupling — many states do
not conform, and each state has to be tested separately.
Security posture
Fully local: standard library plus openpyxl. No network calls, no uploads, no telemetry. The
register is read-only. Output filenames carry client name and year only.
Dependencies
pip install openpyxl
Built by Adopt AI — free to use and modify.
Appendix — bundled files
If you installed the .skill package these files are already in place and you can ignore this appendix. If you copied the skill as text, create the files below at the paths shown, alongside your SKILL.md. The skill will not run without them.
scripts/depr_tieout.py
#!/usr/bin/env python3
"""
Fixed asset register tie-out: rollforwards, prior-year agreement, and asset-level
integrity.
Seven tests. No clean workpaper unless the rollforwards foot, beginning balances
agree to the prior year as filed, the register agrees to the trial balance or
return, and no asset is depreciated beyond its basis.
Asserts NO recovery periods, MACRS percentages, bonus rates, or Section 179
limits. Straight-line assets are recomputed exactly from the cost, salvage, life
and convention ON THE SCHEDULE. Accelerated methods are tested for internal
consistency only and flagged for rate verification against current instructions.
Usage:
python3 depr_tieout.py --register fa_register.csv \
--prior-year prior_balances.csv \
--tb-cost 4820115.00 --tb-accum 2140880.00 --tb-depreciation 412655.00 \
--basis tax --year 2025 --client "Kestrel Fabrication LLC" \
--out "Kestrel - 2025 Fixed Asset Tie-Out.xlsx"
--register CSV:
asset_id, description, asset_class, acquisition_date, in_service_date,
cost, salvage, method, life_years, convention, prior_accum, current_depr,
ending_accum, disposal_date, proceeds, gain_loss,
prior_method (optional), prior_life_years (optional)
method: SL / straight-line recomputed exactly. MACRS, DB, DDB, SYD and others
tested for consistency only.
--prior-year CSV (from the prior-year return or financial statements AS FILED):
asset_class, ending_cost, ending_accum
(a row with asset_class "TOTAL" is accepted for totals only)
"""
from __future__ import annotations
import argparse
import csv
import sys
from collections import defaultdict
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
try:
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
except ImportError:
sys.exit("openpyxl is required. pip install openpyxl")
ZERO = Decimal("0.00")
MONEY = '#,##0.00;[Red](#,##0.00)'
DATEF = "yyyy-mm-dd"
HDR_FILL = PatternFill("solid", fgColor="1F3864")
HDR_FONT = Font(bold=True, color="FFFFFF")
OK_FONT = Font(bold=True, color="006100")
BAD_FONT = Font(bold=True, color="9C0006")
BAD_FILL = PatternFill("solid", fgColor="FFC7CE")
WARN_FILL = PatternFill("solid", fgColor="FFEB9C")
SUB_FILL = PatternFill("solid", fgColor="D9E2F3")
TOP = Border(top=Side(style="thin"))
DBL = Border(top=Side(style="thin"), bottom=Side(style="double"))
SL_METHODS = {"sl", "s/l", "straight-line", "straight line", "straightline"}
HALF_YEAR = {"hy", "half-year", "half year", "mq", "mid-quarter", "mid quarter",
"mm", "mid-month", "mid month"}
def dec(raw):
if raw is None:
return None
s = str(raw).strip()
if s in ("", "-", "--", "n/a", "N/A", "None"):
return None
neg = s.startswith("(") and s.endswith(")")
s = s.strip("()").replace(",", "").replace("$", "").strip()
if s.endswith("-"):
neg, s = True, s[:-1]
try:
v = Decimal(s)
except InvalidOperation:
raise ValueError(f"Unparseable amount: {raw!r}")
return -v if neg else v
def d0(raw):
v = dec(raw)
return v if v is not None else ZERO
def clean(s) -> str:
return " ".join(str(s or "").split())
def pdate(raw):
s = clean(raw)
if not s:
return None
for f in ("%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y", "%d-%b-%Y", "%Y/%m/%d"):
try:
return datetime.strptime(s.split(" ")[0], f).date()
except ValueError:
continue
raise ValueError(f"Unrecognized date {raw!r}. Prefer ISO YYYY-MM-DD.")
def load_register(path: Path) -> list[dict]:
with path.open(newline="", encoding="utf-8-sig") as fh:
rows = list(csv.DictReader(fh))
if not rows:
sys.exit(f"No assets in {path}")
out = []
for i, r in enumerate(rows, start=1):
a = {
"row": i,
"asset_id": clean(r.get("asset_id")) or f"FA{i:05d}",
"description": clean(r.get("description")),
"asset_class": clean(r.get("asset_class")) or "(unclassified)",
"acquisition_date": pdate(r.get("acquisition_date")),
"in_service_date": pdate(r.get("in_service_date")),
"cost": d0(r.get("cost")),
"salvage": d0(r.get("salvage")),
"method": clean(r.get("method")),
"life_years": dec(r.get("life_years")),
"convention": clean(r.get("convention")),
"prior_accum": d0(r.get("prior_accum")),
"current_depr": d0(r.get("current_depr")),
"ending_accum": dec(r.get("ending_accum")),
"disposal_date": pdate(r.get("disposal_date")),
"proceeds": dec(r.get("proceeds")),
"gain_loss": dec(r.get("gain_loss")),
"prior_method": clean(r.get("prior_method")),
"prior_life_years": dec(r.get("prior_life_years")),
"flags": [], "recomputed": None, "recomp_diff": None,
}
if a["ending_accum"] is None:
a["ending_accum"] = a["prior_accum"] + a["current_depr"]
out.append(a)
return out
def load_prior(path: Path | None) -> dict:
out = {"by_class": {}, "total_cost": None, "total_accum": None}
if not path or not path.exists():
return out
with path.open(newline="", encoding="utf-8-sig") as fh:
for r in csv.DictReader(fh):
cls = clean(r.get("asset_class"))
c, ac = dec(r.get("ending_cost")), dec(r.get("ending_accum"))
if cls.upper() == "TOTAL":
out["total_cost"], out["total_accum"] = c, ac
elif cls:
out["by_class"][cls] = {"cost": c or ZERO, "accum": ac or ZERO}
if out["total_cost"] is None and out["by_class"]:
out["total_cost"] = sum((v["cost"] for v in out["by_class"].values()), ZERO)
out["total_accum"] = sum((v["accum"] for v in out["by_class"].values()), ZERO)
return out
# ------------------------------------------------------------------ recompute
def recompute_sl(a: dict, year_end: date) -> Decimal | None:
"""Exact straight-line recomputation from the schedule's own inputs."""
if a["method"].lower() not in SL_METHODS:
return None
if not a["life_years"] or a["life_years"] <= ZERO:
return None
basis = a["cost"] - a["salvage"]
if basis <= ZERO:
return ZERO
annual = basis / a["life_years"]
isd = a["in_service_date"]
first_year = bool(isd and isd.year == year_end.year)
factor = Decimal(1)
if first_year:
conv = a["convention"].lower()
if conv in HALF_YEAR or not conv:
factor = Decimal("0.5")
else:
months = 12 - isd.month + 1
factor = Decimal(months) / Decimal(12)
remaining = basis - a["prior_accum"]
if remaining <= ZERO:
return ZERO
expected = annual * factor
if expected > remaining:
expected = remaining
if a["disposal_date"] and a["disposal_date"].year == year_end.year:
# disposal-year convention varies; report rather than assert
return None
return expected.quantize(Decimal("0.01"))
def asset_flags(assets: list[dict], year: int, year_end: date) -> list[str]:
problems = []
dupes = defaultdict(list)
for a in assets:
f = a["flags"]
basis = a["cost"] - a["salvage"]
if a["ending_accum"] > basis and basis > ZERO:
f.append(f"OVER-DEPRECIATED: accumulated {a['ending_accum']:,.2f} exceeds "
f"depreciable basis {basis:,.2f} by "
f"{a['ending_accum'] - basis:,.2f} - arithmetically impossible, "
f"usually a life change applied retroactively")
expected_end = a["prior_accum"] + a["current_depr"]
if a["disposal_date"] is None and a["ending_accum"] != expected_end:
f.append(f"accumulated does not roll: prior {a['prior_accum']:,.2f} + current "
f"{a['current_depr']:,.2f} = {expected_end:,.2f}, but ending accum "
f"is {a['ending_accum']:,.2f}")
if a["disposal_date"]:
if a["disposal_date"].year < year and a["current_depr"] != ZERO:
f.append(f"DISPOSED {a['disposal_date']} in a prior year but still "
f"depreciating {a['current_depr']:,.2f} - depreciation is "
f"running on an asset the client no longer owns")
if a["disposal_date"].year < year and a["cost"] != ZERO:
f.append(f"disposed {a['disposal_date']} in a prior year but cost "
f"{a['cost']:,.2f} remains in the register")
nbv = a["cost"] - a["ending_accum"]
if a["proceeds"] is not None:
expected_gl = a["proceeds"] - nbv
if a["gain_loss"] is None:
f.append(f"disposal with proceeds {a['proceeds']:,.2f} but no gain or "
f"loss recorded - recomputed {expected_gl:,.2f}")
elif a["gain_loss"] != expected_gl:
f.append(f"gain/loss {a['gain_loss']:,.2f} does not agree to proceeds "
f"less net book value ({a['proceeds']:,.2f} - {nbv:,.2f} = "
f"{expected_gl:,.2f})")
if a["acquisition_date"] and a["in_service_date"] and \
a["in_service_date"] < a["acquisition_date"]:
f.append(f"in service {a['in_service_date']} before acquisition "
f"{a['acquisition_date']}")
if a["in_service_date"] and a["in_service_date"] > year_end:
f.append(f"in-service date {a['in_service_date']} is after year end "
f"{year_end} - not yet depreciable")
if a["cost"] <= ZERO and a["current_depr"] != ZERO:
f.append(f"depreciation {a['current_depr']:,.2f} with cost {a['cost']:,.2f}")
if a["cost"] < ZERO:
f.append(f"negative cost {a['cost']:,.2f}")
if basis > ZERO and a["prior_accum"] >= basis and a["current_depr"] > ZERO \
and not a["disposal_date"]:
f.append(f"fully depreciated at the start of the year but still taking "
f"{a['current_depr']:,.2f} of depreciation")
missing = [k for k in ("method", "convention") if not a[k]]
if not a["life_years"]:
missing.append("life_years")
if missing and a["cost"] > ZERO:
f.append(f"missing {', '.join(missing)} - the schedule cannot be recomputed "
f"or reviewed, which is itself a finding")
if a["prior_method"] and a["method"] and \
a["prior_method"].lower() != a["method"].lower():
f.append(f"METHOD CHANGED from '{a['prior_method']}' to '{a['method']}' - a "
f"method change generally requires a prescribed procedure, not a "
f"schedule edit. Route the treatment question to the signer.")
if a["prior_life_years"] and a["life_years"] and \
a["prior_life_years"] != a["life_years"]:
f.append(f"LIFE CHANGED from {a['prior_life_years']} to {a['life_years']} "
f"years - same consideration as a method change")
if a["in_service_date"] and a["in_service_date"].year < year and \
a["prior_accum"] == ZERO and a["cost"] > ZERO:
f.append(f"in service {a['in_service_date']} (a prior year) but zero prior "
f"accumulated depreciation - a late-recorded addition needing "
f"prior-year consideration, or a duplicate")
if a["recomputed"] is not None and a["recomp_diff"] not in (None, ZERO):
f.append(f"straight-line recomputation differs: schedule "
f"{a['current_depr']:,.2f} vs recomputed {a['recomputed']:,.2f} "
f"(difference {a['recomp_diff']:,.2f})")
dupes[(clean(a["description"]).lower(), a["cost"], a["in_service_date"])].append(a)
for msg in f:
problems.append(f"{a['asset_id']} {a['description'][:36]}: {msg}")
for key, group in dupes.items():
if len(group) > 1 and key[1] > ZERO and key[0]:
ids = ", ".join(x["asset_id"] for x in group)
for x in group:
x["flags"].append(f"possible DUPLICATE asset: same description, cost and "
f"in-service date as {ids} - a double-recorded asset "
f"doubles depreciation and is invisible in totals")
problems.append(f"Possible duplicate assets ({ids}): {group[0]['description']} "
f"at {key[1]:,.2f}")
return problems
# ---------------------------------------------------------------------- tests
def rollforward(assets: list[dict], prior: dict, year: int):
by_class = defaultdict(lambda: {
"begin_cost": ZERO, "additions": ZERO, "disposals": ZERO, "end_cost": ZERO,
"begin_accum": ZERO, "current": ZERO, "disp_accum": ZERO, "end_accum": ZERO})
for a in assets:
c = by_class[a["asset_class"]]
is_addition = a["in_service_date"] and a["in_service_date"].year == year
is_disposal = a["disposal_date"] and a["disposal_date"].year == year
if is_addition:
c["additions"] += a["cost"]
else:
c["begin_cost"] += a["cost"]
if is_disposal:
c["disposals"] += a["cost"]
c["disp_accum"] += a["ending_accum"]
else:
c["end_cost"] += a["cost"]
if not is_addition:
c["begin_accum"] += a["prior_accum"]
c["current"] += a["current_depr"]
if not is_disposal:
c["end_accum"] += a["ending_accum"]
rows = []
for cls, c in sorted(by_class.items()):
cost_computed = c["begin_cost"] + c["additions"] - c["disposals"]
accum_computed = c["begin_accum"] + c["current"] - c["disp_accum"]
rows.append({
"asset_class": cls, **c,
"cost_computed": cost_computed,
"cost_diff": cost_computed - c["end_cost"],
"accum_computed": accum_computed,
"accum_diff": accum_computed - c["end_accum"],
"prior_cost": (prior["by_class"].get(cls) or {}).get("cost"),
"prior_accum_bal": (prior["by_class"].get(cls) or {}).get("accum"),
})
return rows
def totals_of(rows, key):
return sum((r[key] for r in rows), ZERO)
# -------------------------------------------------------------------- workbook
def hdr(ws, n, row=1):
for c in range(1, n + 1):
cell = ws.cell(row=row, column=c)
cell.fill, cell.font = HDR_FILL, HDR_FONT
cell.alignment = Alignment(vertical="center", wrap_text=True)
ws.row_dimensions[row].height = 30
def widths(ws, w):
for c, v in w.items():
ws.column_dimensions[get_column_letter(c)].width = v
def sheet_summary(wb, meta, tests, overall, escalations, verify_notes):
ws = wb.active
ws.title = "Tie-Out Summary"
widths(ws, {1: 4, 2: 52, 3: 18, 4: 18, 5: 62})
r = 1
def line(label, a=None, b=None, *, bold=False, size=11, fill=None, note="",
border=None):
nonlocal r
c = ws.cell(row=r, column=2, value=label)
c.font = Font(bold=bold, size=size)
c.alignment = Alignment(wrap_text=True, vertical="top")
if fill:
c.fill = fill
for col, val in ((3, a), (4, b)):
if val is None:
continue
cc = ws.cell(row=r, column=col,
value=float(val) if isinstance(val, Decimal) else val)
if isinstance(val, Decimal):
cc.number_format = MONEY
cc.font = Font(bold=bold)
if border:
cc.border = border
if note:
n = ws.cell(row=r, column=5, value=note)
n.font = Font(italic=True, size=9)
n.alignment = Alignment(wrap_text=True, vertical="top")
r += 1
line("FIXED ASSET AND DEPRECIATION TIE-OUT", bold=True, size=14)
r += 1
for k, v in meta.items():
ws.cell(row=r, column=2, value=k).font = Font(bold=True)
ws.cell(row=r, column=3, value=v)
r += 1
r += 1
v = ws.cell(row=r, column=2,
value="TIED OUT - rollforwards foot, beginning balances agree, register "
"agrees to the return"
if overall else
"NOT TIED OUT - see the failing test(s) below")
v.font = OK_FONT if overall else BAD_FONT
if not overall:
v.fill = BAD_FILL
r += 2
for t in tests:
line(f"TEST {t['num']} - {t['name']}", bold=True, size=12, fill=SUB_FILL)
if t.get("skipped"):
line(" " + t["note"], fill=WARN_FILL)
r += 1
continue
counts = set(t.get("counts", []))
for label, val in t["lines"]:
line(" " + label, int(val) if label in counts else val)
line(" Assets flagged" if t.get("is_count") else " Difference",
int(t["difference"]) if t.get("is_count") else t["difference"],
bold=True, border=TOP, note=t.get("note", ""))
c = ws.cell(row=r - 1, column=3)
c.font = OK_FONT if t["passed"] else BAD_FONT
if not t["passed"]:
c.fill = BAD_FILL
r += 1
if escalations:
line("ESCALATE", bold=True, size=12, fill=BAD_FILL)
for e in escalations:
line(" " + e)
r += 1
line("FIGURES REQUIRING CONFIRMATION AGAINST CURRENT AUTHORITY", bold=True, size=12,
fill=WARN_FILL)
for n in verify_notes:
line(" " + n)
r += 1
line("This workpaper asserts no recovery period, MACRS percentage, bonus rate, or "
"Section 179 limit. Straight-line assets are recomputed exactly from the "
"schedule's own inputs; accelerated methods are tested for internal "
"consistency only.", bold=True)
r += 2
line("Prepared by / date: __________________ ____________", bold=True)
line("Reviewed by / date: __________________ ____________", bold=True)
def sheet_rollforward(wb, rows, prior):
ws = wb.create_sheet("Rollforward")
heads = ["Asset class", "Begin cost", "Additions", "Disposals", "Computed end cost",
"End cost per register", "Cost diff", "PY end cost", "PY cost diff",
"Begin accum", "Current depr", "Accum on disposals", "Computed end accum",
"End accum per register", "Accum diff", "PY end accum", "PY accum diff"]
ws.append(heads)
hdr(ws, len(heads))
for x in rows:
pcd = (x["begin_cost"] - x["prior_cost"]) if x["prior_cost"] is not None else None
pad = (x["begin_accum"] - x["prior_accum_bal"]) if x["prior_accum_bal"] is not None else None
ws.append([x["asset_class"], float(x["begin_cost"]), float(x["additions"]),
float(x["disposals"]), float(x["cost_computed"]),
float(x["end_cost"]), float(x["cost_diff"]),
float(x["prior_cost"]) if x["prior_cost"] is not None else None,
float(pcd) if pcd is not None else None,
float(x["begin_accum"]), float(x["current"]),
float(x["disp_accum"]), float(x["accum_computed"]),
float(x["end_accum"]), float(x["accum_diff"]),
float(x["prior_accum_bal"]) if x["prior_accum_bal"] is not None else None,
float(pad) if pad is not None else None])
last = ws.max_row
ws.append(["TOTAL"] + [f"=SUM({get_column_letter(c)}2:{get_column_letter(c)}{last})"
for c in range(2, len(heads) + 1)])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i in range(1, len(heads)):
row[i].number_format = MONEY
for i in (6, 8, 14, 16):
if isinstance(row[i].value, (int, float)) and row[i].value:
row[i].font, row[i].fill = BAD_FONT, BAD_FILL
for c in range(1, len(heads) + 1):
cell = ws.cell(row=ws.max_row, column=c)
cell.font, cell.border = Font(bold=True), TOP
ws.freeze_panes = "B2"
widths(ws, {i: 16 for i in range(1, len(heads) + 1)} | {1: 26})
def sheet_assets(wb, assets):
ws = wb.create_sheet("Asset Detail")
heads = ["Asset ID", "Description", "Class", "Acquired", "In service", "Cost",
"Salvage", "Method", "Life", "Conv", "Prior accum", "Current depr",
"Recomputed (SL only)", "Diff", "End accum", "NBV", "Disposed",
"Proceeds", "Gain/loss", "Flags"]
ws.append(heads)
hdr(ws, len(heads))
for a in assets:
ws.append([a["asset_id"], a["description"], a["asset_class"],
a["acquisition_date"], a["in_service_date"], float(a["cost"]),
float(a["salvage"]), a["method"] or None,
float(a["life_years"]) if a["life_years"] else None,
a["convention"] or None, float(a["prior_accum"]),
float(a["current_depr"]),
float(a["recomputed"]) if a["recomputed"] is not None else None,
float(a["recomp_diff"]) if a["recomp_diff"] is not None else None,
float(a["ending_accum"]),
float(a["cost"] - a["ending_accum"]),
a["disposal_date"],
float(a["proceeds"]) if a["proceeds"] is not None else None,
float(a["gain_loss"]) if a["gain_loss"] is not None else None,
"; ".join(a["flags"])])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i in (3, 4, 16):
row[i].number_format = DATEF
for i in (5, 6, 10, 11, 12, 13, 14, 15, 17, 18):
row[i].number_format = MONEY
if row[13].value not in (None, 0):
row[13].font, row[13].fill = BAD_FONT, BAD_FILL
if row[19].value:
row[19].fill = BAD_FILL
if isinstance(row[15].value, (int, float)) and row[15].value < 0:
row[15].font, row[15].fill = BAD_FONT, BAD_FILL
ws.freeze_panes = "C2"
ws.auto_filter.ref = f"A1:T{max(ws.max_row, 2)}"
widths(ws, {1: 12, 2: 34, 3: 20, 4: 12, 5: 12, 6: 14, 7: 11, 8: 10, 9: 7,
10: 7, 11: 14, 12: 14, 13: 18, 14: 12, 15: 14, 16: 14, 17: 12,
18: 13, 19: 13, 20: 80})
def sheet_list(wb, title, rows, heads, fmt_map, empty_msg, footer=""):
ws = wb.create_sheet(title)
ws.append(heads)
hdr(ws, len(heads))
for r in rows:
ws.append(r)
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i, f in fmt_map.items():
if i < len(row):
row[i].number_format = f
if ws.max_row == 1:
ws.cell(row=2, column=1, value=empty_msg)
elif footer:
rr = ws.max_row + 2
c = ws.cell(row=rr, column=1, value=footer)
c.alignment = Alignment(wrap_text=True, vertical="top")
c.font = Font(italic=True)
ws.freeze_panes = "A2"
widths(ws, {i: 18 for i in range(1, len(heads) + 1)} | {2: 34, len(heads): 70})
# ------------------------------------------------------------------------ main
def main() -> int:
ap = argparse.ArgumentParser(description=__doc__)
ap.add_argument("--register", required=True)
ap.add_argument("--out", required=True)
ap.add_argument("--prior-year")
ap.add_argument("--tb-cost")
ap.add_argument("--tb-accum")
ap.add_argument("--tb-depreciation")
ap.add_argument("--basis", choices=["tax", "book"], default="tax")
ap.add_argument("--year", type=int, required=True)
ap.add_argument("--year-end", help="YYYY-MM-DD, defaults to Dec 31 of --year")
ap.add_argument("--client", default="")
ap.add_argument("--force", action="store_true")
args = ap.parse_args()
year_end = pdate(args.year_end) if args.year_end else date(args.year, 12, 31)
assets = load_register(Path(args.register))
prior = load_prior(Path(args.prior_year) if args.prior_year else None)
for a in assets:
rc = recompute_sl(a, year_end)
a["recomputed"] = rc
a["recomp_diff"] = (a["current_depr"] - rc) if rc is not None else None
problems = asset_flags(assets, args.year, year_end)
rows = rollforward(assets, prior, args.year)
tot = {k: totals_of(rows, k) for k in
("begin_cost", "additions", "disposals", "end_cost", "cost_computed",
"begin_accum", "current", "disp_accum", "end_accum", "accum_computed")}
tb_cost, tb_accum, tb_depr = dec(args.tb_cost), dec(args.tb_accum), dec(args.tb_depreciation)
tests = []
tests.append({"num": 1, "name": "Cost rollforward foots",
"lines": [("Beginning cost", tot["begin_cost"]),
("Add: additions", tot["additions"]),
("Less: disposals", -tot["disposals"]),
("= Computed ending cost", tot["cost_computed"]),
("Ending cost per register", tot["end_cost"])],
"difference": tot["cost_computed"] - tot["end_cost"],
"passed": tot["cost_computed"] == tot["end_cost"]})
tests.append({"num": 2, "name": "Accumulated depreciation rollforward foots",
"lines": [("Beginning accumulated", tot["begin_accum"]),
("Add: current-year depreciation", tot["current"]),
("Less: accumulated on disposals", -tot["disp_accum"]),
("= Computed ending accumulated", tot["accum_computed"]),
("Ending accumulated per register", tot["end_accum"])],
"difference": tot["accum_computed"] - tot["end_accum"],
"passed": tot["accum_computed"] == tot["end_accum"]})
if prior["total_cost"] is not None:
dc = tot["begin_cost"] - prior["total_cost"]
da = tot["begin_accum"] - (prior["total_accum"] or ZERO)
tests.append({"num": 3,
"name": "Beginning balances agree to prior year as filed",
"lines": [("Beginning cost per register", tot["begin_cost"]),
("Prior-year ending cost as filed", prior["total_cost"]),
("Beginning accumulated per register", tot["begin_accum"]),
("Prior-year ending accumulated as filed",
prior["total_accum"] or ZERO)],
"difference": dc + da,
"passed": dc == ZERO and da == ZERO,
"note": "" if (dc == ZERO and da == ZERO) else
f"cost off by {dc:,.2f}, accumulated off by {da:,.2f}. "
f"Almost always a software conversion. This silently "
f"misstates every subsequent year and does not resolve "
f"itself."})
else:
tests.append({"num": 3, "name": "Beginning balances agree to prior year",
"skipped": True, "passed": True,
"note": "NOT PERFORMED - prior-year balances not supplied. This is "
"the test that catches a software conversion error, and it "
"is the hardest error to unwind later. Obtain the "
"prior-year return as filed.",
"lines": [], "difference": ZERO})
if tb_cost is not None or tb_accum is not None or tb_depr is not None:
lines, diff = [], ZERO
for label, reg, tb in (("Cost", tot["end_cost"], tb_cost),
("Accumulated depreciation", tot["end_accum"], tb_accum),
("Current-year depreciation", tot["current"], tb_depr)):
if tb is None:
continue
lines += [(f"{label} per register", reg), (f"{label} per return / TB", tb)]
diff += reg - tb
tests.append({"num": 4, "name": "Register agrees to the return or trial balance",
"lines": lines, "difference": diff, "passed": diff == ZERO})
else:
tests.append({"num": 4, "name": "Register agrees to the return or trial balance",
"skipped": True, "passed": True,
"note": "NOT PERFORMED - no return or trial balance figures supplied",
"lines": [], "difference": ZERO})
over = [a for a in assets
if (a["cost"] - a["salvage"]) > ZERO
and a["ending_accum"] > (a["cost"] - a["salvage"])]
tests.append({"num": 5, "name": "No asset depreciated beyond its basis",
"counts": ["Assets over-depreciated"],
"lines": [("Assets over-depreciated", Decimal(len(over))),
("Excess depreciation",
sum((a["ending_accum"] - (a["cost"] - a["salvage"])
for a in over), ZERO))],
"difference": sum((a["ending_accum"] - (a["cost"] - a["salvage"])
for a in over), ZERO),
"passed": not over})
sl = [a for a in assets if a["recomputed"] is not None]
sl_bad = [a for a in sl if a["recomp_diff"] != ZERO]
tests.append({"num": 6, "name": "Straight-line assets recomputed and agreed",
"counts": ["Straight-line assets recomputed", "Assets disagreeing"],
"lines": [("Straight-line assets recomputed", Decimal(len(sl))),
("Assets disagreeing", Decimal(len(sl_bad))),
("Net difference",
sum((a["recomp_diff"] for a in sl_bad), ZERO))],
"difference": sum((a["recomp_diff"] for a in sl_bad), ZERO),
"passed": not sl_bad,
"note": "Accelerated methods are not recomputed - their rates must be "
"verified against current instructions."})
n_flagged = len([a for a in assets if a["flags"]])
tests.append({"num": 7, "name": "Asset-level integrity",
"counts": ["Assets in register", "Assets with findings"],
"is_count": True,
"lines": [("Assets in register", Decimal(len(assets))),
("Assets with findings", Decimal(n_flagged))],
"difference": Decimal(n_flagged),
"passed": n_flagged == 0,
"note": "" if not n_flagged else
f"{n_flagged} asset(s) carry findings - see Exceptions and "
f"Asset Detail. The count is items, not dollars."})
overall = all(t["passed"] for t in tests)
escalations = []
t3 = tests[2]
if not t3.get("skipped") and not t3["passed"]:
escalations.append(
"Beginning balances do not agree to the prior year as filed. Determine "
"whether the prior year, the current year, or both are wrong before "
"proceeding - this may require an amended return or a method-change filing.")
still = [a for a in assets if a["disposal_date"]
and a["disposal_date"].year < args.year and a["current_depr"] != ZERO]
if still:
escalations.append(
f"{len(still)} asset(s) disposed in a prior year are still depreciating, "
f"totalling {sum((a['current_depr'] for a in still), ZERO):,.2f} of "
f"current-year depreciation on assets the client no longer owns.")
if over:
escalations.append(
f"{len(over)} asset(s) depreciated beyond basis by "
f"{sum((a['ending_accum'] - (a['cost'] - a['salvage']) for a in over), ZERO):,.2f}.")
changed = [a for a in assets
if any(f.startswith(("METHOD CHANGED", "LIFE CHANGED")) for f in a["flags"])]
if changed:
escalations.append(
f"{len(changed)} asset(s) had a method or life change. A change in method or "
f"recovery period generally requires a prescribed procedure, not a schedule "
f"edit. Route the treatment question to the signer.")
additions = [a for a in assets
if a["in_service_date"] and a["in_service_date"].year == args.year]
verify = [
"Recovery period and convention for each current-year addition, against current "
"instructions for Form 4562",
"Bonus depreciation rate and eligibility for the year the asset was placed in "
"service",
"Section 179 limit, phase-out threshold, and any business-income limitation",
"State conformity - many states decouple from federal bonus and Section 179, so "
"each state must be tested separately",
"MACRS percentages for any accelerated asset - this workpaper tests consistency "
"only, not the rate",
]
# ---- console
print("=" * 74)
print(f"FIXED ASSET AND DEPRECIATION TIE-OUT ({args.basis} basis)")
print("=" * 74)
print(f"Client : {args.client or '(not stated)'} Year: {args.year} "
f"(year end {year_end})")
print(f"Assets : {len(assets)} Additions: {len(additions)} "
f"Disposals: {len([a for a in assets if a['disposal_date'] and a['disposal_date'].year == args.year])}")
print()
for t in tests:
if t.get("skipped"):
print(f"Test {t['num']}: NOT PERFORMED - {t['note'][:80]}")
continue
if t.get("is_count"):
print(f"Test {t['num']}: {'PASS' if t['passed'] else '*** FAIL ***':<14} "
f"{int(t['difference']):>10} asset(s) flagged {t['name']}")
else:
print(f"Test {t['num']}: {'PASS' if t['passed'] else '*** FAIL ***':<14} "
f"diff {t['difference']:>16,.2f} {t['name']}")
print()
print(f"Cost : begin {tot['begin_cost']:>14,.2f} + add {tot['additions']:>13,.2f} "
f"- disp {tot['disposals']:>13,.2f} = {tot['cost_computed']:>14,.2f}")
print(f"Accum : begin {tot['begin_accum']:>14,.2f} + curr {tot['current']:>13,.2f} "
f"- disp {tot['disp_accum']:>13,.2f} = {tot['accum_computed']:>14,.2f}")
print(f"Net book value: {tot['end_cost'] - tot['end_accum']:>16,.2f}")
if problems:
print(f"\nASSET-LEVEL FINDINGS ({len(problems)}):")
for p in problems[:20]:
print(f" ! {p}")
if len(problems) > 20:
print(f" ... and {len(problems) - 20} more - see Asset Detail")
if escalations:
print("\nESCALATE:")
for e in escalations:
print(f" ! {e}")
if not overall and not args.force:
print("\n" + "=" * 74)
print("WORKBOOK NOT WRITTEN. The register does not tie out.")
print("Fixed asset errors compound - a wrong beginning balance misstates every")
print("subsequent year. Resolve before relying on the depreciation figure.")
print("=" * 74)
return 1
meta = {
"Client": args.client or "(not stated)",
"Year": args.year,
"Year end": str(year_end),
"Basis": args.basis,
"Assets in register": len(assets),
"Current-year additions": len(additions),
"Prepared": datetime.now().strftime("%Y-%m-%d %H:%M"),
}
wb = Workbook()
sheet_summary(wb, meta, tests, overall, escalations, verify)
sheet_rollforward(wb, rows, prior)
sheet_assets(wb, assets)
sheet_list(wb, "Exceptions",
[[i + 1, p] for i, p in enumerate(problems)],
["#", "Finding"], {}, "None - no asset-level findings.")
sheet_list(wb, "Additions",
[[a["asset_id"], a["description"], a["asset_class"], a["in_service_date"],
float(a["cost"]), a["method"], float(a["life_years"]) if a["life_years"] else None,
a["convention"], float(a["current_depr"]),
"verify recovery period, convention, and any bonus or Section 179 "
"election against current instructions for Form 4562"]
for a in additions],
["Asset ID", "Description", "Class", "In service", "Cost", "Method",
"Life", "Conv", "Current depr", "To verify"],
{3: DATEF, 4: MONEY, 8: MONEY},
"No current-year additions.")
disposals = [a for a in assets if a["disposal_date"]]
sheet_list(wb, "Disposals",
[[a["asset_id"], a["description"], a["disposal_date"], float(a["cost"]),
float(a["ending_accum"]), float(a["cost"] - a["ending_accum"]),
float(a["proceeds"]) if a["proceeds"] is not None else None,
float(a["gain_loss"]) if a["gain_loss"] is not None else None,
float((a["proceeds"] or ZERO) - (a["cost"] - a["ending_accum"])),
"depreciation stopped" if a["current_depr"] == ZERO
else f"STILL DEPRECIATING {a['current_depr']:,.2f}"]
for a in disposals],
["Asset ID", "Description", "Disposed", "Cost", "Accum removed", "NBV",
"Proceeds", "Gain/loss per register", "Recomputed gain/loss", "Status"],
{2: DATEF, 3: MONEY, 4: MONEY, 5: MONEY, 6: MONEY, 7: MONEY, 8: MONEY},
"No disposals.",
footer="Recomputed gain or loss is proceeds less net book value. A "
"difference means the gain was computed on the wrong accumulated "
"depreciation figure, or the asset was not fully removed.")
wb.active = 0
out = Path(args.out)
if not overall:
out = out.with_name(out.stem + " [DOES NOT TIE]" + out.suffix)
out.parent.mkdir(parents=True, exist_ok=True)
wb.save(str(out))
print(f"\n{'TIED OUT' if overall else 'DOES NOT TIE (forced)'}. Workbook: {out}")
return 0 if overall else 1
if __name__ == "__main__":
sys.exit(main())
