AI Rent Roll Analysis for Multifamily: Reconcile to the Bank

Export the month-end rent roll, the payment report and the bank statement, remove tenant names, and give them to an AI assistant that runs code, such as ChatGPT or Claude. Have it clean the rent roll, tie the totals back to your system, summarise vacancy and arrears, then match payments to deposits and list every difference it cannot explain.

Let the assistant do the arithmetic by writing and running code on your files, never by reading a long table in chat, and make it reproduce your property system's own totals before any other number counts. Everything it cannot match goes on a list for you to decide on, and it should never invent a reason. The property in the worked example is an invented 48-unit building. Every figure is made up and adds up.

Follow me on Instagram@sagnikteaches

Gather four files and write down the control totals

Use one month for everything, and collect these four files for it:

Connect on LinkedInSagnik Bhattacharya

1. The rent roll as at the last day of the month, exported as Excel or CSV from your property-management system. A PDF printed from the screen is a last resort, because ChatGPT's own help page warns that it may not reliably extract exact values from image-based tables, and it advises uploading a spreadsheet or text-based file when exact values matter.
2. The receipts or deposit report for the month, with every payment posted, its date, unit, amount, method and batch.
3. The tenant ledger or aged receivables report, showing what each unit owes at month-end and how old it is.
4. The bank statement for the operating account as CSV or a text PDF, plus the statement for any separate account that holds security deposits.

Before you upload anything, copy the totals from your system's own reports onto a sheet of paper. These control totals are the figures the AI has to reproduce, and they are your proof that the clean-up kept every row. For the example property they are:

Subscribe on YouTube@sagnikteaches

Redact first, then choose an assistant that can run code

Tenant names, phone numbers and bank details are personal data. Replace names with unit numbers before upload, using find-and-replace or a short script, and delete account numbers from statements. On a personal plan, check the privacy setting that controls whether your chats improve the models. The ChatGPT setting is called "Improve the model for everyone" under Data controls, and its Temporary Chat is not used for training and may be kept for up to 30 days. Business plans, which include ChatGPT Business and Claude Team at about $20 to $25 a seat a month, do not train on your content by default, so a property company should prefer one of those.

The best AI for rent roll analysis is the one that writes and runs code on the file itself. ChatGPT does this in a notebook environment and accepts .xls, .xlsx and .csv uploads. Claude runs code on uploaded files, can build Excel files with formulas, and takes files up to 30 MB. A model that only reads a pasted table drops rows and adds badly, while code does not. If you would rather work inside the workbook, the Claude for Excel add-in tutorial covers that route. OpenAI tells you to review the code, outputs and assumptions, and Claude's help page says outputs may need review and refinement. The rest of this tutorial is a way of doing exactly that.

Stage 1: profile and clean the rent roll before any analysis

Ask for a diagnosis first and the fixes second. An assistant that cleans silently will also lose rows silently. Typical problems in a property-system export, all of which an AI can find in seconds:

• A header split over two lines, so the columns get the wrong names.
• Subtotal rows such as "Floor 1 Total" sitting between units.
• Unit numbers written as 0101 in one report and 101 in another, so the ledger will not join to the rent roll.
• Money stored as text with dollar signs and commas, and credits shown in brackets, for example (125.00).
• Two date formats in one column, with day and month in an order the code has to guess.
• VACANT typed in the tenant column with a blank rent.
• A unit with two occupants listed on two lines, which counts the unit twice.

I am uploading a rent roll export (rent_roll_march.xlsx) with tenant names replaced
by unit numbers. Do not change any values yet. Step 1: profile the file and list every
structural problem you find (split headers, subtotal rows, duplicate units, text
numbers, mixed date formats, bracketed credits, vacant markers) with row numbers.
Step 2: propose a fix for each. Step 3: after I approve, apply the fixes and report
row counts before and after. Step 4: compute these control totals and show them next
to mine: units 48, occupied 45, market rent $69,900, contract rent on occupied units
$63,860, concessions $600. If any total differs, stop and tell me which rows to
look at. Dates are day first. Credits in brackets are negative.

The control totals matter more than anything else in the prompt. In the example, the first pass reported 51 units instead of 48, because three floor-subtotal rows had been read as units. Once the subtotal rows were excluded, the unit count and the rent totals matched, and the file was ready. Without the control totals, every later figure, including occupancy, would have carried the error. For general spreadsheet clean-up outside rent rolls, the tutorial on cleaning messy data goes further.

Stage 2: the vacancy and delinquency summary an owner will read

Before the prompt, fix the terms, because assistants use them loosely and often report a single "occupancy" figure. Gross potential rent is the market rent of every unit as if it were full. Vacancy loss is the market rent of the empty units. Loss to lease is the gap between market and contract rent on occupied units. Concessions are free rent and discounts. Net rental income is what is left. Physical occupancy counts units, and economic occupancy compares net rental income with gross potential rent.

Using the cleaned rent roll and the aged receivables report, produce:
(1) a rent waterfall: gross potential rent, vacancy loss, loss to lease, concessions,
net rental income, other recurring charges, late fees, total billed;
(2) physical occupancy (occupied units over total units) and economic occupancy
(net rental income over gross potential rent);
(3) delinquency by age bucket (0-30, 31-60, 61-90, over 90 days) with unit counts and
balances, plus the five largest balances by unit;
(4) lease expirations by month for the next six months, and the number of
month-to-month units.
Show the formula for each line. Flag any line that does not tie to my control totals.
Treat credits as negative balances and leave them out of delinquency.

Here is an illustrative result for the 48-unit property, the waterfall first:

Physical occupancy is 45 of 48 units, or 93.75 per cent. Economic occupancy is $63,260 over $69,900, or 90.5 per cent. The gap between the two is the number owners tend to miss: about 3.2 points of gross potential rent are being given away through contract rents below market and concessions, not through empty units. If the assistant gives you one occupancy figure, ask which of the two it is.

As exported, balances of $4,085 are 6.1 per cent of the $67,415 billed, and 11 of the 45 occupied units owe something. Unit 214 shows a $1,425 credit, which the prompt keeps out of delinquency. The largest balances are unit 301 at $720, unit 118 at $650, and units 112 and 215 at $450 each. Stage 3 will show that this report understates what is owed. The expiry view says six leases end in April, four in May and nine in June, which is 19 of 45 leases, or 42 per cent, in one quarter, and two units are month-to-month. That is the number to take to a renewal plan, because nine June expiries at once will test the vacancy figure.

The delinquency list is also a work list for follow-up. For reminders and payment-plan messages, the tutorial on automating rent collection and arrears flags picks up where this summary stops.

Stage 3: tie billed rent to payments, then payments to the bank

Run two bridges, in this order. The first goes from what you billed to what tenants paid and ends at the balances owed. The second goes from the payments your system posted to the credits on the bank statement. A difference in either bridge is a posting error, a timing item or a missing record, and the bridge has to say which. Start with the first:

The two corrections come from the second bridge, so the order of work is to build both before you trust either. The system's own ageing report said $4,085 was owed. The true figure is $5,235, which is $1,150 higher, because a returned cheque was never reversed. Delinquency of 7.8 per cent of billed, not 6.1, is the number to report. Now the prompt for the second bridge:

Reconcile payments to the bank. Inputs: the receipts report (payments posted in March
with unit, date, amount, method and batch) and the operating account statement (CSV).
Rules: (1) take out security deposits and show them as their own line; (2) group
online payments into settlement batches using the processor report, and match each
batch to one bank credit net of its fee; (3) match cheque and money-order deposits one
to one on exact amount, within three business days of the posting date; (4) payments
posted on the last two business days of March may settle in April, and the last two
days of February may have settled in March, so list those as timing items; (5) never
force a match: anything left over goes under "unmatched in system" or "unmatched in
bank" with no guess at the cause; (6) also scan the bank debits for returned items and
match each to an earlier receipt. Output a bridge from total payments posted to total
bank credits, every line labelled timing, fee, deposit held, error, other income or
unexplained, and show that it ends at the statement total of $65,732.

An illustrative result, with a check on every line:

The first-pass gap was $71,015 less $65,732, which is $5,283. The bridge assigns every dollar of it, and the unexplained line is $0. The debit side tells the rest of the story: the bank statement shows a $1,150 debit labelled returned item on 9 March, which matches a cheque from unit 226 posted on 3 March and still showing as paid. That is why the ageing report understated what was owed.

Hold the matching to exact cents. A date window is fair, because payments take days to settle, but an amount tolerance is not: a payment that is 50 cents out is a different payment, and a loose match is how the duplicate on unit 214 would have been paired with the genuine $1,425 and hidden. For more on the mechanics of matching, the tutorial on automating bank reconciliation with AI covers match rules in general, and the guide to a reconciliation that will not balance helps when the bridge refuses to close.

Stage 4: an anomaly list you can act on this week

Once the data is clean and both bridges close, ask for exceptions. The useful anomaly list is short, specific to a unit and states the evidence, so that you can check each line in a minute. The prompt gives the assistant the rules to test and bans it from guessing causes:

Scan the cleaned rent roll, the ledger and the reconciliation output for anomalies.
Check at least: duplicate payments (same unit, amount and date); payments with no
matching charge; bank debits that match an earlier receipt (returned items); occupied
units with rent under 50 per cent or over 110 per cent of market; expired leases on
occupied units; move-ins this month with no deposit recorded; vacant units that still
carry charges; credit balances over $500; the same name in two units. Return a table:
unit, what you found, the rows that show it, the dollar effect, confidence (certain,
probable or possible) and the next step I should take. Do not state a cause unless the
data shows it. Write "unexplained" instead.

For the 48-unit property, an illustrative first list looks like this:

Six lines, two of them cash errors worth $2,575 between them, one missing charge, one lease admin gap, one possible data-entry error and one deposit gap. Sort by dollar effect and confidence, work the certain ones first, and ask the property manager about the rest by unit number. Units 214 and 226 would never have appeared in the system's own delinquency report, which is the reason to run a reconciliation at all.

Check the assistant's work before you sign anything off

Treat the AI's output as a draft prepared by a fast junior who never gets tired and sometimes misreads a column. Six checks, in this order:

1. All eight control totals are reproduced to the dollar, with nothing described as "approximately".
2. Both bridges close: the first ends at the corrected balances owed, the second at the bank statement total.
3. The unexplained line is $0, or it is itemised with the name of the person who will chase it.
4. Re-perform five units by hand from the source reports: one vacant, one in the oldest delinquency bucket, one with a concession, one that moved in this month and one picked at random. Five checks take about ten minutes and catch whole classes of error.
5. Ask for the code or the formulas and read the filters. How does it decide a unit is vacant: the word VACANT, a blank tenant or a zero rent? Which rows does it exclude? How does it parse dates?
6. Save the prompts, the code and the outputs in a dated folder, so next month starts from the same method and an accountant can retrace it.

Four mistakes to expect, shown as illustrations of how they surface:

• Dates read the wrong way round. Leases ending on dates such as 03/04/2027 were read as 4 March, not 3 April, so the April expiry count came back as 3 against the six in the system's own report. The mismatch exposed it, and the fix was to state the date order in the prompt.
• A credit treated as a debt. Unit 214's $1,425 credit was added to delinquency as if it were owed, inflating arrears by $1,425. The delinquent-unit count came back as 12 against the system's 11, and "credits are negative and excluded" went into the prompt.
• A made-up explanation. The assistant called the $410 deposit a "likely tenant refund". The bank memo said laundry service. The rule that fixed it was to label unmatched items unexplained and read the memo before assigning a cause.
• A loose match that hid an error. With a tolerance on amounts, the duplicate posting was paired with a genuine payment and disappeared. Exact amounts and a date window fixed it.

A monthly routine and a realistic time budget

The first run is slow because you are fixing exports and tuning prompts. After that the work is mostly checking. These are planning figures for a property of this size, not measurements, so adjust them after your first two months:

That is about three hours the first time and under an hour afterwards. A $20-a-month personal plan is a reasonable place to start, though file-heavy sessions use more of Claude's usage limit, and a business plan adds the no-training default. Run it within the first week of each month, before the owner report goes out, so errors such as a returned cheque are fixed while the tenant can still be reached. When the routine settles, a landlord's monthly bookkeeping routine shows where this check fits among the other month-end jobs.

Using the same method on a rent roll you are about to buy

A seller's rent roll is a claim, and bank deposits are the evidence. Ask for the rent roll with the last three to twelve months of operating-account statements and the receipts reports, then run the same two bridges. Rent roll income that the deposits do not support is a negotiation point, and so is a pattern of the same missing deposits month after month. Add three checks that only matter in a purchase: concessions given to fill vacant units, leases that were signed in the month of the sale, and rents far above the market column. The seller's explanation for each should arrive in writing. Leases, tenant confirmations and the legal side belong with a qualified adviser, and the first-pass underwriting tutorial shows what to do with the cleaned figures once they hold up.

When a dedicated rent-roll tool earns a test

Tools built only for rent rolls mostly solve one problem: pulling rows out of PDFs that sellers, brokers and lenders send in a different layout every time. If you review such documents regularly, a dedicated extractor may save retyping. I cannot vouch for any of them without your documents, so run a test, not a demo:

• Feed it a rent roll whose totals you already know and compare the unit count, the market rent total and the contract rent total.
• Include a messy one, with subtotals, vacant units and credits in brackets, and see what it does with them.
• Ask where uploads are stored, for how long, and whether they train models.
• Check that it exports to Excel or CSV so that the bank reconciliation does not need retyping.
• Price it per document and per month against a $20 plan that does the rest of the work above.

Whatever you choose, the control totals do the real work. An extractor that cannot reproduce a total you can see on the page is not ready for an investment decision.

Questions about AI and rent roll reconciliation

Can ChatGPT or Claude read a PDF rent roll?

They can, but OpenAI's own help page warns that ChatGPT may not reliably extract exact values from image-based tables. Export the rent roll from your property system as Excel or CSV where you can. If you only have a PDF, convert it, then confirm the unit count and the total rent against the figures printed on the report before you trust any other number.

Should security deposits be part of the reconciliation?

Reconcile them separately. A deposit is money you hold for the tenant, not income, so it should not appear in rent collected. If deposits go into the operating account, they will inflate the deposits you are trying to match, and the bridge should list them as their own line and remove them.

How close should the reconciliation get?

Every dollar of difference should land in one of four buckets: timing, fees, a known error, or unexplained. The unexplained bucket should be empty or sit on a list with a named person to chase it. A small unexplained difference left unlisted tends to grow, so treat the target as zero unexplained rather than a tolerance.

Is it safe to upload tenant names to an AI assistant?

Avoid it. Replace names with unit numbers and remove account numbers before upload, and check the privacy setting on a personal plan that controls whether chats improve the models. Business plans from the major assistants do not train on your content by default, which is a better home for property data than a free personal account.

Further reads

• Best AI for Real Estate Asset Management: Reports and Lease Abstracts — Turn the cleaned numbers into reports for owners and lenders.
• AI Accounting for Ecommerce: Reconciling Payouts and Fees — The same batch-and-fee matching for card payouts.
• AI Real Estate Investment Analysis: Vet a Rental Deal Step by Step — Use a clean rent roll to vet a rental deal.
• AI Software for Landlords: Rent, Maintenance and Reminders Compared — Compare software that already builds rent rolls.
• ChatGPT for Accounting With Excel: Seven Tasks, Each With Its Check — Seven spreadsheet tasks, each with its own check.
• Best AI Tools for Commercial Real Estate Brokers, Compared by Job — A job-by-job comparison of AI tools for commercial brokers, with verified costs, a worked OM example and three stacks by team size.
• AI Due Diligence on Real Estate: Read Leases, Surveys, Searches — Upload the lease, survey and searches, build an issue list, rate the red flags and arrive at the solicitor with sharper questions. It never replaces advice.
• Fund Administration Software for a Small Administrator: What AI Adds — Where AI helps a small fund administrator (statements, reconciliations, review notes) and which checks must stay with a named human reviewer.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: OpenAI help page on data analysis with ChatGPT, Claude help page on creating and editing files, ChatGPT and Claude plan and privacy pages as listed in facts checked in October 2026. The 48-unit property, its rent roll and bank figures are invented for illustration.