CAM reconciliation template: the sheets and columns you need

Most small landlords do the yearly CAM reconciliation in a workbook, and a good workbook is a fine way to do it. The trouble with most templates found online is that they handle the easy lease, one tenant, one share, no exceptions, and fall apart at the second lease that has a cap or an exclusion.

This guide describes a template that holds up: the sheets to build, the columns on each, the order of the calculation and the checks to run. It works in Excel or Google Sheets. You can build it in an afternoon, and you will be able to reuse it each year.

The sheets

Build one workbook per property, with five sheets.

1. GL: the ledger detail, pasted in.
2. Accounts: one row per ledger account, assigned to a pool.
3. Leases: one row per tenant with the rent roll data and the lease terms.
4. Calc: one block per tenant, step by step.
5. Summary: one row per tenant with the true-up, which feeds the letters.

Keep the sheets separate. The most common template failure is a single sheet where inputs, formulas and results are mixed together, so nobody can tell which cells are safe to change.

Sheet 1: GL

Paste the general ledger detail for the year. Columns:

Count the rows you set aside as out of period, and keep the number. It is the first thing to check if the totals later disagree.

Sheet 2: Accounts

One row per account that appears on the GL sheet.

Below the table, add a check cell: the sum of every pool total, plus not-recoverable, should equal the GL total. If it does not, an account is missing from this sheet.

Sheet 3: Leases

One row per tenant. The rent roll columns:

• Tenant, suite, square feet
• Lease start date and end date
• Estimates billed for the year (CAM, taxes, insurance), taken from your billing records rather than the lease

Then the lease terms, one column each, left blank when the lease does not use them:

• Share method: square feet, stated denominator or fixed percent
• Denominator or fixed percent
• Pools the tenant pays
• Excluded accounts (a list of account numbers)
• Base year amount or expense stop per square foot
• Cap type (none, non-cumulative, cumulative simple, cumulative compounding), cap percent, cap base year and the base amount
• Administrative fee percent, what it is charged on, and whether it sits inside the cap
• Gross-up occupancy level

Writing the terms in columns, instead of burying them in formulas, is the most useful habit in the template. When a lease changes, you change one cell, and you can see at a glance which tenants have which exceptions.

Sheet 4: Calc

Give each tenant a block of rows, in this order, and do not skip a step even when it is zero.

1. Pool totals less exclusions. For each pool, the Accounts total minus the tenant's excluded accounts.
2. Gross-up. For variable accounts, if the property's occupancy for the year is below the lease's level, multiply the variable amount by the level divided by actual occupancy. Never reduce an amount.
3. Share. The pool times the tenant's share, times the fraction of the year the lease was in effect (days in effect divided by days in the year).
4. Cap. Apply to the controllable pool only, unless the lease says otherwise. For a non-cumulative cap, the limit is the prior year's capped amount times one plus the cap percent. For a cumulative compounding cap, it is the base amount times one plus the percent, raised to the number of years since the base year.
5. Administrative fee. Percent times the base the lease names, inside or outside the cap as the lease says.
6. Base year or stop. Subtract the tenant's base-year amount, or the stop times square feet, from the total so far. Floor the result at zero.
7. Total due, then estimates billed, then true-up (positive is due from the tenant, negative is a credit).

The cap and the fee come before the base year here because leases usually define them on the tenant's share of the costs; the base year then says how much of that total the tenant pays. Some leases word it the other way, and for a lease with both a base year and a cap the two orders give different totals, so follow the lease and note the order on the sheet.

Keep one step per row and label each. When a tenant asks how a number was reached, you can point at a row.

Round once, and in a consistent place

Round each line to cents once, at the line, and add the rounded lines. Otherwise the letter's lines will not add to its total, which tenants notice.

Sheet 5: Summary

One row per tenant: share, each pool, cap effect, gross-up effect, administrative fee, total due, estimates billed, true-up. This is what you read before approving the reconciliation and what you copy into the letters.

Checks to build into the template

• Pool totals equal the GL total, less not-recoverable
• No tenant's share is above 100%, and the shares total no more than 100%
• Every lease with a cap has a base year and a base amount
• Every account on the GL sheet appears on the Accounts sheet
• The sum of the letters' lines equals the Summary total, to the cent

Add a column for last year's total due per tenant and a percent change. A tenant whose bill jumps by an unusual amount usually points to a mis-coded line.

A letter skeleton

Re: [Property name], [Tenant], [Suite]
Reconciliation of common area maintenance, real estate taxes and insurance for [period]

Your share: [share]% ([how it was found: square feet over gross leasable area, stated denominator or fixed percent])

[Pool]   Property total   Your share
[Pool]   [amount]         [amount]
Administrative fee ([percent]% of [base])   [amount]
Total due for the period   [amount]
Estimates billed   [amount]
Balance due / credit   [amount]

[Any cap or exclusion applied, in one sentence each]
Supporting detail is enclosed. Questions: [contact line]

When the workbook stops being worth it

A template works well for one or two properties with simple leases. It gets expensive when you add properties, because each new lease means another block of formulas to build and check, and because the whole workbook has to be rebuilt each year for the new data.

If that is where you are, CAM Rec takes the same structure, with the steps in the order above, and does it for you: it imports the ledger and rent roll, remembers the account-to-pool assignments and the lease terms, shows every calculation line, and produces a letter PDF and backup per tenant after you approve the numbers. The sample runs on fictional data with no sign-up, so you can compare its lines with a template of your own.

Related: CAM reconciliation spreadsheet vs CAM Rec on when a workbook stops being enough, the CAM reconciliation example to test your template against, and pro rata share calculation for the share column.

This is general information, not legal or accounting advice. Your lease governs every term above.