Predator-Prey Population Dynamics
Simulate ecological predator-prey interactions using the classical Lotka-Volterra differential equations solved with SciPy’s solve_ivp. Durable worksheet parameters drive the numerical integration in the Python for Excel notebook, generating population cycles and phase-space orbits directly in your spreadsheet.
Result preview
With the baseline parameters (Prey x0 = 40, Predator y0 = 9, alpha = 1.0, beta = 0.1, gamma = 1.5, delta = 0.075), the system orbits around the theoretical equilibrium center at (x* = 20.0, y* = 10.0). Over a 30-period horizon, prey population peaks at 40.14 (minimum 8.08) while predator population peaks at 22.97 (minimum 3.17).
What this template does
- Reads initial populations (\(x_0, y_0\)) and biological rate parameters (\(\alpha, \beta, \gamma, \delta\)) from labeled worksheet cells.
- Integrates coupled nonlinear differential equations using
scipy.integrate.solve_ivpwith the explicit Runge-Kutta (RK45) method. - Calculates theoretical equilibrium points, population extremes, and instantaneous rates of change (\(dx/dt, dy/dt\)).
- Visualizes time-series trajectories and closed-loop phase-space portraits side-by-side.
- Publishes summary KPIs and a complete 31-row trajectory table back to Excel.
Why Python
Coupled nonlinear differential equations are awkward to model with static spreadsheet formulas, while fixed-step Euler approximations can introduce substantial numerical error and artificial amplitude drift. SciPy’s solve_ivp uses adaptive step-size error control; this template uses the explicit RK45 method with rtol=1e-8 and atol=1e-8, plus dense interpolation for smooth visualization and refined turning-point metrics. RK45 is intended for non-stiff problems and does not guarantee exact conservation of the Lotka-Volterra invariant. See the SciPy solve_ivp documentation.
Try it live
Edit initial populations or rate parameters on the Simulation sheet. The integrated trajectory, summary metrics, and phase portrait recompute immediately from the modified parameters.
Operating workflow
Author: a technical analyst or scientific modeler maintains the Lotka-Volterra equations, numerical integration settings, validation scenarios, and published workbook outputs.
Workbook user: an analyst, educator, or domain specialist changes the population and rate assumptions, then reviews the recalculated KPIs, trajectory table, and phase-space plot without editing the Python model.
When the workbook is used as a repeatable simulation tool, the author can save the notebook to open in App mode for the intended operator. App mode changes the visible authoring surface, not source availability or workbook trust.
Download the Excel template
Inputs and assumptions
The parameter table located at Simulation!A4:B10 contains:
| Parameter | Notation | Baseline Value | Description |
|---|---|---|---|
| Initial Prey | \(x_0\) | 40.0 | Starting prey population count |
| Initial Predator | \(y_0\) | 9.0 | Starting predator population count |
| Prey Growth Rate | \(\alpha\) | 1.0 | Natural prey reproduction rate without predation |
| Predation Rate | \(\beta\) | 0.1 | Prey mortality rate per predator-prey encounter |
| Predator Mortality | \(\gamma\) | 1.5 | Natural predator death rate without prey |
| Predator Efficiency | \(\delta\) | 0.075 | Predator reproduction rate per prey consumed |
The model assumes a constant food supply for prey, predation rate proportional to encounter frequency, and predator growth proportional to consumed prey.
Notebook implementation
The Python for Excel notebook reads parameters via bf.inputs(), solves the ODE system, and publishes summary KPIs and the trajectory table:
inputs = bf.inputs(
parameters=bf.ref("Simulation!A4:B10", headers=True),
)
solution = solve_ivp(...)
bf.publish(outputs={"summary": summary, "trajectory": trajectory})How the calculation/model works
The Lotka-Volterra equations form a pair of first-order non-linear differential equations:
\[ \frac{dx}{dt} = \alpha x - \beta x y \] \[ \frac{dy}{dt} = \delta x y - \gamma y \]
Setting both derivatives to zero yields the non-trivial co-existence center:
\[ x^* = \frac{\gamma}{\delta}, \quad y^* = \frac{\alpha}{\beta} \]
Using solve_ivp(..., method='RK45'), Python exports the state vector \([x(t), y(t)]\) at 31 integer time points across \(t \in [0, 30]\). The same adaptive solution provides dense interpolation for the plots, while zero crossings of \(dx/dt\) and \(dy/dt\) identify turning points used for the summary population extrema.
Validation / expected results
For the canonical baseline parameters:
| Check | Expected value |
|---|---|
| Equilibrium Prey (\(x^*\)) | 20.0000 |
| Equilibrium Predator (\(y^*\)) | 10.0000 |
| Prey Peak | 40.1427 |
| Prey Minimum | 8.0788 |
| Predator Peak | 22.9743 |
| Predator Minimum | 3.1710 |
| Final Prey (\(t=30\)) | 8.6325 |
| Final Predator (\(t=30\)) | 6.9714 |
The offline pytest/Univer gate executes the canonical notebook.py and validates these cells in the real Boardflare browser runtime. It then lowers the worksheet prey growth rate to 0.75 and verifies both the equilibrium and dynamic trajectory metrics recalculate, including a prey peak of 40.3520 and final prey population of 17.6920.
Limitations
The basic Lotka-Volterra model assumes exponential prey growth in the absence of predators (no carrying capacity) and a linear predator hunting response (Type I functional response). In real ecological systems, prey populations face environmental limits and predators experience saturation (e.g. Holling Type II response) or hunting satiation.
When to use this approach
Use scipy.integrate.solve_ivp whenever your model involves continuous rates of change, feedback loops, chemical kinetics, epidemiology (SIR models), or dynamical systems that cannot be captured by discrete spreadsheet difference equations.