DECODED: Stress Testing the Pro Forma with AI
This is No. 6 of the six-part real estate pro forma series.
This is Decoded: AI tools for getting straight answers from complex investment documents.
The past three years have been a multi-year, multi-front stress test of assumptions for commercial real estate. This series will cover some of the most common failure points where flawed assumptions and seemingly small numerical changes can make a good deal go bad.
This is No. 6 in the six-part series.
No. 1 presented the pro forma and market metrics.
No. 2 stress tested income: rent and vacancy rates.
No. 3 stress tested costs: debt and operating costs.
No. 4 stress tested disposition metrics: cap rates and hold time.
No. 5 stress tested correlated metrics: Interest + Cap Rates, and Rent + Vacancy Rates.
Posts one through five of this series have illustrated how small changes can have large effects on cashflow and investor returns by asking “what happens if X variable moves by Y?”. The fictional Fantasy Acres project profiled in this series illustrated how the end point of one market cycle (the peak and collapse of 2020-2026) brought so many deals to the point of not only stress but - in many cases - complete failure.
Now we’re going to switch from backward to forward looking and test a deal’s durability: at what point does the deal go to zero, and how far is that point from the historical average and end points?
Zero applies to:
Free cashflow: the point at which operating income no longer covers debt service and operating expenses. The property is now consuming cash rather than producing it.
Investor return: the point at which the LP equity multiple falls to 1.0x: full return of (but no return on) capital.
DSCR covenant breach: the point at which the asset’s cash flow is in violation of the loan terms, which could require a debt buy-down and infusion of additional capital.
Finding the breaking point of just one variable (let alone a handful of the most critical variables), and then comparing those to the historical context is a daunting task if done manually. Here AI handles the math and market context - leaving the human brain more time and space for decision-making.
The following guide takes you through the process step by step.
Let’s go!
Part 1: Open Your AI Tools
We’re going to use two tools for this job:
NotebookLM for the deal documents.
Gemini/ChatGPT/Claude for market data.
Step 1
Open two tabs in your browser: one for NotebookLM and one for Gemini/ChatGPT/Claude so you can easily toggle between the two.
Step 2
Open a new notebook in NotebookLM and upload the source materials: the pro forma, PPM, OM, and any other offering materials provided by the GP.
Part 2: Confirm the Baseline
Ask NotebookLM to confirm that the baseline numbers are provided in the source materials. Use the following.
Prompt for confirming baseline numbers
List every underwriting assumption stated in this pro forma: rent growth rate by year, vacancy assumption, operating expense growth rate, exit cap rate, hold period, and all debt terms including rate, amortization, and any rate caps. Cite the source for each.
Do not proceed until this comes back clean. If the model can’t cite a figure, it may not exist in the uploaded documents, or it may be buried in an exhibit you didn’t upload. If it’s not in the offering materials, ask the GP to provide it. It’s best to have all the figures provided before proceeding in order to receive accurate and complete responses.
Part 3: Solve for Zero
Here we will find how far each variable can go before it breaks. It includes the most relevant common stress points for a single-asset real estate deal:
Interest rate
Exit cap rate
Rent growth
Vacancy
Operating expense growth
If you want to test other variables, use the prompt below as a template. Substitute the variable within the bracketed text and rerun.
Prompt for Solving for Zero
Holding every other assumption at the pro forma baseline, solve for the value of [interest rate on refinance / exit cap rate / annual rent growth / vacancy rate / operating expense growth rate] at which: (a) annual free cash flow after debt service equals zero, (b) the stated DSCR covenant is breached, and (c) the LP equity multiple at exit equals 1.0x. Show the math for each, and identify which of the three thresholds is reached first as the variable moves unfavorably.
Part 4: Gather Historical Market Data
In order to understand the zero points identified in the previous step, we need to gather the historical high, low, and average values. This will require a bit of back and forth before getting the data we need.
Step 1
Go to your preferred AI assistant (Gemini/ChatGPT/Claude). Use the following prompt to gather the pertinent market data.
Prompt for Historical Market Data
Act as a CRE market analyst assembling historical benchmark data for a limited partner.
IMPORTANT — DO NOT SEARCH YET. Before doing anything, you must collect the deal context from me. Ask me for the following, then STOP and wait for my answers. Do not assume, infer, or use any placeholder values — if I have not given you a value, you do not have it.
Ask me for: 1. Asset type 2. Submarket and metro 3. Underwriting/vintage year (the year the deal was underwritten) 4. Historical lookback period — 15, 20, or 30 years
Once I reply with these four, confirm them back to me in one line, then proceed with the search below. If any of the four is missing or ambiguous, ask again before searching.
— AFTER I CONFIRM THE INPUTS, RUN THIS —
Pull historical market data for the asset type and submarket I gave you. For EACH metric below, return the long-term HIGH, LOW, and AVERAGE over my chosen lookback period, plus the most recent reading. Use my full lookback period for every metric; if a metric’s data does not reach that far back, report the longest series available and state its actual start year.
1. Interest rate — prevailing rate for this asset type’s typical loan structure (or the relevant benchmark rate plus typical spread, if the deal-specific rate isn’t independently tracked). 2. Cap rate — market cap rates for this asset type and submarket, covering both entry and exit-year context. 3. Rent growth — year-over-year rent growth for the submarket. 4. Vacancy — submarket vacancy rate. 5. Operating expense growth — year-over-year growth in operating costs (insurance and property tax specifically, where available, since these move independently of general opex inflation) for this asset type and region.
For each metric, also note where the current reading sits relative to the historical range: at, above, or below the long-term average.
Step 2
Here we toggle between the notebook and the AI assistant:
AI Assistant → notebook: copy and paste the deal details requested by the AI assistant into the notebook.
Notebook → AI assistant: copy and paste the deal details from the notebook back into the AI assistant.
AI assistant: specify your preferred lookback period (I suggest using 30 or more years).
AI assistant: send the request.
AI assistant: save the results as a document that can be uploaded to the notebook.
AI assistant: close the AI assistant; we will only use NotebookLM from here.
Notebook: go back to the notebook and upload the market data document to the source documents.
Part 5: Market Cycle Breaking Points
Step 1
Ask NotebookLM to compare the market context against the breaking points identified previously. But, we are not going to do this individually since some variables (such as interest and cap rates) move in tandem. So, we must stress test the variables as they are most likely to be experienced, not just in isolation.
Prompt to run market downturn scenario analysis
Using the source data, previous single-variable sensitivity analysis, and historical market data in this notebook, identify the single most likely compound scenario that reaches a net zero threshold — not a menu of scenarios, and not variables selected arbitrarily.
STEP 1 — MECHANICAL LINKAGE FIRST: Before selecting any historical period, state which of the five variables (Interest Rate, Exit Cap Rate, Annual Rent Growth, Vacancy Rate, Operating Expense Growth Rate) are mechanically linked to each other by real market mechanics — driven by the same underlying force, not merely coincidentally correlated in one past period. Specifically address: Interest Rate and Exit Cap Rate are mechanically linked because cap rates are a function of the cost of capital — state the typical spread or relationship between them if this notebook's market data supports it. Also address whether Vacancy Rate and Annual Rent Growth are mechanically linked through local supply/demand imbalance, and whether Operating Expense Growth has an independent driver or is linked to any of the others.
STEP 2 — SELECT ONE SCENARIO PER DISTINCT MECHANISM: Group the variables into their mechanically linked clusters. For each distinct cluster (maximum two clusters total), select the single historical period in the lookback window where that cluster's variables moved together most severely, and build the scenario using that period's actual magnitudes. If two candidate periods would produce a functionally similar combination for the same cluster, use only the more severe one — do not present near-duplicate scenarios.
STEP 3 — QUANTIFY THE RESULT, NOT JUST THE BREACH: For each scenario, do not stop at stating that a threshold is 'reached.' Calculate the actual resulting value the deal produces under the full combined scenario, for every metric affected — not just the first one crossed. Specifically:
— Actual annual free cash flow under this scenario, in dollars. If negative, state the dollar shortfall and what covers it (GP capital call, reserve draw, or unfunded).
— Actual DSCR achieved under this scenario, as a ratio — not merely 'breached.' State how far below the 1.25x covenant it falls.
— Actual LP equity multiple at exit under this scenario. Convert this into a plain dollar-and-percentage outcome for a hypothetical $100,000 LP investment: total dollars returned, dollar loss, and loss as a percentage of capital.
— State the buffer: compare each of these actual results to the pro forma's original baseline projection for that same metric, so the size of the gap between the business plan and this scenario is explicit, not implied.
Show the calculation steps for each of these, not just the final figures.
For each scenario produced (maximum two):
— Name it using the historical period it's grounded in.
— State which variables move, by how much, and the specific mechanical relationship connecting them.
— Report the Step 3 quantified outcome in full.
— Compare each variable's move in this scenario to that same variable's solo net zero threshold, and state what percentage of the solo threshold this combined move represents.
Format as one clearly labeled section per scenario, ordered from most to least severe. Do not generate an Infographic or any other Studio output — this is a text summary only.
Prompt for infographic
Using the most severe scenario identified above, generate an Infographic in the Studio panel with two sections: a variable-move chart on top, and a quantified outcome panel below it.
SECTION 1 — VARIABLE MOVE CHART:
INCLUDED VARIABLES: Only the variables that move in the selected scenario — exclude any variable that stays flat.
SINGLE SHARED AXIS: All included variables plot against one continuous horizontal percentage axis, spanning from the lowest historical low to the highest threshold among included variables, with consistent spacing per percentage point across every row.
FOR EACH ROW:
— A bar spanning the historical low to the historical high, labeled with both endpoints and the average.
— One marker for that variable’s SOLO net zero threshold, labeled ‘Solo Threshold.’
— One marker for that variable’s ACTUAL move within this combined scenario, labeled with its value.
— A visible bracket or shaded segment connecting the two markers, showing the gap between what it takes alone versus what it takes in combination.
SECTION 2 — OUTCOME PANEL (below Section 1, visually separated by a divider line):
Three side-by-side comparison blocks, one each for Free Cash Flow, DSCR, and LP Equity Multiple. Each block shows exactly two numbers stacked or side-by-side: the Pro Forma Baseline value, and the Scenario Result value, with the gap between them labeled as the buffer that was lost — in dollars for free cash flow and LP outcome, as a ratio for DSCR. For the LP Equity Multiple block, express the result as a dollar loss and loss percentage on a $100,000 LP investment, not just the multiple. Use a consistent visual treatment (e.g., a simple two-bar or two-number comparison) across all three blocks rather than three different chart types.
One caption line below the entire graphic stating the scenario’s name and historical basis.
MINIMAL TEXT throughout: numeric labels only, no explanatory paragraphs within either section.
COLLISION HANDLING: If markers or labels in Section 1 fall within roughly 5 percent of the axis width of each other, stack labels vertically with short leader lines rather than overlapping.
CONSISTENT STYLING: One neutral bar color for every historical range bar in Section 1. Two distinct, consistent marker types across every row of Section 1 — one for ‘Solo Threshold,’ one for ‘Scenario Move.’ A third, distinct visual treatment for the Section 2 outcome blocks, clearly separated from Section 1 so the two sections don’t compete for the eye. Even vertical spacing within each section.
Include a legend for Section 1’s two marker types, positioned once at the top.
Style: ‘Professional’ or ‘Scientific’ visual style. Detail level: Detailed. Orientation: Landscape. Prioritize a clean, uncluttered read — this is a reference chart, not a decorative graphic.
NotebookLM has now provided an effective market-correction sensitivity analysis: you can see exactly where each variable breaks and how investor returns are impacted based on an asset-specific, historically-accurate stress test.
Step 2 (optional)
If you’d like to see the breaking point for each variable as a stand-alone item, those prompts are provided here.
Prompt to compare individual breaking points with historical data
Using the source data and previous sensitivity analysis in this notebook, synthesize a plain-text summary comparing each variable’s net zero thresholds to its historical range.
For each of the five variables — Interest Rate, Exit Cap Rate, Annual Rent Growth, Vacancy Rate, Operating Expense Growth Rate — report:
— Historical Low, High, and Average
— Every net zero threshold that mechanically applies: Free Cash Flow = 0, DSCR Covenant Breach, LP Equity Multiple = 1.0x. If a threshold doesn’t mechanically apply to a variable, state that explicitly rather than omitting it.
— For each applicable threshold, one line stating where it falls relative to the historical range: inside the range, above the high, or below the low, and by how much.
Format as one clearly labeled section per variable, in plain text, ordered as listed above. Do not generate an Infographic or any other Studio output — this is a text summary only.
Prompt to create an infographic
Using the values summarized above, generate an Infographic in the Studio panel. Do not re-derive or re-check the numbers — use exactly the values as given.
Generate the Infographic as a clean, data-first chart, not a decorated infographic. Follow these constraints exactly:
SINGLE SHARED AXIS: All five variables plot against one continuous horizontal percentage axis, spanning from the lowest historical low to the highest threshold value across all variables, with consistent spacing per percentage point across every row. No variable gets its own zoomed-in scale, and no threshold value is ever placed off-axis in a side column — every marker, however extreme, sits at its true proportional position on the shared axis, connected by a thin dashed line from the historical bar if it falls outside the low-high range.
MINIMAL TEXT: Each row gets only a label (the variable name) and numeric labels on the bar endpoints, average tick, and each threshold marker. Do not include explanatory paragraphs, subtitles, or narrative callouts next to any row. If a one-line takeaway is included at all, it goes in a single caption below the entire chart, not beside individual rows.
COLLISION HANDLING: If two or more labels or markers for the same row would fall within roughly 5 percent of the axis width of each other, stack their labels vertically with short leader lines rather than letting them overlap or merge. Never let two numeric labels touch or overlap.
CONSISTENT STYLING: Use a single neutral bar color (one shade, no gradients) for every historical range bar, so all five rows look visually identical except for their data. Reserve color entirely for the three threshold marker types, using the same three marker shapes and colors on every row. Use even, consistent vertical spacing between all five rows.
Include a legend identifying the three threshold marker types, positioned once at the top of the chart, not repeated per row.
Style: choose ‘Professional’ or ‘Scientific’ visual style. Set detail level to Detailed. Set orientation to Landscape. Prioritize a clean, uncluttered read over visual richness — this is a reference chart, not a decorative graphic.
Part 6: Verify the Model’s Work
AI makes mistakes and treats all information with equal weight regardless of source quality. Before relying on any output:
Ask for the underlying calculation on at least one threshold, not just the answer.
If a number looks off, ask the model to show its work rather than accepting a revised number on faith.
Verify investor returns against the waterfall mechanics.
Conclusion of the Pro Forma Series
The pitch deck and the pro forma are not predictions. They’re marketing materials designed to showcase a story that assumes nothing goes wrong. High returns are made bold while risks may be minimized or not disclosed at all.
When reviewing a pitch deck, ask:
Where and how can this break?
What assumptions are flawed, optimistic, or missing altogether?
What conditions would turn this deal upside down, and how likely is that to happen?
Having completed this series, you now know that small changes to inputs can have outsized effects on the output - your investment. More importantly, you don't have to become an LP to test the GP’s projections against market realities; the tools provided here let you test the deal yourself - before you invest. So net zero is more likely to remain in the hypothetical than your portfolio.