AI Underwriting in Excel: Check Formulas Before the Memo

Check an AI-assisted underwriting workbook by comparing its formulas with an approved template, recalculating material outputs and changing one input at a time against known expected results. A correct starting total is insufficient: a hardcoded result can match today and fail after a revision. Keep formula changes separate from approved input changes.

Start after the inputs have been accepted

This guide addresses the calculation layer. The T-12 adjustment register covers which historical figures and proposed changes enter a case. Here the question is whether those accepted inputs still flow through the workbook as intended after an AI assistant, import or reviewer changes it.

Save an untouched template and a working copy. Record the workbook version, sheet names, named ranges, input cells, formula cells and any external dependencies that matter to the decision. State which cells the workflow is allowed to change. An input-mapping task should not quietly replace a calculation because the replacement displays the same number.

Ask for a change report listing the cell address, old content, new content and reason. Review both formula changes and formulas replaced with constants. A workbook comparison is evidence of a difference, not a verdict about whether the difference is correct.

Check Excel's calculation state before judging the output

A displayed number can be an old result. Microsoft's recalculation documentation explains automatic and manual calculation and notes that desktop calculation settings affect open workbooks. Confirm the setting, recalculate the copy and record the application/version used for the check. Do not assume that a saved preview represents a fresh calculation.

Do not change precision settings to force a reconciliation. Preserve the underlying values and compare outputs using an explicit tolerance suitable for the check. Display rounding can explain a small presentation difference; it cannot justify ignoring a wrong range, period or sign.

Microsoft's formula-error guidance provides tools for investigating errors, while also noting that its list is not exhaustive. Use those tools, then test the financial relationships themselves. A valid formula can still refer to the wrong cells.

Worked example: a hardcoded DSCR passes the first review

The following model and all amounts are synthetic teaching data. The exercise does not represent a deal, lender quote or investment recommendation. It is a deliberately small annual model so the expected results can be calculated independently.

Create one worksheet named Model. Enter the values and formulas shown below. The CSV practice pack supplies the same cell map as review text; it is not a ready-made Excel workbook.

The intended baseline is $408,000 NOI, 1.36x DSCR and 6.80% NOI yield. This exercise keeps debt service outside NOI and has no separate reserve adjustment. Actual models and lender definitions may differ; document the definition before checking the formula.

Now imagine an export replaces B8 with the constant 1.36. The starting DSCR still looks right. Recalculating does not fix it because there is no longer a formula to recalculate. This is why the review needs both a formula inventory and a controlled input change.

Change one input and state the expected result first

Reset the worksheet to the baseline before each independent check. Do not stack unrelated changes unless the scenario explicitly calls for them.

The expense change should affect all three outputs. The debt change should affect DSCR while leaving NOI and its yield unchanged. The price change should affect the yield while leaving NOI and DSCR unchanged. Those relationships make a wrong reference easier to locate than a general request to “check the model.”

With the hardcoded B8, the expense test continues to show 1.36x instead of 1.26x. With the intended formula restored, it should show 1.26x. The answer key includes the expected results and the planted fault. No measured AI-model pass rate is claimed.

Download the formula-check practice pack. It contains the cell map, formulas as text, independent scenario results, deliberate fault cases and a blank review log. Copy the small model into a separate practice workbook and perform the recalculation in your own Excel environment.

Test missing values and incomplete ranges

A zero debt-service input makes =B7/B5 produce a division error. That is a useful failed-precondition signal in this exercise. If debt service is unknown, the workflow should flag it and hold the coverage output open. A formula such as IFERROR(...,0) can conceal the problem behind a plausible-looking numeric field unless the model separately exposes the reason.

Another planted fault calculates =B2-B4, omitting other income. It produces $384,000 of NOI, 1.28x DSCR and 6.40% yield at the baseline. Each result is numerically plausible. The reference range, rather than an error badge, reveals the problem.

Microsoft's precedent and dependent tools can help trace the cells feeding an output and the outputs using an input. The documentation also describes tracing limitations. Treat the visual arrows as one inspection aid; they do not establish that the selected income period or expense definition is correct.

Keep formula repair inside the review record

Give an AI assistant the intended model specification, cell inventory and failed checks. Ask it to propose the smallest formula correction with the affected cell and expected consequences. Apply the change in the practice copy, rerun the baseline and all affected sensitivities, and review the formula itself.

A successful repair needs to preserve unrelated relationships. Fixing DSCR must not alter historical income. Fixing an income range must flow through dependent outputs. For a larger workbook, extend the same approach to dates, debt mechanics, sign conventions, scenario selectors and external links using the actual model's definitions.

Record expected versus observed output, tolerance, reviewer, decision and workbook version. Include failed cases and corrections. Preserve an unresolved item when the team cannot establish the intended formula; an assistant should not choose an investment assumption merely to clear a test.

Carry the verified model version into the memo

Once accepted, the model version should travel with its exports. Inspect the material figures in the resulting memo and any separately generated tables. A corrected workbook does not update a pasted number in a document automatically.

The public IC memo walkthrough illustrates the downstream document workflow. Use the underwriting model-builder pack to organize your own source-to-model handoff, then add formula checks specific to the workbook your team actually uses.

Frequently asked questions

Why is a correct baseline total insufficient?

A constant can match the expected result at the starting inputs. Change one input against a known expected result to check whether dependent outputs update, and inspect whether intended formula cells still contain formulas.

Does the download include an Excel workbook?

No. It contains CSV review tables with the cell map, formula text, synthetic inputs, answer key, deliberate fault cases and review log. Build the small practice worksheet in your own Excel environment and recalculate it there.

Should IFERROR turn an unknown coverage result into zero?

Not without a separately visible and appropriate treatment of the missing input. In this exercise an unknown or zero debt-service input cannot support a valid DSCR. Flag the input and hold the result open instead of hiding it behind zero.

Can passing this exercise validate a production underwriting model?

No. It checks a few specified relationships in a simplified synthetic model. A production review needs its actual formulas, definitions, dates, dependencies, permissions and expected scenarios, plus designated analyst acceptance.