How to calculate average annual growth rate in Microsoft Excel

In Microsoft Excel, the Annual Average Growth Rate refers to a metric used to determine the growth rate of an investment or business-related data, such as revenue, profit, or market share, on a year-over-year basis. This calculation provides an estimation of the average change rate during a specific time frame, based on the assumption that the growth is uniform throughout the period.

In this tutorial, we will learn how to calculate the average annual growth rate in Microsoft Excel. In Excel calculating the average annual growth rate is a common task that can be achieved by the formula “[(Ending Value / Beginning Value)^(1/Number of Years)] – 1”. Also, we can utilize the AVERAGE function for calculating the average annual growth rate in Microsoft Excel.

Method 1: Calculating the Average Annual Growth Rate by Utilizing the AVERAGE Function

Step 1 – Choose an Empty Cell

• Choose an Empty cell.

Step 2 – Utilize the AVERAGE Function

• Utilize the AVERAGE function:

           AVERAGE((B3:B8-B4:B7)/B4:B7)

• Where B3:B8 is the range containing the total revenue for each year and the range B4:B7 is the range excluding the initial and the final value.

Step 3 – Hit the Enter Key

• Hit the Enter key.

Method 2: Calculating AAGR from Individual Growth Rate Per Year

Step 1 – Calculate the Growth Rate for the Second Year

• Calculate the growth rate for the second year.
• For this utilize the formula:

             (B4/B3)

• Where B4 is the cell with the final value for that interval and B3 is the cell with the initial value.
• Hit the Enter key.

Step 2 – Utilize Autofill to Calculate the Growth Rate for Each Year

• Utilize Autofill to calculate the growth rate for each year.

Step 3 – Apply the Percentage Format to the Growth Rate of Each Year

• Apply the percentage format to the growth rate of each year.

Step 4 –  Utilize the Average Function to Calculate the Average Annual Growth Rate 

• Utilize the Average function in a cell:

            AVERAGE(C4:C8)

• The range C4:C8 holds the growth rate for each year in the percentage format.
• Hit the Enter key to get the average annual growth rate.