To calculate annual growth over multiple periods, Compound Average Growth Rate (CAGR) and Annual Average Growth Rate (AAGR) are the two most common and efficient methods in Excel. In this article, you will learn how to calculate the Compound and Average Annual Growth Rate in Excel.
How to Calculate the Compound and the Average Annual Growth Rate in Excel: 3 Ways
In this section, you will learn how to calculate the Compound Annual Growth Rate (CAGR) manually and with the XIRR function and how to calculate the Average Annual Growth Rate (AAGR) in Excel.
1. Calculate the Compound Annual Growth Rate in Excel
You can calculate the growth rate from the beginning investment value to the ending investment value where the investment value will be augmenting over the given period of time with the CAGR.
In mathematical terms, there is a basic formula to calculate the Compound Annual Growth Rate.
The formula is:
We can easily apply this formula to find the Compound Annual Growth Rate for our dataset shown below.
The steps to calculate the Compound Annual Growth Rate in Excel are discussed below.
Steps:
- Pick any cell from your dataset (in our case it is cell E5) to store the CAGR.
- In that cell, write the following formula,
=(C15/C5)^(1/(11-1))-1Here,
- C15 = End Value
- C5 = Start Value
- 11 = Time Period (there are 11 Date records in our dataset)
- Press Enter.
You will get the calculated Compound Annual Growth Rate for your data in Excel. Similarly, you can also Calculate Monthly Growth Rate in Excel!
Read More: How to Calculate Revenue Growth Rate in Excel
2. Compute the Compound Annual Growth Rate with the XIRR Function in Excel
If you want to calculate the Compound Annual Growth Rate with only a formula, then with Excel’s XIRR function you can do that.
Excel’s XIRR function returns the internal rate of return for a series of investments that may or may not occur on a regular basis.
The syntax for XIRR function is:
Parameter Description
| Parameter | Required/ Optional | Description |
|---|---|---|
| value | Required | A schedule of investment flow that corresponds to a series of cash payment dates. |
| date | Required | A series of cash payment dates that corresponds to a schedule of investment flow. Dates should be entered through the DATE function or through Excel Format options or as a result of other functions or formulas. |
| [guess] | Optional | A number to guess which is close to the result of XIRR. |
Before applying the XIRR function, you have to declare the Start Value and the End Value in other cells so that you can use them later inside the formula. And that is exactly what we have done with our dataset shown below.
According to our dataset that consists of Date and Sales Value, we stored the first value, $1,015.00 from the Sales Value column (Column C) in cell F5 and the last value, $1,990.00 from the Sales Value column (Column C) in cell F6. Remember to store the End Value as a negative value. meaning, with a minus sign (-) before it.
Similarly, we stored the corresponding first date, 1-30-2001, from the Date column (Column B) in cell G5 and the last date, 1-30-2011 from the Date column (Column D) in cell G6.
Steps to calculate the Compound Annual Growth Rate with the XIRR function are given below.
Steps:
- Pick any cell from your dataset (in our case it is cell F9) to store the result of the CAGR.
- In that cell, write the following formula,
=XIRR(F5:F6, G5:G6)Here,
- F5 = Start Sales Value
- F6 = End Sales Value
- G5 = Start Date Value
- G6 = End Date Value
- Press Enter.
You will get the calculated Compound Annual Growth Rate with the XIRR function for your data in Excel.
Read More: How to Use the Exponential Growth Formula in Excel
3. Determine the Average Annual Growth Rate in Excel
Till now you have been learning how to get the Compound Annual Growth Rate for your data in Excel. But this time we will show you how to measure the Average Annual Growth Rate (AAGR) for your data in Excel.
You can predict the average annual growth rate by factoring in the existing and the upcoming value of investment regarding the time period per year in Excel with AAGR.
To calculate the Average Annual Growth Rate of every year, the mathematical formula is:
We can easily apply this formula to find the Average Annual Growth Rate for our dataset shown below.
The steps to calculate the Average Annual Growth Rate in Excel are discussed below.
Steps:
- Pick any cell from your dataset (in our case it is cell D6) to store the AAGR.
- In that cell, write the following formula,
=(C6-C5)/C5Here,
- C6 = End Value
- C5 = Start Value
- Press Enter.
You will get the calculated Average Annual Growth Rate for the specific data from your dataset in Excel.
- Now drag the Fill Handle to apply the formula to the rest of the cells in the dataset.
You will get the calculated Average Annual Growth Rate for all the data from your dataset in Excel.
Read More: How to Calculate Growth Percentage with Formula in Excel
Things to Remember
- When storing the End Value for calculating the Compound Annual Growth Rate (CAGR) with the XIRR Function, you must write the value with a minus sign (-).
- While calculating the Average Annual Growth Rate (AAGR), the first cash flow result is optional.
- If you get the result in the decimal format then you can change the format into the percentage format from the Number Format option in Excel.
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
This article explained in detail how to calculate the Compound and Average Annual Growth Rate in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
Related Articles
- How to Calculate Dividend Growth Rate in Excel
- Growth Formula in Excel with Negative Numbers
- How to Calculate Sales Growth over 3 Years in Excel
- How to Calculate Sales Growth over 5 Years in Excel
- How to Calculate Sales Growth Percentage in Excel
- How to Calculate Year over Year Growth with Formula in Excel
- Growth Over Last Year Formula in Excel
<< Go Back to Growth Formula In Excel | Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
