CAM Reconciliation

Best Practices for CAM Expense Exclusion Tracking

A single non-recoverable cost that slips into the CAM pool — a roof replacement booked as “repairs,” a management fee billed above its negotiated cap, a marketing-fund contribution — inflates every tenant’s pro-rata share and is exactly the line an auditor pulls first. This page is a focused implementation recipe for building a deterministic exclusion layer that strips carved-out costs from the recoverable pool before any allocation math runs, and it is part of Defining CAM Expense Categories in Commercial Leases, the parent guide to translating lease language into computable expense buckets under the wider CAM Architecture & Lease Clause Taxonomy framework.

Exclusion tracking removes carved-out costs before allocation runs A GL line item is matched against the exclusion rule set, then a single Excluded? decision splits the flow. On yes the line is tagged and removed from the pool and recorded to a SHA-256 audit trail; on no it stays in the recoverable pool and continues to pro-rata allocation. The excluded branch is drawn in amber, the recoverable branch in blue. GL line item Match exclusion rules Excluded? Tag & remove from recoverable pool Audit trail SHA-256 per line Keep in pool recoverable amount Allocation pro-rata share yes no
The exclusion layer runs before any allocation math: each line is either quarantined with an audit record or kept in the recoverable pool that feeds pro-rata allocation.

Context & When to Use This Approach

Reach for a dedicated exclusion layer the moment your general ledger is broader than your recoverable pool — which is every real CAM portfolio. Category definition tells you what belongs in CAM; exclusion tracking enforces what a lease explicitly carves out, and the two must be decoupled so that a change to one never silently rewrites the other. The concrete triggers on a triple-net (NNN) portfolio are consistent:

  • Capital vs. expense carve-outs. The lease excludes capital improvements but permits their amortized component, so a $180,000 parking-lot resurfacing cannot enter the pool at full value even though it lands in a repairs GL account.
  • Capped or excluded management fees. Administrative or management fees above a negotiated percentage of the recoverable pool are non-recoverable; the overage must be quarantined, not billed.
  • Landlord-benefit costs. Leasing commissions, tenant-improvement allowances, marketing and promotional-fund contributions, and financing costs are recurring GL noise that the lease routes back to the landlord.
  • Related-party and above-market charges that a sophisticated tenant’s audit clause lets them challenge line by line.

If your leases are near-identical and every carve-out is a hard GL-account match, a small lookup table is enough. The engineered approach below earns its keep when carve-outs are threshold-based (a fee cap), lease-specific, or drifting across amendments — the same conditions that make standardizing CAM taxonomies across portfolios hard. The exclusion codes themselves should originate in your lease abstraction database rather than being hard-coded, and the recoverable buckets each surviving line resolves to are assigned by GL code mapping for CAM expenses.

Disposition matrix: common NNN carve-outs, the GL account they hide in, and how the engine excludes them Six triple-net carve-outs mapped to a usual GL account and an exclusion disposition. Capital improvements (repairs 6100) are partial — only the amortized share is recovered. A management fee over cap (admin 6300) is threshold-based — only the overage is removed. Leasing commissions (6500), marketing and promotional fund (6700), and financing or interest costs (8000) are fully excluded. Related-party markups span various accounts and route to human review. Full exclusions are amber, threshold amber-neutral, partial blue, and review a neutral dashed chip. Carve-out (NNN lease) Usual GL account Disposition Capital improvements Management fee over cap Leasing commissions Marketing / promo fund Financing / interest costs Related-party markups Repairs · 6100 Admin · 6300 Leasing · 6500 Marketing · 6700 Interest · 8000 Various · flag Partial — amortized only Threshold — overage only Full exclude Full exclude Full exclude Route to review
The same GL account can carry recoverable and non-recoverable cost, so disposition — not the account alone — decides what leaves the pool: full exclusions, an amortized partial, a cap overage, or a line held for human review.

Step-by-Step Implementation

The pipeline below turns a raw list of GL line items into two disjoint sets — a clean recoverable pool and a tagged quarantine ledger — plus an immutable audit record for each decision. Every monetary value is carried as decimal.Decimal, never float: a fraction of a cent of binary rounding drift compounds across a portfolio and breaks the conservation check that proves the exclusion pass lost no money.

Step 1 — Model exclusion rules as data, not code. Store each carve-out as a typed rule with an explicit disposition. Full exclusions match a GL account outright; threshold rules (a management-fee cap) only exclude the overage. Sourcing these rows from the abstraction database keeps the logic auditable and versionable.

from __future__ import annotations

from dataclasses import dataclass, field
from decimal import Decimal
from enum import Enum


class Disposition(str, Enum):
    """What the exclusion engine does when a rule matches a line item."""
    EXCLUDE_FULL = "exclude_full"        # entire amount is non-recoverable
    EXCLUDE_OVER_CAP = "exclude_over_cap"  # only the amount above a cap
    REVIEW = "review"                    # ambiguous — route to a human queue


@dataclass(frozen=True)
class ExclusionRule:
    """One carve-out abstracted from a lease clause."""
    code: str                       # stable id, e.g. "CAP_IMPROVEMENT"
    gl_accounts: frozenset[str]     # GL accounts this rule watches
    disposition: Disposition
    clause_ref: str                 # lease section, for the audit trail
    cap_rate: Decimal | None = None  # e.g. Decimal("0.03") for a 3% fee cap

Step 2 — Model the GL line item. Keep it minimal but typed. pool_base is the recoverable pool total the cap is measured against, needed only for threshold rules.

@dataclass(frozen=True)
class GLLineItem:
    line_id: str
    gl_account: str
    description: str
    amount: Decimal

Step 3 — Evaluate one line against the rule set deterministically. The evaluator returns the amount to exclude and the rule that fired. No match means the line is fully recoverable; an ambiguous match routes to review rather than defaulting to inclusion — silent inclusion is the failure mode that leaks non-recoverable cost onto tenant statements.

@dataclass(frozen=True)
class ExclusionResult:
    line: GLLineItem
    excluded_amount: Decimal
    recoverable_amount: Decimal
    rule_code: str | None            # None => no exclusion applied
    disposition: Disposition | None


def evaluate(line: GLLineItem,
             rules: list[ExclusionRule],
             pool_base: Decimal) -> ExclusionResult:
    """Return how much of `line` is excluded vs recoverable."""
    for rule in rules:
        if line.gl_account not in rule.gl_accounts:
            continue
        if rule.disposition is Disposition.EXCLUDE_FULL:
            return ExclusionResult(line, line.amount, Decimal("0"),
                                   rule.code, rule.disposition)
        if rule.disposition is Disposition.EXCLUDE_OVER_CAP:
            cap = (rule.cap_rate or Decimal("0")) * pool_base
            over = max(line.amount - cap, Decimal("0"))
            return ExclusionResult(line, over, line.amount - over,
                                   rule.code, rule.disposition)
        # REVIEW: quarantine the whole amount pending human sign-off.
        return ExclusionResult(line, line.amount, Decimal("0"),
                               rule.code, rule.disposition)
    return ExclusionResult(line, Decimal("0"), line.amount, None, None)

Step 4 — Partition the ledger and emit an audit record for every decision. Cryptographically hash each exclusion so a retroactive edit is detectable at year-end. Hashing the rule code, clause reference, and amount ties every removed dollar back to the lease language that justified it.

import hashlib
import json


def audit_hash(result: ExclusionResult, clause_ref: str) -> str:
    """Tamper-evident fingerprint of a single exclusion decision."""
    payload = json.dumps({
        "line_id": result.line.line_id,
        "rule_code": result.rule_code,
        "clause_ref": clause_ref,
        "excluded": str(result.excluded_amount),
    }, sort_keys=True)
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()


def partition(lines: list[GLLineItem],
              rules: list[ExclusionRule]) -> dict[str, object]:
    """Split a GL into recoverable pool, quarantine, and review queue."""
    rule_by_code = {r.code: r for r in rules}
    gross = sum((li.amount for li in lines), Decimal("0"))

    recoverable: list[ExclusionResult] = []
    quarantine: list[tuple[ExclusionResult, str]] = []
    review: list[ExclusionResult] = []

    for line in lines:
        res = evaluate(line, rules, pool_base=gross)
        if res.disposition is Disposition.REVIEW:
            review.append(res)                     # never auto-billed
        elif res.excluded_amount > Decimal("0"):
            clause = rule_by_code[res.rule_code].clause_ref
            quarantine.append((res, audit_hash(res, clause)))
        if res.recoverable_amount > Decimal("0"):
            recoverable.append(res)

    pool_total = sum((r.recoverable_amount for r in recoverable), Decimal("0"))
    return {
        "gross": gross,
        "pool_total": pool_total,
        "recoverable": recoverable,
        "quarantine": quarantine,
        "review": review,
    }

The clean pool_total this returns is the input to pro-rata allocation, where each tenant’s share is computed — and the threshold logic here composes cleanly with managing expense caps and controllable limits, which handles caps applied after the pool is assembled. Tenant-negotiated carve-outs that vary by lease rather than by portfolio belong to exclusion mapping for tenant-specific CAM.

Gotchas & Known Limitations

  • Never default an unmatched or ambiguous line to inclusion. A REVIEW disposition must quarantine the full amount and block billing until a human resolves it; defaulting to the pool is how leakage happens.
  • Threshold rules need a stable pool_base. A fee cap measured against the recoverable pool creates a circular dependency if you compute it after removing the fee. Measure the cap against the gross (pre-exclusion) pool, or run a documented two-pass settlement, and pin the choice per lease.
  • Do not let pandas or JSON parsing coerce money to float. Read amounts as strings and construct Decimal yourself; Decimal("1234.505") and float("1234.505") diverge exactly where a half-cent cap boundary sits.
  • One GL account, many dispositions. The same repairs account holds both recoverable fixes and excludable capital work. Account-only matching over-excludes; add a description or amount predicate, or split the account, before trusting a full exclusion.
  • Amortized capital is partial, not binary. Excluding a capital improvement in full ignores the amortized component many leases do recover. Model it as its own recoverable line, not a carve-out.
  • Exclusion rules drift with amendments. Hard-coded codes rot; source them from the abstraction database and version every change so a prior year’s reconciliation stays reproducible.
  • Overrides must be attributable. Every manual release from the review queue needs a user, timestamp, and reason — enforce it with role-based access controls for CAM data so an override can never be anonymous.

Verification

Because this layer feeds tenant billing, correctness is proven arithmetically, not by scanning the quarantine list. The load-bearing invariant is conservation: every gross dollar must land in exactly one of recoverable, quarantine, or review — nothing created, nothing lost.

from decimal import Decimal


def verify_partition(result: dict[str, object]) -> None:
    """Assert the exclusion pass neither created nor destroyed money."""
    excluded = sum((r.excluded_amount for r, _ in result["quarantine"]),
                   Decimal("0"))
    reviewed = sum((r.excluded_amount for r in result["review"]), Decimal("0"))
    accounted = result["pool_total"] + excluded + reviewed
    assert accounted == result["gross"], f"drift: {accounted} != {result['gross']}"
    # No quarantined dollar may also appear as recoverable.
    for res, _ in result["quarantine"]:
        assert res.recoverable_amount >= Decimal("0")


rules = [
    ExclusionRule("CAP_IMPROVEMENT", frozenset({"6100"}),
                  Disposition.EXCLUDE_FULL, clause_ref="§7.2(c)"),
    ExclusionRule("MGMT_FEE_CAP", frozenset({"6300"}),
                  Disposition.EXCLUDE_OVER_CAP, clause_ref="§7.4",
                  cap_rate=Decimal("0.03")),
]
lines = [
    GLLineItem("L1", "5000", "Landscaping", Decimal("12000.00")),
    GLLineItem("L2", "6100", "Roof replacement", Decimal("80000.00")),
    GLLineItem("L3", "6300", "Management fee", Decimal("6000.00")),
]
result = partition(lines, rules)
verify_partition(result)

Two checks do the real work. The conservation assertion (pool_total + excluded + reviewed == gross) catches a rule that double-counts or a line that silently vanished. The per-line recoverable check confirms no quarantined item leaked a negative or duplicate share back into the pool. In the fixture above the roof replacement is fully excluded, the management fee is trimmed to its 3% cap of the $98,000 gross, and only landscaping plus the capped fee remain recoverable — a result you can reconcile against the lease by hand before wiring it into month-end. Records that clear both assertions are safe to hand to allocation; anything in the review queue carries its source line_id and clause reference into manual review, preserving the audit trail a CAM reconciliation must reproduce on demand.

Once the pool is clean, it flows back into Defining CAM Expense Categories in Commercial Leases for final categorization, and the same clause-to-code mapping is detailed in how to map NNN lease clauses to CAM categories.