In real estate financial modelling, building a model is only half the job. The real value lies in understanding how that model behaves under different assumptions.
In this 30-minute practical session, we explored how Excel sensitivity analysis helps professionals stress-test assumptions, improve decision-making, and bring clarity to real estate underwriting.
Why Sensitivity Analysis Matters
Financial models are often treated as black boxes—complex, opaque, and difficult to interpret. This creates risk.
Sensitivity analysis addresses this by answering a simple but critical question:
What happens if our assumptions are wrong?
By testing a range of outcomes, you can:
- Identify the most impactful variables
- Understand downside risk
- Improve transparency and robustness
- Make better investment decisions
From Base Case to Real-World Uncertainty
Every financial model starts with a base case:
- Yield assumptions
- Rental levels
- Construction costs
This base case represents your “best estimate.” But in reality, markets shift.
Sensitivity analysis allows you to move beyond a static model and explore:
- What if yields increase?
- What if rents underperform?
- What if costs rise?
Identifying Key Variables: The 10% Rule
Not all inputs matter equally.
A practical approach is the 10% rule:
- Adjust each key variable by ±10%
- Measure the impact on returns (typically IRR)
Important nuance:
- This is relative change, not absolute
- Example:
- Yield from 5% → 5.5% (not 15%)
- Rent from 100 → 110 → 121 (compounding effect)
This helps you determine:
- Which variables drive performance
- Where your model is most sensitive
Why IRR Is the Key Metric
While metrics like profit on cost or equity multiple are useful, they ignore time.
IRR (Internal Rate of Return) is preferred because:
- It accounts for timing of cash flows
- It allows comparison with other asset classes
- It reflects true annualised performance
For example:
- 2.0x return over 10 years ≠ strong performance
- 1.2x return in 1 year may be superior
Case Study: Development Model Overview
The model used in this session represents a typical real estate development project:
Key Components:
- Timeline: ~17 quarters (≈ 4.25 years)
- Phases:
- Planning
- Construction
- Lease-up
- Stabilisation
- Exit
Revenue Side:
- Gross Development Value (GDV)
- Net Development Value (NDV)
- Sales proceeds after:
- Concessions
- Rent-free periods
- Sales costs
Cost Side:
- Land acquisition
- Construction (hard & soft costs)
- Financing (senior + mezzanine debt)
Returns:
- Equity contribution: ~£12m
- Final distribution: ~£44m
- Key outputs:
- Equity multiple
- IRR (developer vs investor)
One-Way Sensitivity Analysis in Excel
Step-by-Step:
- Select a variable (e.g. residential yield)
- Create a range of scenarios (e.g. -20%, -10%, base, +10%, +20%)
- Link outputs (e.g. IRR)
- Use:
Data → What-If Analysis → Data Table
- Assign:
- Column input cell = variable being tested
Key Tip:
Excel data tables do not work with formulas as inputs
→ Always paste values before running the table
Interpreting Results
Example insight:
- A 10% increase in yield → noticeable drop in IRR
- A 20% increase → even larger decline
General rule:
Yields ↑ → Values ↓ → IRR ↓
This is fundamental in real estate valuation.
Comparing Variables with Charts
To identify which variables matter most:
- Plot sensitivity results in charts
- Compare slopes
Insight from the session:
- Residential yield had a steeper curve than office yield
- Meaning: returns are more sensitive to residential assumptions
This reflects:
- Asset mix
- Contribution to total GDV
Beyond One Variable: Scenario Analysis
Sensitivity analysis tests one variable at a time.
But real-world changes rarely happen in isolation.
That’s where scenario analysis comes in:
- Combine multiple changes
- Example:
- Higher yields
- Lower rents
- Increased costs
This gives a more realistic picture of downside risk.
Best Practices for Financial Modelling
To build better models:
- Focus on clarity and transparency
- Avoid unnecessary complexity
- Use consistent templates
- Always stress-test assumptions
- Prioritise variables with the highest impact
Key Takeaways
- Sensitivity analysis turns static models into decision-making tools
- The 10% rule is a simple way to test impact
- IRR is the most reliable performance metric
- Excel data tables are powerful—but require correct setup
- The steepest sensitivity curve = highest risk exposure
