Skip to content

Lesson 06 · 13 min read

AI-Assisted Underwriting

Use AI to write and audit Excel and Google Sheets formulas for NOI, cap rate, DSCR, debt yield and IRR, build sensitivity tables, and explain models, while every market input still comes from sources you verify.

Underwriting has two halves. One half is mechanics: formulas, loan math, cash flow timing, IRR. The other half is judgment: what rent the space will really lease for, how much vacancy to carry, what cap rate a buyer will pay at exit. AI is a strong helper on the first half. It writes formulas, explains them, finds broken references, and builds sensitivity grids. On the second half it is a liability, because it will happily pick a market rent or cap rate that sounds reasonable and has no basis in your market.

This lesson shows how to use AI to write and audit underwriting formulas, build sensitivity tables, and explain a model you inherited. It draws a hard line around inputs, describes MaxLife Commercial's own deal analyzer tools accurately, and ends with a worked example on a Sanford flex building where an AI formula audit caught a debt service error that overstated coverage.

Education, not investment advice. Underwriting outputs depend entirely on inputs. Rates, rents and cap rates in this lesson are illustrative, not current market levels. Confirm financing with a lender and tax effects with a CPA.

The division of labor

AI can help withYou must own
Writing formulas for NOI, cap rate, DSCR, debt yield, IRRMarket rent, from lease comps you verified
Explaining what each cell in a model doesVacancy and credit loss, from the submarket and the tenant
Finding broken references, hardcoded numbers, unit errorsOperating expenses, from the T-12 and quotes
Building sensitivity tables and scenario layoutsGoing-in and exit cap rates, from sale comps and brokers
Checking that totals tie and signs are consistentInterest rate, loan terms and reserves, from a lender quote
Drafting the memo that explains the modelThe decision, and what you tell the client

If you remember one rule from this lesson: AI touches formulas, not assumptions. Every input cell in your model should have a source note that a person can check.

Writing formulas with AI

Describe your layout exactly, ask for formulas, and always ask for a test case you can do by hand. The core formulas are short. Here they are in plain spreadsheet form, using an illustrative layout.

Inputs (illustrative layout)
B2  Purchase price
B3  Loan-to-value (percent)
B4  Annual interest rate (percent)
B5  Amortization (years)
B6  Year-1 NOI

Formulas
B8   Loan amount              =B2*B3
B9   Annual debt service      =PMT(B4/12, B5*12, -B8)*12
B10  Cap rate                 =B6/B2
B11  DSCR                     =B6/B9
B12  Debt yield               =B6/B8
B13  Equity                   =B2-B8
B14  Year-1 cash flow         =B6-B9
B15  Cash-on-cash             =B14/B13

IRR over a hold, with cash flows in C20:L20 (year 0 equity as a negative)
     =IRR(C20:L20)
With actual dates in C19:L19
     =XIRR(C20:L20, C19:L19)

These work in both Excel and Google Sheets. Three details cause most errors, so ask the AI to explain each:

  • PMT units. For monthly payments, the rate is divided by 12 and the term is multiplied by 12. Mixing an annual rate with monthly periods, or the reverse, is a classic mistake.
  • Sign convention. PMT returns a negative number for a positive loan. Putting a minus sign in front of the loan, as above, returns a positive payment. Be consistent.
  • IRR timing. IRR assumes equal periods. If cash flows land on irregular dates, use XIRR with real dates.

Calculating Returns covers IRR, equity multiple and cash-on-cash in full.

Auditing a model with AI

Inheriting a model from a seller, a partner or your own past self is common. AI is good at reading formulas and saying what they do, which makes it a useful second reviewer.

How to give it the model:

  • Show formulas as text. In Excel, use Show Formulas, or use =FORMULATEXT() next to key cells. In Google Sheets, turn on Show formulas from the View menu. Copy the formulas, not just the values.
  • Or upload the file to a tool that can read spreadsheets, if your brokerage approves it for that data.
  • Remove client names and anything confidential that the audit does not need.
ROLE: Act as a reviewer of a commercial real estate underwriting spreadsheet.

SOURCE: The formulas below, with cell addresses and labels. [paste]

TASK: Audit the model.
1. For each formula, say in one sentence what it calculates.
2. Flag any formula whose result does not match its label.
3. Flag hardcoded numbers inside formulas.
4. Flag unit problems: annual vs monthly, percent vs decimal, SF vs total.
5. Flag references that point to the wrong row or an empty cell.
6. Flag anything inconsistent with the loan terms stated in the inputs.

RULES: Do not suggest different input values. Only review the mechanics.
For each flag, show the cell, the formula, and the corrected formula.

The last rule matters. Without it, a model reviewing your spreadsheet may "helpfully" suggest a different vacancy rate or exit cap. That is not its job.

After the audit, test the model yourself. Change one input at a time and confirm the outputs move the way they should. Raise the interest rate: debt service should rise and DSCR should fall. Raise the price: the cap rate should fall. If an output does not move, or moves the wrong way, something is wired wrong.

Building sensitivity tables

A sensitivity table shows how an output changes as one or two inputs change. It turns one answer into a range and makes the key risks visible. AI can lay one out and write the formulas.

In Excel, a two-way data table uses Data, then What-If Analysis, then Data Table, with a row input cell and a column input cell. Google Sheets does not offer that feature in the same form, so you build the grid with formulas that reference the row and column headers directly. Either way, ask the AI to explain which cell is which, then check one corner of the grid by hand.

My model computes DSCR in B11 from loan-to-value in B3 and interest rate in B4.
Build a two-way sensitivity grid in [Excel / Google Sheets]:
rows = LTV of 60%, 65%, 70%; columns = interest rate of [x]%, [y]%, [z]%.
Explain the setup step by step, then tell me which single cell I should check by hand.

You choose the ranges. Base them on the lender quotes and comps you have, not on what the model suggests. Sensitivity Tables and Tornado Charts covers design and interpretation.

Explaining a model you inherited

Sometimes you do not need an audit, just an explanation. Paste the formulas and ask:

  • "Walk me through how this model gets from gross rent to levered cash flow, in order."
  • "Which inputs drive IRR the most in this layout?"
  • "What does this nested IF in the reserves row do? Give me an example."

Then confirm one explanation by changing an input and watching the result. An explanation is only useful if it matches what the spreadsheet actually does.

Never let AI choose the inputs

Ask a chat assistant "What vacancy should I use for flex in Sanford?" and you will get a confident percentage. It is not from a survey of your submarket. It is a plausible-sounding number.

InputWhere it comes from
Market rentRecent lease comps for similar space, verified with brokers or leasing data you are licensed to use
Vacancy and credit lossSubmarket data from a named publisher, the property's history, tenant credit, and lease expirations
Operating expensesThe T-12, tax bills from the county, insurance quotes, management agreements
Capital reservesProperty condition report, roof and HVAC ages, contractor estimates
Interest rate and loan termsA current lender quote or term sheet
Going-in cap rateThe contract price and stated NOI basis
Exit cap rateRecent sale comps, broker opinions, and a conservative spread you can defend

Document the source of each input next to the cell. When a client asks "Where did this number come from?", the answer should never be "the AI."

MaxLife Commercial's deal analyzer tools

MaxLife Commercial, a Central Florida commercial brokerage, publishes two free tools on this site. They are useful for practice and first-pass screening, and they follow the same rule as this lesson: the tool does the arithmetic, you supply and check the inputs.

  • The Deal Analyzer is a calculator. You enter price, rent, expenses, financing and a hold period, and it computes NOI, cap rate, DSCR, cash-on-cash, equity multiple and IRR, with a sensitivity view and a letter-grade score. The page shows its formulas and notes that outputs depend entirely on your inputs.
  • The PDF Deal Analyzer uses AI to read an uploaded offering memorandum, extract deal terms such as price, rent and NOI, and then run the same style of underwriting. It asks for your name and a verified email first. Its FAQ explains how uploaded files are handled; read that before uploading anything under a confidentiality agreement, as with any tool.

The site's AI deal analyzer case study runs a real single-tenant net-lease OM for an Orlando auto parts store through the PDF analyzer. At the asking price and the post's stated default assumptions (25% down, a 6.75% rate and 30-year amortization, all adjustable in the tool), the stated NOI did not cover annual debt service, a DSCR of about 0.86x. The post also notes that the lease's 20-year initial term started in 2009, so only a few years remained, and that the renewal options belong to the tenant. It then reports, with disclosure that it was the author's own listing, the price at which the property later sold. The post closes by saying AI-extracted figures should always be verified against the source document.

The takeaway for this course is modest and useful. The tool did the arithmetic quickly and put a coverage problem in front of the reader. The judgment about price, financing and lease risk still came from the documents, the lender's terms, and the market.

A worked example: the Sanford flex DSCR

Illustrative numbers. Rates are not current market levels.

An investor client is considering a small-bay flex building in Sanford. A junior analyst built the model. The agent pastes the formulas into a chat assistant with the audit prompt above.

Inputs:

InputValueSource note
Purchase price$3,400,000Contract
Year-1 NOI$238,000Normalized from T-12 and rent roll
Loan-to-value65%Lender term sheet
Interest rate6.50%Lender term sheet
Amortization25 yearsLender term sheet

The model reported a DSCR of 1.66x and cash-on-cash of 7.93%. The client was pleased.

The audit flag. The AI review flags cell B9, labeled "Annual debt service." The formula is:

B9  =B8*B4

That is loan amount times interest rate: interest-only debt service. The term sheet calls for a 25-year amortizing loan. The AI suggests the corrected formula and a test case:

B9  =PMT(B4/12, B5*12, -B8)*12

The agent checks it by hand rather than trusting either version.

MeasureModel as built (interest-only)Corrected (25-year amortizing)
Annual debt service$143,650$179,065
DSCR1.66x1.33x
Year-1 cash flow$94,350$58,935
Cash-on-cash on $1,190,000 equity7.93%4.95%
Cap rate7.00%7.00%
Debt yield10.77%10.77%

Checks: $238,000 ÷ $179,065 = 1.33. $238,000 − $179,065 = $58,935, and $58,935 ÷ $1,190,000 = 4.95%. Equity here is price minus loan, ignoring closing costs for simplicity. The cap rate and debt yield do not change because they do not use debt service.

Whether 1.33x meets this lender's requirement is a question for the term sheet, not the AI. Either way, the client's return picture changed a lot. The agent then builds a sensitivity grid with LTV on one axis and the lender's quoted rate plus and minus 50 basis points on the other. Illustrative results:

LTV6.00%6.50%7.00%
60%1.51x1.44x1.38x
65%1.39x1.33x1.27x
70%1.29x1.23x1.18x

The agent hand-checks the 65% and 6.50% cell against the corrected model. It matches. The client conversation now covers leverage, rate risk and actual year-one cash flow, all from sourced inputs. AI found the formula error. It did not choose a single number in the model.

Common mistakes

  • Accepting an AI formula without a hand test.
  • Mixing annual and monthly units in PMT and cash flow rows.
  • Asking AI for market rent, vacancy or cap rate and leaving its answer in the model.
  • Auditing values instead of formulas. Paste formulas as text.
  • Letting the audit "improve" assumptions. Tell it to review mechanics only.
  • Treating a free online analyzer's grade as a decision. It is a screen, not underwriting.

What to take away

  • AI is strong on mechanics: formulas, explanations, audits, sensitivity layouts and memos
  • AI touches formulas, not assumptions; every input needs a source note a person can check
  • Ask for a hand-checkable test case with every AI-written formula
  • Watch PMT units, sign conventions and IRR timing; use XIRR for irregular dates
  • Audit by pasting formulas as text, and tell the model not to suggest input values
  • Test models by changing one input at a time and checking the direction of each output
  • Market rent, vacancy, expenses, reserves, loan terms and cap rates come from comps, documents, quotes and named publishers
  • MaxLife Commercial's Deal Analyzer and PDF Deal Analyzer are free screening tools; verify any extracted figure against the OM
  • A sensitivity table turns one answer into a range; you choose the ranges from real quotes and comps

Next lesson: Building a Safe AI Workflow.

Get Market Insights Delivered

Weekly Central Florida CRE updates — cap rates, new listings, market trends, and investment opportunities. No spam, unsubscribe anytime.