Affordable Housing Financial Analysis & Stress Testing

Financial decision-support framework for affordable housing investment, combining project-level financial analysis, public subsidy efficiency, developer risk assessment, geographic prioritisation and multi-scenario stress testing using Python and Power BI.

This framework uses synthetic data for demonstration purposes. The complete Power BI report, including calculated measures and the underlying analytical datasets, is available upon request.

Business Problem

Public authorities financing affordable housing need to assess more than the initial cost of individual projects. Investment decisions require a consolidated view of project affordability, financing structure, public subsidy requirements, developer financial strength, geographic housing needs and resilience under adverse economic conditions.

This project was designed to simulate a financial monitoring and decision-support framework for an affordable housing portfolio, allowing analysts to identify financially vulnerable projects, evaluate the efficiency of public funding, compare municipalities and test how changes in construction costs, interest rates, occupancy and project delays affect financial sustainability.

The framework transforms project and financial data into structured indicators that can support project assessment, portfolio monitoring, risk identification and public investment prioritisation.

Solution Architecture

• Synthetic affordable housing portfolio generation
• Project financial analysis engine
• Developer financial assessment
• Public subsidy efficiency analysis
• Municipality-level strategic prioritisation
• Financial risk scoring
• Project risk flagging and exception monitoring
• Multi-scenario financial stress testing
• Long-term project cash-flow modelling
• Power BI decision-support and visualisation layer

Technology Stack

Power BI Dashboard

The dashboard provides portfolio-level monitoring of affordable housing investments, geographic allocation, project financial risk and resilience under adverse scenarios.

Portfolio Overview

• €1.08bn total investment
• €388.03m public subsidy
• 2,205 planned affordable housing units
• Approximately €488k average project cost per unit
• Approximately €176k average subsidy per unit
• 24 projects presenting at least one financial risk flag

The portfolio view combines project-level indicators such as DSCR, cost per unit, subsidy intensity, liquidity and developer leverage to identify projects requiring additional financial review.

Geographic & Strategic Analysis

Municipality-level analysis combines housing demand, accessibility, public land availability, portfolio financial risk and subsidy efficiency to provide a geographic perspective on affordable housing investment.

A strategic priority score supports comparison between municipalities and helps identify locations where housing need, investment efficiency and portfolio conditions may justify additional attention.

Financial Stress Testing

Each project is evaluated under six scenarios:

• Base
• Construction cost +10%
• Interest rate +1%
• Occupancy -5%
• Delay +6 months
• Combined stress

The model recalculates project cost, financing requirements, debt service, DSCR, net cash flow and financial risk classification under each scenario.

At portfolio level, average DSCR declines from approximately 0.69 in the base scenario to 0.50 under combined stress, while average project cost per unit increases from approximately €484k to €528k.

The combined scenario therefore provides a direct view of projects whose financial sustainability is particularly sensitive to simultaneous adverse developments.

Project-Level Scenario Analysis

Scenario matrices allow individual projects to be compared across financial conditions.

• DSCR
• Cost per unit
• Net cash flow
• Financing requirements
• Occupancy
• Interest costs
• Scenario risk classification

This allows analysts to move from portfolio-level indicators to the underlying projects driving financial vulnerability.

Financial Risk Framework

The project-level monitoring model combines several indicators rather than relying on a single financial ratio.

• Weak debt-service coverage
• Limited own-funds contribution
• High project cost per unit
• High public subsidy per unit
• Weak developer liquidity
• High developer leverage
• Historical project delays
• Historical cost overruns

Projects are assigned a financial risk score and risk category, providing a structured mechanism for identifying cases requiring deeper review.

Public Funding Efficiency

The framework evaluates the relationship between public expenditure and housing output. Indicators such as subsidy per unit and units delivered per €1 million of subsidy allow projects and municipalities to be compared from a public-investment perspective.

This adds an important dimension to traditional financial analysis: the model evaluates not only whether projects are financially sustainable, but also how efficiently public funding translates into affordable housing capacity.

Cash-Flow Analysis

Ten-year simulated cash flows track:

• Rental revenue
• Operating expenditure
• Net operating income
• Interest payments
• Principal repayments
• Debt service
• Net cash flow
• Outstanding loan balance

This provides a forward-looking view of project financing sustainability beyond the initial investment decision.

Key Analytical Findings

The simulated portfolio demonstrates how apparently viable affordable housing investments can exhibit significant financial sensitivity once financing structure and adverse scenarios are considered.

The analysis identifies DSCR as a major portfolio vulnerability, with 23 of the 25 simulated projects below a DSCR of 1.0 in the base data. Construction-cost inflation and higher interest rates further weaken project coverage, while the combined stress scenario reduces average portfolio DSCR to approximately 0.50.

At the same time, substantial differences in subsidy efficiency exist between municipalities, demonstrating the value of combining financial risk analysis with geographic and public-policy indicators rather than assessing projects solely on absolute investment cost.

Future Enhancements

• Monte Carlo simulation for construction costs and interest rates
• Probability-of-distress modelling
• Dynamic sensitivity analysis
• Developer concentration risk
• Portfolio budget constraints and optimisation
• Historical/project actual-versus-budget monitoring
• Automated management reporting
• AI-assisted financial commentary and project review

GitHub Repository

The repository includes Python analytical scripts, synthetic portfolio datasets, scenario outputs, dashboard assets and project documentation.