SIR model in Excel, step by step

The SIR model in one paragraph

The SIR model of Kermack and McKendrick (1927) splits a population of size N into three compartments: Susceptible, Infectious and Recovered (immune, or removed). People move one way only, S → I → R, and S + I + R stays equal to N. Two parameters drive everything. β is the number of effective contacts an infectious person has per day, and γ the daily recovery rate, 1 divided by the average infectious period. It is the simplest epidemic model that produces the familiar rise, peak and decline, and the starting point for SEIR and age-structured models.

Step 1: the equations as formulas

Of the β contacts an infectious person makes, only the share S ÷ N reaches someone who can still be infected. So with the parameters in B2:B5 and day 0 in row 11:

• New infections: =$B$3*B11*C11/$B$2
• Recoveries: =$B$4*C11
• S tomorrow: =B11-E11
• I tomorrow: =C11+E11-F11
• R tomorrow: =D11+F11

Day 0 starts with I₀ = 10 infectious people and S = N − I₀ = 99,990. On day 0 the 10 infectious people cause 0.3 × 99,990 × 10 ÷ 100,000 = 3.0 new infections, and 1.25 of them recover.

Step 2: copy down one row per day

Write the formulas once in row 12 for day 1, then fill them down for 150 to 200 days. Each row is one Euler step: the differential equations of the model are approximated by daily changes. In the first two weeks the epidemic grows by about 17.5% a day, the factor 1 + β − γ, so the 10 infectious people become 95.4 by day 14, a doubling roughly every 4.3 days. Add a check column =B11+C11+D11: it must equal N in every row, otherwise a formula points at the wrong row.

Chart columns B to D against the day as an XY scatter with lines to see the curves: S falls in an S-shape, I rises to a peak and falls, R climbs to its final level. In the full version of this sheet, with the same formulas copied down to day 200, I peaks at about 22,700 people on day 58 and about 88.7% of the town is infected in the end.

Worked example: SIR model for a town of 100,000

Yellow cells are the parameters. Rows 11 to 25 are the first 15 days of the daily Euler steps. Copy row 25 down for the full epidemic. Column E iterates the final-size equation. Synthetic data.

Worked example: SIR model for a town of 100,000

	A	B	C	D	E	F
1	Parameter	Value		Iteration	Final size z
2	Population N	100,000		Start	1.0000
3	Transmission rate β (per day)	0.300		Step 1	0.9093
4	Recovery rate γ = 1 ÷ 8 days	0.125		Step 2	0.8872
5	Infectious on day 0, I₀	10		Step 3	0.8811
6	R0 = β ÷ γ	2.40		Step 4	0.8793
7	Herd immunity threshold 1 − 1/R0	58.3%		Step 5	0.8788
8	Peak share infectious	21.9%		Step 6	0.8787
9
10	Day	Susceptible S	Infectious I	Recovered R	New infections	Recoveries
11	0	99,990	10.0	0.0	3.0	1.3
12	1	99,987	11.7	1.3	3.5	1.5
13	2	99,983	13.8	2.7	4.1	1.7
14	3	99,979	16.2	4.4	4.9	2.0
15	4	99,974	19.1	6.5	5.7	2.4
16	5	99,969	22.4	8.9	6.7	2.8
17	6	99,962	26.3	11.7	7.9	3.3
18	7	99,954	30.9	14.9	9.3	3.9
19	8	99,945	36.3	18.8	10.9	4.5
20	9	99,934	42.7	23.3	12.8	5.3
21	10	99,921	50.1	28.7	15.0	6.3
22	11	99,906	58.9	34.9	17.6	7.4
23	12	99,889	69.2	42.3	20.7	8.6
24	13	99,868	81.2	50.9	24.3	10.2
25	14	99,843	95.4	61.1	28.6	11.9

Synthetic data and a textbook model. Educational only, not public-health guidance.

Step 3: R0 and the herd immunity threshold

R0 = β ÷ γ = 0.3 × 8 days = 2.4: one infectious person in a fully susceptible population infects 2.4 others over the infectious period. The epidemic grows while the effective reproduction number Rt = R0 × S ÷ N is above 1. It turns when S ÷ N falls to 1 ÷ R0, so the herd immunity threshold is 1 − 1 ÷ R0 = 58.3% immune. Vaccinating that share before an outbreak keeps Rt below 1. With an imperfect vaccine of effectiveness E, the coverage needed is the threshold divided by E.

Step 4: peak and final size without the full table

The continuous SIR model has two closed-form results. When almost everyone starts susceptible, the share infectious at the peak is 1 − (1 + LN(R0)) ÷ R0 = 21.9%. The share infected by the end, the final size z, solves z = 1 − EXP(−R0 × z). Excel has no direct function for it, but repeating the formula converges quickly: start at z = 1 and after six steps it settles at 0.8787, about 87.9%, as in column E of the example.

The daily Euler table comes out slightly higher, a peak of about 22.7% and a final size of 88.7%, because one-day steps overshoot a little. Shorter steps, for example a quarter of a day with the rates divided by 4, bring the table closer to the exact values. Note also that the final size is well above the 58.3% threshold: an unchecked epidemic overshoots herd immunity.

Beyond the basic model

Real epidemics need more. An SEIR model adds an Exposed compartment for people who are infected but not yet infectious, which delays the peak. Interventions are modelled by lowering β from a start day, which flattens and delays the curve. Fitting the model means choosing β so that the model curve matches observed cases, for example by minimising the sum of squared errors, an error measure related to the one in the MAPE guide. Births, deaths, waning immunity, age groups and reporting delays all change the results, which is why public-health models are far more detailed than this one.

Common mistakes

• Forgetting to divide by N. β × S × I without ÷ N explodes in the first days. The contacts only infect the susceptible share of the population.
• Referencing the same row. Tomorrow's S must use today's new infections, from the row above. A formula that refers to its own row gives a circular reference.
• Mixing up γ and the infectious period. γ is 1 ÷ period. An 8-day period means γ = 0.125, not 8.
• Relative references on the parameters. Copying down without $ on β, γ and N shifts them to empty cells. Lock them with $B$3, $B$4, $B$2.
• Trusting daily steps blindly. Large β or γ with one-day steps overshoots and can even produce negative S. Use shorter steps if the rates are high.
• Reading the model as a forecast. SIR assumes everyone mixes evenly and nothing changes. It explains dynamics, it does not predict a real outbreak.