The IRR function is one cell; what decides the answer is the column before it: purchase costs, the void, the sale on the forward year's NOI and the timing convention.
To calculate a property IRR in Excel, lay out one column per period: the price including purchaser's costs as a negative in year 0, net operating income in each year of the hold, and the sale price net of selling costs added to the final year, then apply =IRR() to the row. An illustrative 20,000,000 office bought for 21,360,000 with costs, held ten years and sold at a 6.25 per cent exit yield, returns 6.84 per cent. Most errors are in the row, not the formula: leaving out purchase costs alone would show 7.72 per cent.
Worked in full in Commercial Real Estate Investing by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
The IRR is the discount rate at which the present value of every cash flow, out and in, is zero. Excel solves it by iteration in a single cell. That makes the calculation easy and the inputs dangerous, because each convention in the cash-flow row moves the answer by more than most negotiations on price do.
| Input | Value |
|---|---|
| Net price to the vendor | 20,000,000 |
| Purchaser's costs (illustrative) | 6.8% |
| Gross outlay, year 0 | 21,360,000 |
| NOI, year 1 | 1,200,000 |
| NOI growth, a year | 2.5% |
| Lease expiry: void and re-letting cost in year 4 | 600,000 |
| Hold, years | 10 |
| Exit yield, on the forward (year 11) NOI | 6.25% |
| Selling costs | 2.0% |
0 = Σ CFt ÷ (1 + IRR)t, for t = 0 to 10
Net sale = NOI11 ÷ exit yield × (1 − selling costs)
With the outlay in B10 and the ten annual totals in C10:L10, =IRR(B10:L10). With a row of dates in B9:L9, =XIRR(B10:L10,B9:L9). Build the NOI to year 11 even though the hold ends in year 10, because that is the year the buyer prices.
| Year | NOI | Sale, net | Cash flow |
|---|---|---|---|
| 0 | -21,360,000 | ||
| 1 | 1,200,000 | 1,200,000 | |
| 2 | 1,230,000 | 1,230,000 | |
| 3 | 1,260,750 | 1,260,750 | |
| 4 (void) | 692,269 | 692,269 | |
| 5 | 1,324,575 | 1,324,575 | |
| 6 to 9 | 1,357,690 to 1,462,083 | ||
| 10 | 1,498,636 | 24,086,071 | 25,584,706 |
| IRR | 6.84% |
The sale is year-11 NOI of 1,536,101 at 6.25 per cent, 24,577,623, less 2 per cent: 24,086,071. Over the hold the property returns 36,930,129 for 21,360,000, an equity multiple of 1.73x and a profit of 15,570,129. The IRR says how fast that profit arrived; the multiple says how much it was. Report both.
| Version | IRR |
|---|---|
| Base case, =IRR on annual flows | 6.84% |
| =XIRR with annual dates | 6.83% |
| Rent quarterly in arrears, =XIRR on quarter-end dates | 6.97% |
| Rent quarterly in advance, =XIRR | 7.08% |
| Purchaser's costs left out | 7.72% |
| Selling costs left out | 6.99% |
| Sale capitalised on year-10 NOI | 6.65% |
| Sale put in a separate year-11 column | 6.36% |
The timing of rent is real money: quarterly rent in advance, as most UK leases pay, is worth 24 basis points over annual in arrears on the same income. On quarterly columns, use =IRR and annualise it by compounding, (1 + 1.701 per cent)4 − 1 = 6.98 per cent, not by multiplying by four, which gives 6.80; the reason is set out in how to annualise a quarterly IRR.
Read the base case against its own inputs. The property was bought on a net initial yield of 5.62 per cent on the gross outlay, NOI grows 2.5 per cent a year, and the exit yield of 6.25 per cent is higher than the entry. Yield plus growth would suggest about 8 per cent; the IRR is 6.84 because the void, the purchase costs, the selling costs and the softer exit yield each take a slice. A model whose IRR sits above yield plus growth with an exit yield above entry deserves a second look.
Capitalising year 10 instead of year 11 costs 587,465 of sale price here, because year 10's NOI is a year of growth short. On a building with a lease event near the exit the gap is far larger, which is the case for building the forward year explicitly. The full argument is in forward or trailing NOI for the exit cap rate.
| Exit yield | 5-year hold | 7-year hold | 10-year hold |
|---|---|---|---|
| 5.75% | 6.82% | 7.21% | 7.49% |
| 6.25% | 5.30% | 6.18% | 6.84% |
| 6.75% | 3.93% | 5.26% | 6.24% |
The bought-in yield is 5.62 per cent on the gross outlay, so any exit above it gives back part of the rental growth as a lower value for each unit of income. The shorter the hold, the less time it has: half a point of exit yield moves the five-year IRR by about 1.44 points and the ten-year by about 0.62. A short-hold IRR is mostly a forecast of the exit yield.
The formula is =IRR on a row that starts with the price plus costs, carries each year's NOI with its voids, and ends with the sale on the forward year's NOI net of costs: 6.84 per cent here. Then switch to XIRR if the rent is quarterly, and quote the multiple beside it. To add debt to the same row, see levered vs unlevered IRR in real estate. Commercial Real Estate Investing builds one estate's income account with every re-letting applied and prices its sale on the right year; the free workbooks for this case carry both as live formulas.
Use IRR when every cash flow falls on an equal period, such as annual or quarterly columns, and XIRR when flows carry real dates. On an illustrative annual model they give 6.84 and 6.83 per cent. XIRR matters when rent is quarterly: dated quarterly in arrears, the same deal returns 6.97 per cent, and quarterly in advance 7.08 per cent.
The buyer's first year, so the forward NOI of the year after the sale. On an illustrative ten-year hold the year-11 NOI at a 6.25 per cent exit yield, less 2 per cent costs, gives a net sale of 24,086,071. Capitalising year 10 instead takes 587,465 off the price and the IRR from 6.84 to 6.65 per cent.
It depends on the risk and on the rate environment, so set it against a hurdle, not a rule of thumb. An illustrative core-plus office earning a 5.62 per cent net initial yield with 2.5 per cent growth returns 6.84 per cent unlevered over ten years; an equity multiple of 1.73x shows the same deal in money.
This article is one calculation from Commercial Real Estate Investing. 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.