Key Takeaways
- Use Excel Data Tables or Google Sheets automation to populate two-variable sensitivity matrices efficiently.
- Conditional formatting with green/yellow/red zones makes sensitivity tables instantly readable for decision-makers.
- Five essential tables cover return adequacy, debt safety, and execution risk for comprehensive analysis.
- Always mark the base case cell with a bold border so readers can locate the expected outcome immediately.
The two-variable sensitivity table is the most impactful analytical output in any acquisition model. This lesson provides step-by-step instructions for building sensitivity tables in Excel/Google Sheets using Data Tables, and demonstrates how to create professional-quality tables with conditional formatting that immediately highlights which assumption combinations meet investment criteria.
Step-by-Step Data Table Construction
In Excel, two-variable data tables use the Data Table feature (What-If Analysis menu). Step 1: Define your row input variable (e.g., exit cap rate) values across the top row: 6.00%, 6.50%, 7.00%, 7.50%, 8.00%, 8.50%. Step 2: Define your column input variable (e.g., NOI growth rate) values down the left column: 0%, 1%, 2%, 3%, 4%. Step 3: In the top-left corner cell, enter a formula linking to your target output cell (e.g., =IRR_cell). Step 4: Select the entire table range, go to Data > What-If Analysis > Data Table, set the row input cell to your exit cap assumption cell and the column input cell to your NOI growth assumption cell. Step 5: Excel automatically populates every cell with the IRR for that combination of inputs. In Google Sheets, replicate this manually using OFFSET and indirect references, or use Apps Script for automation.
Professional Formatting with Conditional Rules
Apply conditional formatting to make the sensitivity table instantly readable. Use a three-color scale: green for IRR above your hurdle rate (e.g., 15%+), yellow for IRR between your minimum threshold and hurdle (e.g., 10-15%), and red for IRR below your minimum (e.g., below 10%). Add a bold border around the cell representing your base case assumptions so the reader can immediately locate the expected outcome and see how much room exists before reaching the red zone. For DSCR sensitivity tables, use green for DSCR above 1.40x, yellow for 1.20-1.40x, and red for below 1.20x. The visual impact of a well-formatted sensitivity table far exceeds its numerical precision—decision-makers process color patterns faster than number grids.
The Five Essential Sensitivity Tables
Every acquisition model should include at least five sensitivity tables. Table 1: IRR by exit cap rate and NOI growth rate (the most critical table). Table 2: Cash-on-Cash by vacancy rate and rent level. Table 3: DSCR by vacancy rate and interest rate. Table 4: Equity Multiple by exit cap rate and holding period. Table 5: For value-add deals, IRR by renovation cost and rent premium achieved. These five tables cover the full spectrum of investor concerns: return adequacy (Tables 1, 2, 4), debt safety (Table 3), and execution risk for value-add (Table 5). Present these tables in the investment memo as a dedicated sensitivity analysis section.
| Table | Row Variable | Column Variable | Target Output | Primary Audience |
|---|---|---|---|---|
| 1 | NOI Growth | Exit Cap Rate | IRR | Equity investors |
| 2 | Rent Level | Vacancy Rate | Cash-on-Cash | Income-focused investors |
| 3 | Interest Rate | Vacancy Rate | DSCR | Lenders |
| 4 | Hold Period | Exit Cap Rate | Equity Multiple | LP investors |
| 5 | Renovation Cost | Rent Premium | IRR | Value-add sponsors |
Five essential sensitivity tables for acquisition models
Guided Practice: Building an IRR Sensitivity Table for the 20-Unit Example
You need to create the exit cap rate / NOI growth sensitivity table for the 20-unit acquisition to include in your investment memo.
- 1Set up the table grid: exit cap rates across the top (6.50% to 8.50% in 0.50% steps), NOI growth rates down the left (0% to 4% in 1% steps).
- 2Link the top-left cell to your model's IRR output cell.
- 3Select the full range and create a Data Table with row input = exit cap assumption cell, column input = NOI growth assumption cell.
- 4Apply conditional formatting: green for IRR >= 15%, yellow for 10-15%, red for < 10%.
- 5Bold-border the cell at 7.25% exit cap / 2% NOI growth (base case)—it shows approximately 18% IRR.
- 6Verify the table: at 8.50% exit cap / 0% NOI growth, IRR should be the lowest (~5.8%); at 6.50% exit cap / 4% NOI growth, IRR should be the highest (~23.6%).
Key Takeaways
- ✓Use Excel Data Tables or Google Sheets automation to populate two-variable sensitivity matrices efficiently.
- ✓Conditional formatting with green/yellow/red zones makes sensitivity tables instantly readable for decision-makers.
- ✓Five essential tables cover return adequacy, debt safety, and execution risk for comprehensive analysis.
- ✓Always mark the base case cell with a bold border so readers can locate the expected outcome immediately.
Sources
- Argus Software — Sensitivity and Scenario Analysis(2025-01-15)
- CCIM Institute — Advanced Financial Modeling(2025-01-15)
Common Mistakes to Avoid
Building sensitivity tables with assumption ranges that are too narrow
Consequence: Narrow ranges miss tail risks and give a false impression of stability
Correction: Use ranges that span at least the historical min and max for each variable in the relevant market
Presenting sensitivity tables without identifying the base case cell
Consequence: Readers cannot quickly identify the expected outcome versus upside and downside scenarios
Correction: Always highlight or border the base case intersection and label it clearly in the table
Test Your Knowledge
1.What is the recommended structure for a two-variable sensitivity table?
2.How should conditional formatting be applied to sensitivity tables?
3.Which two variables are most commonly paired in IRR sensitivity tables?