Lotka-Volterra Equilibrium and Small-Amplitude Period in Excel

Define the model and its units

Use dN/dt=N*(alpha-beta*P) and dP/dt=P*(delta*N-gamma), with prey N and predators P. All four coefficients are positive. Alpha and gamma have units 1/time; beta has units 1/(predator unit*time), and delta has units 1/(prey unit*time). Use counts or densities consistently.

Delta is the predator growth coefficient multiplying N*P. It is not simply a dimensionless conversion efficiency: in the common notation with efficiency e, delta=e*beta when the population units permit that interpretation. The example uses years for time.

Find coexistence and the nearby oscillation period

The positive equilibrium is N*=gamma/delta and P*=alpha/beta. The example gives N*=100 and P*=40. E3 and E4 calculate these values without rounding. E2 rejects missing, text, zero or negative coefficients.

Linearization gives eigenvalues plus or minus i*SQRT(alpha*gamma), so E5 gives the small-amplitude period 2*PI()/SQRT(alpha*gamma), about 9.934588 years here. It is not the exact period for every large-amplitude orbit. At the exact equilibrium both populations stay constant.

In this ideal model the coexistence point is a center, not an attracting equilibrium. Nearby trajectories do not damp back to it. The formula alone does not compute a time series, amplitude or starting phase.

=IF(E2="OK",2*PI()/SQRT(B2*B4),E2)

Inspect rates of change in real cells

B7 and B8 hold the current populations. E10 and E11 evaluate the two baseline derivatives. At 100 prey and 40 predators both are zero. Change prey to 120 while leaving predators at 40: prey initially changes at zero, while predators increase at 4 population units/year.

With prey=100 and predators=30, the initial prey rate is +20/year and predator rate is zero. These are instantaneous slopes, not one-year population increments. To trace trajectories, use a numerical ODE solver and check convergence with its step size; a simple large Euler step can create spurious growing oscillations.

Explore constant per-capita removal, within its assumptions

B6 adds the same nonnegative per-capita mortality m to each species continuously: alpha becomes alpha-m and gamma becomes gamma+m. This is proportional removal, not a fixed number harvested each year or a one-time pesticide pulse. Positive coexistence requires m<alpha. E6:E9 reject the boundary and larger removal rates.

For m=0.2/year, E7 gives 140 prey, E8 gives 30 predators, and E9 gives a nearby period of about 9.695165 years. The equilibrium prey level rises in this model; that is not a general claim that spraying always increases real pest numbers.

For a positive closed orbit, integrating dLN(N)/dt=alpha-beta*P over one full cycle gives average P=alpha/beta. Integrating dLN(P)/dt=delta*N-gamma similarly gives average N=gamma/delta. Apply the adjusted coefficients to obtain the removal-scenario cycle averages. These are full-cycle time averages, not arbitrary snapshots or a short observation-window average.

=IF(E6="OK",(B4+B6)/B5,E6)

Use the worksheet to test relationships

Set m=0 and the scenario outputs match the baseline. Set m=alpha and confirm that the sheet reports no positive coexistence instead of returning a misleading zero-population oscillation period. Double beta: the baseline predator equilibrium halves while the prey equilibrium and local period stay the same.

The model omits carrying capacity, seasonal forcing, handling time, migration and random extinction. Parameter estimates and any real intervention require a more suitable ecological analysis; this is an educational exploration of the stated equations.

Further reading

• Jeffrey R. Chasnov, HKUST — Mathematical Biology, section1.4
• BingKan Xue, University of Florida — Lotka-Volterra system