# 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).**

## Introduction to the Dataset

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**.

## 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)**:

To calculate **AAGR **in Excel you can use one of these formulas:

=(Ending Value - Starting Value)/Starting Value

Or,

=(Ending Value/Starting Value)-1

Either way is correct. I will use the first formula to show you the method.

**Usage Guide**

**Step_1: **Write this formula in cell **C3**:

**=(B3-B2)/B2**

**Formula Explanation**

**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 **and but the starting value would be **zero**.

**Step_2: **Press **ENTER**.

**Step_3: **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.

**Step_4: **Click on the** Home** tab.

**Step_5: **From the **Number **group, select the ‘**%**’ option.

**Final Result >**

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. Now 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:

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.

I am going to calculate the **AAGR **for 1990-1995.

**Usage Guide**

**Step_1: **Type this formula in an empty cell:

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

I used cell **C7 **as it is corresponding to cell **A7 **of the year 1995.

**Formula Explanation**

**1/5** is the **1/T** in this formula. The Ending Value is **B7**. The Starting Value is **B2**.

**Step_2: **Press **ENTER**.

**Final Result >**

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 **AAGR**s, you can also calculate the **Average Growth Rate (AGR)** from the results. We will be using the **AVERAGE function** to find out the **AGR**.

**Usage Guide **

**Step_1: **Choose an empty cell beside the data table and type this formula:

**=AVERAGE(C3:C14)**

I chose cell **D8 **because it is the middle position of **column D**.

**Step_2: **Then press **ENTER**.

**Final Result >**

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 #4: 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 you.

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

If we write the formula mathematically:

To calculate** CAGR** in Excel, use this formula:

=(Ending Value/Starting Value)^(1/Time)-1

**Usage Guide**

**Step_1:** Write this formula in an empty cell:

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

**Formula Explanation **

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

**Step_2: **Press **ENTER**.

**Step_3: **Change the decimal into a percentage using the **Home **tab >>> **Number **group >>> **% **option.

**Final Result >**

Done. You get your desired **CAGR **value.

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

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

**Usage Guide**

**Step_1: **Copy this formula in an empty cell:

**=RRI(12,B2,B14)**

**Step_2: **Press **ENTER**.

**Final Result >**

Please remember to count the time period for this technique like the previous methods. And 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!

**Related Articles**