CAM reconciliation spreadsheet template: where it breaks

A CAM reconciliation workbook that will survive a tenant’s questions has eight tabs, in the order of the reconciliation: inputs about the property, the expense pool pulled from the books with every exclusion marked and explained, the gross-up split into variable and fixed, the recovery groups, the tenants with their leases’ terms, the allocation, the statements, and a cap history that carries from year to year. The formulas are short and are given below in words. The checks are the part most workbooks skip and the part that makes the difference between a reconciliation and a calculation.

This post is the template. There is no file to download; the tabs, columns, formulas and checks below are everything a competent spreadsheet user needs to build it in an afternoon, and the worked Harbor Point Plaza example is a test case the finished workbook should reproduce to the dollar. After that comes the honest part: the five places where this workbook, built well, stops being the right tool.

The eight tabs

Each tab is a list of columns and the formulas that fill them. The discipline of the workbook is that nothing is typed that could be referenced.

Inputs

One row per property and year. Columns: property name; rentable square feet of the building; the denominator the leases use for shares, usually the building’s rentable area but sometimes the occupied area or a figure the lease states; occupancy by month, twelve cells; average occupancy, which is the sum of the twelve monthly figures ÷ 12; the gross-up percentage the leases allow, commonly 95% or 100%; the accounting basis; and the year. Harbor Point Plaza in 2026: 100,000 RSF, denominator 100,000 RSF, 80% average occupancy, gross-up to 95%, accrual.

Expense pool

The general ledger export, one row per transaction, for the property and year, filtered to the operating expense range. Columns: account number; account name; vendor; date; amount; recoverable, Y or N; reason, required whenever recoverable is N; recovery group; variable or fixed. The recoverable column is where the exclusions happen, and the reason column is where they are defended. Harbor Point’s export totals $1,240,000; three rows are marked N — the $180,000 roof replacement in 6800 Repairs & Maintenance, reason “capital, lease section 4.2”; $44,000 in leasing commissions, reason “cost of leasing”; $16,000 of refinancing legal in 7200 Professional Fees, reason “owner’s cost of capital”. The pool after exclusions is the sum of amounts where recoverable is Y: $1,000,000.

Gross-up

A summary by account of the Y rows. Columns: account; actual amount; variable or fixed; grossed-up amount. In words: for a variable account, grossed-up amount = actual amount × (gross-up percentage ÷ average occupancy); for a fixed account, grossed-up amount = actual amount. Harbor Point: the variable accounts, utilities, janitorial and trash, total $400,000, and $400,000 × (95 ÷ 80) = $475,000; the fixed accounts, insurance, landscaping, security and management, total $600,000 and stay there; grossed-up pool $1,075,000. Show the $75,000 difference as its own line, because that is the number a tenant will ask about.

Recovery groups

Where different tenants share different pools — retail tenants paying for the parking lot, office tenants for the lobby — each account is assigned to one group. Columns: group name; the accounts in it; grossed-up total for the group, which is the sum of grossed-up amounts for its accounts; the denominator for the group, which may be smaller than the building’s if only part of the building shares the cost. Harbor Point has one group, so this tab has one row: $1,075,000 over 100,000 RSF. Build the tab anyway; the first lease that carves out a cost will need it.

Tenants

One row per lease, with the terms the reconciliation needs and nothing else. Columns: tenant; suite; rentable square feet; share, which is tenant RSF ÷ denominator RSF; recovery group; base year, or expense stop amount, or neither; base-year share in dollars, carried from the base year’s reconciliation; cap percentage; cap mechanic — simple from base, compounded from base, or on prior year’s actual; cap scope, controllable only or all; cap order, before or after the base-year deduction; estimates billed in the year, the sum of the monthly estimate invoices. Brightleaf Yoga: Suite 210, 8,000 RSF, 8.00%, base year 2023, base-year share $74,000, estimates $900 × 12 = $10,800. Meridian Title Co. 7,670 RSF, 7.67%; Atlas Wealth Partners 13,160 RSF, 13.16%; Harbor Cafe 6,430 RSF, 6.43%; two more tenants make up the 80,000 occupied square feet.

Allocation

One row per tenant, all formulas. Columns: gross share = group grossed-up total × share; less base-year share, or less expense stop × tenant RSF; share after base year; cap ceiling, from the cap history tab; share after cap, the lesser of share after base year and the cap ceiling where a cap applies; less estimates billed; true-up = share after cap − estimates billed, positive meaning the tenant owes and negative a credit. Brightleaf Yoga: $1,075,000 × 8.00% = $86,000; less $74,000 = $12,000; no cap on this lease; less $10,800 = $1,200 owed.

Statements

One block per tenant, laid out as the tenant will read it, pulling every figure from the allocation and pool tabs by reference, never by typing: gross pool, exclusions as a total with the reasons available, the gross-up line, share and denominator, base-year deduction, cap effect, estimates billed, balance. The sample statement shows one layout; a workbook can produce the same lines.

Cap history

One row per tenant per year, permanent. Columns: year; controllable share actual; cap ceiling for the year; billed, which is the lesser of the two; unused room, which is ceiling − billed, where the cap is cumulative; and the next year’s ceiling, which depends on the mechanic. In words: simple from base, next ceiling = base-year share × (1 + cap × number of years since base); compounded from base, next ceiling = prior ceiling × (1 + cap); on prior year’s actual, next ceiling = prior year’s actual share × (1 + cap). On a $50,000 base-year controllable share with a 5% cap and actuals of $54,000, $58,500 and $64,000, the three mechanics give 2026 ceilings of $57,500, $57,881 and $61,425. This tab must never be rebuilt from scratch.

The checks

A workbook without checks is a calculator. Each of these is one cell that should read zero, and a reconciliation is not finished until all of them do.

The fourth check catches the most. Harbor Point’s six tenants occupy 80,000 of 100,000 RSF, so their shares sum to 80.00% and the gross shares sum to $1,075,000 × 80% = $860,000; the remaining $215,000 is the vacant space’s share, which the owner absorbs. A workbook whose shares sum to 100% has either used occupied area as the denominator without the leases saying so, or counted a tenant twice.

The seventh check is the base-year trap made into a cell. If 2026 is grossed up to 95% and 2023 was never grossed up, the stored and recomputed base-year shares differ, and Brightleaf Yoga pays the difference in methodology every year until the lease ends.

Harbor Point Plaza, run through the tabs

The whole chain, tab by tab, so the finished workbook can be tested against it.

Every check reads zero: the pool ties to $1,240,000, three N rows carry reasons, shares sum to 80%, gross shares sum to $860,000, and the statement balance equals the allocation true-up. The calculator runs the same chain on your own figures.

Where it breaks

A workbook built as above is a sound tool. These are the five points at which it stops being one, each with the failure it produces.

Cumulative caps need memory the workbook does not have

The cap history tab works as long as it is never rebuilt. The moment someone starts the 2027 workbook by copying the 2026 one and clearing the inputs, the cap history is either carried by hand or recomputed from base, and recomputing from base on a cumulative cap silently drops the unused room. Where the 2024 actual was under the ceiling, the 2025 ceiling carries that room forward; a rebuilt tab computes 2025 from base alone and bills the tenant less than the lease allows, or, with the room carried twice by mistake, more. The workbook does not know which year it is; it knows what is typed into it.

Leases are not uniform

The tenants tab has one cap mechanic column, one cap order column, one base-year column. The first lease that caps controllable expenses but carves insurance and utilities out of the cap needs the pool split into controllable and uncontrollable before the cap: a second gross-up subtotal, a second allocation column, a second cap history. The next such lease, with a different definition of controllable, needs a third. By the fourth, the allocation tab has a column per lease per rule, and the person who built it is the only one who can read it. The failure is not a wrong number; it is a workbook nobody else can check, which is the same thing eighteen months later.

Mid-year changes

A tenant who expands from 8,000 to 10,000 RSF on 1 July has a share of 8.00% for six months and 10.00% for six; a tenant who arrives on 1 October has a share for three months and a base year that may be a partial year, a first full year, or twelve months from commencement, as the lease decides. The tenants tab has one share per tenant per year. The workaround is two rows per tenant with the months weighted, and the failure is that the shares no longer sum to occupancy, so the check that catches the most is switched off to make the workbook balance.

The dispute arrives eighteen months later

A tenant’s auditor asks for the derivation of the 2025 statement in the spring of 2027. The 2025 workbook exists, but the ledger export on its pool tab was pasted as values, the exclusion reasons were in a column deleted to make the tab print, and the gross-up tab was overwritten when the 2026 workbook was started from the 2025 file. What is left is a statement and a true-up. The failure is a negotiation entered without evidence, as the dispute post puts it, and the usual outcome is a settlement at a figure neither side can derive.

The person leaves

Every one of the above is survivable while the person who built the workbook is still in the building, because they remember which column means what. When they leave, the successor opens eight tabs, finds a cap history whose formulas were replaced with values “to stop it changing”, and rebuilds the reconciliation from the leases, which takes a month, and from the books, which produces a different pool because the exclusion reasons went with the previous person. The property’s recovery history now has a seam at the handover, and every base-year tenant’s comparison crosses it for the rest of their term.

When a spreadsheet is still the right tool

A single property with a handful of tenants on similar leases — the same base-year convention, the same exclusions, caps that are absent or all of one mechanic — run by one person who will be there next year, is well served by the eight tabs above. The checks hold, the cap history has three rows, and the derivation is in the file. The honest test is the second tenant whose lease differs, the second property, or the first handover; any one of those is the point at which the workbook starts needing a workbook to explain it.

What a system changes

Three things, and they map onto the five breaks. The derivation is kept rather than reconstructed: every figure on the statement is traceable to the pool line, the lease term and the rule that produced it, and the period is sealed when the statement goes out, so the 2025 derivation in 2027 is a lookup. The recovery rules live on the lease rather than in a column — caps, base years, gross-ups, exclusions, recovery groups, each with its own mechanic — so the fourth non-uniform lease is a fourth lease, not a fourth column. And the pool is read from the books rather than pasted from them: Ledgerstone reads actual expenses from QuickBooks Online, scoped by class, location and account, and writes the CAM recovery results back, so the first check is where the pool came from.

Build the workbook first. It is the fastest way to learn which of the five breaks your portfolio will hit, and a reconciliation done by hand once is the best specification for whatever runs it next.

Related reading

• What a CAM reconciliation actually involves — the five steps the tabs follow, with the same example.
• The CAM reconciliation checklist — the dated steps, and the evidence to keep at each one.
• CAM caps: cumulative, non-cumulative and compounding — why the cap history tab must never be rebuilt.
• Gross-up clauses, explained with the arithmetic — the variable and fixed split the gross-up tab depends on.