Commercial Real Estate & Property Accounting
Commercial Lease CAM Gross-Up & Expense-Stop Formula
Gross up occupancy-sensitive variable operating expenses Var_OpEx * MAX(1, GrossUp_Cap / Actual_Occ) without inflating fixed costs, and compute pro-rata tenant expense-stop recoveries.
Avoid the two classic CAM audit errors: NEVER gross up fixed costs, and NEVER scale down above 95%!
In multi-tenant office leases with a Base-Year Expense Stop S_stop and Tenant Pro-Rata Share s_ten, variable operating costs (janitorial, floor HVAC, water/trash) drop when building occupancy Occ_act falls below stabilized GrossUp_Cap (typically 95% or 100%). Without a BOMA Gross-Up Clause, a 60%-occupied tower artificially depresses total building OpEx below S_stop — forcing the landlord to subsidize occupied tenants’ variable cost inflation! Correct CAM accounting splits Fixed_OpEx (security, roof, insurance — NEVER grossed up!) from Variable_OpEx, grossing up Variable_OpEx ONLY when Occ_act < GrossUp_Cap via Var_GrossUp = IF(Occ_act < GrossUp_Cap, Var_OpEx * (GrossUp_Cap / Occ_act), Var_OpEx), then billing Tenant Recovery = s_ten * MAX(0, Fixed_OpEx + Var_GrossUp - S_stop)!
Verify on Fixed_OpEx = 400,000 (B2), Actual_Var_OpEx = 180,000 (B3), Actual_Occupancy = 0.60 (B4 = 60%), GrossUp_Cap = 0.95 (B5 = 95%), Expense_Stop = 600,000 (B6), and Tenant_ProRata = 0.10 (B7 = 10%): unadjusted total OpEx is 580,000 (< 600,000 stop -> 0 recovery!), whereas 95%-grossed-up Variable OpEx is 180,000 * (0.95 / 0.60) = 285,000.00, Grossed-Up Total Pool is 685,000.00, and Tenant Pass-Through is 0.10 * (685,000 - 600,000) = 8,500.00! Test 98% occupancy (B4 = 0.98 > 0.95) to confirm Variable OpEx stays at actual 180,000.00 (never scaled down!)!
| CAM Reconciliation Line (60% Occupied, 95% Gross-Up) | Formula | Verified Output |
|---|---|---|
| Fixed Operating Expenses (Unadjusted) | Fixed_OpEx | 400,000.00 |
| Grossed-Up Variable Operating Expenses | IF(Occ < Cap, Var * (Cap / Occ), Var) | 285,000.00 (vs 180,000 Actual) |
| Total Reconciled CAM Expense Pool | Fixed_OpEx + Grossed_Up_Var | 685,000.00 (vs 600,000 Stop) |
| Tenant 10% Expense-Stop Recovery | 0.10 * MAX(0, 685,000 - 600,000) | 8,500.00 |
BOMA-Compliant Grossed-Up Building Operating Expense Pool Formula
Validate Fixed, Var, Stop >= 0 and occupancy proportions 0 < Occ_act <= 1, 0 < GrossUp_Cap <= 1, 0 < s_ten <= 1.
=LET(fx_op, B2, vr_op, B3, oc_ac, B4, gu_cp, B5, IF(OR(fx_op < 0, vr_op < 0, oc_ac <= 0, oc_ac > 1, gu_cp <= 0, gu_cp > 1), "Invalid CAM Input", LET(vr_gu, IF(oc_ac < gu_cp, vr_op * (gu_cp / oc_ac), vr_op), ROUND(fx_op + vr_gu, 2))))Pro-Rata Tenant Expense-Stop Pass-Through Billing Formula
In a shared Arte Sheets lease-audit workbook, leave a whole-cell comment on B2 confirming property insurance is classified in Fixed_OpEx so no landlord gross-up inflates non-occupancy expenses!
=LET(fx_op, B2, vr_op, B3, oc_ac, B4, gu_cp, B5, st_bs, B6, sh_tn, B7, IF(OR(fx_op < 0, vr_op < 0, oc_ac <= 0, oc_ac > 1, gu_cp <= 0, gu_cp > 1, st_bs < 0, sh_tn <= 0, sh_tn > 1), "Invalid CAM Input", LET(vr_gu, IF(oc_ac < gu_cp, vr_op * (gu_cp / oc_ac), vr_op), ROUND(sh_tn * MAX(0, fx_op + vr_gu - st_bs), 2))))Try the example
Use the original values or change them to test your understanding.