CAM Reconciliation Template: The 8 Sections Every Defensible Reconciliation Must Have

Why the Template Is the Starting Point

CAM reconciliation errors are almost never arithmetic errors. The math is simple. The errors are structural — the wrong expenses in the pool, the wrong denominator in the share calculation, a gross-up applied to the wrong accounts, a cap that should have triggered but did not. These are documentation and process failures, not calculation failures.

A proper reconciliation template prevents structural errors by building the right sequence of steps into the document itself. Each section validates the next. The exclusion log feeds into the expense pool. The gross-up worksheet feeds into the pool total. The denominator reconciliation feeds into every tenant's pro-rata share. If any section is missing, the errors it was designed to prevent go undetected until a tenant auditor finds them.

This guide describes what every section of a defensible CAM reconciliation template must contain, what error each prevents, and what the most common failure mode looks like when that section is skipped.

CapVeri provides a downloadable CAM reconciliation template in Excel format. Download the template — we'll send it to you immediately.

The 8 Required Sections

Section 1: Property and Period Header

The header is not administrative formality. It is the source of truth for every calculation that follows.

Required fields:

• Property name and address
• Fiscal year start and end dates (e.g., January 1, 2025 – December 31, 2025)
• Total rentable square footage (GLA/RSF) used in this reconciliation
• BOMA measurement standard the building is certified under (1996, 2010, 2017, or 2024)
• Date of most recent BOMA measurement certification
• Preparer name, reviewer name, and approval sign-off

Why it matters: If the header shows 90,000 SF GLA and the certified BOMA measurement certificate says 85,000 SF, every pro-rata share in the reconciliation is wrong by 5.9%. A tenant auditor who pulls the BOMA certificate will find this in the first 30 minutes. The header forces the preparer to confirm these numbers before any calculations run.

Section 2: Expense Pool Summary

The expense pool summary is the bridge between the raw GL and the recoverable expense total. It does not just list expenses — it organizes them by GL account, maps each account to an expense category, and identifies the recoverable amount for each.

Required structure:

GL Account | Account Description | Annual Total | Recoverable? | Recoverable Amount | Notes
6110       | Day Janitorial      | $98,000      | Yes          | $98,000            |
6115       | Janitorial Supplies | $12,400      | Yes          | $12,400            |
6210       | HVAC Maintenance    | $38,500      | Yes          | $38,500            |
6310       | Roof Replacement    | $140,000     | No (CapEx)   | $0                 | See exclusion log, line 3
6420       | Property Tax        | $387,000     | Yes          | $387,000           |

Why this format matters: Mapping from GL account to expense category is where most expense pool errors originate. A roof replacement coded to "Building Maintenance" instead of "Capital Improvements" looks recoverable until someone checks the vendor invoice. The expense pool summary forces the preparer to make an explicit recoverable/non-recoverable determination for every GL account — not just the obvious ones.

What most Excel templates miss: They sum expenses by category without showing the underlying GL accounts. The category total is visible but the composition is not. A tenant auditor who requests GL detail will immediately see accounts in the pool that should have been excluded — and the template gives them no way to see that the exclusion decision was made deliberately.

Section 3: Exclusion Log

The exclusion log documents every expense that was removed from the recoverable pool and why. This is the single most overlooked section in most reconciliation templates — and the most valuable one when a tenant challenges the calculation.

Required fields for each exclusion:

• GL account number and description
• Annual amount excluded
• Reason for exclusion (CapEx, landlord-only cost, above-standard service, anchor carve-out, etc.)
• Lease clause reference that authorizes the exclusion (e.g., "Section 7.3(b) — Capital Expenditures excluded")
• Preparer initials

Example log entries:

The dispute this prevents: Without an exclusion log, a tenant who sees that the GL total is $2,240,000 but the recoverable pool is $2,061,200 will ask what happened to the $178,800 difference. Without a documented exclusion log, the answer requires reconstructing the decision-making from emails and memory. With a log, the answer is a single document.

Section 4: Gross-Up Worksheet

The gross-up worksheet is the most technically complex section of the reconciliation template, and the one most likely to be executed incorrectly in an ad hoc spreadsheet.

What it must show:

1. Variable/fixed classification table — Every GL account classified as variable (eligible for gross-up) or fixed (not eligible). This classification drives the gross-up calculation. It must be explicit and documented, not embedded in a formula.

2. Monthly occupancy table — Actual occupancy percentage for each month of the fiscal year. This is the source data for the gross-up adjustment; it must come from the rent roll, not from an estimate.

3. Gross-up calculation — The variable expense total adjusted to the target occupancy percentage from the lease. Standard formula: Grossed-Up Amount = Variable Expenses / Average Actual Occupancy × Target Occupancy %.

4. Fixed expense passthrough — The fixed expense total, carried through without adjustment.

5. Adjusted pool total — Variable (grossed up) + Fixed = total recoverable expenses after gross-up.

Example:

The gross-up increased the pool by $71,841 — the difference between expenses at 88% occupancy and what they would have been at 95%. This adjustment is contractually required by the gross-up clause. It is also the adjustment most frequently challenged if the worksheet does not show the variable/fixed classification clearly.

Section 5: Denominator Reconciliation

The denominator is the building's total rentable square footage used in every tenant's pro-rata share calculation. A wrong denominator creates a systemic error — it is not a one-tenant mistake, it is a whole-building mistake that affects every line of every statement.

Required components:

• BOMA-certified RSF — The building's total rentable area as certified under the applicable BOMA standard. Source: the measurement certificate.
• System RSF — The square footage currently loaded in the property management system.
• Reconciliation — If these two numbers differ, document why and which one is correct for this reconciliation.
• Mid-year changes — If any tenant expanded, contracted, or vacated during the year, document the effective date and the resulting change to the denominator (for leases where the denominator changes with occupancy).
• Lease-specific denominators — If any tenant's lease defines a non-standard denominator (e.g., total GLA excluding anchor space), document it here and carry it into that tenant's per-tenant sheet.

The BOMA standard matters: A building measured under BOMA 1996 will show different RSF than the same building measured under BOMA 2017. If the lease cites "BOMA 2010" and the landlord uses BOMA 2024 measurements, the denominator is wrong for that tenant. See BOMA 2024 Implementation Guide for the specifics of each standard.

Section 6: Per-Tenant Calculation Sheet

This is the section that generates the actual true-up amount for each tenant. Every tenant in the building gets their own calculation, because no two tenants have identical lease terms.

Required fields per tenant:

Mid-year occupancy changes require time-weighting. A tenant who occupied 10,000 SF from January through June, then expanded to 12,500 SF from July through December, does not get a flat 10,000 SF or 12,500 SF applied to the full year. The calculation uses 10,000 SF for six months and 12,500 SF for six months, weighted accordingly. Many spreadsheet templates apply the year-end square footage to the full year — which is a lease violation that audit firms find in the first 30 minutes of any review.

Section 7: Cap Structure Verification

CAM expense caps are among the most lease-specific provisions in any reconciliation. Cumulative vs. non-cumulative, base year, cap percentage, what counts as controllable — all of these vary by tenant. A template that does not have a dedicated cap verification section will apply a single cap logic across all tenants, which is almost certainly wrong for at least some of them.

Required fields:

• Tenant name
• Cap type (cumulative or non-cumulative)
• Base year (the year from which the cap is calculated)
• Base year controllable CAM amount
• Cap percentage per year
• Current year cap ceiling (calculated)
• Current year actual controllable CAM amount
• Billed amount (lower of ceiling and actual)
• Expenses excluded from cap (taxes, insurance, and other uncontrollables per lease)

Cumulative vs. non-cumulative — the key distinction:

A non-cumulative 5% cap resets each year against the prior year's actual. If prior-year controllable CAM was $100,000, the ceiling this year is $105,000 — regardless of what happened in prior years.

A cumulative cap compounds from the base year. If the base year controllable CAM was $90,000 with a 5% cumulative cap, the ceiling in year five is $90,000 × (1.05)^5 = $114,866.

These produce materially different results. Applying non-cumulative logic to a cumulative cap understates what the landlord can bill. Applying cumulative logic to a non-cumulative cap overbills the tenant and is recoverable in a dispute.

For the full treatment, see Cumulative vs. Non-Cumulative CAM Caps.

Section 8: Audit Trail Log

The audit trail log is the section that separates a professional reconciliation from a spreadsheet exercise. It documents every material change made to the reconciliation after the initial draft — who made the change, when, what the prior value was, and what authorization supported the change.

Required fields:

Why this matters in a dispute: A tenant auditor who reviews the reconciliation file will look for unexplained adjustments. If the pool total changed between the first draft and the final statement, they will ask why. Without an audit trail, "we corrected an error" is the only available answer — and it invites scrutiny of every other number in the reconciliation. With a log, the change is documented, authorized, and traceable.

Why Most Excel Templates Fail

Standard Excel templates — the kind available for download across the internet — share four structural failures that make them unsuitable for portfolios with more than a handful of tenants or complex lease provisions.

1. No exclusion tracking. A typical Excel template has a single cell for "total recoverable expenses." There is no GL-to-pool bridge and no documentation of what was removed. The final number is correct (or not), but the path from GL to pool is invisible.

2. No gross-up audit trail. Excel templates often apply the gross-up adjustment as a formula: =B12/AverageOccupancy*TargetOccupancy. The formula result is visible, but the variable/fixed classification that should precede it does not exist as a separate step. When the result is challenged, there is no documentation showing which accounts were classified as variable.

3. No cap enforcement. Most templates have a field for "CAM cap" but no formula that validates the cap against each tenant's specific lease terms. The controller manually enters a cap amount — if they remember to. The template does not prompt them, does not validate the calculation, and does not flag tenants for whom a cap should apply.

4. No version control. Spreadsheets change. Formulas break. A value entered in cell C47 in February gets overwritten in March when the GL is updated. Excel has no native audit trail. The final version of the reconciliation may not match what was calculated two weeks earlier, and there is no way to detect the difference.

These are not Excel limitations — they are template design failures. A well-engineered Excel workbook can address all four. But it requires deliberate architecture, not a downloaded generic template.

Template vs. Platform: When the Difference Matters

A template — even a well-designed one — is a tool that requires disciplined execution by the person using it. The template does not know if you skipped the exclusion log. It does not enforce the gross-up classification. It does not pull the cap language from the lease and verify the calculation against it.

A reconciliation platform automates the enforcement. CapVeri ingests the GL export, maps accounts to expense categories using your chart of accounts, flags potential CapEx for review, runs the gross-up with the variable/fixed classification documented, pulls the denominator from the certified measurement, and applies the cap logic from each tenant's lease abstract. Every section above is generated from source data, not populated manually.

The practical difference: a template reduces errors when executed correctly. A platform prevents the errors that occur when execution is rushed, when the preparer is unfamiliar with a specific lease provision, or when the portfolio is too large for a single controller to review every detail.

For the comparison of building vs. buying a reconciliation system, see CAM Build vs. Buy. For migrating off Excel, see Data Migration Off Excel.

Getting the Template

CapVeri provides a downloadable CAM reconciliation template in Excel format. Download the template — we'll send it to you immediately.

The template includes all eight sections described above, with structured worksheets for the exclusion log, gross-up classification, denominator reconciliation, per-tenant calculation (with cap verification built in), and audit trail log. It works for single properties and can be replicated across a portfolio.

Related Resources

• Data Migration Off Excel — Moving from spreadsheets to a reconciliation platform
• Year-End Close Checklist — GL close process before reconciliation begins
• CAM Pre-Send Checklist — 12-point QA before statements go to tenants
• Defensible Reconciliation Package — Documentation to include with reconciliation statements
• CAM Gross-Up Calculation Guide — Full variable/fixed classification methodology

Sources

1. BOMA floor measurement standards — BOMA International
2. IREM CAM reconciliation methodology — IREM Oregon (PDF)
3. IREM income/expense analysis — IREM