FINANCIAL MODELING
How to Audit a Real Estate Financial Model: A Reviewer's Checklist for Formulas, Assumptions, and Outputs
Key Takeaways
- A real estate financial model audit is two audits, not one: a formula audit (does the math calculate what the labels claim) and an assumption review (are the inputs defensible). A model can be arithmetically perfect and still wrong, and it can carry sound math on top of indefensible inputs.
- The procedure runs in a fixed order: structure, then hardcodes, then errors, then identities, then independent recomputation, then a liveness flex-test, then source-document reconciliation, then a logged sign-off. Each step exists because the step before it cannot catch that failure class.
- Test by model block, not by worksheet tab: revenue, OpEx, NOI, CapEx, debt, cash flow, exit, returns, sources and uses, and the waterfall each have their own identity and their own typical failure.
- A cheap, high-yield test is the change-one-driver test: move the exit cap, the rent growth rate, or the hold period, and confirm everything that should move, moves, and nothing that shouldn't, does.
- Log every finding with a severity, critical, major, or minor, re-test after every fix, and keep that log as the audit trail IC, a lender, or an LP will eventually ask to see.
A real estate financial model audit is a structured review that tests whether a model's formulas calculate what their labels claim, and whether its assumptions are defensible, before the model is relied on at IC, by a lender, or by LPs. This checklist is the reviewer's procedure for that review: an 8-step sequence, a master table of what to test in each of the model's ten blocks, and the formulas-versus-assumptions split that most informal reviews skip.
This checklist is written for a reviewer who has inherited a model, bought one, or built one, and has to sign off on it before it moves. That reviewer needs two things: an ordered procedure, so the review doesn't become a random walk through fifteen tabs, and a block-by-block list of what to test and how. What follows is the manual procedure itself, the same one an analyst runs with a mouse, Ctrl+` and a calculator, whether or not any tooling is involved later.
BUILD IT IN APERS
Apers runs the formula integrity checks in this procedure, tracing identities, re-performing outputs, flexing drivers, against your model before it goes to IC.See how underwriting review works →
What Is a Real Estate Financial Model Audit?
A model audit is the discipline of testing a spreadsheet against two separate standards: whether its formulas compute what they claim to compute, and whether its inputs are reasonable. Those are different questions, answered by different methods, and a reviewer who runs only one of them signs off on half an audit.
What's the difference between a formula audit and an assumption review?
The formula audit asks "does the math do what the label says." The assumption review asks "would an informed reader accept these inputs." A model can pass one and fail the other, a perfectly wired model built on an 8% rent growth assumption is arithmetically sound and still wrong for IC; a model with a defensible 3% rent growth assumption and a hardcoded balloon payment is directionally reasonable and still broken.
| Formula audit | Assumption review | |
|---|---|---|
| Question asked | Does this cell calculate what its label claims? | Would an informed reader accept this input? |
| Method | Trace precedents, recompute independently, flex a driver | Compare to market benchmarks and comparable deals |
| Evidence | The formula text itself, R1C1 references, recomputed values | Rent comps, lender term sheets, published growth and cap-rate benchmarks |
| Who can do it | Any reviewer who can read a formula, regardless of market knowledge | Someone with current market judgment on the asset class and submarket |
| Output | A pass/fail on mechanical integrity | A flag on inputs that need a defended answer, not a verdict |
The differentiator: a model that passes the formula audit can still fail the assumption review, and vice versa. See operating cash-flow drivers and assumptions for the benchmark detail behind the right-hand column.
When should you audit a model?
| Trigger | What goes wrong if you skip it |
|---|---|
| Pre-IC | A formula error or an aggressive assumption reaches the committee as a confirmed number |
| Pre-lender submission | A lender's own analyst finds the error first, and the credibility cost is worse than the error |
| On inheriting a model | You sign your name to someone else's hardcodes and broken links without knowing it |
| After a structural edit (new tab, new loan, new tier) | The edit breaks a cross-sheet reference or a balance tie-out that nobody re-checks |
| Before LP reporting | A reporting error compounds the trust cost of a return miss |
What an audit is not
- Not a valuation opinion: the audit tests the model's mechanics, not whether the deal is attractively priced.
- Not a view on the deal: a reviewer can sign off on a model that still describes a bad deal.
- Not an automated benchmark score: a reviewer runs this procedure by hand; Apers runs a version of it at machine scale, but the procedure below is the manual version either way.
How Do You Audit a Real Estate Financial Model? The 8-Step Procedure
Work from structure to identities to independent recomputation, then to judgment, and log everything. Each step exists because the step before it cannot catch that failure class, mapping the structure won't find a hardcode, and finding hardcodes won't catch a wrong formula that's live and well-referenced.
| Step | What you do | What it catches | Tool / technique |
|---|---|---|---|
| 1. Map the structure | List every sheet, separate input from calc from output, note external links | Hidden tabs, undocumented dependencies | Sheet inventory; Ctrl+` to reveal formulas |
| 2. Find hardcodes | Isolate constants sitting inside formula ranges | Buried assumptions, values typed over formulas | Go To Special → Constants |
| 3. Scan for errors and broken links | Sweep for error values and orphaned external references | #REF!, #N/A, stale workbook links | Go To Special → Formulas → Errors; Workbook Links (Data → Queries and Connections; Edit Links in older versions) |
| 4. Trace the core identities | Confirm the five identities every model must satisfy | Balance breaks, duplicate lines, period misalignment | Trace Precedents / Dependents |
| 5. Re-perform the key outputs | Recompute NOI, DSCR, IRR, and equity multiple independently | Dating errors, wrong ranges, silent formula drift | A clean sheet, pasted values, the raw cash-flow row |
| 6. Flex-test the drivers | Change one input and watch what moves | Static snapshots dressed up as live models | One-way sensitivity as a probe, not a method |
| 7. Reconcile to source documents | Tie the model to the rent roll, T-12, term sheet, and LPA | Model drift from the documents it's supposed to represent | Line-by-line reconciliation |
| 8. Log, fix, and re-test | Record every finding with a severity and a re-test date | Fixes that introduce a new error nobody re-checks | The findings log (see sign-off below) |
1. Map the structure
List every sheet, separate inputs from calculations from outputs, and note the direction cash and logic flow across tabs. Catches: hidden tabs, undocumented dependencies, and a model whose calculation order nobody can reconstruct from the tab bar alone.
2. Find hardcodes and constants
Isolate constants sitting inside formula ranges: numbers typed over what should be a formula, or buried inside one. Catches: a driver that looks live in the summary tab but doesn't move anything downstream because the cell underneath it is a typed number.
3. Scan for error values and broken links
Sweep every tab for #REF!, #N/A, circularity warnings, and orphaned references to an external workbook that was moved, renamed, or deleted. Catches: a failure class with wide reach, because one broken link can cascade errors into returns, the sensitivity grid, and the summary tab simultaneously.
4. Trace the core identities
An identity is a relationship that must hold exactly, by definition, regardless of any input: sources equal uses, NOI equals EGI minus OpEx, a debt balance carries forward without a gap, LP plus GP distributions equal distributable cash, and the period-zero IRR cash flow equals the equity invested.Catches: a duplicate NOI row, a balance that resets instead of rolling forward, a waterfall tier that drops a dollar.
5. Re-perform the key outputs independently
Recompute NOI, DSCR, IRR/XIRR, and equity multiple off the raw cash-flow row, outside the model's own formulas, a fresh sheet, pasted values, a clean XIRR(). Catches: a reported IRR that the model's own formula doesn't actually reproduce, usually because the date range or cash-flow range is off by one row.
6. Flex-test the drivers
Change one input, the exit cap, a rent growth rate, the hold period, and confirm that everything that should move, moves, and nothing that shouldn't, does. Catches: the static snapshot: a model that looks live but was partially pasted as values at some point in its edit history. Full treatment in the liveness section below.
7. Reconcile to source documents
Tie the model's inputs back to the rent roll, the T-12, the loan term sheet, and the LPA or operating agreement. Catches: drift between what the model says and what the documents say: a unit count that doesn't match the rent roll, a rate or day-count basis that doesn't match the term sheet. This checklist covers the principle at the summary level; reconciling the LPA to the waterfall tier by tier is a separate, more detailed exercise.
8. Log findings, fix, and re-test
Record every finding with a severity, fix it, and re-run the step-3 error scan on the fixed file, a fix is itself an edit, and edits are exactly where new errors get introduced. See the sign-off section below for the findings-log format.
What Should You Check in Each Part of the Model? The Master Checklist, by Block
The 8-step procedure tells you the order; this table tells you what to test in each of the model's ten blocks, the red flag that means something's wrong, and the severity if it is.
| Block | What to test | How to test it | Red flag | Severity |
|---|---|---|---|---|
| Revenue | GPR, loss-to-lease, vacancy, concessions tie to the rent roll | Sum the rent roll independently; compare unit count and total monthly rent | Unit count or GPR doesn't match the rent roll total | Critical |
| Operating expenses | Growth rates by line, fee bases, tax reassessment, per-unit sanity | Year-over-year by line; flag lines with an identical growth rate | Every expense line grows at the same rate, including fixed costs | Minor–Major |
| NOI | NOI = EGI − OpEx; nothing above the line that doesn't belong; reserves not buried in OpEx | Trace Dependents from every NOI consumer; all must resolve to one row | Two NOI rows that disagree, or a summary tab linked to the wrong one | Critical |
| CapEx, TI/LC, reserves | Timing driven by lease events, not hardcoded by year | Shift one lease expiry date; TI/LC must move in that year | TI/LC doesn't move when a rollover date changes | Major |
| Debt schedule | Monthly-rate derivation, balance continuity, IO→amortizing switch, balloon derived | Confirm the day-count basis matches the term sheet; month-1 interest = balance × rate × days ÷ 360 (30 days under 30/360, actual days under Actual/360); balloon = schedule balance at maturity | Balloon is a typed number instead of a formula result | Critical |
| Cash-flow cascade | NOI → CFADS → levered cash flow; sign conventions; period alignment | Σ levered cash flow = Σ distributions − Σ contributions | Period-0 cash flow has the wrong sign, or doesn't equal equity invested | Critical |
| Exit and terminal value | Forward vs. trailing NOI at exit; exit-cap-vs-going-in spread; selling costs netted | Confirm the exit cap is applied to next year's NOI, not the final projected year | Exit cap applied to trailing instead of forward NOI | Critical |
| Returns | Recompute IRR/XIRR and equity multiple; dating; period-zero; MIRR vs. IRR | NPV at the reported IRR ≈ 0 on a fresh, pasted-values recompute (use XNPV with the same dates when the model reports XIRR) | Model's own IRR formula doesn't reproduce on an independent recompute | Critical |
| Sources and uses | Sources = uses; closing costs and fees captured once; equity is a formula, not a plug | Move the price by a round amount; equity must move by the unfunded share plus any price-linked closing costs and fees | Equity is a typed number that happens to balance | Critical |
| Waterfall | Pref accrual dating, hurdle tests, catch-up basis, clawback, LP+GP = distributable | Per-period check: Σ tier distributions − distributable cash = 0 | Tier distributions don't sum to the cash actually available that period | Critical |
The master checklist, by block. Severity is the default; it escalates with reach, so a shifted reference is minor in a cosmetic label but critical in the debt schedule. Any red flag that changes a headline return or a covenant test is critical, per the severity definitions in the sign-off section below.
How do you check the revenue build?
Sum the rent roll independently (unit count, in-place rent, and any step-ups) and compare the total to GPR before loss-to-lease and vacancy are applied. A 100-unit property whose model shows 98 units feeding GPR has a lookup range that stopped two rows short. Deep dive: lease-by-lease modeling.
How do you check operating expenses?
Pull each line's year-over-year growth rate and flag any two lines that move identically: a tax line and a repairs line growing at exactly 3.0% every year usually means both point at one growth input instead of their own. Compare the expense ratio to the asset's own trailing actuals (T-12) and to comparables for the same property type and lease structure before accepting the line-item build. Deep dive: operating cash-flow drivers.
How do you check the NOI line?
Trace Dependents from every cell that consumes NOI, the cap rate, the DSCR, the exit calculation, the summary tab, and confirm they all resolve to the same row. A second NOI row added during an edit, with the summary tab still linked to the original, is one of the highest-yield checks in the whole procedure because so much depends on this single number. Deep dive: institutional vs. broker NOI, the NOI formula.
How do you check CapEx, TI/LC and reserves?
Move one lease's expiry date by a year and confirm the TI/LC line for that unit moves with it. If the tenant-improvement and leasing-commission figures are typed per year instead of computed from the rollover schedule, moving the expiry date changes nothing, the clearest sign the line is hardcoded, not driven. Deep dive: TI, LC, CapEx and reserves.
How do you check the debt schedule?
Recompute month-1 interest independently, using the day-count basis on the loan's term sheet. Under 30/360, month-1 interest is the opening balance times the annual rate divided by 12, so $50,000 on a $10M loan at 6%. Under Actual/360, which Freddie Mac's multifamily fixed-rate term sheet lists as its standard, it is the balance times the rate times the actual days in the month divided by 360, so $51,666.67 for a 31-day month on the same loan. If the model shows $600,000, the annual rate was applied on a monthly basis somewhere in the schedule. Confirm the balloon balance is the schedule's own ending balance at maturity, not a typed number, and that an IO-to-amortizing switch actually changes the principal line in the period it's supposed to. Deep dive: DSCR sizing, amortizing vs. IO, LTV vs. LTC, caps, swaps and hedging, defeasance and yield maintenance, refinancing risk, debt yield. A construction loan's interest reserve can create a legitimate circular reference, see interest reserves and draw schedules for when circularity is by design rather than by accident.
How do you check the cash-flow cascade?
Confirm the sign convention is consistent from NOI through CFADS to levered cash flow, and that period-0 cash flow equals the equity invested with the correct sign. A levered cash-flow row that doesn't sum to the total of distributions minus contributions usually means a loan proceed or a fee landed in the wrong period.Deep dive: hold-period analysis.
How do you check exit and terminal value?
Confirm the exit cap is applied to next year's forward NOI, not the trailing final year of the projection. This is a common exit-value error, covered in full in the terminal-value guide linked below. Confirm the exit cap sits a defensible spread wide of the going-in cap, and that disposition costs are netted out rather than ignored. Deep dive: terminal value and the forward-NOI convention, going-in vs. exit cap, DCF vs. direct cap.
How do you check the returns block?
Recompute IRR and equity multiple on a fresh sheet from the model's own raw cash-flow row, pasted as values, independent of the model's own formula. If the model's reported IRR doesn't match the independent recompute, a common cause is a date or cash-flow range that's off by one row: a period-0 entry omitted, or a trailing blank included. When the model reports XIRR, check the recompute with XNPV on the same dates rather than NPV, because NPV assumes equally spaced periods. Deep dive: IRR, MIRR and XIRR, the IRR formula, equity multiple.
How do you check sources and uses?
Confirm sources minus uses equals zero with equity as a formula result, not a typed number that happens to balance. Move the purchase price by a round $1M and confirm equity moves by the unfunded share of that $1M plus any closing costs and fees priced as a percentage of the purchase price. With a loan sized at 65% LTV and a 1% acquisition fee, equity should rise by $360,000: $350,000 of unfunded price plus $10,000 of fee. If equity doesn't move at all, it was hardcoded to force the tie-out. Deep dive: building a full capital stack model.
How do you check the waterfall?
Run a per-period check that LP and GP distributions sum exactly to the cash actually available that period, confirm the preferred return accrues on the correct base and dating, and confirm the catch-up basis and any clawback match the document.Deep dive: preferred return mechanics, catch-up provisions, multi-hurdle structures, clawback provisions, promote, step by step, American vs. European waterfall, GP co-invest in the waterfall.
What Are the Most Common Errors in Real Estate Models?
This checklist tests the structure; a separate catalogue tests the specific failures that structure is built to catch. Five representative entries from that catalogue, to show the shape of the problem:
| Error | Symptom | Quick test |
|---|---|---|
| Exit cap applied to trailing NOI | Terminal value understated by roughly one year of growth | Confirm the NOI feeding the exit cap is next year's, not the final projected year's |
| An absolute reference drifts after fill-right | Column 1 ties out; later columns go blank or wrong | Compare the formula text in the first and last column |
| A balloon payment typed in instead of calculated | The balloon doesn't change when the rate or amortization changes | Recompute the maturity balance independently and compare |
| A management or asset-management fee on the wrong base | The fee line doesn't equal the stated percentage of the stated base | Divide the fee by its rate and check it against the contractual base |
| Values pasted over a formula during a prior edit | Changing a driver upstream leaves the cell unchanged | Go To Special → Constants, limited to the calculation range |
These five are a sample, not the full list. The complete catalogue runs thirty entries across every block of the model, each with its symptom, its cause, and the exact comparison that catches it. Seecommon errors in real estate financial models and how to catch each one.
How Do You Test That a Model Is Actually Live?
Change one driver and confirm everything that should move, moves, and nothing that shouldn't, does. A model can look complete and still fail this test if an earlier edit pasted values over part of the formula chain.
The change-one-driver test
| Driver you change | What must move | What must NOT move |
|---|---|---|
| Exit cap (6.00% → 6.50%) | Gross sale price, net reversion, terminal IRR | Operating NOI in every prior period |
| Rent growth (3.0% → 2.0%) | Every future period's GPR, NOI, and downstream debt service coverage | Year-1 in-place rent, the loan amount, the purchase price |
| Interest rate (6.00% → 6.50%) | Fixed or LTV-sized loan: monthly debt service, DSCR, levered cash flow. DSCR-sized loan: loan amount, equity, exit loan payoff, levered IRR | NOI and unlevered returns in all cases. For a fixed or LTV-sized loan, the loan amount. For a DSCR-sized loan, debt service and DSCR (the loan should fall and equity should rise instead) |
| Hold period (5 years → 7 years) | The exit year, the reversion timing, total distributions | The number of reversions (still exactly one) |
Static snapshot vs. live model
"I changed a number and nothing else changed" is the failure this test exists to catch. A model that was partially pasted as values at some point in its history, often during a fix, an export, or a hand-off, can look entirely normal at rest. It only reveals itself when a driver is actually flexed and the cells that should respond don't. Use a one-way sensitivity strictly as a probe here, not as the method: full sensitivity and stress-testing technique is its own topic. Deep dive: sensitivity and stress testing, sensitivity analysis.
Are the Assumptions Defensible? A Reviewer's Reasonableness Pass
A model that passes every formula test can still rest on assumptions nobody would defend at IC. This pass restates the differentiator from the top of this page: formula integrity and assumption defensibility are different questions, tested differently.
| Assumption | What to compare it against | Where to learn the benchmark |
|---|---|---|
| Rent growth / loss-to-lease | Comparable in-place rent growth and current market loss-to-lease | Operating cash-flow drivers |
| Vacancy and concessions | Submarket vacancy and current concession packages | Operating cash-flow drivers |
| OpEx growth | Line-item growth against recent actuals and tax reassessment schedule | Operating cash-flow drivers |
| Going-in vs. exit cap | The spread, not just the level, against current transaction evidence | Going-in vs. exit cap |
| Leverage, LTV, DSCR | Lender term sheets and the covenant that actually governs sizing | LTV vs. LTC, DSCR sizing |
| Hold period | Strategy fit and sponsor confidence in the exit-year market read | Terminal value and hold period |
A reviewer's job here is to flag an assumption, not to own it. If the exit cap is 50 bps tighter than every comparable transaction on the desk, say so and ask for the support, the deal team owns the answer, the reviewer owns making sure the question gets asked.
How Do You Document and Sign Off an Audit?
The findings log
Log every finding against a fixed schema as you go, not after the fact:
| Severity | Block | Cell / range | Expected | Found | Status | Re-test date |
|---|---|---|---|---|---|---|
| Critical | Debt | Amort!F42 | Balloon = schedule balance | Balloon typed as $9.2M vs. $9.0M schedule | Fixed | 2026-10-05 |
| Critical | Revenue | RentRoll!B2:B101 | 100 units feeding GPR | Lookup range stopped at row 99 (98 units) | Fixed | 2026-10-05 |
| Minor | OpEx | Opex!C18 | Label matches contents | Row labeled "Repairs" contains landscaping cost | Open | 2026-10-07 |
Severity: critical, major, minor
- Critical: changes a headline return or a covenant test. Examples: a hardcoded balloon that misstates the refinance gap by $200,000; a rent-roll lookup that drops 2 of 100 units from GPR.
- Major: misstates a block but not the headline. Example: an OpEx line growing at the wrong rate that shifts NOI by a few thousand dollars without moving the IRR materially.
- Minor: presentation or hygiene. Example: a mislabeled row that doesn't change any number.
What does a sign-off statement need?
Scope (which tabs and which version were reviewed), the version and date reviewed, what was independently re-performed versus merely inspected, any open items with their severity, and who signed. A sign-off without a stated scope is not useful to the next reader, it invites the assumption that everything was checked when, in practice, a reviewer rarely re-derives every waterfall tier by hand.
How Do You Audit a Large or Inherited Model?
An inherited model carries someone else's edit history, and that history is invisible unless the model was built with version hygiene in mind. Three habits make a large or long-lived model auditable: a change log noting what changed and why on every structural edit, a clear visual convention for which cells are live inputs versus calculated output (so a reviewer isn't guessing), and a "last verified" checkpoint on the key outputs so a reviewer knows how stale the last audit is before starting a new one. Without these, every inherited-model audit starts from step 1 of the procedure with no shortcuts, map the structure first, because nothing about an inherited model's layout can be assumed.
Manual Audit vs. Automated Audit: What Does Each Catch?
A manual reviewer catches what judgment and intent require: whether an assumption is defensible, whether the model's structure matches how the deal is actually supposed to work, and whether the numbers reconcile to the source documents in a way that needs a human reading both. What a manual reviewer cannot easily catch at scale is breadth, a cross-sheet reference buried in a whole-column formula that never shows the input's address in its formula text, across fifteen tabs, is slow and easy to miss by eye.
Formula-level automation catches breadth and repeatability: every #REF!, every hardcode, every whole-column dependency, checked exhaustively and the same way every time. What it does not catch is assumption merit or business intent, whether 3% rent growth is defensible for this submarket in this cycle is a judgment call no automated check makes for you.Apers runs this kind of check at the formula level, and the same team's work onself-correcting models extends it to models that fix what they find. For a side-by-side on where manual review, outsourced QA, and automated checking each fit, see the comparison of audit options.
BUILD IT IN APERS
Apers checks the formula-integrity half of this procedure, identities, re-performance, hardcode sweeps, before a model reaches IC. Try Apers free →
Go Deeper: The Model-Review Series
- Common Errors in Real Estate Financial Models (and How to Catch Each): the full 30-entry error catalogue the five-entry sample above draws from.
Related Articles
- NOI: Institutional vs. Broker Calculation: the definitional convention behind the NOI checkpoint.
- Terminal Value and the Year 11 Forward NOI Convention: the full detail behind the trailing-NOI exit error.
- IRR, MIRR and XIRR: the dating and convention detail behind the returns re-performance step.
- DSCR Sizing, Amortizing vs. IO: the sizing logic the debt-schedule checks test against.
- Preferred Return: Simple vs. Compounding vs. Accruing: the mechanic behind the waterfall identity check.
- IRR Sensitivity Analysis and Stress Testing: the full method behind the liveness probe.
FAQ
Frequently Asked Questions
What is a real estate financial model audit?
A real estate financial model audit is a structured review that tests whether a model's formulas calculate what their labels claim, and whether its assumptions are defensible, before the model is relied on at IC, by a lender, or by LPs. It runs in two parts: a formula audit (is the math right) and an assumption review (are the inputs reasonable), using an ordered 8-step procedure and a block-by-block checklist.
How long does a model audit take?
It depends on the model's size and how many blocks carry custom logic rather than a standard template. A single-asset acquisition model with a simple debt structure audits faster than a multi-tranche portfolio model with a tiered waterfall. The main drivers of time are the number of tabs to map in step 1, how many external links need tracing in step 3, and how much source-document reconciliation step 7 requires. Reconciliation and sign-off add time on top of the mechanical steps, depending on how quickly the rent roll, T-12, term sheet, and LPA can be obtained.
What's the difference between a model audit, a model review and model QA?
This article uses one convention throughout: "audit" means the full 8-step procedure end to end, and "review" means a lighter pass, checking a few key outputs and the most error-prone blocks without running every step. "Model QA" is used informally for either, depending on the firm. What matters is scope, not the label: state explicitly in the sign-off which steps were actually run.
How do I find hardcoded numbers in an Excel real estate model?
Use Go To Special → Constants, limited to the calculation range (not input cells, which are supposed to be constants): this selects every cell holding a typed number inside what should be a formula. Then flex one upstream driver and watch what fails to move; a cell that should respond but doesn't is very likely a hardcode or a value pasted over a formula during a prior edit.
How do I check that IRR is calculated correctly?
Recompute it independently on a fresh sheet: paste the model's raw cash-flow row as values, confirm period-0 is included with the correct sign, confirm (if using XIRR) that every cash flow has a matching date and that no date precedes the first date, and run NPV at the reported IRR (XNPV with the same dates if the model uses XIRR), which should come out at approximately zero. If the independent recompute disagrees with the model's own formula, a common cause is a cash-flow or date range that's off by one row.
What are the most common errors in real estate pro formas?
Five representative patterns: applying the exit cap to trailing instead of forward NOI, an absolute reference that drifts after a fill-right, a hardcoded balloon payment, a fee calculated on the wrong base, and values pasted over a formula during a prior edit. The Apers model-review series includes a full catalogue of thirty errors, organized by model block with a detection test for each.
Who should audit a model: the builder or someone else?
Both, at different points. The builder should run the formula-integrity steps (1–6) as a matter of discipline before handing the model off. A second, independent reviewer should still run the full procedure before IC or a lender submission. The builder's own blind spots are exactly the errors a fresh set of eyes catches, particularly hardcodes and assumptions the builder has stopped noticing after weeks inside the same file.
Can AI audit a real estate financial model?
Software can run the formula-integrity half of this procedure at a scale and consistency a manual reviewer can't match, sweeping every tab for hardcodes, broken links, and identity failures exhaustively rather than by sampling. What it can't do is judge whether an assumption is defensible for a specific submarket and cycle, or whether the model's structure matches the deal's actual intent; that half stays a human judgment call. Apers runs formula-level checks of this kind.
Sources
Public, primary references for the auditing techniques and conventions used throughout this checklist:
- Microsoft Excel Help & Learning: documentation for the formula-auditing tools referenced throughout, including Trace Precedents/Dependents and Go To Special.
- Microsoft: Manage workbook links: the Workbook Links pane (Data > Queries and Connections > Workbook Links), which replaces the legacy Edit Links command.
- FAST Standard: an open standard for financial model structure (input/calc/output separation, formula consistency) that informs the structural-mapping step of this procedure.
- European Spreadsheet Risk Interest Group (EuSpRIG): an interest group on spreadsheet risk management.
- Microsoft: Excel XIRR function: official reference for the XIRR function used in the independent-recompute step of the returns check, including its date rules and its relationship to XNPV.
- Microsoft: Excel NPV function: official reference for NPV, which assumes equally spaced cash flows.
- Freddie Mac Multifamily: Fixed-Rate Loan term sheet: lists Actual/360 as the standard amortization calculation, with 30/360 available, and sets loan size by maximum LTV and minimum DCR.