By Maria Wiedner, CEO & Founder, Cambridge Finance
In an age where artificial intelligence is advancing rapidly, it’s easy to think Excel-based financial modelling might soon become obsolete. Perhaps one day that will be true — but not yet. For now, Excel remains one of the most powerful and flexible tools for real estate professionals to analyse assets, forecast performance, and support investment decisions.
At Cambridge Finance, financial modelling in Excel is at the core of what we do. Every asset is unique — no two projects are identical — which is why we continue to rely on bespoke, dynamic models. While templates help us maintain consistency, adaptability is key. Understanding where the numbers come from and how they interact is essential for sound decision-making.
What Makes a Great Financial Model?
After mastering the basics, what truly separates great financial modellers from the rest is how clear, dynamic, and decision-ready their spreadsheets are. I review countless models from developers, investors, agents, and funds, and the best ones all share the same qualities:
- Clarity: They’re easy to follow and beautifully structured.
- Transparency: You can trace every number to its source.
- Elegance: They look simple — even when what’s under the hood is complex.
When a spreadsheet looks chaotic or overcomplicated, it usually means the modeller rushed it or doesn’t fully understand how it works. Great modelling is about making complexity digestible.
Structuring Models for Transparency
One principle I make all my students swear by is never hard code inputs into formulas.
Separate your model into three clear sections:
- Inputs
- Calculations
- Outputs
Hard coding — for example, writing “0.8” directly into a formula to represent 80% loan-to-value — makes models inflexible and error-prone. Always reference inputs from a dedicated section so they can be easily updated or stressed during analysis.
Consistent formatting is also critical. Use clear naming conventions for tabs, avoid spaces in sheet names, and apply logical colour coding. Small practices like these make a huge difference when someone else (or future you) needs to understand the model.
Building Dynamic Rent Rolls
Few areas in real estate modelling can become as complex as rent rolls. Between step-up rents, indexation, rent reviews, and break options, formulas can quickly spiral into unreadable monsters.
The key is simplifying logic. Identify patterns in your contracts — for example, one cash flow for fixed annual indexation and another for rent reviews every five years — and then link them through a “live table.” By separating and simplifying, you maintain flexibility while keeping your formulas manageable.
Sensitivity vs. Scenario Analysis
Most modellers are familiar with sensitivity analysis — the classic “What if interest rates increase by 1%?” exercise. It’s great for identifying key drivers such as yields, rent growth, or capex. But real-world decisions often depend on multiple factors moving at once.
That’s where scenario analysis comes in.
In scenario analysis, instead of testing one or two variables, you design broader “Good,” “Neutral,” and “Bad” scenarios that reflect different futures. Each scenario might adjust exit yield, rental growth, construction costs, and project duration simultaneously.
This exercise forces you to think analytically — not just about numbers, but about the story your investment might take.
For example:
- In a Good scenario, exit yields compress by 50 basis points, rents rise by 10%, and construction finishes ahead of schedule.
- In a Bad scenario, yields expand, costs rise, and completion is delayed.
Running these scenarios gives you a richer view of risk and helps you prepare for uncertainty.
Implementing Scenario Analysis in Excel
Here’s how I typically set it up:
- List your scenarios and key variables.
Example: Exit yield, ERV (estimated rental value), rental growth, construction cost, and duration. - Create an “Active Scenario” dropdown.
Use Excel’s Data Validation feature to build a drop-down list (Good, Neutral, Bad). - Link variables to the active scenario.
Using functions likeINDEXorMATCH, link each input to its corresponding scenario value. When you change the dropdown, all linked inputs update automatically. - Connect to your model.
Ensure all formulas reference the active scenario values, not static numbers. - Run your analysis.
Use Excel’s Data Table function to calculate KPIs such as IRR, NPV, and equity multiple across different scenarios. - Interpret results.
For instance:- Good scenario → IRR 37%, Equity Multiple 6.5x
- Neutral scenario → IRR 16%, Multiple 2.8x
- Bad scenario → Negative IRR, loss of equity
You can also assign probability weights to each scenario to calculate risk-adjusted returns, which give a more realistic estimate of expected performance.
Common Pitfalls (and How to Avoid Them)
- Forgetting to revert to the base case.
If your model remains set to the “Good” scenario, your entire analysis — and your investment recommendation — could be wrong. Always double-check that your base case reflects neutral assumptions. - Ignoring rounding errors.
Instead of checking if “Total Assets = Total Liabilities,” use “Total Assets – Total Liabilities = 0.” That way, minor rounding differences don’t trigger false errors. - Overusing data tables.
Too many sensitivity tables can make your workbook unbearably heavy. Be strategic — focus on key drivers, not endless combinations.
The Art of Financial Modelling
At its heart, financial modelling is not just about formulas — it’s about thinking clearly, simplifying complexity, and communicating decisions through numbers.
A well-structured model is not only technically accurate but also elegant, logical, and trustworthy. It gives confidence to decision-makers and clarity to anyone who reads it.
Excel may one day be replaced by AI-powered systems, but until that day comes, there’s still immense value in mastering the craft of building clear, dynamic, and intelligent models.
So, keep modelling — beautifully.
Interested in learning more?
Cambridge Finance offers expert-led training in real estate financial modelling, valuation, and investment analysis.
Visit Cambridge Finance to explore upcoming courses.