How the model works
What the engine decides that a template leaves to you, how it works backwards from the return you need, and why every cell in a grid is the whole model run again.
Why not a spreadsheet
Seven things a spreadsheet template gets wrong
Each of these is a decision in the code with a test holding it in place. Together they are the reason to use this rather than the template you already have.
-
Leases actually expire
At expiry the space rolls on a weighted-average basis: rent resets to market, tenant improvements and leasing commissions blend renewal and new-deal terms by renewal probability, and expected downtime is (1 − p) × downtime. A five-year lease inside a ten-year hold does not quietly pay rent forever at a compounding escalator. And where you know better than the blend (a renewal already signed, a tenant who has given notice), you say so for that one expiry, and the ones around it stay on the assumptions.
-
The exit capitalizes forward NOI
The twelve months modeled past the hold, not the trailing year grossed up. On a lease-up deal the trailing year still contains partial occupancy, and capitalizing it understates the sale by exactly the value you spent two years creating.
-
NPV and IRR agree what “10%” means
Loan interest accrues nominally: APR/12, the way lenders quote it. Return metrics are effective. Mixing the two conventions is the classic modeling error, and it is silent.
-
Credit loss applies to occupied rent only
Physical vacancy comes from the rent roll, so vacancy and credit loss are never charged against the same square foot twice. A suite with no lease start earns nothing and reports what the empty months cost, in rent forgone and in the recoveries that space would have reimbursed.
-
Every hurdle is measured on the LP's cash flow
Which is what a term sheet quotes. The model asserts that LP plus GP reconciles to the project cash flow in every single month. Not at the end: every month. Sponsor fees are deducted before the promote rather than folded into it.
-
Every term in a subtotal has a row
If a figure moves the levered cash flow it is printed on a line you can see: the origination fee, the loan payoff at sale, the refinance costs, a prepayment penalty. That test is written as arithmetic over the model rather than as a check on the row list, because a row list can be edited into agreement with a mistake.
-
When there are two IRRs, it says so
A land deal that sells lots early, then carries the land and pays off a loan, has cash flows that change sign more than once, and more than one rate that sets NPV to zero. Excel’s IRR() returns whichever it finds first. Here the reported IRR is the root where NPV falls through zero, and the tile and the memo say there is more than one and point you at NPV and the multiple instead. Before that rule, a deal with a positive NPV and a 1.66x multiple could show −38.6%.
Working backwards
What would it take?
Name the return you need and the model tells you what would have to move to get there. Every answer is a run of the full model that reaches the target, not a slope read off a chart.
One lever, several, or two traded
- Each lever alone: the exit cap, the rent, the cost, the rate: how far each would have to move by itself.
- All of them together, each moving the same share of its own distance, so nothing carries the whole answer.
- Two traded against each other on a curve: a shorter hold for more rent, and every mix between that reaches the target.
- Caps and locks. Say how far a lever may move, untick what cannot move, and a locked field stays where it is.
- Margin of safety, the same question asked the other way: how far each assumption can go wrong before the deal misses.
- Room either way, beside the levers and in the memo: one line per assumption, today in the middle, how far it can fall before the deal misses on one side and how far it has to move to reach the target on the other, both drawn to scale.
Try it · drag a lever, or ask for a return
What moves the return
Every cell is the whole model, run again
The Analysis tab’s grid, on the sample retail deal: exit cap rate across, rent down the side. Each cell below is a full run of the engine in this page (no interpolation between corners), shaded by outcome, with the deal as entered ringed in the middle.
Live · levered IRR, one engine run per cell