Population Projection Formula in Excel (3 Applications)

This article illustrates how to apply a population projection formula in Excel. Population projection is especially important for any kind of preplanning or preparedness. It gives a picture of the size of the future population of a particular species. The projection can be made based on several components like past trends, migration, calculated assumptions, etc. In this article, we will explain how to use formulas in Excel for population projection.

How to Apply Population Projection Formula in Excel: 3 Methods

There are 3 common ways you can forecast or project the future population in Excel.

• Linear Projection
• Geometric Projection
• Exponential Projection

Linear projection is rarely used. It should be used only when the population censuses from the recent past are available, the population growth rates are stable, and the projection year is in the near future. This method assumes that the population is increasing at a constant amount.

Geographical projection is the most used projection method. It focuses on the percentage change rather than the numeric change of the population. Instead of consistency in numeric increase, it assumes the growth rate is constant.

In exponential projection, the rate of change in population is assumed to be directly proportional to the current population and the growth rate percentage.

Now we will discuss how to apply these methods in Excel for population projection.

1. Linear Population Projection Formula

The Linear Population Projection formula is as follows:

Pt = P0 + m Ã— âˆ†t

Where,
Pt = Projected Population
P0 = Latest Population
m = Average Increase
âˆ†t = Number of Periods

We have the following dataset containing the population of the last 5 years in the USA. We have also calculated the population growth in column D.

Follow the steps below to forecast the future population using this method.

📌 Steps:

• First, enter the following formula in cell E6 and drag the Fill Handle icon below.
`=C6-C5`

• Then, apply the following formula in cell E11 to get the average increase using the AVERAGE function.
`=AVERAGE(E5:E9)`

• Finally, enter the following formula in cell E16 to get the projected population as follows.
`=E13+E15*E11`

Notes: You need to consider the following points while using the Linear Population Projection method.

• The number of periods is low (within 5 to 10 years)
• The stable growth rate in the existing data.
• Data is collected from recent past and current censuses.

2. Geometric Population Projection Formula

The Geometric Population Projection formula is as follows:

Pt = P0 (1 + r)n

Where,
Pt = Projected Population
P0 = Base Year Population
r = Growth Rate
n = Number of Periods

• Assume you have the following dataset instead of the earlier one. It contains the base year population, the average growth rate for the coming years, and the total number of periods until the projection year.

• Then apply the following formula in cell C9 to get the Geometric Population Projection as shown below
`=C7*(1+C8)^C6`

3. Exponential Population Projection Formula

The Exponential Population Projection formula is as follows:

Pt = P0 er Ã— t

Where,
Pt = Projected Population
P0 = Base Year Population
r = Growth Rate
t = Total Periods

• Now enter the following formula in cell C9 instead of the earlier one to get the Exponential Population Projection as follows.
`=C7*EXP(C8*C6)`
• Here, we have used the EXP function, which returns the value of the constant e based on the power.

Read More: How to Analyze Demographic Data in Excel

Things to Remember

• Try to avoid using the linear projection unless you have enough data that meets the criteria described earlier.
• You may find a slight difference in the results obtained using the geographical and exponential projections.

Conclusion

Now you know how to use the Population Projection formula in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below.Â  Stay with us and keep learning.

Related Articles

<< Go Back to Excel Demographic DataÂ | Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF