The Excel mechanics of a two-way sensitivity on exit cap rate and rental growth, the grid it produces on one levered deal, and how to read the trade-off line off it.
Put a reference to the IRR in the top-left corner of a grid, type one input's test values across the top row and the other's down the first column, select the block and run Data, What-If Analysis, Data Table, giving the exit cap rate cell as the row input and the growth cell as the column input. On an illustrative levered office with a 7.48 per cent base IRR, the table runs from 0.29 to 14.17 per cent, and it shows that 25 basis points on the exit is worth 71 basis points a year of rental growth.
Worked in full in Real Estate Financial Modeling by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
A one-way sensitivity tells a committee which inputs matter. A two-way table tells it how two of them trade against each other, which is the question an investment paper actually has to answer: how much growth do we need if the exit is softer than we think? Excel's data table does this without macros or copies of the model, as long as it is set up the way Excel expects.
| Input | Value |
|---|---|
| Price / acquisition costs | 25,000,000 / 5.0% |
| NOI, year 1 (a 6.00% yield) | 1,500,000 |
| Rental growth (column input) | 2.5% |
| Exit cap rate on year-6 NOI (row input) | 6.25% |
| Sale costs | 1.5% |
| Loan: 55% LTV, interest-only at | 5.25% |
The loan is 13,750,000 and the equity 12,500,000 including costs. Equity cash flows are −12,500,000, then 778,125 rising to 893,461, and 13,930,334 in year 5 after repaying the loan from a gross sale of 27,153,797 on year-6 NOI of 1,697,112. The levered IRR is 7.48 per cent.
Corner cell (say H10): =Returns!Levered_IRR, or a link to wherever the IRR lives
Row across the top (I10:M10): exit cap rates 5.75%, 6.00%, 6.25%, 6.50%, 6.75%, typed as values
Column down the side (H11:H15): growth rates 0.5%, 1.5%, 2.5%, 3.5%, 4.5%, typed as values
Run it: select H10:M15, Data › What-If Analysis › Data Table, row input cell = the exit cap rate input, column input cell = the growth input
Excel fills the block with {=TABLE(Exit_Cap,Growth)}. Each cell is the whole model recalculated with that pair of inputs.
Three rules make it work. The two input cells must be on the same sheet as the table, so build the table on the assumptions sheet or move the two inputs there. The axis values must be typed, not formulas that refer to the inputs, or the table becomes circular. And the base values must sit in the middle of each axis so the centre cell can be checked against the model.
| Growth | 5.75% | 6.00% | 6.25% | 6.50% | 6.75% |
|---|---|---|---|---|---|
| 0.5% | 6.47% | 4.88% | 3.33% | 1.80% | 0.29% |
| 1.5% | 8.49% | 6.95% | 5.45% | 3.97% | 2.52% |
| 2.5% | 10.44% | 8.94% | 7.48% | 6.05% | 4.65% |
| 3.5% | 12.33% | 10.86% | 9.44% | 8.04% | 6.68% |
| 4.5% | 14.17% | 12.73% | 11.33% | 9.97% | 8.64% |
The centre cell reproduces the model's 7.48 per cent: that is the first check, and if it fails the input cells are wrong. From the centre, one step right (exit +25 bp) costs 1.43 points and one step up (growth −1.0 point) costs 2.03 points. The full grid spans 13.88 points.
The diagonal is the useful part. Moving the exit from 6.25 to 6.50 per cent and solving for the growth that restores 7.48 per cent gives 3.21 per cent, so 25 basis points of exit is worth 71 basis points a year of rental growth on this deal. A sponsor who argues that a softer exit will be "covered by growth" has to defend a 3.21 per cent growth rate, not a 2.5.
| Growth | Exit cap for 8.0% |
|---|---|
| 0.5% | 5.51% |
| 1.5% | 5.83% |
| 2.5% | 6.16% |
| 3.5% | 6.51% |
| 4.5% | 6.87% |
Against an 8.0 per cent hurdle, 12 of the 25 cells pass. Conditional formatting on the table, with the hurdle in its own cell, draws that boundary on the page; the second table gives it exactly. At the base growth the deal needs an exit of 6.16 per cent or tighter, 9 basis points inside the 6.25 per cent assumed: on its own base case it misses the hurdle.
Make the axes symmetric and realistic. Steps of 25 bp on the exit and 1 point on growth here are deliberately unequal because the plausible ranges are unequal. A table whose steps are chosen to look comfortable is worse than none; set the ranges from evidence before the result is known, as for a tornado chart for property IRR.
The common mistake is to build the table and leave it unchecked. With calculation set to "automatic except for data tables", the grid shows stale values until F9 is pressed; with the inputs on another sheet Excel refuses the table, and modellers work around it by pointing the input cells at a copy, so the grid moves nothing. A centre cell that does not equal the model output is the signal. The second mistake is to read the worst corner, 0.29 per cent, as the sum of two independent bad cases: on this levered deal it is 0.21 points worse than adding the two single moves, which is the leverage effect worked in whether a combined downside is worse than the sum.
A two-way data table is five minutes of work: IRR in the corner, typed axes, row and column input cells on the same sheet, and a centre cell that matches the model. Read it for the trade-off line, here 71 basis points of growth per 25 of exit, and for the hurdle boundary. The complete sensitivity page, with the two-way table on the top two drivers, is built for the book's model in the free workbook for this case.
Usually because calculation is set to automatic except for data tables, so press F9, or because the row and column input cells point to the wrong inputs or to a different sheet. The input cells must be on the same sheet as the table. Check that the cell at the base values reproduces the model's 7.48 per cent.
The two that move the IRR most, found from a tornado chart first. For a levered five-year hold that is usually the exit cap rate and rental growth: here one step of 25 basis points on the exit moves the IRR 1.43 points and one point of growth moves it 2.03.
Find the cells either side of the target and solve each row for it. Against an 8.0 per cent target, 12 of the 25 cells pass; at 2.5 per cent growth the exit cap rate that delivers 8.0 per cent is 6.16 per cent, and at 0.5 per cent growth it is 5.51.
What a sensitivity section owes a committee, including the two-way table on the top two drivers, is specified in chapter 15 of Real Estate Financial Modeling. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: real estate investing, finance and fund management → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.