Reconciling Estimated vs Actual CAM Charges

Every tenant who pays monthly CAM estimates is, in effect, extending the landlord an interest-free loan against a number nobody knew at billing time — and the annual reconciliation is where that loan gets squared against what the building actually spent. This page is a focused recipe within year-end CAM reconciliation and true-ups for the ledger step that sits underneath every true-up: pairing the twelve monthly estimate billings each tenant received against their reconciled share of the actual recoverable pool, computing a signed variance per tenant, and proving that the individual variances tie out to the building’s total over- or under-recovery before a single statement is cut. It is deliberately narrower than computing the true-up balance itself — the concern here is the estimate-versus-actual reconciliation ledger and the variance analysis that validates it, not the downstream billing arithmetic.

Context & When to Use This Approach #

Reach for a dedicated estimate-versus-actual ledger whenever tenants are billed CAM on an estimated schedule during the year and reconciled against actuals afterward — which is nearly every gross, modified-gross, and net lease with a monthly recovery clause. The reconciliation ledger is the evidentiary backbone the true-up rests on. Before you compute what a tenant owes or is owed, you need a defensible, per-tenant record of two numbers and the difference between them:

• What was actually billed. The sum of the estimate installments the tenant was invoiced across the reconciliation period — not the budgeted figure, the billed figure. Mid-year estimate resets, move-ins, and credit memos mean the two diverge, and only the billed history is auditable.
• What the tenant actually owes. Their reconciled share of the real recoverable pool, produced after caps, expense stops, gross-up, and exclusions have already been applied by the allocation engine. This ledger consumes that reconciled amount; it does not recompute it.
• The signed variance. The difference, carried with its sign so the direction is unambiguous: a tenant who was billed more than they owe is due a refund, one billed less owes a supplemental invoice.

The reason to build this as an explicit ledger rather than a one-line subtraction is auditability and defensibility. Tenants dispute reconciliations, and the first thing a tenant’s auditor asks for is the estimate billing history reconciled line-by-line against the actual pool. If your system can only emit the net true-up number, you cannot answer that question without rebuilding the calculation by hand. Keep this step separate from the downstream computation covered in automating CAM true-up calculations in Python: that page turns a validated variance into a prorated, signed balance ready to bill; this one produces and proves the variance in the first place.

Step-by-Step Implementation #

The pipeline joins two ledgers, derives a signed variance per tenant, screens each variance for materiality, routes what matters, and refuses to proceed unless the whole thing ties out. Every monetary field stays in decimal.Decimal — never float — because a reconciliation that is a cent off across a few hundred tenants is a reconciliation a tenant’s auditor can reject. The Python decimal documentation covers the deterministic arithmetic this depends on.

Step 1 — Pull the monthly estimates ledger. Aggregate the estimate installments each tenant was actually billed over the reconciliation period. Model each billing line, then fold them per tenant so a mid-year rate change or a partial month sums correctly.

from __future__ import annotations

from collections import defaultdict
from dataclasses import dataclass
from decimal import Decimal


@dataclass(frozen=True)
class EstimateBill:
    """One estimated CAM installment as it was actually invoiced to a tenant."""

    tenant_id: str
    period: str          # e.g. "2025-03"
    billed_amount: Decimal


def total_estimated(bills: list[EstimateBill]) -> dict[str, Decimal]:
    """Sum the estimate installments each tenant was billed across the year."""
    ledger: dict[str, Decimal] = defaultdict(lambda: Decimal("0"))
    for bill in bills:
        ledger[bill.tenant_id] += bill.billed_amount
    return dict(ledger)

Step 2 — Pull the actual reconciled pool. Load each tenant’s reconciled recoverable share — the post-cap, post-stop, post-gross-up figure the allocation engine produced for the real pool. This is authoritative input; the ledger records it verbatim rather than deriving it.

@dataclass(frozen=True)
class ReconciledShare:
    """A tenant's final recoverable amount for the actual (not estimated) pool."""

    tenant_id: str
    reconciled_amount: Decimal   # already net of caps, stops, gross-up, exclusions


def total_actual(shares: list[ReconciledShare]) -> dict[str, Decimal]:
    """Index reconciled recoverable amounts by tenant for the join in Step 3."""
    return {s.tenant_id: s.reconciled_amount for s in shares}

Step 3 — Compute the signed variance per tenant. Outer-join the two ledgers so a tenant present on only one side is surfaced rather than dropped, and record the variance as billed minus owed. A positive variance means the tenant overpaid and is due a credit; a negative variance means they underpaid and owe a supplement.

CENTS = Decimal("0.01")


@dataclass(frozen=True)
class VarianceRow:
    tenant_id: str
    estimated_billed: Decimal
    reconciled_actual: Decimal
    variance: Decimal            # estimated_billed - reconciled_actual (signed)


def build_variance_ledger(
    estimated: dict[str, Decimal],
    actual: dict[str, Decimal],
) -> list[VarianceRow]:
    """Join billed estimates to reconciled actuals; a missing side counts as zero."""
    rows: list[VarianceRow] = []
    for tenant_id in sorted(estimated.keys() | actual.keys()):
        billed = estimated.get(tenant_id, Decimal("0"))
        owed = actual.get(tenant_id, Decimal("0"))
        rows.append(VarianceRow(
            tenant_id=tenant_id,
            estimated_billed=billed,
            reconciled_actual=owed,
            variance=(billed - owed).quantize(CENTS),
        ))
    return rows

For a single tenant the relationship the ledger records is simply:

Step 4 — Apply the materiality gate. Not every variance deserves human attention; a tenant three dollars off does not warrant an analyst’s afternoon. Screen each row against both an absolute floor and a percentage floor so that small dollar amounts on large accounts and large percentages on tiny accounts are both caught. The exact bands come from threshold tuning for allocation accuracy, which is where these floors are calibrated against historical dispute rates.

@dataclass(frozen=True)
class MaterialityPolicy:
    dollar_floor: Decimal        # e.g. Decimal("250.00")
    pct_floor: Decimal           # e.g. Decimal("0.05") for 5%


def is_material(row: VarianceRow, policy: MaterialityPolicy) -> bool:
    """A variance is material if it clears either the dollar or the percentage floor."""
    magnitude = abs(row.variance)
    if magnitude >= policy.dollar_floor:
        return True
    base = row.estimated_billed
    if base > 0 and (magnitude / base) >= policy.pct_floor:
        return True
    return False

A variance is flagged when either test trips:

Step 5 — Route the large variances. Partition the ledger into an auto-accept set and a review queue, and tag each routed row with its direction so the queue is actionable at a glance. A refund and a supplemental invoice have different approval paths, and the reviewer should not have to re-derive which is which.

from enum import Enum


class Direction(str, Enum):
    REFUND_DUE = "refund_due"        # tenant overpaid; landlord owes a credit
    SUPPLEMENT_DUE = "supplement_due"  # tenant underpaid; tenant owes more
    BALANCED = "balanced"


def direction_of(row: VarianceRow) -> Direction:
    if row.variance > 0:
        return Direction.REFUND_DUE
    if row.variance < 0:
        return Direction.SUPPLEMENT_DUE
    return Direction.BALANCED


def route(
    rows: list[VarianceRow], policy: MaterialityPolicy,
) -> tuple[list[VarianceRow], list[tuple[VarianceRow, Direction]]]:
    """Split into (auto_accept, review_queue); review rows carry their direction."""
    auto_accept: list[VarianceRow] = []
    review_queue: list[tuple[VarianceRow, Direction]] = []
    for row in rows:
        if is_material(row, policy):
            review_queue.append((row, direction_of(row)))
        else:
            auto_accept.append(row)
    return auto_accept, review_queue

Step 6 — Tie out the ledger. Before anything is billed, prove the reconciliation balances: the sum of every tenant’s signed variance must equal the building’s total over- or under-recovery, which is total estimated billed minus the total reconciled pool. If the two disagree, a tenant was dropped from the join, a duplicate estimate slipped in, or a rounding residual leaked — and the ledger is not yet trustworthy.

def tie_out(
    rows: list[VarianceRow],
    total_billed: Decimal,
    total_pool: Decimal,
) -> Decimal:
    """Assert per-tenant variances reconstruct the pool-level over/under-recovery."""
    sum_variance = sum((r.variance for r in rows), Decimal("0"))
    pool_delta = (total_billed - total_pool).quantize(CENTS)
    if sum_variance != pool_delta:
        raise ValueError(
            f"ledger does not tie out: Σ variance {sum_variance} != pool delta {pool_delta}"
        )
    return pool_delta

Gotchas & Known Limitations #

• Budgeted estimates are not billed estimates. The most common error is reconciling against the estimate budget rather than the sum of what the tenant was actually invoiced. Mid-year resets, prorated move-in months, and credit memos make them diverge; always aggregate the real billing history in Step 1.
• A tenant on only one side must not vanish. A tenant who moved out mid-year may have estimate billings but no full-year reconciled share, or vice versa. An inner join silently drops them and the ledger will still look internally consistent while under-recovering. The outer join in Step 3 surfaces these as large variances instead of hiding them.
• Float anywhere breaks the tie-out. A single float in the estimate sum or the reconciled amount reintroduces binary drift, and Step 6 will fail intermittently for reasons no one can reproduce. Keep every amount Decimal from ingest through tie-out.
• Materiality has two floors for a reason. A dollar-only floor lets a large percentage error slip through on a small tenant; a percentage-only floor misses a modest-percentage but large-dollar swing on an anchor. Screen on both, as Step 4 does, and calibrate them together.
• Proration is a separate concern. Partial-year tenants need their owed figure prorated before the variance is meaningful — that adjustment belongs to the true-up computation, not this ledger. Feed this step a reconciled amount that already reflects the occupancy period, or the variance will overstate what a mid-year tenant owes.
• Sign conventions must be stated once and enforced. Mixing “tenant owes” positive in one place and “landlord owes” positive in another is how a refund becomes an invoice. Fix the convention (here, positive variance means refund due) in the data contract and let direction_of be the only place it is interpreted.

Verification #

Because these variances become refunds and supplemental invoices, the ledger is proven arithmetically before it is trusted. Two invariants are load-bearing: each row’s variance must equal its own billed-minus-owed, and the variances in aggregate must reconstruct the pool-level over- or under-recovery to the cent.

def verify_ledger(
    rows: list[VarianceRow], total_billed: Decimal, total_pool: Decimal,
) -> None:
    """Assert per-row arithmetic and whole-ledger tie-out before billing."""
    # 1. Every row's variance is exactly its billed minus its owed.
    for r in rows:
        expected = (r.estimated_billed - r.reconciled_actual).quantize(CENTS)
        assert r.variance == expected, f"{r.tenant_id}: row variance inconsistent"
    # 2. The ledger ties out to the building's over/under-recovery.
    tie_out(rows, total_billed, total_pool)


estimated = total_estimated([
    EstimateBill("T-100", "2025-Q1", Decimal("3000.00")),
    EstimateBill("T-100", "2025-Q2", Decimal("3000.00")),
    EstimateBill("T-200", "2025-Q1", Decimal("1500.00")),
])
actual = total_actual([
    ReconciledShare("T-100", Decimal("5400.00")),   # billed 6000 -> 600.00 refund
    ReconciledShare("T-200", Decimal("1650.00")),   # billed 1500 -> 150.00 supplement
])

ledger = build_variance_ledger(estimated, actual)
verify_ledger(ledger, total_billed=Decimal("7500.00"), total_pool=Decimal("7050.00"))
# T-100 variance +600.00 (refund), T-200 variance -150.00 (supplement);
# Σ variance = 450.00 == 7500.00 - 7050.00, so the ledger ties out.

The per-row assertion catches a corrupted variance — a stray rounding or a hand-edited amount that no longer matches its own inputs. The tie-out catches the structural failures that matter most: a tenant dropped from the join, a double-counted estimate, or a pool total that disagrees with the sum of reconciled shares. A ledger that clears both is safe to route; the material rows go to review with their direction attached, and everything else is accepted within the tolerance band.

Once the ledger ties out, these validated variances flow into automating CAM true-up calculations in Python, which prorates partial years and turns each signed variance into a billable balance, all under the umbrella of year-end CAM reconciliation and true-ups.

Related #

• Year-End CAM Reconciliation and True-Ups — the parent stage: the full annual close in which this estimate-versus-actual ledger is the first proven step.
• Automating CAM True-Up Calculations in Python — the next step, which prorates and converts each validated variance into a signed, billable true-up balance.
• Threshold Tuning for Allocation Accuracy — where the dollar and percentage materiality floors used by the gate here are calibrated against historical dispute data.