Numerical Solutions to the Logistic Equation: Exploring Population Models in Spreadsheets

Introduction

The logistic equation is a fundamental mathematical model used to describe population growth in a constrained environment. It was first proposed by Pierre-François Verhulst in 1838 and is expressed as:

Here, P represents the population size at time t, r is the intrinsic growth rate, and K is the carrying capacity of the environment, or the maximum population size that can be supported indefinitely. The logistic equation models growth that starts exponentially, but as the population approaches K, the growth rate slows down due to limited resources.

In its simplest form, the equation predicts how populations evolve over time, transitioning from an initial rapid increase to a more stable size. This model applies to a variety of biological, ecological, and social systems.

Applications

1. Population Biology: The logistic equation is widely used in ecology to model the population dynamics of species, particularly those in environments with limited resources, such as food or space.
2. Epidemiology: It is also used to model the spread of infectious diseases, where the population is replaced by the number of susceptible and infected individuals.
3. Economics: Logistic models are used to forecast the adoption of new technologies or products in a market, showing how initial rapid growth slows as saturation is reached.
4. Sociology: It helps explain the spread of ideas or cultural practices, where early adopters grow rapidly before stabilizing as most of the population adopts them.

This versatile model is a key tool in understanding constrained growth across disciplines.

Why use a Spreadsheet

Using spreadsheets to solve a logistic equation offers an accessible, visual approach to modeling population growth or similar processes without needing advanced programming skills. Spreadsheets allow for step-by-step iteration, making it easy to observe how changes in parameters like growth rate or carrying capacity impact the solution. Additionally, they offer built-in functions for plotting, enabling quick visualization of results. This makes spreadsheets an ideal tool for those looking to experiment with different scenarios, perform sensitivity analyses, or communicate complex ideas through intuitive tables and graphs in an easily understandable format.

Solving the equation

Given the equation above, we can use a similar approach as in the previous post where we define an interval h by which time increases, and calculate P at the (i+1)th time as a function of P at the ith time.

So,

which leads to:

So, assuming that P(0) = 2, K = 500, r = 1.5 and h = 0.1, we can easily get a solution to this equation.

Let’s input the values of h, r, and K in cells B4, B5 and B6 respectively. In cell A10 we can write t, indicating that it is the time column, and in cell B10 we can write P indicating that it is the Population column.

In cell A11 we can write 0 indicating time = 0 and we defined earlier that P(0) = 2 so we can input 2 in cell B11. Cell A12 takes the formula =A11 + $B$4 (notice the $ signs used to fix the cell) and cell B12 takes the formula =$B$4*$B$5*B11*(1-(B11/$B$6)) + B11

Looking at the spreadsheet below:

We see that as the population approaches K, the value of P becomes asymptotic.

Conclusion

The logistic equation provides a powerful framework for modeling population dynamics and other processes constrained by limited resources. By using spreadsheets to solve this equation, we can easily explore how changes in growth rate, carrying capacity, and initial conditions affect population behavior over time. The spreadsheet approach offers a step-by-step, visual method to solve the equation and experiment with different scenarios, making it accessible even to those without programming experience. As demonstrated, the population initially grows rapidly, but as it nears the carrying capacity (K), it slows down and eventually stabilizes, reflecting real-world growth patterns in many systems.