Reconcile each tenant and period using opening receivables plus charges, less credits and rent cash applied. Keep security deposits and unapplied cash outside that calculation until their treatment is established.
A rent-roll check becomes more useful when it identifies the exact tenant, period and unexplained balance. This workbook reconstructs closing receivables and compares that result with the ledger. It does not certify a lease, collectibility or lender acceptance.
The OCC’s income and file-documentation discussion supports examining operating evidence. The reconciliation below is an original control that can accompany that evidence. It is deliberately narrower than a complete lease or acquisition audit.
Define the period before comparing totals
Use opening receivables immediately before the selected period and include only charges, credits and applied rent cash within it. Keep the same tenant identifiers across the rent roll, ledger and receipts schedule. If an amount was moved between tenant accounts, document both sides of the transfer.
Use positive amounts for charges and credits in their respective columns. The formula subtracts credits. Entering a negative credit would increase receivables and reverse its intended effect. Keep the source ledger unchanged and make the analytical adjustment in a documented reconciliation.
Blank working file
tenant_id,period_end,opening_ar_usd,charges_usd,credits_usd,rent_cash_applied_usd,calculated_closing_ar_usd,ledger_closing_ar_usd,unexplained_difference_usd,evidence_ref
T001,,,,,,,,,Field instructions
| Field | Evidence to identify | Common error |
|---|---|---|
| opening_ar_usd | Prior period’s closing balance on the same basis | Starting from a later aged report |
| charges_usd | Period billings and their lease support | Equating everything billed with collectible income |
| credits_usd | Authorized concessions, corrections or write-offs | Subtracting a negative credit twice |
| rent_cash_applied_usd | Receipts actually allocated to rent/charges | Including a security deposit as rent |
| ledger_closing_ar_usd | Observed period-end ledger balance | Replacing the observed value with the calculated result |
| unexplained_difference_usd | Observed less reconstructed balance | Treating zero difference as proof of collectibility |
The spreadsheet calculates the two control columns. Its CSV contains the completed values so the example is readable without spreadsheet software. Missing numeric inputs produce “n.a.” in the workbook, while an explicit zero is a valid input.
Completed hypothetical reconciliation
tenant_id,period_end,opening_ar_usd,charges_usd,credits_usd,rent_cash_applied_usd,calculated_closing_ar_usd,ledger_closing_ar_usd,unexplained_difference_usd,evidence_ref
T001,2026-09-30,2000,10000,1000,9000,2000,2500,500,LEASE-1;LEDGER-1;CASH-1
T002,2026-09-30,0,6000,0,6500,-500,-500,0,LEASE-2;LEDGER-2;CASH-2Tenant T001 begins with $2,000 due. Adding $10,000 of charges, subtracting a $1,000 documented credit and $9,000 of applied rent cash produces $2,000 closing receivables. The ledger says $2,500, leaving a $500 unexplained difference. Do not erase it with an unsupported adjustment.
T002 has $6,000 of charges and $6,500 applied cash. The calculated negative $500 balance agrees with the ledger. That is a credit balance requiring classification; it is not automatically negative bad debt or additional recurring revenue.
Now assume the bank received $18,500 from these tenants during the period: $15,500 applied rent cash plus a separately identified $3,000 security deposit. The bank can reconcile perfectly while rent cash is only $15,500. The deposit belongs in its own liability and cash-control reconciliation under the actual documents and applicable requirements.
Investigate the residual without manufacturing income
For the $500 discrepancy, examine posting dates, prior-period adjustments, misapplied receipts, duplicate charges and unapplied cash. Record the evidence and correction owner. If the ledger used a different cutoff, reconcile that timing difference explicitly.
A zero arithmetic residual still leaves substantive questions. A billed charge may lack lease support; a credit may be disputed; a receipt may relate to an earlier period. The control identifies whether the roll-forward works, not whether every component should enter underwritten income.
Transfer findings to underwriting
Carry supported recurring amounts into the cash-flow analysis with the relevant period and classification. Keep old arrears, prepaid rent, deposits and nonrecurring settlements separately identifiable. Annualizing a cash receipt without understanding its period can distort the lending case.
Retain the completed reconciliation beside the source files and a list of open exceptions. When a corrected ledger arrives, preserve the old version and show which residual changed. A lender can then distinguish better evidence from a change in the requested underwriting treatment.
Sources and evidence
- OCC — Commercial Real Estate LendingPrinted pages 42–43: Income-Generating Capacity of CRE; page 51: file documentation · Checked 2026-10-21 · Handbook version 2.0 published March 2022. Supervisory context; the template and all example values are original optional working aids, not required lender forms.
Source dates and applicability matter. Report an error or a changed source.