Back to Blog

Replace Excel for CAM Reconciliation: What to Use Instead

By Angel Campa·Founder, CapVeri5 min read

If your CAM reconciliation process looks like this — export from Yardi, paste into a master spreadsheet, apply gross-up formula, copy to tenant-specific tabs, verify caps manually, check against estimated payments, generate statements — you're not alone.

And your process has a risk profile that most property controllers don't think about until something goes wrong.

Why Spreadsheets Work at First

For a 3-building portfolio with straightforward leases, Excel CAM reconciliation is genuinely fine. One controller, one spreadsheet, two hours per building. The calculation is simple enough to verify manually. If the formula is wrong, someone will catch it.

The problems emerge with scale — more buildings, more tenants, more complex lease structures, more people touching the spreadsheet.

The Ways Excel CAM Reconciliation Fails

Silent Formula Errors

Excel calculates whatever formula you give it. It doesn't know that cell B47 should reference the gross-up threshold from the lease database, not the hard-coded value someone typed three years ago. When the gross-up threshold in the lease changed from 95% to 90% at renewal and the spreadsheet wasn't updated, Excel happily continues calculating at 95% — producing a wrong answer that looks right.

This is the most dangerous class of error: a wrong number that's not obviously wrong. The spreadsheet balances, the format looks professional, and the statement goes out with a systematic error embedded in every row.

Version Control Collapse

At Q1 crunch, three controllers are working simultaneously across 30 buildings. Controller A has "Reconciliation_2025_v4_FINAL.xlsx". Controller B has "Reconciliation_2025_v5_FINAL_corrected.xlsx". Controller C has a version they downloaded from the shared drive last Tuesday.

Someone will generate statements from the wrong version. Someone will overwrite another controller's work. This is not a theoretical risk — it happens regularly in portfolios over 15 buildings.

No Audit Trail

When a tenant disputes a reconciliation and asks how the gross-up was calculated, Excel cannot tell you. You can show them the current spreadsheet, but you can't prove when the formula was set, whether it was modified during the reconciliation, or whether the version that produced the statement is the same version you're showing them now.

In a formal dispute or tenant audit, the absence of an audit trail is a significant liability.

Institutional Knowledge Risk

The controller who built the master spreadsheet in 2019 understands every formula, every conditional logic branch, every workaround for the three buildings that have unusual lease structures. When that person leaves — and they eventually leave — the knowledge leaves with them.

The new controller inherits a spreadsheet that works until it doesn't, with no documentation of why the unusual formulas are there.

Lease Term Enforcement

Excel doesn't know your leases. It applies the formulas you build. If your lease says the cap applies to controllable expenses only (excluding taxes and insurance), that restriction must be manually implemented in the formula. If someone updates the formula without understanding that nuance, the cap starts applying to the full expense pool — and the error persists until a tenant auditor finds it.

What to Use Instead

The Simplest Upgrade: ERP-Native Reconciliation

If you're already on Yardi, MRI, or AppFolio, the ERP's native reconciliation module is the minimum viable improvement over Excel. It maintains an audit trail, handles most standard calculations correctly, and integrates with your existing tenant data.

The limitation: ERP-native reconciliation doesn't validate whether the ERP's own configuration matches your lease terms. If your Yardi recovery pool was configured wrong in 2020, it's still configured wrong today — and the native reconciliation module will continue producing the wrong answer.

Independent Verification Layer

The most defensible approach for mid-market portfolios: keep your ERP for GL management and billing, add independent reconciliation software that reads your ERP's export and validates calculations against lease terms.

This approach:

  • Catches ERP configuration errors (the thing ERP-native reconciliation can't catch)
  • Provides an immutable audit trail independent of the ERP
  • Works with any ERP via CSV export — no integration required
  • Lets controllers review discrepancies instead of calculate from scratch

What this replaces: The Excel spreadsheets used to validate and calculate reconciliations. The ERP remains for everything else.

The Transition Process

Week 1: Export one building's GL from your ERP. Run it through the new software. Compare to your Excel output.

Week 2–3: Identify and explain every discrepancy. Most discrepancies fall into two categories: (1) legitimate errors in the Excel spreadsheet or ERP configuration, and (2) differences in methodology that need to be reconciled against lease terms.

Q1 Parallel Run: For the first full reconciliation cycle, run both the old Excel process and the new software in parallel. This surfaces any configuration issues before you're relying on the new system exclusively.

Q1 Next Year: Generate statements from the new system. Keep Excel as a check for the first two or three buildings, then retire it.

What the Transition Won't Fix

Replacing Excel doesn't automatically fix errors in your ERP configuration. If your Yardi gross-up threshold is wrong, that error will show up in the independent reconciliation software's discrepancy report — which is actually the point. The discrepancy report is how you find and fix ERP configuration errors.

Budget for 2–4 weeks of configuration cleanup in your first reconciliation cycle with the new software. There will be discrepancies. Most of them represent real errors that need to be corrected, either in the ERP configuration or in prior-period statements.

The Business Case

For a 30-building portfolio:

  • Current Excel process: 8 weeks, 4 controllers, high error rate discovered post-statement
  • Independent verification approach: 3 weeks, same 4 controllers, errors caught pre-statement

The direct time savings is significant. The indirect savings — in dispute management, staff time responding to tenant auditors, and reduced credit/refund exposure — is typically larger.

Related Resources