Common Errors in Real Estate Financial Models (and How to Catch Each)

Key Takeaways

The errors this catalogue targets are mechanical, not conceptual: a reference that moved, a number typed over a formula, a balance that doesn't carry forward. This page catalogues 30 such errors in real estate financial models, organized by model block, with the symptom you'll see, the cause, and a test that catches each one.

Two readers get equal use out of this page: a builder who wants to know what to avoid while modeling, and a reviewer who has a live model and needs a list to test against. Every entry carries a detection test, which is the point of the "and How to Catch Each" half of the title. This is a working checklist, not a listicle of mistakes to feel bad about.

What Counts as an Error Here, and How Is This Catalogue Organized?

A model error, for this page, is a cell, link, or structure that calculates something other than what its label claims. Convention and judgment questions (which NOI year feeds the exit cap, how to treat an IRR reinvestment assumption) are covered on their dedicated articles and in the model audit checklist's assumption-review pass; this catalogue lists them once, with a link, because a reviewer still has to test for them, but doesn't re-teach them here.

Mechanical errors vs. convention errors

The two are easy to conflate and worth separating cleanly. A mechanical error is objectively wrong: a formula that doesn't do what its label says, regardless of anyone's opinion. A convention error is a choice some models get wrong relative to institutional practice, but the choice itself requires judgment to evaluate. Which NOI year feeds the exit cap is a convention question with a right institutional answer, documented on its own article; this catalogue flags it once and sends the reviewer there rather than re-deriving it.

How is severity defined?

The severity shown in the catalogue below is the default. The same error escalates when it feeds debt service, the exit, or the waterfall: a shifted anchor is major sitting in an OpEx row and critical sitting in the debt schedule, because of what it reaches downstream, not because of what it is. The procedure that sequences these tests in order is the model audit checklist; this page is the detail layer underneath it.

What Are the Most Common Errors in Real Estate Financial Models?

Five patterns have the widest downstream reach: a reference that drifts on fill-right, a hardcode sitting where a formula should be, a balance that doesn't tie across tabs, a trailing instead of forward exit NOI, and a waterfall tier that doesn't sum to distributable cash. The full table below carries all 30, grouped by block.

The full catalogue: 30 entries, 16 critical, 13 major, 1 minor. Rows C2, F1, and F4 link to a dedicated article rather than re-teaching a convention question in full here.

Which Errors Hide in References, Hardcodes and Links?

These six sit at the model-wide level: they can hide in any tab and often cascade into several blocks at once, which is why they're checked first in the audit procedure rather than wherever a reviewer happens to start.

A1. Fill-right shifts an absolute reference

What you see: the first projection column ties to source, but later columns drift or read a blank cell. Why it happens: the anchor on a reference is missing, or anchored on the wrong axis, so Excel moves it on fill instead of holding it fixed. How to catch it: compare the formula text in the first and last column with FORMULATEXT or R1C1 view; they should differ only in the columns meant to move. The fix: anchor the row, column, or both, depending on which should stay fixed. Worked example: a rate cell lives at H9 (5.00%) and a formula reads =E12*H9; filled right without the $ anchors, column 2 reads I9(blank), so interest computes as zero from that column forward.

A2. Whole-column or cross-sheet reference missed by Find

What you see: an input looks unused, you delete it, and outputs elsewhere change anyway.Why it happens: the input is consumed through a whole-column or range reference on another sheet, so the cell's own address never appears in any formula's text. How to catch it: run Trace Dependents on the input directly, and search formula text for the column pattern rather than the specific address. The fix: replace the whole-column reference with an explicit range once you've confirmed what it's meant to capture.

A3. Hardcode typed over a formula

What you see: you change a driver, and a cell that should respond doesn't move at all.Why it happens: a value was pasted over a formula during a prior fix or export, and it looks identical to a live cell until something upstream changes. How to catch it: Go To Special → Constants, scoped to the calculation range only, then flex one driver and confirm every selected cell responds. The fix: replace the constant with the formula it should have been.Worked example: the NOI row for projection years 4 and 5 was pasted as values during an earlier correction; rent growth now flows correctly through year 3 and stops.

A4. Constant buried inside a formula

What you see: one cell ignores a changed input even though it's clearly a formula, not a typed value. Why it happens: a numeral (a cap rate, a growth factor) is typed directly inside the formula instead of referencing the input cell. How to catch it: scan formula text for non-structural numerals; 12 and 100 used for unit conversion are usually legitimate, a rate or growth number typically is not. The fix: replace the embedded number with a reference to the driver cell.

A5. Orphaned external link and #REF! cascade

What you see: an "update links" prompt on open, or #REF! values surfacing in returns, the sensitivity grid, or XIRR. Why it happens: a workbook the model links to was moved, renamed, or deleted, or a tab copied in from another file kept its external link. How to catch it: open Data → Queries and Connections → Workbook Links (Edit Links in older versions) for anything pointing outside the current file, and then locate each use by hand in formulas, defined names (Name Manager), objects, chart titles and chart series, since Excel has no automatic way to find every place a link is used; then run Go To Special → Formulas → Errors to find every affected cell at once. The fix: repoint the link to the local copy of the source, or rebuild the reference internally. Worked example:a returns tab copied from a prior deal's workbook keeps an external link; the first open after delivery throws errors into the sensitivity grid and the XIRR cell simultaneously.

A6. Stale scenario or sensitivity table

What you see: a data table or scenario grid doesn't change when inputs change, or a scenario selector updates the summary tab but not the debt tab. Why it happens:calculation is set to "automatic except data tables," or the selector is wired into some tabs and not others. How to catch it: force a full recalculation, then flip the scenario and diff every output tab against the base case; the grid's center cell must equal the base case exactly. The fix: rewire every tab to the same scenario selector and confirm the calculation setting.

Which Errors Distort Revenue and Operating Expenses?

Four errors here, mostly about the base a percentage or lookup applies to, rather than the formula's logic being wrong in isolation.

B1. Rent roll doesn't tie to gross potential rent

What you see: GPR doesn't equal the rent roll's monthly rent times 12, or the unit count in the model differs from the rent roll. Why it happens: the lookup range stops short of the last row, or units were added below the range after the model was built. How to catch it:tie both GPR and unit count to an independent sum of the rent roll. The fix: extend the lookup range and re-tie. Worked example: a 100-unit rent roll feeds a model whose lookup range picks up only 98 units; GPR is understated by roughly 2% before any growth assumption is applied.

B2. Vacancy, concessions, or loss-to-lease on the wrong base

What you see: effective gross income differs from a hand rebuild of the same line items.Why it happens: a haircut is applied to the wrong base, or a concession is counted twice: once as its own line, once again inside effective rent. How to catch it: rebuild Year 1 EGI by hand in the model's own stated order and compare line by line. The fix: correct the base or remove the double-count. Deep dive: concession modeling, loss-to-lease analysis.

B3. Fee calculated on the wrong base

What you see: a fee line doesn't equal its stated percentage of its stated base.Why it happens: a management fee is computed on GPR instead of EGI, or an asset-management fee is computed on the wrong equity or cost base. How to catch it: divide the fee by its stated rate and confirm the result matches the contractual base exactly. The fix: repoint the fee formula to the correct base. Deep dive (asset-management fee base): management fees and GP economics.

B4. One growth input drives every OpEx line

What you see: every expense line, including fixed-cost lines like insurance, shows an identical year-over-year growth rate. Why it happens: every line's growth cell points at one shared input instead of its own assumption. How to catch it: compute year-over-year growth by line and flag any rows that match exactly. The fix: give each line its own growth driver, or an explicit note that it's intentionally shared. Deep dive: operating cash-flow drivers.

Which Errors Break NOI, CapEx and the Cash-Flow Cascade?

C1. Two NOI lines that disagree

What you see: the summary tab's NOI doesn't match the cash-flow tab's NOI, and DSCR differs depending on which one feeds it. Why it happens: a second NOI row was added during an edit and the summary tab was never repointed. How to catch it: Trace Precedents from every consumer of NOI (the cap rate, DSCR, the exit calculation, the summary tab) and confirm they all resolve to the same single row. The fix: delete the duplicate and repoint every consumer to one row.

C2. NOI built on the wrong convention

A management fee omitted from the expense build, or reserves treated inconsistently above or below the line, breaks comparability to the comp set even when every formula in the model is internally consistent.Deep dive: institutional vs. broker NOI.

C3. Sign flip or period misalignment between blocks

What you see: levered cash flow doesn't sum to total distributions minus contributions, or period 0 carries the wrong sign. Why it happens: a mixed sign convention across tabs, or loan proceeds and fees landing in the wrong period because an annual column was fed from a monthly tab with an off-by-one alignment. How to catch it: check that the sum of levered cash flow equals the sum of distributions minus contributions, and that period 0 equals negative equity invested exactly.The fix: standardize the sign convention and re-align the period index across every feeding tab.

C4. TI/LC and capex timing hardcoded, not driven by lease events

What you see: moving a lease's expiry date doesn't move its tenant-improvement or leasing-commission figure. Why it happens: TI/LC and commissions are typed per year rather than computed from the rollover schedule. How to catch it: shift one expiry date and confirm the TI/LC line moves in the corresponding year. The fix: drive the line from the rollover schedule instead of a typed annual figure. Deep dive: lease-by-lease modeling, TI, LC, CapEx and reserves.

Which Errors Throw Off Sources and Uses?

D1. Sources ≠ uses; equity as a hidden plug

What you see: sources minus uses doesn't equal zero, or it does, but only because equity is a typed number chosen to force the balance. Why it happens: equity is hardcoded to make the tie-out work, often after a fees-depend-on-loan circularity was broken by typing a number instead of resolving it. How to catch it: confirm sources minus uses equals zero with equity computed as a formula, then move the purchase price by a round amount and confirm equity moves by exactly the unfunded portion. The fix: replace the typed equity figure with the formula that derives it. Worked example: $20M in uses, a $13M loan, and equity typed as $7M; raise the price by $1M and equity stays at $7M, revealing that sources no longer equal uses.

D2. Total project cost doesn't tie to its line items

What you see: total project cost doesn't equal the sum of the budget buckets, fees, and capitalized interest that are supposed to build it. Why it happens: a budget bucket was omitted from the rollup, or a SUM range wasn't extended after a row was inserted. How to catch it: recompute TPC independently outside the model's own rollup formula, then insert a test row and confirm the total updates. The fix: rebuild the rollup with a range that captures every bucket, ideally a structured table rather than a fixed cell range. Deep dive: building a full capital stack model, and for the capitalized-interest line specifically, interest reserves and draw schedules.

Which Errors Corrupt the Debt Schedule?

E1. Hardcoded balloon balance

What you see: the balloon payment doesn't change when the rate or amortization schedule changes. Why it happens: the balance was typed in, or pulled from the wrong period in the schedule. How to catch it: recompute the maturity balance independently with an FV formula and confirm it matches the schedule's own ending balance; move the rate and confirm the balloon moves with it. The fix: replace the typed value with a reference to the schedule's own ending balance.Worked example: a 5-year loan's balloon is typed as $9.2M while the amortization schedule's own ending balance is $9.0M.

E2. Monthly/annual rate or period mix-up

What you see: interest comes out roughly a factor of 12 off, or the computed payment doesn't match an independent calculator. Why it happens: an annual rate is applied in a monthly period, or years are used as the number of periods against a monthly rate. How to catch it: confirm month-1 interest equals the opening balance times the annual rate divided by 12, and that PMT(rate/12, years×12, −loan) matches the schedule's own payment. The fix: align the rate and period basis throughout the schedule. Worked example: a $10M loan at 6% should show $50,000 of month-1 interest; a period mix-up produces $600,000.

E3. Balance doesn't carry across tabs or periods

What you see: the loan balance on the monthly cash-flow tab doesn't match the amortization tab, or an opening balance doesn't equal the prior period's closing balance. Why it happens:two schedules are maintained separately, or an opening balance is hardcoded instead of linked.How to catch it: confirm closing balance at t−1 minus opening balance at t equals zero across every row. The fix: link the opening balance directly to the prior period's closing balance and remove the duplicate schedule.

E4. Capitalized interest or interest reserve runs away

What you see: capitalized interest comes out absurdly large or oscillates, often with an iteration warning on open. Why it happens: an unintended loop, such as period-t interest computed on period t's own closing balance instead of the prior period's (which triggers the circular-reference warning and, under iteration, overstates interest or can latch an error value), or interest computed on the committed amount rather than the actually drawn balance. How to catch it: confirm capitalized interest is approximately the average drawn balance times the rate times time, and switch iterative calculation off temporarily to see what breaks. The fix: base the calculation on drawn balance, not commitment, and confirm the iteration is converging rather than compounding an error. A circular reference here isn't automatically wrong; see the FAQ below on when circularity is deliberate.

E5. Refinance proceeds counted without netting the payoff

What you see: a large refinance inflow appears and the equity multiple jumps sharply.Why it happens: new-loan proceeds are treated as cash to equity without subtracting the old loan's payoff, closing costs, and any prepayment charge. How to catch it: confirm net refinance cash equals the new loan minus the payoff, costs, and reserves, and that the old loan balance goes to exactly zero at the refinance date. The fix: net every cost explicitly rather than showing gross proceeds. Deep dive: refinancing risk, prepayment costs.

Which Errors Distort Exit Value and Returns?

F1. Trailing NOI at exit

Applying the exit cap to the final projected year's NOI instead of the following year's forward NOI understates terminal value by roughly one year of growth. Deep dive: the Year 11 forward NOI convention.

F2. Exit year not tied to the hold-period input

What you see: the hold period changes from 5 to 7 years, but the exit stays modeled in year 5, or a reversion cash flow appears in two different years. Why it happens: the exit column is typed or flagged by hand rather than computed from the hold-period input. How to catch it: change the hold period and confirm the reversion moves with it, that cash flows after the new exit year go to zero, and that exactly one reversion exists. The fix: drive the exit column from the hold-period cell directly.

F3. IRR/XIRR range or dating wrong

What you see: the model's reported IRR doesn't match an independent recompute, or XIRR returns an unusual value or #NUM!. Why it happens: the cash-flow range omits period 0 or includes a blank cell, or the date range is offset from the cash-flow range by a row.How to catch it: confirm NPV at the reported IRR comes out at approximately zero on a fresh recompute, and that the date count matches the cash-flow count exactly. The fix:realign the ranges and recompute. Worked example: a cash-flow range starting one cell below period 0 returns #NUM! if period 0 was the only outflow, because IRR needs at least one negative and one positive value. Where later capital calls keep a negative value in the range, it returns a flattering IRR instead, because the largest negative outflow is silently dropped.

F4. IRR convention errors

Running IRR() on monthly cash flows without annualizing, or without checking for multiple sign changes in the cash-flow stream, produces a number that's internally consistent and still misleading.Deep dive: IRR, MIRR and XIRR.

F5. Equity multiple on the wrong base

What you see: the reported equity multiple doesn't equal total distributions divided by total contributions. Why it happens: the denominator uses initial or peak equity only, ignoring later capital calls, or return of capital gets counted on both sides. How to catch it: recompute the sum of distributions divided by the sum of all contributions directly.The fix: rebuild the ratio from the full contribution and distribution history.Deep dive: equity multiple.

Which Errors Leak Cash in the Waterfall?

Every entry in this block defaults to critical: a waterfall error moves real dollars between LP and GP by definition, which is why it gets the catalogue's tightest scrutiny.

G1. LP + GP ≠ distributable cash

What you see: tier distributions don't sum to the cash actually available in some period.Why it happens: tier amounts are computed independently with no remainder logic, or a nested MIN/MAX formula drops a tier in one year. How to catch it: run a per-period check that the sum of tier distributions minus distributable cash equals zero, across every period, not just the total. The fix: rebuild the tier logic so the final tier absorbs the remainder rather than computing independently. Worked example: $1.00M is distributable in a period; the tier formulas as built pay out $0.97M, leaving $30,000 unaccounted for. Deep dive: the promote, step by step.

G2. Preferred return accrued on the wrong base or dating

What you see: the preferred return balance doesn't roll forward correctly period to period.Why it happens: the accrual is computed on original capital instead of unreturned capital, the accrual starts at period start rather than the actual funding date, or the compounding frequency is wrong. How to catch it: confirm opening balance plus accrual minus paid equals closing balance every period, and recompute a 3-period sandbox by hand to check the mechanics independently.The fix: correct the accrual base and dating to match the LPA. Deep dive: preferred return mechanics.

G3. Row offset or stale split pushes cash to the wrong tier

What you see: a cash flow landing exactly at the hurdle gets flagged into the wrong tier, or the tier split shifts a period late. Why it happens: the hurdle test references the prior-period row instead of the current one, or a split percentage wasn't updated when the tier structure changed. How to catch it: engineer a boundary test, a distribution exactly at the hurdle, and confirm the tier flags fire correctly. The fix: correct the row reference and confirm every split percentage against the current tier structure. Deep dive: multi-hurdle structures.

G4. Model differs from the LPA on hurdle or catch-up basis

What you see: the model passes every internal consistency check and still disagrees with the governing document. Why it happens: the hurdle is defined by IRR in the model but by equity multiple in the LPA, the catch-up percentage or lookback isn't implemented as written, or a clawback provision is absent entirely. How to catch it: reconcile each relevant LPA clause to a specific cell in a clause-to-cell table, one row per clause. This is the deepest form of this check; a full waterfall-against-the-document review is beyond this catalogue. The fix: correct the model to match the document, or flag the discrepancy to counsel if the intent is ambiguous.Deep dive: catch-up provisions, clawback provisions.

What About Aggressive Assumptions?

A formula-clean model can still carry indefensible assumptions. That's a separate pass from everything above: this catalogue tests whether the model computes correctly, not whether its inputs are the right ones. For the assumption side of the review, see the model audit checklist's reasonableness pass and operating cash-flow drivers and assumptions. No benchmark numbers are repeated here; this catalogue stays on mechanics.

Where Should You Start When You Inherit a Model?

Run the six highest-yield tests first, the ones that touch the most downstream cells for the least effort:

• C1: confirm there's one NOI, not two.
• A3: sweep for hardcodes sitting in calculation ranges.
• D1: confirm sources equal uses with equity as a formula.
• E3: confirm the debt balance carries across every tab.
• F3: recompute IRR independently.
• G1: confirm LP plus GP equals distributable cash every period.

That order reflects reach and cost to test, not how often each error actually occurs. Run these six before the rest of the catalogue, then work the full 8-step procedure on the model audit checklist. Once a finding is fixed, turning the check itself into a permanent cell inside the model, so the next person doesn't re-run it by hand, is a separate topic.

Can These Errors Be Caught Automatically?

Formula-level automation reaches whole-column and cross-sheet cases a manual search misses, and it checks every tab the same way every time rather than by sampling. It does not judge whether an assumption is reasonable; that stays a human call. Apers runs checks of this kind against a model before it reaches IC. See how underwriting review works →

Related Articles

• How to Audit a Real Estate Financial Model: the 8-step procedure and the block-by-block checklist this catalogue plugs into.
• Terminal Value and the Year 11 Forward NOI Convention: the full detail behind F1.
• NOI: Institutional vs. Broker Calculation: the full detail behind C2.
• IRR, MIRR and XIRR: the full detail behind F4.
• DSCR Sizing, Amortizing vs. IO: the sizing logic behind the debt-schedule checks in block E.
• Preferred Return: Simple vs. Compounding vs. Accruing: the mechanic behind G2.

FAQ

Sources

Public, primary references for the techniques used throughout this catalogue:

• Microsoft: Display the relationships between formulas and cells: Trace Precedents and Trace Dependents.
• Microsoft: Manage workbook links: the Workbook Links pane that replaces Edit Links, and where to look for links by hand.
• European Spreadsheet Risk Interest Group (EuSpRIG): the research body documenting spreadsheet error classes across industries.
• FAST Standard: an open financial modeling standard; general context on consistent structure and transparent formulas.
• Microsoft: Excel IRR function: official reference for the IRR function behind block F.