A bid is a model run backwards, and the levered and unlevered targets rarely give the same ceiling.
The maximum price for a target IRR is the present value of the property's cash flows at that IRR, divided by one plus the acquisition costs. On a fictional building with 2,000,000 of year-1 NOI, a five-year hold and a 6.25 per cent exit cap, the flows are worth 32,912,390 at 7.50 per cent, so the most a buyer can pay is 31,646,529, a 6.32 per cent going-in cap. With 55 per cent debt and an 11.00 per cent equity target, the ceiling falls to 30,564,202.
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 →
Most models are built forwards: enter a price, read the IRR. A bid is built backwards: set the return the capital requires and solve for the price. Goal Seek will do it, but the closed form is one line, is faster to audit, and shows exactly which assumption the price depends on. It also makes clear that the levered and unlevered targets can give different answers, and the lower one is the bid.
| Input | Value |
|---|---|
| Year-1 NOI | 2,000,000 |
| NOI growth | 2.50% |
| Capex reserve, each year | 100,000 |
| Hold | 5 years |
| Exit cap rate, on year-6 NOI | 6.25% |
| Selling costs | 1.50% |
| Acquisition costs, on price | 4.00% |
| Target unlevered IRR | 7.50% |
| Loan-to-value, interest-only rate | 55%, 5.75% |
| Target levered IRR | 11.00% |
| Year | NOI | Cash flow after capex |
|---|---|---|
| 1 | 2,000,000 | 1,900,000 |
| 2 | 2,050,000 | 1,950,000 |
| 3 | 2,101,250 | 2,001,250 |
| 4 | 2,153,781 | 2,053,781 |
| 5 | 2,207,626 | 2,107,626 |
| 5, sale net of costs | 35,661,987 |
The exit is year-6 NOI of 2,262,816 at 6.25 per cent, 36,205,063 gross, less 1.50 per cent of selling costs.
Unlevered: Price = NPV(target, CF1..CFn) ÷ (1 + acquisition costs)
Levered, loan = LTV × Price, interest-only:
Price = NPV(re, CF) ÷ [1 + costs − LTV + LTV × (rate × AF + DF)]
AF is the annuity factor and DF the discount factor at the levered target over the hold. In Excel: =NPV(Target,C2:C6)/(1+AcqCost). Excel's NPV already discounts the first flow by one period, which is what you want here; do not include a time-zero cell in the range.
The levered version is still linear in the price, because the loan, the interest and the repayment are all a fixed share of it. At 11.00 per cent the annuity factor over five years is 3.6959 and the discount factor 0.5935. The unlevered flows are worth 28,524,988 at that rate, and the formula gives a price of 30,564,202: a loan of 16,810,311 and equity of 14,976,459, including the acquisition costs. Feeding that price back in returns an 11.00 per cent levered IRR.
The two targets disagree. At 30,564,202 the unlevered IRR is 8.34 per cent, comfortably above 7.50, yet the equity only just reaches 11.00. At the unlevered ceiling of 31,646,529 the equity would earn only 9.34 per cent: the spread between the asset's 7.50 per cent and the debt's 5.75 per cent is too thin to lift it to 11.00 on 55 per cent leverage, so the price has to fall until the asset earns 8.34 per cent. The binding constraint is the levered target, and the bid is 30,564,202, not 31,646,529. Whichever target the investment committee actually holds the deal to, solve for both and bid the lower.
Why not just Goal Seek? Goal Seek gives the same answer if the model has no circularity, but it hides the structure. The closed form shows that each extra point of acquisition cost removes about one per cent of the price, and that the exit flows carry most of the present value. When the loan is sized on something other than the price, such as a debt yield, the closed form breaks and Goal Seek or a data table is the right tool.
| Target IRR | Exit 5.75% | Exit 6.25% | Exit 6.75% |
|---|---|---|---|
| 6.50% | 35,179,967 | 33,003,629 | 31,149,712 |
| 7.00% | 34,441,879 | 32,315,918 | 30,504,913 |
| 7.50% | 33,723,508 | 31,646,529 | 29,877,251 |
| 8.00% | 33,024,229 | 30,994,886 | 29,266,185 |
| 8.50% | 32,343,445 | 30,360,431 | 28,671,198 |
Half a point on the target return moves the price by 651,643 to 669,389. Half a point on the exit cap moves it by 1,769,278, more than two and a half times as much. On a five-year hold the exit is most of the value, so the exit cap is the assumption the bid really rests on; the break-even exit yield is the same question asked the other way round.
The common mistake is to treat the NPV as the price. Bidding 32,912,390 means paying the acquisition costs on top, 1,316,496 in this case, and the IRR falls to 6.57 per cent: almost a full point lost to a line the model knew about. The second mistake is to discount with Excel's NPV over a range that includes the time-zero cell, which pushes every flow one year too far and understates the price. A forward IRR check on the final bid catches both; calculating the IRR in Excel covers the conventions.
Discount the flows at the target, divide by one plus the costs, and solve the levered case too: 31,646,529 unlevered, 30,564,202 levered, and the bid is the lower. Then spend the diligence time on the exit cap, which moves the answer more than two and a half times as much as the target. The free workbooks for this book include a development practice case where the land cell is the one you iterate, the same backwards solve applied to a scheme.
Point Goal Seek at the IRR cell, set it to the target and change the price input. It works if nothing else in the model refers back to the IRR. In the worked case it lands on 31,646,529 for a 7.50 per cent unlevered target, the same as NPV at 7.50 per cent of 32,912,390 divided by 1.04. The closed form is quicker to audit.
Solve both and bid the lower. In the example the unlevered 7.50 per cent target allows 31,646,529, but at that price 55 per cent debt at 5.75 per cent gives the equity only 9.34 per cent. To reach an 11.00 per cent levered target the price must fall to 30,564,202, where the unlevered IRR is 8.34 per cent.
Usually the exit cap rate, because on a short hold the sale is most of the present value. In the worked case, 50 basis points on the exit cap moves the ceiling by 1,769,278, while 50 basis points on the target IRR moves it by 651,643 to 669,389. That is 2.64 times the effect.
This article is one calculation from 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 453 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.