AI Commercial Real Estate Underwriting: A First-Pass Model

Give the AI the rent roll and the operating statement to turn into clean tables, build the pro forma in a spreadsheet, and use the AI again to challenge your vacancy and cap-rate assumptions. Then test the result in a sensitivity table. It produces a screening view of value, not an appraisal, and the AI never supplies the cap rate.

The usual trap is one income figure quoted three ways. The seller's operating statement leaves out a management fee and reserves, the rent roll shows income the statement does not, and the asking price capitalises a building that is fully let. In the six-suite example below, the same property is worth about $3.36 million on today's income and $3.92 million fully let, against a $3.95 million ask.

Follow me on Instagram@sagnikteaches

Six suites, one asking price, three versions of income

An illustration with invented figures: a single-storey building of 18,000 square feet with six suites, offered at $3,950,000 (about $219 per square foot). Every lease is triple-net, meaning each tenant pays its share of property tax, insurance and common-area costs on top of base rent. This is the rent roll once cleaned:

Connect on LinkedInSagnik Bhattacharya

Three facts from the roll shape everything that follows. Of the 18,000 square feet, 15,000 are let, so the building is 83% occupied. The weighted average rent on the let space is $20.34 per square foot and the median is $21.00, which makes the $20.00 asking rent on suite 6 look sensible until you read the broker's note that it has been marketed for 14 months without a taker. And suites 3 and 5 cover 5,400 square feet, 30% of the building and 32.7% of in-place base rent, with one lease ending in March 2027 and the other already month to month.

Subscribe on YouTube@sagnikteaches

Cleaning the rent roll: a prompt, its output and the fix

Rent rolls arrive as PDFs exported from property software, and the columns rarely survive a copy and paste. Ask the assistant to rebuild the table, and build the checks into the same prompt so it has to show its working.

I am underwriting a small commercial building. Below is the seller's rent roll, pasted from a PDF.
Return one table with these columns only: Suite, Use, Square feet, Annual base rent, Rent per square foot per year, Lease type, Lease end (month and year), Notes.
Rules:
- Copy numbers exactly as written. Do not round or estimate.
- If a value is missing or unclear, write MISSING and say why in Notes.
- If a date could be read two ways, give both readings in Notes.
After the table give: the number of suites, total square feet, total annual base rent, and every row where annual rent divided by square feet does not equal the stated rent per square foot.

A realistic reply to that prompt on a messy export (illustrative):

Suites: 6 (5 let, 1 vacant). Total area: 18,000 sq ft. Total annual base rent: 305,100.
Flags:
1. Suite 5: the PDF shows 4,125 in the rent column. 4,125 / 3,000 sq ft = 1.38, not the stated 16.50, so 4,125 is probably monthly rent. I entered annual rent as 49,500 (4,125 x 12) and marked it ASSUMED.
2. Suite 3: the lease end is printed 03/02/27. This could be 3 February or 2 March 2027. Please confirm.
3. Suite 5: no lease end date. Notes column says "MTM", which I have read as month to month.
4. Suite 6: vacant, no rent shown. An asking rent of 20.00 appears in the Notes column.

Flag 1 is why the cross-check line is in the prompt: without it, the assistant would have put 4,125 in the annual column and the model would have understated suite 5 by $45,375 a year. Flag 2 is why the date rule is there; dates written with slashes can be read two ways, and a lease ending in February is a different risk from one ending in March. You confirm both with the seller's broker, which is the real fix, because the assistant can only point at a doubt, not settle it.

Never accept the assistant's totals as a source of truth. Paste the table into a spreadsheet, recompute the sum of square feet and rent with =SUM, and compare. If the row count or a total differs by even one suite, the extraction dropped or merged a row. The tutorial on cleaning messy data with AI covers the same check on other tables.

Reconciling the rent roll with the operating statement

The two documents answer different questions. The rent roll says what tenants should pay from today; the operating statement (usually a trailing twelve months, called a T-12) says what the seller actually collected and spent. Put them next to each other and the gaps tell you where to ask questions. In the example, the rent roll implies $305,100 of base rent a year while the T-12 shows $298,800 collected, a gap of $6,300 or 2.1%. The roll implies recoveries of $78,750 (the tenants' share of a $94,500 pool of tax, insurance and common-area cost) while the T-12 shows $71,400, a gap of $7,350 or 9.3%.

Gaps are not accusations. A lease that started part-way through the year explains the first; a recoveries reconciliation not yet billed, or a cap on increases, could explain the second. But size matters. At a 7.25% cap rate every $1,000 of annual income is worth about $13,800 of value, so the recoveries gap alone is worth about $101,000. That is worth a phone call. Ask the assistant to do the comparison and, importantly, to stop short of deciding which explanation is true:

Compare the rent roll table and the T-12 income lines I have pasted. List every difference with its dollar size. For each, give three harmless explanations and three worrying ones. Then write questions for the seller, most valuable first. Do not say which explanation is correct and do not invent figures.

The top of a useful answer reads like this (illustrative): "1. Recoveries billed per the roll are $7,350 higher than collected. Were any tenants on caps or base-year clauses? Is the annual reconciliation outstanding? 2. Base rent collected is $6,300 lower than the roll annualises to. Did any lease start, or any rent step up, during the last twelve months? Was free rent given? 3. Please supply the lease for suite 5 or confirm it is month to month, and the date it last paid a rent increase." Notice what is missing: any opinion on who is right. For the multifamily version of this step, where the comparison is to the bank statements, see AI rent roll analysis: reconcile to the bank.

The pro forma, column by column

A pro forma is a projection of one year's income and costs. A first pass needs three columns, because each answers a different question. The seller's T-12 shows what the seller would like you to capitalise. The in-place column shows what the building earns today once costs the seller leaves out are added back. The stabilised column shows what it could earn if suite 6 were let and the building ran at normal vacancy, a future you should pay for only partly.

Four choices produce the differences. The seller's statement shows no management fee because the seller manages the building, so the in-place and stabilised columns add 4% of income, a placeholder to replace with a quote from a manager. Reserves for roof, parking and similar replacements are added at $0.25 per square foot; whether they sit above or below the NOI line is a convention, and the point is to use the same one on every deal you compare. The in-place column takes the rent roll and recoveries at face value and deducts 2% for credit loss. The stabilised column adds suite 6 at $18.50 (below the $20.00 asking rent), counts the full cost pool as recoverable, and deducts 8% for vacancy and credit loss.

The result is three NOI figures from one building: $257,100, $243,526 and $284,344. At a 7.25% cap rate (an assumption, covered below) those capitalise at about $3.55 million, $3.36 million and $3.92 million. The asking price of $3.95 million equals 6.5% of the seller's NOI, 6.2% of today's income and 7.2% of the stabilised figure. In other words, the ask only works if you pay now for a fully let building.

Formulas to ask the AI for, and the checks you do by hand

Keep the arithmetic in a spreadsheet and use the assistant to draft the formulas, not to do the sums. Language models can slip on multi-step arithmetic, and a number typed into a chat is not auditable. Name your assumption cells so the formulas read in words, add a Status column to the rent roll (Let or Vacant) for the formulas to filter on, ask the assistant to explain each formula in one sentence, and then test it on two rows you can add up yourself.

Assumptions (named cells)
Vac_Stab = 8%          Credit_InPlace = 2%      Mgmt = 4%
Reserve_psf = 0.25     Mkt_Rent_S6 = 18.50      Cap = 7.25%
Area = 18000           Pool = Tax + Insurance + CAM
VacantArea = Area - Occ_Area

In place
Base_InPlace  = SUMIFS(RentRoll!E:E, RentRoll!G:G, "Let")
Occ_Area      = SUMIFS(RentRoll!C:C, RentRoll!G:G, "Let")
Recov_InPlace = Pool / Area * Occ_Area
EGI_InPlace   = (Base_InPlace + Recov_InPlace) * (1 - Credit_InPlace)
NOI_InPlace   = EGI_InPlace * (1 - Mgmt) - (Pool + Utilities + Payroll + Legal) - Reserve_psf * Area

Stabilised
GPR_Stab      = Base_InPlace + Mkt_Rent_S6 * VacantArea
EGI_Stab      = (GPR_Stab + Pool) * (1 - Vac_Stab)
NOI_Stab      = EGI_Stab * (1 - Mgmt) - (Pool + Utilities + Payroll + Legal) - Reserve_psf * Area

Value         = NOI_Stab / Cap
Implied cap   = NOI_Stab / AskingPrice

Two hand checks catch most errors. First, NOI must equal effective gross income minus total operating costs, so $376,173 less $132,647 must give $243,526. Second, value must equal NOI divided by the cap rate, which you can do on a phone. If either fails, the formula, not the arithmetic, is wrong. The tutorial on whether ChatGPT can calculate yield reliably tests this on real prompts, and the ChatGPT in Excel guide shows how to have it write and explain formulas.

Vacancy and cap rate: the two numbers the AI must not choose

These two assumptions move the answer more than any formula, and an assistant has no way to know either for this building this month. For vacancy, start from evidence you can show: the building's own history over the last three years from the operating statements, and a market vacancy figure from your broker or from a published report you can cite. Take the higher of the two. In the example the building is 16.7% vacant today, so an 8% stabilised assumption is a bet on leasing suite 6, and the model should say so in its notes.

For the cap rate (net operating income divided by price, the yield a buyer accepts), collect three to five recent sales of similar buildings with the price and the NOI as reported, add your broker's opinion, and check your cost of debt with a lender. Say your three comparable sales show 6.9%, 7.2% and 7.6%. A base case of 7.25% sits in the middle, and 6.75% and 8.00% give you the range to test. The assistant's real job is arithmetic on those comps: converting prices and incomes into cap rates, and calculating what cap rate the asking price implies.

Here is how it goes wrong. Asked "what cap rate should I use?", an assistant replies with something like "6.8% is typical for small retail" (an illustrative figure, not market data) and no source. Plug that into the stabilised case and the value rises from $3.92 million to $4.18 million, about $260,000 on a number nobody can trace. The rule is that every assumption in the model needs a named source (a comp, a quote, a document) or the word "assumed" next to it. The tutorial on ChatGPT for commercial real estate: source-checked reports and comps shows how to ask for figures with their sources so you can check them.

Sensitivity: what the asking price needs to be true

A sensitivity table recalculates value across a range of assumptions so you can see which ones the price depends on. This one varies vacancy down the side and the cap rate across the top, with stabilised NOI recalculated for each vacancy level (management fee included). Figures are in thousands of dollars.

Read the table against the $3,950,000 ask. It is covered only in the top-left corner: vacancy of 8% or less with a cap rate of about 7.2% or lower, or vacancy of 5% with a cap rate up to about 7.5%. Move to the 12% row, close to the building's real history, and only the 6.75% cell reaches the ask. Separately, the stabilised figure ignores the cost of getting suite 6 let. Allow, say, a $15 per square foot improvement allowance ($45,000), a leasing commission of 6% of five years' rent ($16,650) and nine months without rent ($41,625). That is $103,275, which takes the stabilised value at 7.25% from $3.92 million to about $3.82 million.

The same arithmetic prices the rollover risk. If the salon in suite 3 leaves in March 2027, you lose its $50,400 of base rent and $12,600 of recoveries while the space is empty, so nine months of downtime costs $47,250. Add the same $15 per square foot allowance ($36,000) and a 6% commission on five years at $21.00 ($15,120), and one departure costs about $98,370, nearly as much as getting suite 6 let. Hold that figure against the seller's price whenever a lease is close to its end date.

Now the debt check, with placeholders in place of a quote. Suppose a lender offers 65% of the price ($2,567,500) at 6.75% over 25 years. The annual payment is $212,870. Divide NOI by that payment to get the debt service coverage ratio (DSCR): 1.14 on in-place income and 1.34 on stabilised income. If the lender wants at least 1.25 on the income the building earns today, the loan it supports is about $2.35 million, or 59% of the price, which means roughly $218,000 more equity. The rate matters too:

On the stabilised structure at 6.75%, DSCR falls to 1.25 at about 12% vacancy and to 1.0 at about 24%. The 1.25 threshold is my placeholder until a lender gives you its own. The point of the table is not the exact figure but the shape: on today's income the cover runs from 1.23 down to 1.02 across these rates, which leaves little room for a lost tenant, so the loan depends on leasing suite 6 and keeping suites 3 and 5.

An audit prompt that argues against your own model

Once the model is built, give the assistant the assumptions and the three-column pro forma and ask it to attack them. The prompt matters here: if you let it suggest numbers from its own knowledge, you reintroduce the problem you just removed.

Here is my underwriting model: the assumptions table and the three-column pro forma. Act as a sceptical credit analyst.
For every assumption give: (1) the source I have for it, using only the documents and comps I gave you in this chat, or "none"; (2) whether it is more or less favourable than that source; (3) what happens to value in dollars if it is wrong in the unfavourable direction; (4) one question to ask the seller or a lender.
Do not suggest figures from your own knowledge. If a number looks unusual, say so and say what evidence would settle it.

A reply worth having looks like this (illustrative):

1. Vacancy 8% (stabilised). Source: none. The building is 16.7% vacant today and suite 6 has been marketed for 14 months. More favourable than the history. At 12% value falls by about 241,000. Ask: what showings and offers has suite 6 had?
2. Market rent for suite 6, 18.50. Source: broker asking rent 20.00. Less favourable than asking, above suite 5 at 16.50. Each 1.00 per sq ft is worth about 37,000. Ask: what are the last three lettings in the area, with rents?
3. Management fee 4%. Source: none; the T-12 shows 0. Each point is worth about 58,000. Ask: what would a third-party manager charge?
4. Recoveries billed 78,750 against collected 71,400. More favourable than the T-12 by 7,350, about 101,000 of value. Ask: reconciliation statements for the last two years.
5. Cap rate 7.25%. Source: your three comps (6.9%, 7.2%, 7.6%). Inside the range. Ask: how recent are the comps, and were any of them partly vacant at sale?

Treat that as a list of leads, not a verdict. Recompute every dollar effect in the spreadsheet: assistants are often close but not exact, and a value effect you cannot reproduce should not go in the summary. The same applies to anything the reply adds unprompted. If it writes "management fees are typically 3%", delete the sentence, because that came from its general knowledge and the prompt told it not to. If an item calls an assumption "reasonable" without a source, make it name one.

What the model cannot tell you, and what to do with the result

A first pass is a screen. It cannot see the roof, the drainage, the parking, the environmental history or the state of the tenants' businesses. It reads only the rent roll, so it knows nothing about renewal options, rent caps, exclusive-use clauses or early termination rights buried in the leases. It cannot verify title, zoning or the property tax position after a sale. It is not an appraisal, which is a supported opinion of value from a qualified person who inspects the property, and a lender may require one regardless of your numbers. Say so on the page, in one line under the table: "Screening model only. Not an appraisal. Figures from seller documents, unverified."

What you do with the result is a decision, and the sensitivity tables make it easier to take. In the example, there are three honest options. Walk away, because the ask needs the best-case cell. Offer between the in-place value of about $3.36 million and the stabilised value less lease-up costs of about $3.82 million, closer to the lower figure while suites 3 and 5 are unresolved. Or spend on due diligence first (leases, a survey, quotes for insurance and management) and rerun the model with verified inputs. Whichever you pick, write the reasons in three lines and file them with the model; six months on, you will want to know which assumption failed.

The next stage is reading the leases themselves, which is covered in AI due diligence on real estate: read leases, surveys, searches. Carry the open questions from your reconciliation and audit lists straight into that work.

Questions about AI underwriting a first pass

Can AI choose the cap rate for me?

No. It can calculate the cap rate implied by an asking price and compare it with your comparable sales, but the number itself should come from sales, a broker's opinion and lender quotes you can name. A cap rate an assistant recites from memory is a pattern, not a sale, and it moves the value by hundreds of thousands.

Which file format should I give the assistant?

A spreadsheet export is best, then a text-based PDF. Scanned PDFs and photographs of rent rolls are the riskiest, because columns slip and digits are misread. Whatever you use, ask the assistant for row counts and totals, then recompute them in your own spreadsheet before you trust anything.

Is a first-pass model an appraisal?

No. An appraisal is a supported opinion of value from a qualified person who inspects the property and uses market evidence, and a lender may insist on one. A first pass only helps you decide whether the deal deserves more time and money, and what to ask the seller.

How long should a first pass take?

Allow two to three hours for a small building once you have done it twice: about twenty minutes to clean the documents, half an hour to reconcile them, three-quarters of an hour for the model, then the audit and a one-page summary. If it takes a day, you are probably modelling detail the data cannot support.

Further reads

• Best AI Tools for Commercial Real Estate Brokers, Compared by Job — Which tools suit which brokerage job.
• AI Real Estate Investment Analysis: Vet a Rental Deal Step by Step — The same discipline for a small rental deal.
• Using AI for Property Investment: Screening a Deal Step by Step — A faster screen before you build any model.
• Best AI for Real Estate Asset Management: Reports and Lease Abstracts — What to do with the model after you buy.
• How to Use AI as a Commercial Real Estate Broker: A Deal-Week Routine — Where underwriting fits in a broker's week.
• AI Property Development Feasibility: A First-Pass Appraisal — A worked first-pass appraisal for a small developer: inputs, GDV, costs, sensitivity and planning risks, with AI as challenger and a spreadsheet as calculator.
• Best AI for Real Estate Wholesalers: Underwrite a Deal Before You Call — Use AI to screen a wholesale deal before the seller call: comps, ARV and repair sanity checks, a maximum offer sum, and the checks you still do by hand.
• Commercial Real Estate Prospecting With AI: Owners and Outreach — Public-record owner research, a one-page owner profile and a four-touch outreach sequence for commercial brokers, with the limits to respect.
• Can AI Abstract a Commercial Lease With Page Citations? — A lease abstract template, a prompt that forces page-cited quotes, a spot-check method and an exceptions list for the clauses AI most often misreads.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: the worked example uses invented, illustrative figures; the interest rate, loan terms, reserve and management-fee percentages are placeholders to replace with quotes. No vendor pricing is quoted.