NPV answers the question IRR dodges: how much value, in money, the deal creates at the return you require. The arithmetic is short; the Excel trap is in the first cell.
Net present value is the present value of every year's NOI and of the net sale proceeds, discounted at the return you require, minus the total cost of buying the property. On an illustrative 15,000,000 office with 900,000 of NOI, a ten-year hold and a 7.0 per cent required return, the cash flows are worth 16,215,354 today against 15,600,000 of cost: an NPV of 615,354. Positive NPV means the deal beats your hurdle; the figure says by how much, in money.
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 →
A fictional single-let office bought unlevered. All figures are illustrative, not market data. Debt is left out on purpose: NPV on the property's own cash flows judges the real estate, and the financing is tested separately.
| Input | Value |
|---|---|
| Purchase price | 15,000,000 |
| Acquisition costs (tax, legal, agents), 4.0% | 600,000 |
| Total cost, year 0 | 15,600,000 |
| Year 1 NOI (6.00% on price, 5.77% on total cost) | 900,000 |
| NOI growth a year | 2.5% |
| Hold | 10 years |
| Exit cap rate, on year 11 NOI | 6.25% |
| Sale costs | 1.5% |
| Discount rate (required unlevered return) | 7.0% |
Step 1: the sale. A buyer in year 10 pays for the income it will receive next, so the exit cap is applied to year 11 NOI.
Year 11 NOI = 900,000 × 1.02510 = 1,152,076
Gross sale = 1,152,076 / 6.25% = 18,433,217; less 1.5% costs of 276,498 = net sale 18,156,719
Step 2: discount each year. The discount factor for year t is 1 / (1 + r)t.
| Year | NOI | Discount factor | PV of NOI |
|---|---|---|---|
| 1 | 900,000 | 0.9346 | 841,121 |
| 2 | 922,500 | 0.8734 | 805,747 |
| 3 | 945,562 | 0.8163 | 771,861 |
| 4 | 969,202 | 0.7629 | 739,399 |
| 5 | 993,432 | 0.7130 | 708,303 |
| 6 | 1,018,267 | 0.6663 | 678,515 |
| 7 | 1,043,724 | 0.6227 | 649,979 |
| 8 | 1,069,817 | 0.5820 | 622,643 |
| 9 | 1,096,563 | 0.5439 | 596,457 |
| 10 | 1,123,977 | 0.5083 | 571,373 |
| PV of income | 6,985,399 | ||
| Net sale, year 10 | 18,156,719 | 0.5083 | 9,229,955 |
| PV of all cash flows | 16,215,354 |
Step 3: deduct the cost.
NPV = PV of cash flows − total cost = 16,215,354 − 15,600,000 = 615,354
Excel, with year 0 in B2 and years 1 to 10 in C2:L2 (year 10 including the net sale): =NPV(7%,C2:L2)+B2
With dated cash flows: =XNPV(7%,B2:L2,B1:L1), where row 1 holds the dates and the first date is the purchase.
An NPV of 615,354 is 3.9 per cent of the total cost. It means you could pay up to 591,686 more for the building, a price of 15,591,686 (a going-in cap of 5.77 per cent), and still earn exactly 7.0 per cent: that is the break-even price, worked the other way in how to back-solve a purchase price for a target IRR. The deal's IRR is 7.53 per cent, the rate at which the NPV would be zero, so the two measures agree: positive NPV, IRR above the hurdle.
Look at where the value sits. The sale supplies 9,229,955 of the 16,215,354, or 56.9 per cent; ten years of rent supply 43.1 per cent. More than half the NPV of a ten-year hold rests on one assumption, the exit cap, which is why the sensitivity below matters more than the base case.
| Discount rate | Exit cap 5.75% | Exit cap 6.25% | Exit cap 6.75% |
|---|---|---|---|
| 6.5% | 2,070,126 | 1,229,034 | 512,548 |
| 7.0% | 1,417,959 | 615,354 | −68,346 |
| 7.5% | 795,970 | 29,923 | −622,634 |
| 8.0% | 202,582 | −528,729 | −1,151,697 |
| 8.5% | −363,689 | −1,061,989 | −1,656,837 |
A 100 basis point rise in the discount rate removes 1,144,082 of NPV; a 50 basis point wider exit removes 683,700, enough to turn the deal negative at 7.0 per cent. Growth is as powerful: at 1.5 per cent a year the NPV is minus 522,262 and the IRR 6.54 per cent; at 3.5 per cent it is 1,846,184 and 8.52 per cent. An NPV that survives only one row of this grid is a bet on that row.
The discount rate is your required unlevered return for this asset's risk, not your borrowing rate. Discounting property cash flows at a 5 per cent cost of debt makes almost every deal look valuable, because the equity's risk premium has vanished from the test.
Excel's =NPV() assumes the first cell of its range is received one period from today. Select the whole row, purchase included, and the function discounts the purchase price by a year: it returns 575,097 instead of 615,354, an error of 40,257 that grows with the discount rate and the deal size. The opposite slip is to discount years 1 to 10 and forget to add year 0 at all, which reports the PV of 16,215,354 as if it were the NPV. Keep year 0 outside the function, as in the formula above, and check the answer by hand on one year.
The second mistake is leaving acquisition costs out of year 0. On this office they are 600,000; drop them and the NPV is overstated by the same amount, nearly doubling it.
=NPV(), or use =XNPV() with dates.The book builds the income account, the sale and the return year by year on one estate; the live models are in the free workbook for this case, and the same build is set out in how to calculate IRR in Excel for real estate.
Your required unlevered return for that asset's risk: the return you could earn on a comparable property, not your cost of debt. In the illustrative office, moving the rate from 7.0 to 8.0 per cent takes NPV from 615,354 to minus 528,729, a swing of 1,144,082. The rate matters more than any single cash flow, so state it and show the grid.
NPV tells you how much value a deal adds in money at your hurdle; IRR tells you the rate it earns. For a single accept or reject decision they agree: the office returns 7.53 per cent, above a 7.0 per cent hurdle, and its NPV is positive at 615,354. When deals differ in size or timing, rank them by NPV, because a high IRR on a small cheque can create less value.
=NPV() assumes the first value in the range arrives one period from now. Put the year 0 purchase inside it and Excel discounts the price by a year, here returning 575,097 instead of 615,354. Use =NPV(rate, years 1 to 10) plus the year 0 cell, or =XNPV() with real dates.
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 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.