How to Calculate Annual Growth Rate in Excel (3 Methods)

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.


Download Workbook

You can download the free practice Excel workbook from here.


Calculate the Compound and the Average Annual Growth Rate in Excel

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:

=((End Value/Start Value)^(1/Time Periods)-1

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))-1

Here,

  • C15 = End Value
  • C5 = Start Value
  • 11 = Time Period (there are 11 Date records in our dataset)
  • Press Enter.

Calculate the Compound Annual Growth Rate in Excel

You will get the calculated Compound Annual Growth Rate for your data in Excel.

Related Content: How to Calculate Monthly Growth Rate in Excel (2 Methods)


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:

=XIRR(value, date, [guess])

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.

Calculate the Compound Annual Growth Rate with the XIRR Function in Excel

You will get the calculated Compound Annual Growth Rate with the XIRR function for your data in Excel.

Related Content: How to Use the Exponential Growth Formula in Excel (2 Methods)


Similar Readings:


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:

=(End Value – Start Value)/ Start Value

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)/C5

Here,

  • C6 = End Value
  • C5 = Start Value
  • Press Enter.

Calculate the Average Annual Growth Rate in Excel

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.

Calculate the Average Annual Growth Rate (AAGR) in Excel

You will get the calculated Average Annual Growth Rate for all the data from your dataset in Excel.

Related Content: How to Forecast Growth Rate in Excel (2 Methods)


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.

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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo