Sensitivity Analysis in Real Estate Excel Models: The 5×5 Grid

Sensitivity Analysis in Real Estate Excel Models: The 5×5 Grid

A pro forma's returns page reports one IRR, computed from one set of assumptions — and the one thing certain about those assumptions is that the future will not match them. The point estimate is not wrong so much as incomplete: it answers "what does this deal return if I'm right?" while the investable question is "what does it return across the ways I'm plausibly wrong?" Sensitivity analysis is the machinery for the second question, and the 5×5 two-variable grid — twenty-five recomputed outcomes across the two assumptions that matter most — is its institutional standard form.

This explainer covers the grid end to end: the Excel data-table mechanics, choosing the two axes and their ranges, reading the output (with a fully computed real grid), and the discipline that separates a live analytical instrument from decorative pasted values. All figures are illustrative examples.

Why a Grid, and Why Two Variables

One-variable sensitivity — a row of IRRs across five exit caps — is useful and insufficient, because assumptions fail together: the scenario that hurts is rarely "exit caps soften, everything else per plan" but "caps soften while rent growth disappoints," and the interaction is not the sum of the two one-variable reads. The two-variable grid computes every combination, which is exactly what a committee, a lender, or a disciplined buyer wants to see: not the deal's answer, but the deal's shape.

Why stop at two? Because two is what a human can read at a glance, and the grid's job is communication as much as computation. Higher-dimensional stress belongs in named scenarios (below); the grid is the two dominant risks, fully mapped.

The Excel Mechanics: the Two-Variable Data Table

Excel's Data Table feature recalculates the entire workbook for every cell of the grid, which is both its power and its list of rules:

1. Layout: a 6×6 block. The top-left corner cell contains a reference to the output (=IRR_cell — the levered IRR, or whichever metric the grid reports). The top row holds the five values of variable one (say, exit cap); the left column holds the five values of variable two (rent growth).
2. Create: select the full block → Data → What-If Analysis → Data Table → Row input cell = the model's exit-cap input cell; Column input cell = the rent-growth input cell.
3. The rules that bite: both input cells must be the actual live inputs the model consumes (a data table pointed at a label recomputes nothing); the table must live on the same worksheet as its input cells (the classic silent failure of putting the grid on a summary tab while inputs live elsewhere — solved by mirroring the inputs locally or housing the grid with them); and on large models, data tables are the usual reason a file turns sluggish — the Automatic Except for Data Tables calculation setting exists precisely for this, recalculating the grid on demand (F9) rather than at every keystroke.
4. Format the corner (the output reference) to something unobtrusive, and conditional-format the grid against the hurdle — the green/red boundary is the analysis.

Twenty minutes of setup, permanently wired: change any other assumption in the model and the entire grid reprices, which is the property that makes it an instrument rather than an exhibit.

Choosing the Axes: the Two Assumptions the Return Actually Rides On

The default pairing for stabilized and value-add acquisitions is exit cap rate × rent growth — the reversion's price and the income's trajectory, which between them drive most of a levered IRR's variance. But the honest rule is deal-specific: the axes are the two inputs this deal's return genuinely depends on, and across this site's models the pairing shifts with the strategy — cost overrun × exit cap for development, ADR × occupancy for short-term rentals, renovation premium × capture timeline for value-add plans. Same grid, same mechanics, different load-bearing assumptions — and choosing the pair is itself underwriting judgment: a grid stressing two inputs the deal barely feels is theater.

Ranges: center the grid on the base case, with steps that are individually meaningful and jointly plausible — quarter-point steps for cap rates, quarter-point steps for growth are the workhorse conventions. The corners should be uncomfortable but defensible: a grid whose worst corner is a scenario nobody believes has wasted a corner, and one whose best corner is fantasy has flattered the average reader's eye.

Reading the Grid: a Computed Example

Here is the real grid from the site's worked 48-unit value-add deal (the full case study carries the deal itself) — levered 10-year IRR across exit cap and rent growth, base case at the center:

Four reads, in the order a practiced eye makes them:

1. The spread: 9.0% to 12.6% — three and a half points of IRR across assumptions any honest person would call plausible. That spread is the deal's risk, stated more truthfully than any single number on the returns page.
2. The gradient: each quarter-point of exit cap costs ~0.5–0.6 points of IRR; each quarter-point of rent growth moves ~0.35. The cap axis dominates, nearly two-to-one — so the exit assumption deserves proportionally more diligence than the growth assumption, and the grid just told you where to spend your hours.
3. The hurdle contour: against a 10% requirement, the failing region is the adverse-cap corner — the deal survives weak growth at today's caps but not cap expansion with weak growth together. Conditional formatting draws this boundary automatically, and where the boundary runs is the grid's single most decision-relevant output.
4. The base case's position: 10.8% sits with modest headroom above the hurdle and most of the grid's downside pointing through it — the quantitative version of "this deal works but is not forgiving," which matches exactly what the deal's thin yield-on-cost spread said in a different language. When two independent diagnostics agree, believe them.

Live, Not Pasted — and What the Grid Is Not

Two disciplines complete the practice.

The grid must be wired. A pasted grid — values copied from a run someone did once — is worse than none, because it carries authority without currency: the purchase price has been renegotiated twice since, and the grid still reports the old deal. The test takes five seconds: change the price input and watch the corner cell. Institutional reviewers run this test, which is why "wired to live assumptions" appears in every serious model standard, this site's included.

Sensitivity is not scenarios. The grid varies two inputs mechanically while everything else holds — the right tool for mapping a return surface, the wrong one for stories in which many things move together (the recession case: soft growth and longer downtime and wider exit caps and a refi that prices worse). Those belong in named scenarios — coherent full-assumption sets toggled as a group — and a complete model carries both: the grid for the surface, three-to-five scenarios for the narratives, each answering a question the other cannot.

And present it as an argument, not an exhibit. In a committee memo or LP deck, the grid earns its page only with its reading attached: the spread, the dominant axis, where the hurdle boundary runs, and the base case's position — the four reads above, in a paragraph. A grid dropped in without interpretation invites each reader to perform the analysis themselves, badly; a grid with its reads is the sponsor demonstrating they know where their own deal breaks. Sophisticated presentations pair the IRR grid with an equity-multiple companion on the same axes — the same surface in the metric that ignores timing — since the two together answer the objection either alone invites.

Frequently Asked Questions

How do I make a two-variable sensitivity table in Excel? Output reference in the corner, one variable's values across the top row, the other's down the left column; select the block, Data → What-If Analysis → Data Table, and point the row and column input cells at the model's live input cells — which must be on the same sheet as the table.

What variables should a real estate sensitivity analysis use? The two the deal's return actually depends on: exit cap × rent growth as the acquisition default, with strategy-specific pairs (cost × cap for development, ADR × occupancy for STR) where the risk lives elsewhere. Ranges centered on base, in individually meaningful steps.

Why is my Excel data table not calculating? The usual suspects: the input cells referenced aren't the ones the model actually consumes; the table sits on a different sheet from its input cells; or calculation is set to exclude data tables (press F9). Debug in that order.

What is the difference between sensitivity analysis and scenario analysis? Sensitivity varies one or two inputs mechanically to map the return surface; scenarios move whole coherent assumption sets to price named futures. Both, not either.

Why 5×5 specifically? Two steps each side of base is the smallest grid that shows curvature, asymmetry, and a hurdle boundary while remaining readable at a glance — which is why it settled in as the institutional convention, and why it ships as the standard in every model here.

The Grid as Standard Equipment

Every YieldSheets model ships with the 5×5 grid wired to its own load-bearing pair — starting with The Multifamily Sheet, whose exit-cap × rent-growth grid produced the computed example above, alongside the 10-year DCF, debt sizing, and returns machinery it stresses — fully unlocked and formula-transparent, so the wiring itself is inspectable, with a documented methodology PDF. The full catalog, each model with its strategy-specific grid, is in the store; the metric the grid reprices in every cell is the subject of the IRR guide.

This article is for educational purposes only and does not constitute investment, legal, or tax advice. All figures are illustrative examples, not market data or forecasts. Consult qualified professionals before making investment decisions.