Project 02 | Real-estate portfolio risk
A portfolio risk system for analyzing multifamily property performance, CMHC-aware financing exposure, downside scenarios, refinancing risk, and capital deployment priorities across a real-estate portfolio.
Business question
The project looks at a simulated multifamily portfolio the way an operator or analyst would: not as a static list of buildings, but as a set of changing revenue, expense, debt, occupancy, and valuation exposures.
The goal is to identify where financial risk is building, understand what happens when operating conditions worsen, and recommend where limited capital should be deployed first.
The workspace connects property operations to investment decisions: which assets can cover debt, where refinancing requires new equity, how income supports valuation, and which capital projects fit the budget. Python and pandas calculate the financial measures; SQL assembles the operating history; SciPy solves the constrained capital plan.
Capacity = Minimum(LTV limit x Value, Annual NOI / DSCR limit / Mortgage constant)
Debt yield, break-even occupancy, cash after debt, and the equity funding gap identify assets that need attention. The model uses illustrative limits of 65% LTV and 1.25x DSCR, not official CMHC underwriting criteria.
Value = Present value of projected NOI + Present value of net sale proceeds
The income valuation uses 2% annual NOI growth, an 8% discount rate, the scenario cap rate at exit, and 2% sale costs. It excludes recurring capital expenditure and taxes; it is an unlevered income valuation, not an equity IRR model.
Portfolio variance = wT Covariance w
Aligned monthly NOI changes show which assets move together. Each property's variance contribution reconciles to total portfolio variance. Historical 95% NOI VaR, expected shortfall, and concentration HHI support review. These describe synthetic operating data, not traded investment returns.
Maximize total priority; total cost ≤ budget; at most one project per property
SciPy's mixed-integer solver chooses a feasible project combination with binary decisions. Tests compare its result with exhaustive enumeration on a small case. The objective combines supplied NOI yield, risk-reduction assumptions, and property risk.
CTEs, joins, rolling twelve-month windows, LAG, safe division, and market-level ranking build an operating review dataset. Validation rejects duplicate property-months, invalid occupancy, non-finite values, and unreconciled NOI. CSV exports support independent financial review and Tableau import.
MATLAB scripts reproduce debt service, DSCR and DCF calculations, generate credit-review plots, and compare the capital-selection objective using intlinprog. The scripts are provided for independent execution; MATLAB results have not been verified in a licensed environment here.
MATLAB finance and plots · MATLAB optimizationAll property data and project benefits are synthetic. The research package documents financial conventions, sensitivity assumptions and model limitations. Read methodology · Download reproducible CSV inputs
The project uses a database-style workflow instead of one giant spreadsheet.
Properties, financials, CMHC and non-CMHC debt, valuations, and capital projects are separate tables.
Joins, CTEs, aggregations, and window functions build the analysis dataset.
Python and pandas calculate NOI, DSCR, LTV, trends, concentration, and risk scores.
Stress tests and capital recommendations are published in a reviewable report.
Financial calculations and statistics
The engine converts each property's operating, financing, and valuation data into comparable measures. It then recomputes those measures under downside scenarios, ranks risk using stated weights, and allocates a fixed capital budget to the highest-priority projects.
NOI = Revenue - Operating expenses
Net operating income shows the cash generated by the property before financing. NOI margin = NOI / Revenue, while revenue, expenses, and NOI per unit make properties of different sizes comparable.
DSCR = NOI / Monthly debt service
DSCR measures the property's ability to cover its loan payment. LTV = Loan balance / Property value shows how much of its value is financed. The model recalculates debt service using the loan rate and amortization schedule.
Property value = Annual NOI / Cap rate
This links operating performance to valuation. A lower NOI or a higher cap rate reduces implied value, which can increase LTV even when the loan balance itself has not changed.
Change (%) = (Current month / Same month last year - 1) x 100
SQL window functions compare NOI, operating expenses, and occupancy with the same month 12 periods earlier. A trailing 12-month average of NOI reduces the effect of a single unusual month.
Stressed NOI = Stressed revenue - Stressed expenses
The app applies occupancy, expense, interest-rate, and cap-rate shocks, then recalculates revenue, NOI, property value, debt service, DSCR, and LTV. Rate sensitivity varies by maturity, rate type, and loan structure, including CMHC-insured debt.
Weighted metric = Sum(Property metric x Property value) / Sum(Property value)
Portfolio DSCR and LTV are value-weighted so a large property has an appropriate influence on the result. Market, rate-type, loan-type, and maturity exposures are also measured as each segment's share of total portfolio value or debt.
Risk score = Sum(Normalized risk factor x Stated weight)
Each factor is normalized to a 0-100 range, then combined: DSCR 25%, LTV 20%, occupancy deterioration 15%, NOI deterioration 15%, expense growth 10%, refinancing exposure 10%, and concentration 5%.
Priority = Return on capital x 100 + Risk reduction x 2 + Risk score x 0.25
Return on capital = Expected NOI lift / Capital required. The engine ranks renovation, systems, debt-paydown, and efficiency projects, then selects only the projects that fit inside the chosen capital budget.
This is a deterministic, reviewable analytical model rather than a prediction black box: its assumptions, formulas, weights, stress inputs, and budget constraint are visible for an analyst to challenge or adjust.
Capital allocation
The project does not pretend properties can be traded like liquid securities. Instead, it asks a more realistic real-estate question: given a fixed capital budget, which renovations, building systems work, debt paydowns, or efficiency projects should be prioritized?
Validation
Explore the output
The app lets a viewer adjust stress scenarios, review the risk matrix, inspect highest-risk properties, and see capital allocation recommendations in the dashboard.