How to Calculate Annual Growth Rate in Excel (5 Different Cases)

If you are involved in the business world, you know how important it is to learn about the annual growth rate to analyze the progress of a company’s market plans. This blog will show you 5 different cases regarding how to calculate the annual growth rate in Excel.

The annual growth rate can be calculated using two methods. The first one is the average growth rate (also known as simple growth rate or yearly growth rate). And the second one is the compound average growth rate (CAGR). Microsoft Excel has lessened your workload with some amazing formulas to calculate the annual average growth rate (AAGR) and compound average growth rate (CAGR).

Annual Growth Rate in Excel.xlsx

I am going to use a sales record (column B: Sales) of a business startup from the year 1990 to 2002 (column A: Year). The AAGR is in column C.

Table of Contents

What is the Average Annual Growth Rate (AAGR) or Simple Growth Rate?

It is the average increase of the return of a revenue stream or investment of a company, in a given period.

To give you an example let’s say you have a small business of bakery items and your AAGR for the last 3 years is 25%. So that means your business has earned a 25% profit in these 3 years from what you invested at the beginning of your bakery shop. But that does not mean that your business grows with 25% returns each year. It is the average growth of the last 3 years.

A. Average Annual Growth Rate (AAGR)

Case 1: Average Annual Growth Rate (AAGR) for One Year

To determine the average annual growth rate, first, find out the difference between the starting value and the final value. And then, fractionate the outcome with the starting value. Easy right?

Basic Formula of Average Annual Growth Rate,

(AAGR) =(Ending Value – Starting Value)/Starting Value

=(Ending Value/Starting Value)-1

To calculate AAGR in Excel you can use the formula below:

=(B3-B2)/B2

In this formula, B3 is the ending value and B2 is the starting value. The difference is then divided by cell B2. We haven’t applied the formula of the AAGR in cell C2. It’s because, for cell C2, the ending value would be B2 but the starting value would be zero.

To calculate the average annual growth rate, go through the following method:

Select an empty cell
Write this formula: =(B3-B2)/B2

Press ENTER.
Copy down the formula using the Fill Handle.
The result is in decimal form. Normally, AAGR is represented in the percentage form. So now change the decimal to a percentage.
Click on the Home tab.

From the Number group, select the ‘%’ option.

Now, you have the AAGR of 1990 to 2002. If you want to find out the AAGR per year of a company, this is the best way to do it.

Case 2: Average Annual Growth Rate (AAGR) for a Given Time Frame

In the previous technique, I showed you how you can calculate the AAGR per year. But it was not for a given time frame. For instance, if you want to calculate the AAGR for 1990-1995 or 1990-2002, you will need to use the LN function.

The formula with LN function for Annual Average Growth Rate= (1/T)*ln(Ending Value/Starting Value)

Here,

T is the number of years we are calculating an increase or decrease.
Ln is the natural log with a base e.
The Ending Value is the final value of the given duration.

The Starting Value is the initial investment.

The formula to calculate the average annual growth rate for a given time frame:

=(1/5)*LN(B7/B2)

In this formula, 1/5 is the 1/T. The Ending Value is B7. The Starting Value is B2.

I am going to calculate the AAGR for 1990-1995. To do that, go through the steps below:

Select an empty cell.
Type this formula: =(1/5)*LN(B7/B2)
I used cell C7 as it corresponds to cell A7 of the year 1995.

Press ENTER.

Here you get the final result for 1990-1995.

Case 3: Calculate Average Growth Rate (AGR) in Excel

When you are done with determining the AAGRs, you can also calculate the Average Growth Rate (AGR) from the results. We will be using the AVERAGE function to find out the AGR.

Here is the formula to calculate AGR in Excel:

=AVERAGE(C3:C14)

Choose an empty cell beside the data table.

Type this formula: =AVERAGE(C3:C14)
I chose cell D8 because it is the middle position of column D.
Then press ENTER.

Determining the AGR from the AAGR is a bit rare case. But it is important to know all the methods.

B. Compound Annual Growth Rate (CAGR)

Case 1: Calculate Compound Annual Growth Rate (CAGR) in Excel

You must be thinking about what CAGR is used for. CAGR is used to calculate the compounded returns from your annual investments. But it does not count your company’s return individually.

The formula of CAGR may look a little complex at first. But if I break down its explanation, it will seem understandable.

To calculate the CAGR, first, fractionate the Ending Value by the Starting Value (Ending Value/Starting Value). Secondly, choose the period. Then, fractionate 1 by time (1/Time). After that, take the Ending Value/Starting Value and raise it to the power 1/Time. Then finally, minus 1 from the outcome.

The formula to calculate compound annual Growth Rate, CAGR =(Ending Value/Starting Value)^(1/Time)-1

To calculate CAGR in Excel, use this formula:

=(B14/B2)^(1/12)-1

Here,

B14 is the final value and B2 is the starting value.
12 is the given period. It is counted as: (Total number of cells in the ‘Year’ column – 1).

To calculate CAGR with the above formula, here are the steps below:

Select an empty cell.
Write this formula: =(B14/B2)^(1/12)-1

Press ENTER.
Change the decimal into a percentage using the Home tab > Number group > % option.

Done. You get your desired CAGR value.

Case 2: Get Compound Annual Growth Rate (CAGR) in Excel Using RRI Function

It’s an alternative method to calculate the CAGR using the RRI function.

The formula to get CAGR in Excel:

=RRI(12,B2,B14)

Steps to use the RRI function for calculating compound growth rate in Excel:

Select an empty cell.
Copy this formula: =RRI(12,B2,B14)

Press ENTER.

Please remember to count the period for this technique like the previous methods. Here is the final result:

Conclusion

I hope the methods I have shown you will help you solve all annual growth rate-related problems. If you can’t remember the formulas, keep practicing them often. Feel free to tell us if you find our articles helpful. Have a good day!

Frequently Asked Questions

What is the growth formula in Excel?

The growth formula in Excel is expressed as: =GROWTH(known_y’s, known_x’s, [new_x], [const])
It calculates predicted exponential growth based on existing data points and can be useful for forecasting trends in financial, scientific, or business contexts.

What is a good CAGR rate?

A good Compound Annual Growth Rate (CAGR) rate varies by industry and investment type, but generally, a CAGR of 8-10% or higher is considered strong. It’s important to assess CAGR in the context of specific investments and market conditions for a more accurate evaluation.

Related Articles

2+ Ways to Calculate Monthly Growth Rate in Excel

Rate this post