Calculating growth rates in Excel is a valuable skill for various purposes, such as business, finance, and personal growth tracking.
By learning how to calculate growth rates, you can identify trends, analyze performance, and make informed decisions based on data.
There are two types of growth rates.
- Average Annual Growth Rate (AAGR)
- Compound Annual Growth Rate (CAGR)
If you try to calculate the above two growth rates manually, you have to do many calculation steps.
But, if you are using Excel to find growth rates, it is just a matter of seconds (you just need to know the right formulas).
In this article, we will show you how to calculate growth rate in Excel using simple formulas.
Understanding the Growth Rates
Before you start calculating the Average Annual Growth Rate (AAGR) or Cumulative Annual Growth Rate (CAGR) in Excel, it’s essential to understand the basic concepts involved.
Annual Growth Rate
The Annual Growth Rate is a simple measure that calculates the change in value over a certain period of time.
It compares the starting value of an investment, revenue, or any data point with the ending value and shows the rate of change between the two figures. With the formula:
Annual growth rate = (ending value - starting value) / starting value
This helps you to understand the growth of your investment or business on a yearly basis.
Compound Annual Growth Rate (CAGR)
The Compound Annual Growth Rate (CAGR) is a slightly more advanced concept.
It measures the average annual rate of return for an investment over a period of time, taking into account compounding interest.
CAGR is more accurate than the simple Annual Growth Rate, as it assumes regular growth over the whole period.
Average Annual Growth Rate (AAGR)
Average Annual Growth Rate (AAGR) is the arithmetic mean of individual annual growth rates calculated over multiple periods.
This is a simpler calculation compared to CAGR and is useful when you have data for multiple discrete periods, like annual sales or revenue numbers.
To calculate AAGR in Excel, you can use the AVERAGE function, and you first need to calculate the growth rate for each period before finding the average of those rates.
Also read: Calculating Year-Over-Year (YOY) Growth in Excel
Calculating Average Annual Growth Rate (AAGR) in Excel
The word itself gives us a clue as to what Average Annual Growth Rate means.
The annual growth rates are averaged. We have to calculate the growth rate for each year and then average the calculated growth rates.
The annual revenue is shown in the table below. Column B displays the revenue, whereas Column A displays the year.
Now we need to find the average annual growth rate in revenue.
We can follow the below steps to find the average annual growth rate in Excel.
- First, create a separate column to find the annual growth rate for each year.
The annual growth rate formula is given below.
Annual Growth Rate = Current year value/Previous year value-1
So, in this example, we will go to cell C3 and apply the below formula.
=(B3/B2)-1
After applying the above formula in cell C3, we can copy that cell to cells below that.
Now we have calculated the annual growth rate for each year in our table.
- Next, we have to find the average value of the calculated growth rates. To find the average in Excel, we can use the AVERAGE function.
=AVERAGE(C3:C7)
The syntax of the AVERAGE function is AVERAGE(number1, [number2], …). In this case, we select the range C3 to C7 for the AVERAGE function.
So, for our example, we calculated the Average Annual Growth Rate (AAGR), which is 15.0%.
We don’t always prefer to add extra columns for our calculations. Without adding a new column, we can get the result for the calculation shown above as well. Then we need to write the array formula shown below.
=AVERAGE(((B3:B7)/(B2:B6))-1)
If you’re using Microsoft 365, you can enter the formula and then press the “Enter” key.
Otherwise, you need to press the “Ctrl + Shift + Enter” keys to enter the array formula. So, hold down the Ctrl and Shift keys before pressing the Enter key.
Calculating Compound Annual Growth Rate (CAGR) in Excel
The Average Annual Growth Rate (AAGR) formula does not consider the compounding impact.
This is why is you calculate the AAGR and CAGR for the same dataset, you will get different values (AAGR tends to be slightly higher than CAGR).
For example, if we use the computed average annual growth rate for each year in the above example, the revenue in the sixth year is more than $100M.
This is incorrect as the AAGR value was calculated using the data in column B, so when we reverse it and try to get the revenue values for years 2 to 6 using the AAGR, we should get the same values in column B.
AAGR sometimes leads to an overestimation of the growth rate. To overcome this issue, we have to use the Compound Annual Growth Rate (CAGR).
The general Compound Annual Growth Rate (CAGR) formula is as follows.
Compound Annual Growth Rate (CAGR)= (End value/Start Value)^(1/n)-1
In the above formula, n means the number of periods. We can calculate it by subtracting the start period from the end period.
There are several methods to calculate the compound annual growth rate in Excel.
Method 1 – Using Excel Operators to Calculate Compound Annual Growth Rate in Excel
In this method, we use the Excel operators and cell references to build the above CAGR formula.
The below table shows the annual revenue details. Column B shows the revenue, and column A shows the year.
Now we need to find the compound annual growth rate in revenue.
We can apply the below formula to find the compound annual growth rate in Excel.
=(B7/B2)^(1/(A7-A2))-1
In the above formula;
- B7 is the revenue in the last year (Year 6) which is $100M.
- B2 is the revenue in the first year (Year 1) which is $ 50M.
- A7-A2 is used to find the number of periods. So, in this case, we are calculating 6-1, which is 5.
Now, if we apply the calculated compound annual growth rate for every year in the above example, the revenue of the 6th year is exactly equal to the revenue of last year ($100M).
Also read: Compound Interest Formula in Excel
Method 2 – Use Excel RATE Function
We can also use Excel functions to calculate the Compound Annual Growth Rate. The RATE function, in my opinion, is the most user-friendly for calculating CAGR.
The annual revenue figures are shown in the table below. Column B shows the revenue, and Column A shows the year.
We now have to calculate the compound annual growth rate in revenue.
In Excel, we can use the formula below to calculate the compound annual growth rate.
=RATE((A7-A2),0,-B2,B7)
The syntax of the RATE function is RATE(nper, pmt, pv, [fv], [type], [guess]).
The first argument of the function is the number of periods. To find the number of periods, we deduct the first year from the last year.
So, in this case, we deduct cell A2 from cell A7.
The next argument is payment. We have to enter zero for that.
Then, you need to enter the starting value as a negative value. So, we enter a minus sign and select cell B2 here.
Next, we have to select the end value. For that, we are selecting cell B7. We can ignore the remaining two arguments of the RATE function.
Method 3 – Use Excel POWER Function to Calculate Compound Annual Growth Rate in Excel
For calculating the compound annual growth rate in Excel, we can also use the POWER function.
The annual revenue figures are shown in the table below. Column B displays the revenue, whereas Column A displays the year.
Now we need to find the compound annual growth rate in revenue.
We can apply the below formula to find the compound annual growth rate in Excel.
=POWER((B7/B2),(1/(A7-A2)))-1
Here, we are using the POWER function to create the CAGR formula.
Compound Annual Growth Rate (CAGR)= (End value/Start Value)^(1/n)-1
The syntax of the POWER function is POWER(number, power). As the first argument, we need to enter the base number.
(End value/Start Value) is the base number for the CAGR formula.
So, we divide the last year’s revenue (cell B7) by the first year’s revenue (cell B2) in the first argument.
The next argument of the POWER function is power. As per the formula, power is (1/n).
So, we simply divide the 1 by the number of periods. To get the number of periods, we subtract the start year (cell A2) from the last year (cell A7).
Finally, we subtract 1 from the POWER function to get the compound annual growth rate.
Method 4 – Use Excel IRR Function to Calculate Compound Annual Growth Rate in Excel
You can even use the Excel IRR function to estimate the compound annual growth rate. But, in this case, you need to create a new adjusted column for values.
The below table shows the annual revenue details. Column B shows the revenue, and column A shows the year.
Now we need to find the average annual growth rate in revenue.
As the first step, we need to create a new revenue-adjusted column for the IRR calculation.
We have to create an adjusted revenue column as follows.
- Select the first year’s value as a negative value to cell C2. So, we enter =-B2 in cell C2.
- Select the last year’s value to cell 7. So, we enter =B7 in cell C7.
- Enter zeros for all values between cells C2 and C7.
Next, you have to simply enter the below formula to calculate the compound annual growth rate.
=IRR(C2:C7)
The syntax of the IRR function is IRR(values, [guess]). We have to select all the values in adjusted revenue for the IRR (Column C). So, we select cells C2 to C7 for the first argument. We can skip the next argument of the function.
If the years in our data table are given as actual dates, we can use the XIRR function to calculate the Compound Annual Growth Rate.
The syntax of the XIRR function is XIRR(values, dates, [guess]). Similar to what we did with the IRR calculation, we have to add an adjusted column. When we insert the XIRR function, we have to select adjusted revenues as the first argument and dates as the second.
You now understand how to calculate the Average Annual Growth Rate (AAGR) and Compound Annual Growth Rate (CAGR).
You can select the method that is the simplest for you from the ones we’ve covered to find the CAGR in Excel.
Other Excel articles you may also like:
