How to Calculate Population Growth Rate in Excel

Get FREE Advanced Excel Exercises with Solutions!

The necessity to calculate growth rate is unavoidable, especially when you are analyzing data for a long period of time. Growth rate can be calculated by population, GDP, income, fertility, mortality, etc. for a specific area, city, or country. Microsoft Excel has paved some easy ways for us to calculate population growth. In this article, we will learn how to calculate the population growth rate in Excel with 2 effective methods.


What Is Population Growth Rate?

Population growth rate refers to the annual average rate of change in population size. It can be for a given country, territory, or geographic area, during a specified period of time. It illustrates the ratio between the increase and the total population for that year. Afterward, it is usually multiplied by 100 to get the ratio in percentage.


Average Annual Growth Rate (AAGR) vs. Compound Annual Growth Rate (CAGR)

The process of calculating the population growth rate is possible in the following two ways:

1. Average Annual Growth Rate (AAGR)

2. Compound Annual Growth Rate (CAGR)

Though both of them calculate annual growth rates, there is a thin line difference between them. AAGR completely avoids the impact of compounding that occurs over the years. On the other hand, CAGR strictly takes into account the compounding effect.

Another difference is, AAGR helps us to know the overall growth trend, while CAGR benefits us with determining possible errors.


How to Calculate Population Growth Rate in Excel: 2 Effective Methods

We have taken a sample dataset to describe the process in this tutorial. This dataset shows the Total Population over the Years 2011-2020 in columns B and C.

2 Effective Methods to Calculate Population Growth Rate in Excel


Let us find the population growth rate in column D following the methods below.

1. Calculate Average Annual Population Growth Rate

In this first method, we will find out the average annual growth rate of the population. For this, we need to find the growth rate for each year first and then we will use the AVERAGE function to get the final output. The basic formula to find individual growth rate is,

=(Ending Value / Beginning Value) – 1

Now, let’s follow the steps below.

  • First, select cell D6 to insert this formula.
=(C6-C5)/C5

Calculate Average Annual Population Growth Rate

  • Then, press Enter and you will see the Annual Growth Rate as a decimal number. But we need the output in a percentage.

Calculate Average Annual Population Growth Rate

  • Therefore, go to the Home tab and select Percentage from the drop-down menu in the Number group.

  • Along with it, you can increase or decrease decimal places by clicking on the icons as shown below.

  • Following, use the AutoFill tool to get the Annual Growth Rate in the cell range D6:D14.

  • Now, insert this formula in cell E5.
=AVERAGE(D6:D14)

How to Calculate Population Growth Rate in Excel

  • Lastly, press Enter and you will get the Average Annual Growth Rate (AAGR).

Here, we used the AVERAGE function to return the average of the arguments in the cell range D6:D14.

Read More: How to Calculate Population Mean in Excel


2. Compound Annual Population Growth Rate Estimation in Excel

Another efficient process is to calculate the compound annual growth rate in terms of population. We can do this by using a regular formula and the XIRR function. Let us see how they work.

2.1. Use Regular Formula

In this section, we will use the following formula to estimate population growth.

=(Ending Value / Beginning Value)^1 / n – 1

where n = Period of time

Now, follow the steps below.

  • First, click on cell D5 and type this formula.
=(C14/C5)^(1/(10-1))-1

Compound Annual Population Growth Rate Estimation in Excel

  • Then, hit Enter.
  • Following, you can see the output is showing as a decimal number.

  • Therefore, change the format into Percentage as discussed in the first method.
  • Finally, you will get the Compound Annual Growth Rate (CAGR).


2.2. Apply XIRR Function

In this last section, we will apply the XIRR function in Excel to calculate the population growth rate. The XIRR function follows this syntax,

XIRR(value, date, [guess])

Here, value = Beginning Value: Ending Value

date = Beginning Date: Ending Date

Now, let’s go through the process below.

  • In the beginning, we typed the time periods as dates in a span of 10 years in cell range B5:B14.

Compound Annual Population Growth Rate Estimation in Excel

  • Following, create a new table to insert Beginning and Ending Values and calculate the CAGR.

Compound Annual Population Growth Rate Estimation in Excel

  • Next, insert the cell reference of the beginning value for the total population in cell F5.
=C5

  • Then, type this formula in cell G5.
=B5

  • Following, type this formula in cell F6.
=-C14

  • Lastly, insert the cell reference of B14 in cell G6 with this formula.
=B14

  • So far, we have inserted our required beginning and ending values in the new table.
  • Now, type this formula in cell F7.
=XIRR(F5:F6,G5:G6)

  • Finally, hit Enter.
  • That’s it, you will get the CAGR for the required time period.

Here, we applied the XIRR function to return the value of the Compound Average Growth Rate for the cell range F5:F6 based on the time period in the cell range G5:G6.

Read More: Population Projection Formula in Excel


Things to Remember

  • Don’t forget to insert a Minus () sign before the cell reference of the Ending Value during the calculation with the XIRR function.
  • Make sure to keep a similar format for each time value. It is also applicable to the population values.

Download Practice Workbook

You can download the worksheet to practice by yourself.


Conclusion

Finally, we are at the end of our article on how to calculate population growth rate in Excel with 2 effective methods. Let us know your insightful suggestions in the comment box.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo