Maintainable Models in Risk Quantification: Introduction
A quantification model of the kind used in (insurance) risk assessment is usually straightforward to build and considerably harder to keep running. The output structure changes little once it is settled, but everything around it moves: fields are added to the questionnaire, methodology assumptions are revisited each cycle, calculations are refined, curves are recalibrated as claims data accumulates, and someone eventually asks how a figure quoted three years ago was arrived at, for a version of the model that no longer exists.
In this post, I introduce an archetype of such a model, present a schematic decomposition of it, and set out the requirements that make it difficult to maintain over time. The subsequent posts in this series work through the components in turn.
The ideas draw on my personal experience in model development. They are presented in a generalised form and reflect my own views, not those of my current or former employers. The framework I refer to throughout is not something I am able to publish, so this series presents design decisions and short excerpts rather than a library that can be installed.
The problem and base model
The archetype is a model where an asset is evaluated using a questionnaire, and those inputs are then used in a quantification model to determine several risk metrics. The problem generalises to a wide range of financial models, though the trade-offs might be chosen slightly differently elsewhere.
I will focus on one example, a maximum foreseeable loss (MFL) model, where given a set of inputs we aim to calculate the highest loss the asset could suffer if its protective measures fail. The arithmetic of such a model is rarely the difficult part: multiplication, lookups against mapping tables, and interpolation along a curve. Where substantial data engineering effort has gone in - fitting a severity curve, deriving occupancy factors from claims history - that work has usually happened upstream, and arrives at the model already reduced to a constant or a handful of points. Implementing the calculation itself in a spreadsheet is a reasonable first choice, and many such models begin that way.
Over the years, those implementations tend to become difficult to work with. Assumptions end up scattered across cells that no longer have clear owners, and each proposed methodology change carries an unclear risk of breaking something elsewhere in the sheet, so changes are made reluctantly or not at all. The problem class has high requirements for validation, correctness and change management, and without careful attention to those requirements the models tend to become unmaintainable “spaghetti code”.
Problem schematic
At its simplest, any financial model has three parts: inputs that capture assumptions and values, calculations that apply the model’s logic to them, and outputs that present the results. This input→calculation→output flow is a standard framing in financial modelling, and a MFL model is no exception: a questionnaire goes in, arithmetic happens, loss figures come out. However, there is a considerable amount of room inside that shape. A MFL model can be very simple, for instance taking an expert-derived generic MFL and applying downscaling factors based on region. It can also become very fine-grained, splitting loss across buildings, machinery, interruption and liability, each with its own drivers and its own parameters. The three parts stay the same either way; what changes is how much structure sits between them.
The schematic below commits to a fair amount of structure, and it is worth noting that this is already a design decision rather than a neutral description of the problem. Declaring the inputs as a schema rather than accepting whatever the questionnaire produces, separating fixed parameters from the calculations that consume them, arranging those calculations as a tree instead of a flat sheet, fixing the outputs as a named set - each of these is a choice, and each costs something up front. Declaring the input schema, for instance, means a new questionnaire field is no longer a free edit to a cell: it becomes a change to a contract that has to be versioned and migrated, so that answer sets collected under the old schema still run.
These are the per-risk inputs: one set of answers per asset, for example Construction year = 1990, varying in type from numeric values and booleans through selections from a fixed list to monetary amounts in different currencies. The form holds what was collected at a point in time, which is not the same as what any one model needs: each model declares the subset it requires, and a risk rating or exposure model will declare a different one. Compatibility is therefore a question about a specific pairing: Whether a given set of answers still covers what a given version of a model asks for , and it has to keep holding as fields are added, removed and redefined over the years.
Each node states what it returns, and nothing below a node is visible above it, thus a leaf's intermediate values stay inside the leaf. That scoping is what makes a leaf substitutable. Machinery is calculated one way for a metalworking plant and another for food processing, and swapping one for the other is bounded by the node's declared result rather than by whatever else happened to reference it. A ƒ marks a node that carries a calculation; results aggregate back up the tree.
A parameter arrives at a fixed value: a constant, a lookup, or a fitted curve. What produced it, a claims study, a regulation, an expert panel, happens outside the model. Curves are also a principal way non-linear behaviour enters an otherwise linear calculation.
A calculation is arithmetic over inputs and parameters. Usually simple arithmetic, complemented with curves and mapping tables. The intermediates exist only here: other leaves of the structure tree above see Interruption and nothing else.
This set is very much a speficiation of what answers a model shall provide. We could characterize a generic MFL model that only returns a single maximum loss number, or we could require more meta information. For example for a property MFL model we could require all such models to return more specific loss components if downstream analytics require it. We add the breakdown by component where downstream work needs it, and keep the set narrow otherwise, since every figure named here becomes something each typed model is obliged to produce for every risk. Nothing here says how the figures are reached, so a different model can be swapped in as long as it meets the same interface.
I have drawn it this way because the boundaries are what the series is about. Every line between two components is a place where something can be declared, checked, or versioned on its own. A spreadsheet implementation has the same three parts and none of the boundaries; whether that is a problem worth investing time and effort to fix is the question the remainder of this post sets up, and the rest of the series attempts to answer.
The exposure and risk rating models shown alongside are siblings rather than downstream steps. They read the same input schema and return their own outputs, which is a first indication of why the schema matters: it is a shared contract between several models rather than a private detail of this one.
Challenges
The structure looks simple, and at the point of construction it is. The following are the requirements I have found have to hold if a model of this kind is to survive the years that follow.
Input compatibility across versions. Questionnaires change. Fields are added when a new peril becomes relevant, removed when they turn out not to discriminate between risks, and redefined when someone finds a better wording. Answer sets collected under older versions remain on file, and a model asked to run them has to know what those answers meant at the time. The most difficult case is a field whose meaning changes while its name and type stay identical, as no structural check will detect it.
Totality. Any allowed input must be calculable: the model has to be defined over its whole input space. A questionnaire of 10 to 120 fields admits considerably more combinations than can be reviewed by hand, and a single unanticipated combination is enough to reach a division by zero or a lookup that falls off the end of a table.
Scope isolation. In a spreadsheet, any cell can reference any other cell. Nothing prevents a calculation from reading an intermediate value belonging to an unrelated part of the model, such as a figure before a discount was applied, and nothing indicates that it has happened. The result is a plausible number that is incorrect.
Swappable and reusable calculations. Machinery damage is calculated one way for a metalworking plant and another for a food processor. Substituting one calculation for another should be a bounded change against a known interface. In a spreadsheet the affected area is not known in advance, and what depended on a cell is often discovered by breaking it. This sits in apparent tension with comparability: swapping the machinery leaf is exactly the kind of change that could break a shared definition. But the two are held together by the same mechanism: because each node declares what it returns, a substitute leaf is bound to that same result, and two risks stay comparable even when the calculations behind them differ.
Comparability across risks. Two risks must produce figures that mean the same thing, even where the calculations behind them differ entirely. A portfolio view that sums business interruption across a metalworking plant and a food processor is only meaningful if both computed the same quantity under the same definition.
Recalibration without redevelopment. Curves and loading factors are refit as claims data accumulates, often in a separate pipeline with its own release cadence. From the model’s perspective this is a routine and expected event, and it should be a versioned change to data rather than an edit inside a calculation.
Testability and traceability. The input space is combinatorially large, and the outputs cannot be compared against reality for years, least of all in the tail, where the evidence is thinnest and the figure matters most. Separately, when a specific figure is questioned some years later, it should be answerable for that specific run rather than approximately reconstructed.
One implementation. Prototyping, verification and deployment should ideally happen against the same artifact. The common alternative is a model prototyped in a spreadsheet, reimplemented for production, and subsequently maintained in both places, at which point what was verified is no longer quite what ships.
Accessibility and Usability Despite their shortcomings, spreadsheet-based models are highly flexible and accessible, including to less-technical users. A single file can contain the entire model and its inputs, and can be opened, reviewed, annotated, and shared using familiar Office applications and email. While this also creates long-term challenges, such as multiple versions and inputs distributed across email threads, this also allows less-technical stakeholders to maintain visibility into and interact with the model. Moving to a more code based framework therefore requires careful change management to make sure the model is not gate-kept. It requires tools and practices that preserve this accessibility.
Upcoming posts
Approximately three posts follow, working down the schematic, then stepping back to how the result is used in practice, and finally to how a model like this is introduced in the first place.
- A typed, composable model. What the model accepts and returns, how those declarations are made binding, and why a change to the questionnaire should break a type check rather than produce an incorrect number. The same type system carries the calculations, composed as a tree of isolated leaves that can be swapped and tested on their own, and the parameters and curves they consume as versioned data rather than embedded constants.
- Model development in practice. What it looks like to build and debug a model inside an opinionated framework, and why the prototype and the production implementation should be the same thing.
- From spreadsheet to system. The least technical post in the series, and the one about people rather than types: how a model like this is actually introduced in an organisation. The move off an established spreadsheet is gradual, with the two run side by side through intermediate stages, and adoption is earned rather than mandated.