CAM reconciliation template: the Excel spreadsheet, statement format, and the lease terms behind every line

A CAM reconciliation template is a worksheet that compares the common area maintenance expenses a landlord actually incurred over a period against the estimated CAM a tenant paid monthly, and produces the difference. If actual costs came in above the estimates, the tenant owes a true-up. If they came in below, the tenant gets a credit.

The template does three jobs at once: it holds the expense ledger for the property, it applies each tenant lease to that ledger, and it produces a statement the tenant can check. Most templates do the first job well and the second badly, because the lease terms live in a PDF nobody opened. That is where reconciliations get disputed. See the full method in our CAM reconciliation guide.

Build it as four linked tabs rather than one sheet. A single flat sheet is why most reconciliation workbooks break in year two.

• Tab 1, expense ledger. One row per invoice or GL line: date, vendor, GL code, category, amount, and two flags: controllable and excluded. Every later calculation filters on those flags.
• Tab 2, pool build. Sums the ledger by category, subtracts exclusions, applies the gross-up, and produces the adjusted pool. Show each step as its own row so a tenant can follow it.
• Tab 3, tenant matrix. One row per tenant: rentable area, denominator, pro-rata share, base year, cap type and rate, prior year capped amount, estimates billed. This tab is the abstract of the leases.
• Tab 4, statements. One statement per tenant, driven by lookups against tabs 2 and 3, so nothing is typed twice.

Two structural rules matter more than the formulas. Keep the pro-rata denominator as an explicit cell per tenant, never a shared constant, because leases disagree about what the denominator is. And keep controllable and uncontrollable expenses separable at the ledger level, because caps almost always apply only to the controllable half.

The arithmetic is short. Getting the inputs right is the work.

1. Total the pool. Add every operating expense charged to the property for the period.
2. Remove exclusions. Strip whatever the lease carves out: capital replacements, leasing commissions, ownership and financing costs, landlord legal fees, expenses reimbursed by insurance or a single tenant.
3. Gross up variable costs. If the building ran below the stated occupancy, adjust the variable expenses to what they would have cost at that occupancy, usually 95 percent and sometimes 100 percent. Never gross up fixed costs such as real estate taxes, insurance, or management fees.
4. Apply the pro-rata share. Tenant rentable area divided by the denominator the lease names, applied to the adjusted pool.
5. Apply the cap and the base year. Limit controllable expenses to the ceiling the lease sets, and subtract the base year or expense stop if the lease has one.
6. Subtract what was paid. Owed minus estimates collected is the true-up or credit.

In the worked example above, the adjusted pool of $843,500 at a 4.00 percent share gives $33,740, the controllable cap trims $700, and estimates of $31,800 leave $1,240 due.

A reconciliation is arithmetic applied to lease language, so the template is only as good as the terms loaded into it. Six of them decide the number.

These clauses sit in different articles and are routinely rewritten by amendment, which is why a reconciliation built from the original lease alone is often wrong. Our CAM and operating expense extraction page shows the exact fields.

A statement that shows only a total and a balance due gets challenged. A statement that shows the derivation usually does not. Include, in order: the reconciliation period, the total pool, the exclusions removed, the gross-up adjustment and the occupancy it assumes, the adjusted pool, the tenant pro-rata share with both numerator and denominator visible, any cap or base year adjustment, the amount owed, the estimates collected month by month, and the balance.

Add a category breakdown for the pool: landscaping, janitorial, security, utilities, snow and ice, repairs, management fee, taxes, insurance. Tenants compare categories year over year, and a jump they can see explained is a jump they do not audit. The CAM charges explainer covers what belongs in each bucket.

The failures repeat across portfolios:

• One denominator for the whole property. Anchor tenants often negotiate their own pool or their own exclusion from it. A single shared constant quietly overcharges everyone else.
• Caps applied to the full pool. Caps usually cover controllable expenses only. Applying one to taxes and insurance understates the bill and invites a restatement later.
• Gross-up on fixed costs. The most common tenant audit finding, and the easiest to lose.
• Amendments not reflected. A premises expansion in month seven changes the share for part of the year. Most templates carry one static square footage.
• No backup reference. When a tenant exercises an audit right, an unsourced spreadsheet costs days to defend.
• Estimates never trued to the ledger. Billed and collected are different numbers, and the reconciliation needs collected.

If you are on the receiving end of one of these statements, our guide to running a CAM reconciliation audit walks through how to check it before you pay.

The spreadsheet takes an afternoon. Populating the tenant matrix for 40 leases takes weeks, because each lease has to be read for six clauses buried in different articles and then re-read for every amendment that touched them.

That is the part Leaseabstracts does. Upload the leases and amendments and the tool returns the operating expense terms in a structured grid: pro-rata share and the denominator language, base year or expense stop, cap type, rate and base, gross-up provision, the full exclusion list, and the audit rights window. Every field links back to the page and clause it came from, so when a tenant asks why their share is 4.00 percent you can answer in one click.

Export to Excel or CSV and it drops straight into tab 3 of the template. Property teams running the same exercise across a portfolio usually pair this with the rent roll template, and teams loading the output into a platform can see how it maps in Yardi or MRI.