I have put together an Excel workbook in an attempt to get a handle on how close we are to reaching herd immunity as the Wuhan virus pandemic drags on. The workbook facilitates a comparison between a primitive model and an estimate of the number of people infected by the virus based on published test results. The ‘Panel’ worksheet gives access to all of the parameters for both the model and for estimating the number of infections. along with three key charts for viewing the results of the calculations. The main idea is to adjust the model parameters to match the infection estimate over the course of the pandemic and then use other model calculations to evaluate how many people are currently immune and get an idea of what could happen next. The next two paragraphs provide some basic information on key calculations. A guide on how to use the workbook is available on this page.

The model computes the number of contagious people on a day-to-day basis. The number of newly infected people on a given day is computed by:

R₀ * C / (t_e – t_s)

where R₀ is the number of new infections from one contagious person over the course of an infection, C is the number of contagious people on that day, t_e is the number of days after infection that a person is no longer contagious, and t_s is the number of days after infection that a person becomes contagious. At the end of being contagious, the model either counts a person as dead, immune, or susceptible to re-infection based on other model parameters. The model doesn’t treat immunity as a permanent condition, but sets a minimum and maximum time that the immunity lasts, between which there is constant probability that the immunity ends. All the model parameters can be adjusted, and the value of R₀ can be set to multiple values, each with it’s own start date to reflect changes in season and behavior (lockdowns, holidays, etc).

Estimation of actual infections is based on the number of viral tests conducted and the number of tests that are counted as positive. Data sets are available for the USA as a whole or for individual states. The data is taken from The Atlantic’s Covid Tracking Project with some edits to remove major discrepancies. The estimated infection rate is computed as

P * c/T^0.75 + b

Where P is the positivity rate, and T is the number of tests per 100,000 people. The parameter c is based on the ratio of the total number of people who had actually been infected to the total number positive test results at the crest of the first peak in April. This ratio can be set on the panel. The parameter b is automatically set so that that the positivity rate equals the infection rate if the entire population is tested in one day (when T = 100,000).