CAM Reconciliation

Automating CAM True-Up Calculations in Python

A production true-up job reads what each tenant was billed monthly, recomputes what it actually owed once the year closed, and writes back a single signed number — and this page is the working Python recipe for that job, a focused build companion to year-end CAM reconciliation and true-ups, which frames why the settlement exists. Where the parent topic explains the accounting, this walkthrough hands you the code: the data contracts, the Decimal-safe subtraction, the partial-year proration, and the batch loop that runs the whole thing across a rent roll without a single floating-point cent going astray. The running example is a fictional open-air retail property, Cedar Crossing, whose fiscal year closed on 2026-12-31 with three tenants at different occupancy profiles — a full-year anchor, a mid-year move-in, and a tenant that paid conservative estimates. Every amount below is carried as decimal.Decimal and quantized to cents with an explicit rounding mode, because a true-up is a number a tenant’s accounts-payable team will subtract by hand to check you.

The true-up job as a five-stage pipeline ending in a signed balance Five stages run left to right: gather the estimates a tenant paid, compute the actual recoverable share after caps and stops, subtract estimate from actual, prorate the result for a partial occupancy year, and emit a single signed balance that is either an amount the tenant owes or a credit back to the tenant. Gather estimates Compute actual post caps/stops Subtract actual − estimated Prorate partial year Signed balance owe or credit
The job flows left to right: estimates in, actual share computed after caps and stops, a signed subtraction, proration for partial occupancy, and one balance out — positive means the tenant owes, negative means a credit.

Context & When to Use This Approach

Automate the true-up the moment your reconciliation moves past a single building on a spreadsheet. One property with four tenants can be trued up by hand; a portfolio of forty properties with hundreds of tenants, each on a different lease with a different cap and a different move-in date, cannot — and the manual version fails silently, because a transposed digit or a stale estimate total produces a plausible-looking number that no one catches until the tenant does. A codified job earns its place when any of the following is true:

  • Estimates and actuals live in different systems. The monthly estimate is a billing fact in the accounts-receivable ledger; the actual recoverable share is the output of the allocation engine. Reconciling them by eye across two exports is where errors enter.
  • Occupancy changed mid-year. A tenant that took possession in month seven paid six monthly estimates, not twelve, and its actual share must be prorated to the days it was actually in the premises. Hand-proration across a rent roll is slow and error-prone.
  • Caps or expense stops apply. When the actual share is itself the clamped output of managing expense caps and controllable limits, the true-up is only defensible if the code that produced the cap is the same code the settlement consumes.
  • A tenant will dispute the number. A reproducible job that logs its inputs is the difference between answering a challenge in an hour and reopening a closed year for a week.

This page assumes the actual recoverable share has already been through the constraint pipeline — exclusions, gross-up, area-weighted allocation, expense stops, and caps — and its job is the settlement layer that sits on top. If you need the estimate-versus-actual variance analysis that flags which tenants need a closer look before billing, that is the sibling procedure for reconciling estimated vs actual CAM charges; this page produces the billable balance itself.

Step-by-Step Implementation

The job below turns a tenant’s billing history and its finalized recoverable inputs into one signed true-up per tenant, then runs that transformation across the whole rent roll. Money is Decimal end to end, and a small money helper quantizes every result to cents so the signs and totals are exact rather than approximately right.

Step 1 — Gather the estimated amounts paid. The estimate total is a historical fact, not a recomputation: it is the sum of the monthly estimates the tenant was actually invoiced across the period, read from the billing ledger. Model each tenant’s inputs as a frozen record so the settlement pass cannot mutate a figure in place, and carry the count of estimate periods so proration downstream stays honest.

from __future__ import annotations

from dataclasses import dataclass
from datetime import date
from decimal import Decimal, ROUND_HALF_UP

CENTS = Decimal("0.01")


def to_cents(amount: Decimal) -> Decimal:
    """Quantize a monetary Decimal to cents with explicit rounding."""
    return amount.quantize(CENTS, rounding=ROUND_HALF_UP)


@dataclass(frozen=True)
class TenantReconInputs:
    """Everything the true-up needs for one tenant in one period."""
    tenant_id: str
    rentable_sf: Decimal            # tenant's rentable area
    total_rentable_sf: Decimal      # denominator for the pro-rata share
    recoverable_pool: Decimal       # final recoverable expenses for the period
    base_year_stop: Decimal         # expense stop subtracted from the share
    controllable_cap: Decimal       # ceiling on the recoverable share
    estimated_paid: Decimal         # sum of monthly estimates billed
    days_occupied: int              # days the tenant held the premises
    days_in_period: int             # days in the reconciliation period


def sum_monthly_estimates(monthly: list[Decimal]) -> Decimal:
    """Total the monthly estimates actually billed to a tenant."""
    return to_cents(sum(monthly, Decimal("0.00")))

Step 2 — Compute the actual recoverable share after caps and stops. The tenant’s pro-rata share of the recoverable pool is rentable_sf / total_rentable_sf, applied to the pool. From that share the base-year expense stop is subtracted and clamped at zero — a stop cannot create a negative recovery — and the result is then clamped down to the controllable cap. The area ratio and every intermediate value stays in Decimal.

actual_sharet=min⁡(controllable_capt,  max⁡(0,  pool×rentable_sfttotal_rentable_sf−base_year_stopt))actual\_share_{t} = \min\left(controllable\_cap_{t},\; \max\left(0,\; pool \times \frac{rentable\_sf_{t}}{total\_rentable\_sf} - base\_year\_stop_{t}\right)\right)
def compute_actual_share(inp: TenantReconInputs) -> Decimal:
    """Tenant's recoverable share after the expense stop and the cap.

    Mirrors the allocation engine's order: pro-rata slice, subtract the
    base-year stop (floored at zero), then clamp to the controllable cap.
    """
    pro_rata = inp.rentable_sf / inp.total_rentable_sf
    gross_share = inp.recoverable_pool * pro_rata
    after_stop = max(Decimal("0.00"), gross_share - inp.base_year_stop)
    capped = min(after_stop, inp.controllable_cap)
    return to_cents(capped)

Step 3 — Subtract to a signed true-up. The true-up is actual minus estimated, and the sign carries the meaning: positive means the estimates under-collected and the tenant owes a balance; negative means they over-collected and the tenant is owed a credit. Keeping the value signed — rather than storing an absolute amount plus a direction flag — means the portfolio total is a straight sum and a net-zero portfolio proves itself.

true_upt=actual_sharet−estimated_paidttrue\_up_{t} = actual\_share_{t} - estimated\_paid_{t}

Step 4 — Prorate for a partial occupancy year. A tenant that did not hold the premises for the full period owes only its occupied fraction of the actual share. Prorate the actual share by days_occupied / days_in_period before the subtraction, because the estimates the tenant paid already reflect its shortened tenancy — prorating both sides would double-count the reduction.

prorated_actualt=actual_sharet×days_occupiedtdays_in_periodprorated\_actual_{t} = actual\_share_{t} \times \frac{days\_occupied_{t}}{days\_in\_period}
@dataclass(frozen=True)
class TrueUpResult:
    """Signed settlement for one tenant; positive means the tenant owes."""
    tenant_id: str
    actual_share: Decimal
    prorated_actual: Decimal
    estimated_paid: Decimal
    true_up: Decimal            # signed: > 0 owes, < 0 credit

    @property
    def tenant_owes(self) -> bool:
        return self.true_up > Decimal("0.00")


def true_up_tenant(inp: TenantReconInputs) -> TrueUpResult:
    """Compute one tenant's signed true-up, prorated for partial occupancy."""
    actual = compute_actual_share(inp)
    occupancy_ratio = Decimal(inp.days_occupied) / Decimal(inp.days_in_period)
    prorated = to_cents(actual * occupancy_ratio)
    settlement = to_cents(prorated - inp.estimated_paid)
    return TrueUpResult(
        tenant_id=inp.tenant_id,
        actual_share=actual,
        prorated_actual=prorated,
        estimated_paid=inp.estimated_paid,
        true_up=settlement,
    )

Step 5 — Batch over the rent roll. Run the per-tenant settlement across every tenant in the period and fold the signed results into a portfolio summary. Because the balances are signed, the portfolio’s net position is a single sum, and the split of who owes versus who is owed a credit falls out of the signs without a second pass.

@dataclass(frozen=True)
class PortfolioTrueUp:
    """Aggregate settlement across a rent roll for one period."""
    results: list[TrueUpResult]

    @property
    def net_position(self) -> Decimal:
        return to_cents(sum((r.true_up for r in self.results), Decimal("0.00")))

    @property
    def total_billable(self) -> Decimal:
        owed = (r.true_up for r in self.results if r.tenant_owes)
        return to_cents(sum(owed, Decimal("0.00")))

    @property
    def total_credits(self) -> Decimal:
        credits = (r.true_up for r in self.results if r.true_up < Decimal("0.00"))
        return to_cents(sum(credits, Decimal("0.00")))


def batch_true_up(rent_roll: list[TenantReconInputs]) -> PortfolioTrueUp:
    """Compute a signed true-up for every tenant in the period."""
    return PortfolioTrueUp([true_up_tenant(inp) for inp in rent_roll])

Fed the Cedar Crossing rent roll, the batch produces the anchor tenant’s full-year balance owed, the mid-year move-in’s prorated balance, and the conservative payer’s credit — each a signed Decimal the statement generator can render directly and the billing system can post without a sign-conversion step.

Gotchas & Known Limitations

The subtraction is one line; the ways a portfolio breaks it are not. Treat each of these as a case the job must handle explicitly rather than absorb into a wrong balance.

  • Never let float touch money. A single pool * 0.1 in the pro-rata step reintroduces binary rounding drift, and across hundreds of tenants the portfolio net stops summing to zero. Keep the area ratio and every product in Decimal, and quantize only at result boundaries — quantizing every intermediate value bleeds cents.
  • Prorate one side, not both. The estimates a partial-year tenant paid already reflect its shortened tenancy. Prorating the estimate total again applies the reduction twice and manufactures a phantom credit.
  • Read the estimate total, do not recompute it. The billable balance is defined by what the tenant was actually invoiced. Re-deriving the estimate from the current forecast produces a number the tenant’s payment history will not match.
  • Respect the constraint order. The stop is subtracted before the cap is applied; reverse them and a tenant near its ceiling is clamped against the wrong base. The order here mirrors the allocation engine on purpose.
  • Zero is not a credit. A true-up of exactly 0.00 is a settled account, not a credit. Guard the sign checks with strict comparisons so a settled tenant is neither billed nor refunded.
  • Freeze the inputs. A true-up recomputed after the recoverable pool has been edited is unreproducible. Bind each run to an immutable period snapshot so a disputed figure eighteen months later replays exactly.

Verification

A true-up bug is invisible until a tenant recomputes the balance by hand, so the job is tested against a fixture where the correct signed result is known from the lease terms. Two assertions carry the weight: an exact-cent check on a representative tenant, and a conservation check proving the portfolio net equals the sum of the parts to the cent.

from decimal import Decimal


def test_true_up_signs_and_conserves() -> None:
    anchor = TenantReconInputs(
        tenant_id="CEDAR-ANCHOR",
        rentable_sf=Decimal("40000"),
        total_rentable_sf=Decimal("100000"),
        recoverable_pool=Decimal("500000.00"),
        base_year_stop=Decimal("0.00"),
        controllable_cap=Decimal("250000.00"),
        estimated_paid=Decimal("180000.00"),
        days_occupied=365,
        days_in_period=365,
    )
    movein = TenantReconInputs(
        tenant_id="CEDAR-CAFE",
        rentable_sf=Decimal("10000"),
        total_rentable_sf=Decimal("100000"),
        recoverable_pool=Decimal("500000.00"),
        base_year_stop=Decimal("5000.00"),
        controllable_cap=Decimal("250000.00"),
        estimated_paid=Decimal("20000.00"),
        days_occupied=184,        # took possession on 2026-07-01
        days_in_period=365,
    )

    anchor_result = true_up_tenant(anchor)
    # 40% of 500,000 = 200,000 owed; paid 180,000 -> owes 20,000.00 exactly.
    assert anchor_result.actual_share == Decimal("200000.00")
    assert anchor_result.true_up == Decimal("20000.00")
    assert anchor_result.tenant_owes is True

    movein_result = true_up_tenant(movein)
    # (10% of 500,000 - 5,000) = 45,000, prorated 184/365, less 20,000 paid.
    assert movein_result.prorated_actual == Decimal("22684.93")
    assert movein_result.true_up == Decimal("2684.93")

    portfolio = batch_true_up([anchor, movein])
    # Conservation: the net equals the sum of the two signed balances, exactly.
    assert portfolio.net_position == Decimal("22684.93")
    assert portfolio.total_billable == Decimal("22684.93")
    assert portfolio.total_credits == Decimal("0.00")

Because every amount is Decimal, the assertions use exact equality rather than a floating-point tolerance, so a one-cent leak in the proration fails the test instead of hiding under isclose. Adding a third tenant whose estimates over-collected — a strictly negative true_up — then proves that total_credits and total_billable split cleanly along the sign and that net_position still equals their sum. Once the batch ties out, the signed results flow straight into statement generation and into the variance review described in reconciling estimated vs actual CAM charges, while the cap that shaped each actual share is owned upstream by managing expense caps and controllable limits. Together these close the loop from the concept laid out in year-end CAM reconciliation and true-ups to a billable, reproducible number.