CAM Reconciliation: Build vs. Buy Analysis
The Spreadsheet Is Not Free
Excel doesn't charge per reconciliation. That makes it feel free. It is not.
The actual cost of spreadsheet-based CAM reconciliation is buried in three places: labor hours nobody tracks, errors nobody catches, and audit exposure nobody prices until it arrives.
A property controller running reconciliation for 10 NNN properties spends 200–600 hours per year on the process. At a fully loaded cost of $55–$75/hour for an experienced property accountant, that's $11,000–$45,000 per year in labor. Add the cost of errors — underbilling that reduces NOI, overbilling that triggers tenant audits — and the true cost of "free" spreadsheets is $40,000–$90,000 per year for a 10-property portfolio.
Purpose-built CAM software for the same portfolio typically costs $5,000–$25,000 per year.
What Spreadsheet Reconciliation Actually Looks Like
If you've done this, you recognize the workflow:
- Export the GL trial balance from your property management system (Yardi, MRI, RealPage)
- Paste it into an Excel template
- Manually classify each GL account as recoverable or non-recoverable
- Check each classification against each tenant's lease for exclusions
- Calculate the recoverable pool
- Look up occupancy data and apply gross-up (if applicable)
- Calculate each tenant's pro-rata share
- Check each tenant for cap provisions and apply caps
- Check for base year provisions and calculate the excess
- Subtract monthly estimates already collected
- Generate the tenant true-up amount
- Repeat for each property
Steps 3 through 10 are where errors enter. Each step requires a manual lookup against the lease abstract, a manual input into the spreadsheet, and a manual verification that the formula is correct. For a 20-tenant building, that's 20 iterations of steps 6–11, each with its own opportunity for a typo, a wrong cell reference, or a misread lease term.
The Hidden Costs of Excel
1. Labor That Doesn't Scale
| Portfolio Size | Hours per Property | Total Hours | Fully Loaded Cost ($65/hr) |
|---|---|---|---|
| 3 properties | 25 hrs | 75 hrs | $4,875 |
| 5 properties | 25 hrs | 125 hrs | $8,125 |
| 10 properties | 25 hrs | 250 hrs | $16,250 |
| 25 properties | 30 hrs* | 750 hrs | $48,750 |
| 50 properties | 35 hrs* | 1,750 hrs | $113,750 |
Hours per property increase at scale because cross-property consistency checks, cap tracking across years, and QA reviews take proportionally more time.
These hours are spent during Q1, when the accounting team is also closing the books, filing tax workpapers, and preparing budgets. Reconciliation competes directly with other critical deadlines.
2. Error Rates That Compound
Studies of spreadsheet accuracy in financial modeling consistently find error rates of 1–5% of cells containing formulas. In CAM reconciliation, the relevant errors are:
| Error Type | Frequency | Typical Impact per Occurrence |
|---|---|---|
| Wrong cell reference in pro-rata calculation | Common | $2,000–$15,000 per tenant |
| Gross-up applied to fixed expenses | Common | $10,000–$50,000 per property |
| Cap escalator not carried forward | Common | $1,000–$5,000 per tenant per year |
| Excluded expense included in pool | Moderate | $5,000–$25,000 per property |
| Prior year template overwritten | Moderate | Full rebuild required (8–20 hours) |
| Denominator not updated after remeasurement | Rare | Affects every tenant in the building |
A single gross-up error on a 200,000 SF building can exceed the annual cost of CAM software for the entire portfolio. The error doesn't announce itself — it sits in the spreadsheet producing the wrong number until a tenant auditor finds it.
3. Key-Person Risk
In most organizations, one person built the reconciliation spreadsheet and one person (often the same person) runs it. If that person leaves, the replacement inherits a workbook with no documentation, custom formulas that reference named ranges from three years ago, and conditional logic that nobody else understands.
The typical cost of a key-person departure during reconciliation season: 2–4 weeks of delayed statements plus $5,000–$15,000 in consultant fees to reconstruct the process.
4. No Audit Trail
When a tenant auditor asks "what was this cell's value before the adjustment on March 15?", Excel cannot answer that question. There is no change log. There is no version history that tracks individual cell edits. The controller's best answer is "I believe it was..." — which is not a defensible position in an audit.
Purpose-built software logs every change, every user, every timestamp. This is not a feature — it is a requirement for audit defense.
5. Year-Over-Year Tracking Failures
Cumulative CAM caps require carrying a balance from year to year. Base year provisions require referencing a fixed historical amount. When the controller starts a new spreadsheet each year — or copies last year's template and overwrites it — historical data gets lost or corrupted.
The cost: unrecoverable cap bank balances, base year errors that compound annually, and no ability to verify that this year's starting point matches last year's ending point.
What Purpose-Built Software Does Differently
The fundamental difference is not automation — it's validation. Software doesn't just calculate faster. It checks each calculation against the lease terms and flags discrepancies.
| Capability | Excel | CAM Software |
|---|---|---|
| GL import and classification | Manual paste + manual tagging | Automated import with saved mappings |
| Lease term enforcement | Controller checks each tenant manually | System validates against stored lease abstracts |
| Gross-up calculation | Manual formula, manual occupancy input | Automated with occupancy data feed |
| Cap tracking (cumulative) | Manual year-over-year carry-forward | System maintains cap bank automatically |
| Pro-rata share validation | Manual check against rent roll | System cross-references lease and rent roll |
| Error detection | Peer review (if time allows) | Rules engine flags anomalies automatically |
| Audit trail | None | Full change log with user, timestamp, and prior values |
| Multi-year consistency | Manual template management | Built-in historical data |
| Time per property | 20–40 hours | 2–8 hours |
Decision Framework
Not every portfolio needs software. Here's a framework for deciding.
Stay With Excel If:
- You have 1–3 properties with simple NNN leases
- All tenants have the same lease structure (no mix of NNN, gross, modified gross)
- No gross-up clauses in any lease
- No cumulative cap provisions
- The controller who built the spreadsheet is not planning to leave
- You have a robust QA process (second person reviews every calculation)
- You are comfortable with the audit risk
Move to Software If:
- You have 4+ properties
- Leases include gross-up clauses, cumulative caps, or base year provisions
- Multiple lease structures across the portfolio
- You've had a tenant audit finding in the last three years
- Reconciliation takes more than 100 hours per year total
- You've experienced key-person turnover during reconciliation season
- Your asset manager or ownership wants an audit trail
The Gray Zone: 3–5 Properties
This is where the decision is closest. At 3 properties, a disciplined controller with a well-built spreadsheet can produce accurate reconciliation in 60–75 hours per year. The risk is manageable if the leases are straightforward.
At 5 properties, the labor is 125+ hours, cross-property consistency becomes harder to verify, and the probability of at least one material error across all properties is high enough that the risk cost exceeds the software cost.
Cost Comparison
| Factor | Excel (10 Properties) | CAM Software (10 Properties) |
|---|---|---|
| Software cost | $0 (already licensed) | $5,000–$25,000/year |
| Labor (reconciliation) | $16,250/year (250 hrs × $65) | $5,200/year (80 hrs × $65) |
| Labor (QA/review) | $3,250/year (50 hrs × $65) | $1,300/year (20 hrs × $65) |
| Error-related costs* | $8,000–$30,000/year | $1,000–$5,000/year |
| Audit defense costs | $4,000–$8,000 per audit | Minimal (audit trail exists) |
| Key-person risk | $5,000–$15,000 per event | Low (process is in the system) |
| Total estimated annual cost | $31,500–$72,250 | $12,500–$36,300 |
Error-related costs include underbilling (lost NOI), overbilling refunds, and time spent correcting discovered errors.
The software pays for itself when it eliminates 170+ hours of labor and prevents one or two material errors per year. For most portfolios above 5 properties, this happens in year one.
The Middle Ground: Automated Spreadsheets
Some teams try to split the difference — keeping Excel but building macros, VBA scripts, or Power Query connections to automate parts of the workflow. This reduces labor but does not address the core problems:
- Macros break. VBA code is fragile across Excel versions and requires maintenance by someone who can program.
- No validation layer. The spreadsheet still cannot check itself against lease terms.
- No audit trail. Macros execute and overwrite — there is no log of what changed.
- Higher key-person risk. Now you need someone who understands both the reconciliation process and the VBA code.
Automated spreadsheets typically reduce labor by 30–40% while increasing key-person risk. They occupy the worst position in the cost-risk matrix: still expensive to maintain, still unauditable, and now dependent on custom code that a replacement hire may not be able to support.
How to Evaluate the Switch
If you're considering moving from spreadsheets to software, here's what to evaluate first:
-
Count your true hours. Track time spent on reconciliation for one cycle. Include GL prep, lease lookups, calculation, QA review, statement generation, and responding to tenant questions. Most teams underestimate by 40–60%.
-
Count your errors. Review tenant audit findings from the last three years. Calculate the dollar impact. Add any self-caught errors that required re-issuing statements.
-
Price the risk. Multiply your average error cost by the probability of occurrence. If tenant auditors find errors in 60–80% of statements they review, and you have no self-audit process, assume your error rate is at least 15–20%.
-
Compare total cost of ownership. Software license + reduced labor + reduced errors vs. current state. Include the value of the audit trail and the reduction in key-person risk.
-
Request a pilot. Run one property through both your spreadsheet and the software candidate. Compare outputs, time invested, and findings.
How CapVeri Fits
CapVeri is built specifically for the spreadsheet-to-software transition. It ingests the same GL exports you already pull from Yardi, MRI, or RealPage — no API integration, no IT project, no system migration.
Upload a CSV. CapVeri classifies expenses, applies gross-up, checks caps, validates pro-rata shares, and flags every finding against lease terms. The output includes the reconciliation calculation and a finding report showing every discrepancy with its dollar impact.
For teams currently running reconciliation in Excel, CapVeri replaces the most error-prone and time-consuming steps while preserving your existing workflow. You keep your property management system. You keep your GL structure. You just stop doing the lease-to-billing math by hand.
Related Resources
- Self-Audit Your CAM Billing — The manual QA process CapVeri automates
- CAM Software Evaluation Checklist — 25 criteria for comparing solutions
- CAM Reconciliation Timeline — How software compresses the timeline
- Recovery Ratio Analysis — Measure whether your current process is working
- Data Migration Off Excel — How to move historical data when you're ready