How to Calculate Physical Climate Risk: Models, Methods & Excel
Physical climate risk is now a mandatory disclosure requirement under TCFD, ISSB S2, and CSRD E1 — yet most organisations don't know where to start. This guide takes you from first principles through to a working Excel model, with pointers to the professional tools used by insurers, banks, and consultants.
1. What is Physical Climate Risk?
Physical climate risk refers to the financial and operational impacts on assets, supply chains, and people arising from changes in the physical climate system. Under the TCFD framework — and now codified in ISSB IFRS S2 and CSRD ESRS E1 — companies must assess and disclose these risks across multiple time horizons and warming scenarios.
Physical risks are divided into two categories:
Acute Risks
Driven by extreme weather events of increasing frequency and severity.
- • Flooding (fluvial, pluvial, coastal)
- • Tropical cyclones & extreme wind
- • Wildfires
- • Hailstorms & freezing rain
- • Extreme heat events
Chronic Risks
Driven by longer-term shifts in climate patterns.
- • Sea level rise
- • Chronic heat stress (WBGT)
- • Permafrost thaw
- • Changing precipitation patterns
- • Water scarcity / drought
2. The Four-Step Assessment Framework
Regardless of which model or tool you use, all physical risk assessments follow this core logic:
- 01
Hazard
What climate hazards are projected at a given location and time horizon? (e.g. 1-in-100-year flood probability at 2°C warming by 2050)
- 02
Exposure
Are your assets, operations, or supply chain nodes located where those hazards occur? (e.g. factory on floodplain within 100m of river)
- 03
Vulnerability
How sensitive is the asset to the hazard, and how adaptive is the organisation? (e.g. ground-floor electrical equipment vs elevated plant)
- 04
Financial Impact
What is the expected financial loss — direct damage, business interruption, supply chain disruption, stranded assets? Expressed as Expected Annual Loss (EAL).
3. Available Models & Data Sources
The quality of your physical risk assessment depends heavily on the climate data and models you use. Here is a structured overview from free/open-source to enterprise-grade:
| Model / Source | Type | Best For | Cost |
|---|---|---|---|
| IPCC AR6 / SSP Scenarios | Global climate scenarios | Scenario selection, temperature & precipitation projections | Free |
| NGFS Physical Risk Scenarios | Finance-sector scenarios | TCFD / ISSB S2 financial risk translation | Free |
| CLIMADA (ETH Zurich) | Open-source risk platform | Multi-hazard EAL modelling for assets globally | Free (Python) |
| NASA NEX-GDDP-CMIP6 | Downscaled climate data | Grid-level temperature & precipitation projections | Free |
| NOAA / Copernicus ERA5 | Historical climate data | Baseline hazard calibration | Free |
| XDI Cross Dependency Index | Asset-level risk scores | Portfolio screening, bulk asset analysis | Commercial |
| Jupiter Intelligence | Asset-level risk scores | Real estate, infrastructure, financial services | Commercial |
| Four Twenty Seven (Moody's) | Asset-level risk scores | Investment-grade location risk data | Commercial |
| Munich Re / Swiss Re tools | Catastrophe models | Insurance underwriting, NatCat EAL | Commercial |
💡 Which scenario should you use?
For TCFD/ISSB S2 compliance, assess at minimum two contrasting scenarios: a low warming scenario (SSP1-2.6 or NGFS "Net Zero 2050") and a high warming scenario (SSP5-8.5 or NGFS "Current Policies"). This captures both transition and physical risk trade-offs.
4. Can You Calculate Physical Climate Risk in Excel?
Yes — and here's exactly how.
For organisations without a dedicated risk platform budget, a well-structured Excel model can produce a credible, TCFD-aligned physical risk assessment. It won't match the resolution of CLIMADA or XDI, but it is sufficient for initial screening, board reporting, and regulatory disclosure narratives.
Build Your Asset Register
Create a structured list of all assets (or business operations) to be assessed. For each asset include: Name, Location (lat/long or postcode), Asset type (building, plant, warehouse, data centre), Replacement value (£), Annual revenue contribution (£), Key dependencies (water, electricity, road access).
Tip: Start with your top 10 highest-value or highest-revenue assets. Cover >80% of portfolio value.
Select Scenarios & Time Horizons
Define three rows per asset: Short-term (2030), Medium-term (2050), Long-term (2100). For each time horizon, apply two scenarios: SSP2-4.5 (intermediate, 2–3°C) and SSP5-8.5 (high-end, 4–5°C). Download scenario data from NASA NEX-GDDP-CMIP6 or use the IPCC AR6 Interactive Atlas.
Tip: The IPCC AR6 WGI Interactive Atlas (interactive-atlas.ipcc.ch) lets you download gridded temperature and precipitation data by region at no cost.
Score Hazard Exposure (0–5 scale)
For each hazard type (flooding, heat, drought, sea level rise, wildfire, wind), assign an exposure score (0 = not exposed, 5 = very high exposure) using: Distance from flood zone or coastline, Historical event frequency from NOAA or EM-DAT, Projected change in hazard intensity from your chosen scenario. Multiply by a confidence weight (0.6–1.0) based on data quality.
Assign Vulnerability Factors
Vulnerability reflects how much damage the hazard causes given exposure. Use a vulnerability factor (VF) from 0.0 to 1.0 based on: Asset construction type (e.g. steel frame = low VF vs. unreinforced masonry = high VF), Elevation above flood level, Existing adaptation measures (flood barriers, cooling systems), Business continuity planning maturity. Source: IPCC WGII AR6 Chapter 17 provides sector-level vulnerability multipliers.
Calculate Expected Annual Loss (EAL)
P = Annual exceedance probability of the hazard event (e.g. 1% for 1-in-100-year flood). Severity = physical damage ratio (e.g. 0.3 = 30% of asset value damaged). This can be summed across multiple hazard types.
Discount EAL to NPV Across Time Horizon
Apply a discount rate (typically 3–7% for infrastructure) to translate future EAL values to present value. This enables comparison with adaptation investment costs — a key TCFD requirement. Sum discounted EAL across your time horizon to get Total Physical Risk Exposure (£) per asset.
Aggregate, Rank & Disclose
Rank assets by Total Physical Risk Exposure. Identify the top 3–5 "hotspot" assets and 1–2 "critical" hazard types. Produce a heat map matrix (hazard type vs. time horizon) for board-level reporting. This output directly feeds your TCFD Physical Risk disclosure (Strategy section) and ISSB S2 paragraph 25 requirements.
5. Limitations of the Excel Approach
An Excel model is a strong starting point but has real constraints. Know when to upgrade:
Large portfolios (100+ assets)
Use CLIMADA (Python) or XDI for bulk processing
High spatial resolution needed
Commercial tools offer 90m–1km grid resolution vs. regional Excel estimates
Multi-hazard compound events
CLIMADA handles correlated hazards; Excel cannot easily
Regulatory audit trail required
Enterprise platforms provide documented methodology and data provenance
Real-time updating
Platforms auto-update with new CMIP6 runs; Excel requires manual refresh
Board-ready automated outputs
Dedicated tools generate disclosure-ready reports automatically
6. The Next Level: Automating Physical Risk with AI
Beyond Excel, AI tools like Claude Code can be used to automate the entire physical risk pipeline — from pulling NASA NEX-GDDP data via API, to running CLIMADA hazard modules in Python, to generating a formatted TCFD disclosure narrative. What took a consultant two weeks can run in hours.
This is exactly what the Claude Code for Climate Risk & ESG Reporting course covers — real, project-based workflows that automate GHG calculations, climate risk screening, and ESG dashboard generation using AI agents.
Want to go deeper with hands-on practice?
The Climate Risk Masterclass on BTW Academy walks through physical and transition risk modelling end-to-end — including real Excel models, TCFD alignment, ISSB S2 mapping, and NGFS scenario narratives.
View Climate Risk Masterclass7. Key Takeaways
- Physical climate risk = Hazard × Exposure × Vulnerability — always start with this equation.
- Use IPCC AR6 SSP scenarios (at minimum SSP2-4.5 and SSP5-8.5) across 2030, 2050, and 2100 time horizons.
- CLIMADA (free, Python) is the most powerful open-source tool for quantitative EAL modelling.
- Excel modelling is credible for initial screening and regulatory disclosure narratives — use the 7-step EAL framework above.
- ISSB S2 and CSRD E1 require quantitative scenario analysis with financial impact estimates — not just qualitative narrative.
- AI tools like Claude Code can automate the entire pipeline from data ingestion to disclosure drafting.
Ready to Master This?
Turn This Knowledge Into a Career-Defining Skill
BTW Academy offers two courses that take you from understanding to execution — with real models, real data, and real deliverables you can use immediately in client or employer contexts.
🌍 Climate Risk Masterclass
Physical & transition risk, NGFS scenarios, TCFD/ISSB S2 alignment, financial impact quantification — full Excel models included.
Enroll Now🤖 Claude Code for Climate Risk & ESG
Automate physical risk pipelines, ESG reporting, and dashboard generation using AI agents. No prior coding experience required.
Enroll Now