Annualising the IRR of a quarterly property model, and why the popular rule of thumb has the sign of the error backwards.
No. Compound it: annual IRR = (1 + quarterly IRR)4 − 1. On an illustrative levered deal with a quarterly IRR of 3.091 per cent, multiplying by four gives 12.36 per cent; compounding gives 12.95 per cent, 59 basis points higher. Times four understates at every positive rate, and the error grows with the square of the return.
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 →
Property models run quarterly because rent is paid quarterly, debt service is quarterly and lease events fall on quarter days. Excel's IRR applied to a quarterly column returns a quarterly rate. Somebody then has to turn it into the annual figure the committee paper quotes, and the quickest route, multiplying by four, is the wrong one. A rule of thumb in circulation says the shortcut overstates by about a hundred basis points. It does the opposite.
| Item | Value |
|---|---|
| Equity invested at quarter 0 | 10,000,000 |
| Distribution each quarter, quarters 1 to 20 | 180,000 |
| Net exit proceeds to equity, quarter 20 | 13,500,000 |
| Total distributions | 17,100,000 |
The equity multiple is 1.71 times over five years. Run =IRR() on the 21 quarterly flows and it returns 3.091 per cent. That is a rate per quarter, and it is exact.
Effective annual IRR = (1 + q)4 − 1
(1 + 3.091%)4 − 1 = 12.95%, against 4 × 3.091% = 12.36%
In Excel: =(1+IRR(C10:W10))^4-1, or =XIRR(C10:W10,C4:W4) on the quarter dates, which returns an effective annual rate directly.
Why compounding is right: an investor earning 3.091 per cent a quarter has its distributions available for reinvestment at the same rate, which is exactly what the IRR assumes. A dollar left in the deal for a year grows by four successive quarters of 3.091 per cent, not by one step of four times that. Multiplying by four produces a nominal rate compounded quarterly, a different unit from the effective annual rate every other number on the page uses.
The size of the gap follows from the expansion (1 + q)4 = 1 + 4q + 6q2 + …. The shortcut keeps the first two terms and drops the rest, so the error is roughly six times the square of the quarterly rate: small at low returns, large at high ones, and never negative.
| Quarterly IRR | Times four | Compounded | Understatement, bp |
|---|---|---|---|
| 1.0% | 4.00% | 4.06% | 6 |
| 2.0% | 8.00% | 8.24% | 24 |
| 2.5% | 10.00% | 10.38% | 38 |
| 3.0% | 12.00% | 12.55% | 55 |
| 4.0% | 16.00% | 16.99% | 99 |
| 5.0% | 20.00% | 21.55% | 155 |
The hundred basis points in the rule of thumb is real, but it belongs to a quarterly IRR of about 4.03 per cent, an effective annual 17.1 per cent. At core and core-plus returns the error is a few dozen basis points: 23 at an effective 8.07 per cent, 43 at 11.07. At opportunistic returns it exceeds a hundred and keeps growing.
The error matters most where a return is compared with a threshold. If the investment committee's hurdle for this deal is 12.5 per cent, the shortcut reports 12.36 and the deal misses; the correct 12.95 clears it. The same applies to a joint venture promote tier struck at an IRR, and to a fund's target return. Nothing in the deal has changed, only the arithmetic used to describe it.
There is a second shortcut that errs in the same direction. Summing each year's four quarterly flows into a single year-end figure and running an annual IRR gives 12.64 per cent on this deal, 31 basis points below the true 12.95, because it pretends every distribution arrived on 31 December. It is less wrong than times four and still wrong, and it discards the quarterly precision the model was built to deliver.
The same rule applies to any period. A development model run monthly returns a monthly IRR, and the error from multiplying by twelve is larger still, because there are more compounding steps to drop. A monthly IRR of 1 per cent is 12.68 per cent effective, not 12. Whatever the period of the model, raise one plus the periodic rate to the number of periods in a year and subtract one, and label the result as an effective annual rate.
XIRR from the fund model will show two different numbers for the same flows. A reviewer will find it, and the explanation costs more credibility than the error.Check in ten seconds: discount the quarterly flows at (1 + annual IRR)0.25 − 1. If the annual IRR is right, the NPV is zero. At 12.95 per cent it is; at the times-four 12.36 per cent the same test leaves 226,199 of value unexplained.
Compute the IRR in the model's own period, then compound it to an effective annual rate, or use XIRR on real dates and skip the conversion. On this deal that is 12.95 per cent, not 12.36. The free companion workbooks for the book include the complete Meridian House model, whose income engine runs quarterly, and a related article shows what happens to the same kind of model under combined downside cases.
Use =(1+IRR(range))^4-1 on the quarterly flows, or =XIRR(values,dates) on the actual dates, which returns an effective annual rate directly. On a deal with a 3.091 per cent quarterly IRR both give about 12.95 per cent. Multiplying by four gives 12.36 per cent and understates the return by 59 basis points.
No, it understates it at every positive rate, because it ignores compounding within the year. The error is roughly six times the square of the quarterly rate: 24 basis points at 2.0 per cent a quarter, 55 at 3.0 and 99 at 4.0. The often quoted hundred basis points applies only near an effective 17.1 per cent.
Take the fourth root: 1.08 to the power 0.25, minus 1, is 1.94 per cent a quarter. Dividing 8 by four gives 2.00 per cent, which overstates the quarterly hurdle and makes a promote harder to reach than the documents intend. The conversion must run the same way in both directions.
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 324 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.