The Excel Formulas Every Real Estate Analyst Should Know

If you work in real estate and you're not fluent in Excel… we need to talk.

Because here’s the reality: almost every strategic decision in property – from acquisitions and development to asset management and exit – comes back to one thing. A spreadsheet.

And yet, most people are secretly struggling.

I’ve seen £100m+ deals modelled on spreadsheets that are completely hardcoded and people several years into their careers still asking for help to update a lease date.

So let’s fix that.

You don’t need to be a spreadsheet wizard. You just need to know what to use, when – and why.

Below are 10 Excel formulas every real estate analyst should know. Whether you're building models from scratch or reviewing someone else’s work, these will not only give you the confidence to work faster, cleaner, and with fewer mistakes, but also establish you as the go-to expert in your team.

1. IF()

Let’s start with the foundation of all logic in Excel: the humble IF.

You’ll use this for everything from:

• Flagging break options

• Modelling stepped rent increases

• Triggering rent-free periods

• Structuring exit scenarios

Syntax: =IF(logical_test, value_if_true, value_if_false)

Example: =IF(A1="Yes", 100 , 0)

Why it matters: IF lets you build dynamic logic into your model that reflects how deals actually work. ie IF the current date is before the lease expiry date, put the rent figure, if not put 0.

2. AND() and OR()

These extend the power of your IF statements.

Use AND when multiple conditions must be met. Use OR when only one condition needs to be true.

Example: =IF(AND(Date >= Lease Start, Date <= Lease End), Rent, 0)

Why it matters: You’ll often need to model rent payments, caps, uplifts or charges that only apply if two or more things are true. This is how you avoid hardcoding – and the errors that come with it.

3. SUM() and SUMIFS()

These are the backbone of every model. If you're not using them, you're probably doing something wrong.

SUM() adds up a range – simple but essential.

SUMIFS() lets you add up values only if they meet certain conditions.

Examples:

=SUM(Range) – total rent, cost, area, you name it

=SUMIFS(RentColumn, UseClassColumn, "Retail") – sum only the retail units

=SUMIFS(RentColumn, StatusColumn, "Occupied", TypeColumn, "Office") – layered filters

Why it matters: Every summary table in a real estate model relies on these. From total rent per use class, to occupancy analysis, to debt covenants and KPI outputs – these formulas do the heavy lifting.

4. EOMONTH()

This one’s overlooked but incredibly useful when building timelines.

Syntax: =EOMONTH(start_date, months)

Example: =EOMONTH(A1, 3) → gives you the end of the month, 3 months after A1

Why it matters: Use EOMONTH to map cash flow dates, lease events, interest periods, rent reviews, and more.

5. INDEX() and MATCH()

If you’re still using VLOOKUP in 2025, we need to have words.

INDEX + MATCH gives you more flexibility, is less prone to breakage, and works in every direction.

Example: =INDEX(RentTable, MATCH(Date, RentTable[StartDate], 1), 2)

Why it matters: Whether you’re picking up ERVs from a tenancy schedule or selecting inflation assumptions, this combo is a must. It’s also far easier to audit and control once your model gets complex.

6. TEXT()

This is the secret to making your outputs look slick.

Syntax: =TEXT(value, format_text)

Examples:

=TEXT(TODAY(), "dd-mmm-yyyy") → formats date

=TEXT(A1, "£#,##0.00") → formats currency cleanly

Why it matters: Outputs matter. Especially when you’re handing a model to investors, clients, or committees. TEXT gives you control without messing with number formats.

7. ROUND(), ROUNDUP(), ROUNDDOWN()

Never let Excel round for you by accident.

You should always be in control of rounding – particularly when dealing with unit counts, square footage, or cost per sq ft.

Example: =ROUND(NetRent / Area, 2) → rounds rent £psf to 2 decimal places

Why it matters: Rounding errors add up. If you’ve ever been off by £0.01 on every unit in a 500-unit scheme, you’ll know why this matters.

8. IFERROR()

Use this to prevent your model from breaking when a formula can’t find something.

Example: =IFERROR(A1/B1, 0)

Why it matters: A single #DIV/0! error in a summary output can kill trust in your model. You might know why it’s fine – but the person reviewing it may not. Wrap your risky formulas in IFERROR to avoid drama.

9. YEARFRAC()

This one does exactly what it says on the tin: it tells you how much of a year has passed between two dates.

Syntax:

=YEARFRAC(start_date, end_date)

Example:

=YEARFRAC(A1, A2) → tells you how many years (or part-years) have elapsed between A1 and A2

Why it matters: When calculating interest, finance costs, rent free top ups, or prorated fees, you often need to be precise. YEARFRAC gives you a clean decimal representation of time.

10. CHOOSE()

This one’s underrated – but extremely useful for building dropdown-driven models or toggling between options.

Syntax:

=CHOOSE(index, option1, option2, option3, ...)

Example:

=CHOOSE(ScenarioCell, RentLow, RentMedium, RentHigh)

Why it matters: It’s a fast way to create switch logic. If you want to model best/worst case scenarios, toggle assumptions, or create simple dashboards without overcomplicating things, CHOOSE is your friend.

So, how many of those did you already know?

Mastering Excel functions doesn’t mean memorising 100+ formulas. It means understanding how to use the right ones in the right situations.

Most real estate models come down to a handful of things:

• A timeline

• Lease assumptions

• Cash inflows and outflows

• Key outputs (IRR, equity multiple, WAULT, etc)

If you can get comfortable with the formulas above, you’ll be ahead of most people in the industry.

And if you’re serious about levelling up, we teach all of this (and more) in our course: 👉 Excel Functions for Real Estate Analysts – available now inside the EiP Academy.

Final word

Real estate isn’t a spreadsheet business. It’s a judgement business. But you can’t apply sound judgement if you don’t trust the numbers in front of you.

Learn these Excel formulas. Use them often. Build better models.

The result? Your career gets a whole lot easier, and your impact a whole lot bigger.