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 ValueOr,
=(Ending Value/Starting Value)-1Either 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 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.
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)-1Usage 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
![How to Calculate Profitability Index in Excel [Free Template]](https://excelgraduate.com/wp-content/uploads/2023/04/8-Formula-to-calculate-in-PI-in-Excel.png)
![Calculate Dividend Growth Rate in Excel [2+ Formulas]](https://excelgraduate.com/wp-content/uploads/2022/06/Calculate-Dividend-Growth-Rate-in-Excel-3.png)
