This tutorial presents a patient-level health economic model implemented entirely in Microsoft Excel (version 2505, build 16.0.18827.20102), using formula-based logic rather than VBA. Recent enhancements to Excel’s formula language, including the LAMBDA [5], LET, and REDUCE [6] functions, now allow recursive, state-based simulation to be constructed directly within spreadsheet cells. These developments enable individual-level simulation without the need for macros or external code. An overview of key functional programming components, particularly LAMBDA and REDUCE, is provided in Section 2 of Online Resource 1.

The model simulates an individual patient over time, capturing stochastic health events such as vertebral and hip fractures and all-cause mortality, along with associated costs and utilities. Event times are sampled from statistical distributions, and the simulated patient’s state is updated iteratively until death. Outcomes are continuously discounted and can be returned either as a summary of total QALYs, costs, and survival status or as a time-stamped event history, depending on user preference.

The simulation is built from a set of user-defined LAMBDA functions that handle event sampling, cost and QALY calculations, state transitions, and looping logic. These are integrated using Excel’s dynamic array tools, including REDUCE, VSTACK, and HSTACK, to construct a row-wise matrix of patient states. A full listing of functions and associated code is included in Sections 4–6 of Online Resource 1 in addition to a working copy of the model file itself in Online Resource 2.

2.1 Using the Excel Advanced Formula Environment

The Advanced Formula Environment (AFE) is a free Excel add-in developed by Microsoft that simplifies the creation and management of complex named formulas, particularly those built using LAMBDA functions [9]. Although the model described here can be constructed using Excel’s built-in Name Manager, the AFE improves usability through a structured editor that supports indentation, syntax highlighting, and multi-line formatting. These features enhance readability and reduce errors, especially in models with nested or modular logic.

The AFE supports a functional programming workflow by allowing users to define each formula as a reusable component with named parameters and tracked dependencies. It also facilitates version control, as formulas can be exported and shared in text-based formats, supporting transparency and auditability. Importantly, the AFE automatically wraps formula definitions in LAMBDA, so users do not need to include the outer function call manually. As a result, the code examples in this tutorial are written in the style used within the AFE, omitting the explicit LAMBDA wrapper for clarity and consistency. Using the snapshot presented in Fig. 1 as a basis, the model can be replicated by creating all functions in the AFE using the formula code provided in Online Resource 1 and copying it into the Function Definition Box. Arguments to the function can be typed into the Arguments Box. Function names must be identical to preserve interoperability. While installation of the AFE is beyond the scope of this tutorial, the process is straightforward and documented online [9]. Once installed, Excel Labs is accessed on the main Home ribbon of Excel.

Fig. 1

Snapshot of Excel labs advanced formula editor

2.2 Model Setup

The simulation model requires a structured set of inputs for both parameter values and patient-specific stochastic draws. These inputs are organized within the Excel workbook to support batch simulation across multiple patients. An example of this structure is shown in Fig. 2.

Fig. 2

Structure of the Excel-based individual-level simulation

Global model parameters, such as discount rates, cost inputs, utility multipliers, and distributional assumptions, are listed in a named range (e.g., columns A and B). These values are referenced directly by the runSim function, allowing consistent application across patients and simplifying scenario comparisons.

To simulate patient-level heterogeneity, each patient is assigned a unique set of random numbers, stored in a matrix (e.g., columns F through J). Each row represents one simulated patient and supplies random draws to the model’s sampling functions. This deterministic setup enables full reproducibility of results and supports batch execution.

The runSim function is called row-wise across the random number matrix, with outputs such as QALYs, costs, and death status recorded in adjacent columns. These outputs can then be aggregated, filtered, or visualized using standard Excel functions and charts.

Figure 2 illustrates this structure: parameters are stored in the left panel, random seeds in the central matrix, and model outputs in the right panel. This layout separates data input, simulation logic, and results. The model runs by changing input parameters and waiting for the model to perform the calculations; progress is displayed in the bottom right corner (not shown in Fig. 2).

2.3 Conceptual Framework and Model Structure

This simulation model is a discrete-event, individual-level framework that tracks a single hypothetical patient through a sequence of potential health events over time. With each iteration, the model evaluates time-to-event for a defined set of clinical outcomes, updates the patient’s health and economic state accordingly, and accumulates both quality-adjusted life years (QALYs) and costs. The simulation proceeds until a death event is processed.

The model is a hip fracture model, structured according to the framework described in NICE Decision Support Unit (DSU) Technical Support Document 15 [2]. The model considers three mutually exclusive health events that may occur at any time: vertebral fracture, hip fracture, and all-cause mortality. Vertebral fracture incurs a one-time cost and a temporary reduction in utility. Hip fracture results in both a one-time and ongoing cost burden, a decline in utility, and an elevated risk of subsequent mortality. All-cause mortality is sampled independently but may be influenced by post-hip fracture death risk. At each cycle, the model determines which of these events occurs first, then processes its consequences on the patient’s current state. Event times are sampled from probability distributions defined by user-specified parameters, as described in the next section.

The patient’s state is represented as a row vector containing all relevant clinical and economic information at a given time. This includes current time, accrued and discounted QALYs, accrued and discounted costs, flags indicating whether a vertebral or hip fracture has occurred, cumulative annual costs (such as those following hip fracture), and a binary indicator for death. The simulation begins with an initial state in which the patient is alive and has not experienced any fractures. This state is recorded as the first row in the event history matrix. Subsequent states are generated iteratively and appended to the matrix as new events are processed.

The model uses Excel’s REDUCE function to implement recursive accumulation of the event history. At each iteration, the simulation extracts the most recent row from the history matrix and evaluates whether the patient is still alive. If so, it determines the next event, updates the state using the processEvent function (Fig. 3), and appends the new row using VSTACK. This process continues until the patient dies or a user-defined maximum number of iterations is reached. The model then returns either the full history or summary outcomes such as total QALYs, total costs, and mortality status. Section 1 of Online Resource 1 documents a full mapping of function inputs, outputs, and dependencies for reference.

Fig. 3

Functional structure of model implementation

2.4 Sampling Functions

A key feature of individual-level simulation is the stochastic generation of event times. In this Excel-based implementation, event times are sampled using user-defined LAMBDA functions that mimic standard probability distributions. These sampling functions are evaluated once in the runSim function and remain constant throughout the patient trajectory.

Each simulated patient requires a unique set of uniform random draws. These are passed to the model as fixed inputs to ensure deterministic replication. Specifically, two random values are used to sample vertebral fracture time, two are used for hip fracture time, and one is used for survival. These random draws can be generated externally or using Excel’s native RAND() function, and are typically organized in a table format, with each row corresponding to a simulated patient. Further details of the sampling functions are provided in Section 4 of Online Resource 1.

2.5 Building the Main Simulation Loop (runSim)

The runSim function forms the core engine of the simulation model. It initializes the patient state, applies the processEvent function iteratively to evolve that state over time, and returns either a summary of final outcomes or a full event-by-event patient history, depending on the output mode selected. This section describes the structure of runSim, explains the use of Excel’s REDUCE function to simulate the patient timeline, and details how the output is managed on the basis of the verbosity setting. Key features of the formula are explained below, but the complete formula is provided in Section 6 of Online Resource 1.

2.5.1 Overview of Looping without VBA

Excel’s traditional formula language does not include built-in support for recursion or looping. However, the REDUCE function introduced in recent versions enables a form of controlled iteration. In the context of this model, REDUCE is used to generate a sequence of event history rows, building a complete patient pathway within a dynamic array.

2.5.2 Initialization of the Patient State

Before iteration begins, the simulation must establish an initial patient state. The times to each possible event, vertebral fracture, hip fracture, and death are sampled earlier in the formula and assigned via the LET function as fixed values. The patient state is constructed using the HSTACK function (Box 1), which creates a horizontal row vector representing the patient at time zero. The initial row includes the current time, baseline utility, cumulative QALYs and costs set to zero, flags indicating no prior fractures, time to each event, and event statuses set to false. This row serves as the seed for the REDUCE function, which will iteratively update the state across successive time steps.

Box 1

Example of patient state initialization

2.5.3 History Accumulation Using REDUCE

The simulation loop is executed through the REDUCE function, which takes as input the initial history matrix and a sequence corresponding to the maximum number of iterations. At each iteration, the most recent state is extracted using INDEX. If the patient is still alive, processEvent is called to generate a new row representing the next state, which is appended to the history using VSTACK. If the patient has died, the loop returns the current history unchanged, halting further state transitions. This construction ensures that the simulation loop terminates upon death, and no further events are processed once a terminal state has been reached. Box 2 illustrates a simplified formula highlighting the logic used to iterate through a patient’s events.

Box 2

Simplification of the history accumulation logic using REDUCE

2.5.4 Output Control via Verbose Parameter

The runSim function includes a verbose parameter to control the form of output, the logic of which is shown in Box 3. When the verbose setting is set to TRUE, the function returns the full event history as a dynamic matrix, showing each transition in patient state over time (see, for example, Fig. 1 of Online Resource 1). When set to FALSE, the function extracts only the final row of the matrix, retrieving cumulative values such as discounted and undiscounted total QALYs, total costs, and death status.

Box 32.6 Implementing processEvent

The processEvent function determines the next event to occur (vertebral fracture, hip fracture, or death), updates the patient state using the appropriate handler, and returns a new row representing the updated state. This function is a central control node in the simulation and is called at each iteration of the model.

2.6.1 Inputs and Logic

The processEvent function serves as the decision-making component of the simulation. It takes as input the patient’s current state, along with all relevant event times and model parameters. The function begins by identifying which of the three possible events occurs first according to the sampled times: a vertebral fracture, a hip fracture, or death. Once the earliest event is determined, the function invokes the corresponding event handler to process the clinical transition and compute the resulting changes in the patient’s health and economic outcomes. It then returns a new state vector that includes the updated time, accumulated QALYs, total costs, and binary indicators reflecting the occurrence of events.

2.6.2 Selecting the Next Event

To determine which event occurs next, a helper function getNextEvent is used. It takes the three event times and returns the minimum. It is implemented using the formula shown in Box 4. In processEvent, the function compares this minimum against each event time to decide which event handler to call.

Box 4

Function to select the next event, getNextEvent

Box 5 illustrates the general structure of the logic. The event routing logic is implemented using Excel’s LET and CHOOSE functions, allowing the model to select and apply the appropriate event handler based on a predetermined event code. The LET function is used to define intermediate variables for clarity and performance, including the current simulation time and the outputs of each event handler. The CHOOSE function then routes execution to one of three branches corresponding to vertebral fracture, hip fracture, or death. Within each branch, the selected handler is invoked, and the relevant output values are extracted using INDEX. These values are then assembled into a new state vector using HSTACK.

Box 5

General structure of processEvent depicting event routing logic. “(...)” indicates code shortened for brevity

2.6.3 Integration with Simulation Loop

The processEvent function is called during each iteration of the simulation loop. It takes the last row in the patient’s history (the current state) and returns a new row, which is appended to the history matrix.

2.7 Defining Event Handlers

During the simulation, each occurrence of a health event necessitates an update to the patient’s health and economic state. This update is performed using dedicated event handler functions, each of which is designed to process the specific consequences associated with a particular event type. These handlers receive the current patient state as input, apply the appropriate transformations to reflect changes in utility and cost, and return an updated state vector that is appended to the patient’s event history.

The model defines three primary event handlers, each corresponding to a distinct clinical transition. The vertebral fracture handler processes utility reductions and one-time costs associated with vertebral events. The hip fracture handler incorporates both immediate and ongoing cost components, utility decrements, and the potential elevation in mortality risk that follows such an event. Finally, the death handler finalizes the simulation by computing terminal utilities and costs and flagging the patient as deceased.

2.7.1 Vertebral Fracture Handler

A vertebral fracture results in a one-time cost and a temporary decrement in health utility. The event occurs at a specified time, and the model calculates the QALYs accrued and costs incurred since the previous state, using continuous discounting.

Within the vertebral fracture handler (Box 6), calcDiscountedQALY is used to calculate the QALYs accrued between the previous and current time points, incorporating both the utility decrement associated with the event and continuous discounting over time. Next, calcDiscountedCost is applied to compute any one-time costs incurred at the time of the event, also using continuous discounting. The handler then assembles a new patient state vector using HSTACK, which includes the updated time, cumulative discounted QALYs and costs, and event flags indicating the occurrence of the relevant health transition.

Box 6

Vertebral fracture handler

2.7.2 Hip Fracture Handler

A hip fracture leads to both a utility decrement and the onset of annual ongoing costs (e.g., nursing care). It also increases the patient’s probability of death. The handler processes these effects and updates the state accordingly.

In addition to the updates applied for vertebral fractures, the hip fracture handler (Box 7) incorporates additional elements specific to the clinical and economic consequences of a hip fracture. One-time and ongoing annual costs are calculated separately, with the present value of each component determined through appropriate discounting. The handler also introduces a stochastic mortality component: Death following hip fracture is triggered probabilistically by comparing a random uniform draw against a user-specified mortality threshold. If the draw exceeds the threshold, the patient is flagged as deceased. The resulting state vector includes the cumulative impact of hip-related costs and utilities, and the patient’s death status as determined by the mortality risk assessment.

Box 7

Abbreviated hip fracture handler function. “(...)” indicates code shortened for brevity

2.7.3 Death Handler

The death handler (Box 8) finalizes the simulation for an individual patient by calculating utility and ongoing costs accrued between the last recorded time point and the time of death. These values are computed using continuous discounting, consistent with the approach used in other event handlers. Upon processing the death event, the patient’s status is updated by setting the death flag to TRUE, which signals the termination of the simulation loop. Once this handler has been invoked, no further events are considered, and the final state vector reflects the patient’s outcomes at the point of death.

Box 82.8 Discounting Utilities and Costs

Health economic models typically apply time-based discounting to reflect the reduced present value of future health benefits and costs. This simulation applies continuous discounting to both QALYs and monetary costs. All functions are defined as named LAMBDA functions in Excel and return scalar values that are incorporated into the evolving patient state vector (see Section 5 of the Online Supplement for further details).