Articles

How do you use timing flags in a real estate financial model?

Operating, exit, lease and void flags, the Excel formulas behind them, and the off-by-one errors that produce a plausible wrong IRR.

Julian R. Sterling · from Real Estate Financial Modeling

A timing flag is a row of 1s and 0s that says whether something is happening in each period: the property is owned, a lease is running, a unit is void, the asset is sold. Every cash flow line is then an input multiplied by a flag, so changing the hold period or a lease date in one cell moves the whole model. On an illustrative £15,000,000 two-tenant building the flags produce a 6.13 per cent unlevered IRR; one wrong comparison sign in the operating flag drops the final year's £990,000 of NOI and the IRR to 4.94 per cent.

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 →

Why models use flags

A property model written with hard-coded start and end columns breaks the moment someone asks for a four-year hold or a later lease expiry. Flags separate the timing from the arithmetic. The flag rows sit at the top of the cash flow sheet, each driven by a date or period input on the assumptions sheet, and nothing below them contains a period number. It is the convention the model architecture in the book builds on, and in a modelling test it is what lets you answer "what if we sell a year later?" in seconds rather than minutes.

The assumptions

Illustrative multi-let building, annual periods, GBP. Period 0 is the acquisition date.

Input	Value
Price, including costs	15,000,000
Hold period	5 years
Lease A: rent / expiry	600,000 / end of year 3
Lease A unit: void / new rent from year 5	1 year / 650,000
Lease B: rent, running beyond the hold	400,000
Non-recoverable costs / extra cost while void	60,000 / 50,000
Exit yield on forward NOI / sale costs	6.25% / 2.0%

Step 1: build the flag rows

With period numbers in row 4 (E4 onwards), the hold period in C6, lease A's expiry year in C10 and the void length in C11:

Multiplying logical tests returns 1 or 0 without an IF. Each flag answers one question, and the lease flags run to the end of the timeline, not to the end of the hold, because the exit needs the year after the sale.

Step 2: let the flags drive the cash flow

Flags and cash flow, GBP. Year 6 is shown only for the forward NOI used at exit.

Year	Operating	Exit	A in place	A void	A re-let	Rent	NOI	Cash flow
0	0	0	0	0	0	0	0	−15,000,000
1	1	0	1	0	0	1,000,000	940,000	940,000
2	1	0	1	0	0	1,000,000	940,000	940,000
3	1	0	1	0	0	1,000,000	940,000	940,000
4	1	0	0	1	0	400,000	290,000	290,000
5	1	1	0	0	1	1,050,000	990,000	16,513,200
6	0	0	0	0	1	1,050,000	990,000	0

The unlevered IRR is 6.13 per cent on a 1.31x multiple, a profit of 4,623,200 after 316,800 of sale costs. No cell in the cash flow refers to "year 5" directly; the 5 lives only in C6.

What if: change one cell

Hold period changed in C6 only. Same flags, same formulas.

Hold	Forward NOI at exit	Gross exit value	Unlevered IRR	Multiple
3 years	290,000	4,640,000	−23.33%	0.49x
4 years	990,000	15,840,000	6.07%	1.24x
5 years	990,000	15,840,000	6.13%	1.31x
6 years	990,000	15,840,000	6.17%	1.37x
7 years	990,000	15,840,000	6.19%	1.44x

The three-year row is the warning. The flags work perfectly: they sell at the end of year 3 and capitalise year 4's NOI, which is the void year. No buyer would capitalise a temporary void in perpetuity; it would value the re-let income and deduct the void and letting costs. Flags make a model flexible, not right. Every time a timing input moves, read what the exit is capitalising.

The common mistakes

• Off by one in the operating flag. Writing E$4<$C$6 instead of <= switches the flag off in the sale year. The model then drops that year's 990,000 of NOI and the IRR falls from 6.13 to 4.94 per cent, a 1.19 point error that looks like a plausible result.
• Off by one in a lease flag. Testing lease A with < its expiry year removes 600,000 of rent in year 3 while the void flag still starts in year 4, so for one year the unit is neither let nor void. The IRR becomes 5.34 per cent. A check row that adds the three lease A flags and must equal 1 in every year from year 1 catches it immediately.
• Ending the lease flags at the exit. The exit needs forward NOI, so the timeline and the lease flags must run at least one period past the longest hold you will test.

Illustrative and simplified: annual periods, no growth, no debt. In a quarterly or monthly model the same flags compare dates with EOMONTH instead of period numbers, and the logic is unchanged.

Takeaway

Build one flag per timing question, drive every flag from an input, multiply the cash flow by the flags, and add check rows that make overlapping or missing periods impossible to miss. Then test the timing inputs and read what the exit capitalises. The free workbooks for the book include the full Meridian House model and an empty template with the flag rows already built. For the exit side of the same cash flow, see whether the exit cap rate applies to forward or trailing NOI.

Questions readers ask

What is a flag in a financial model?

A row that returns 1 in the periods when something is true and 0 otherwise, such as the property being owned or a lease running. Cash flows are inputs multiplied by flags. In the worked model the exit flag is 1 only in year 5 and picks up 15,523,200 of net sale proceeds; change the hold input and the 1 moves.

How do you write a timing flag in Excel without IF?

Multiply logical tests: =(E$4>0)*(E$4<=$C$6) returns 1 when the period is after acquisition and within the hold. Using <= rather than < matters: in the worked case the wrong sign drops the 990,000 of NOI in the sale year and cuts the IRR from 6.13 to 4.94 per cent.

Why does a shorter hold give a strange IRR in a flagged model?

Because the flags will capitalise whatever NOI follows the exit. In the worked case a three-year hold sells on the void year's 290,000 of NOI, a gross value of 4,640,000 and an IRR of minus 23.33 per cent. The flags work; the exit assumption needs a stabilised NOI.

Also on this site

• Should the exit cap rate apply to forward or trailing NOI?Forward NOI, the year after the sale, as at entry. On a 5-year hold with 3% growth, capping trailing NOI cuts exit value by 3.0% and the IRR by 53 bp.
• How do you model a five-yearly rent review in Excel?Grow the ERV, not the rent, and reset the rent on review dates with MAX(). Annual growth on the rent here overstates the IRR by 25 bp: 7.66% against 7.41%.
• How do you model lease-up of a vacant building in a cash flow?Lease the space suite by suite: downtime, absorption, rent-free, TI and fees. Here that costs 5,020,000 and gives 9.26%, against 11.11% on a vacancy allowance.
• Direct capitalisation vs DCF: why do the property values differ?They agree only if cap rate = discount rate less growth on stable income: 18,181,818 both ways here. One lease event and a softer exit take the DCF to 16,792,845.
• Equity first or pro rata funding: which gives a higher IRR?Pro rata gives the higher IRR, equity first the higher profit: 33.7% against 24.9%, but 3,479,443 of profit against 3,886,625 on the same 8,400,000 of equity.
• Should you multiply a quarterly IRR by four to annualise it?No: compound it, (1 + q)^4 minus 1. A 3.091 per cent quarterly IRR is 12.95 per cent a year, not 12.36. Times four understates at every positive rate.

Reading guide: real estate investing, finance and fund management → · All 1614 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.