×

How To Calculate A CAGR Formula In Excel (Correctly)

By Chris Newman •  Updated: 06/22/22 •  5 min read
How to determine average growth in Excel using a CAGR (Compound Annual Growth Rate) formula.

Interactive Excel CAGR Calculator Tool

If you are looking for a way to quickly calculate a CAGR for a PowerPoint slide or quick conversation callout, below is an interactive spreadsheet you can use to input your data to quickly determine the CAGR %.

You technically only need to input the first period and last period values into the below calculator, however, for a sanity check, you are more than welcome to populate the periods in between (you'll still get the same result).

Unfortunately, if you need to clear a cell, you need to double-click into it and use your backspace key (or select the numbers with your cursor and delete them).

What Is A CAGR Formula & What Does CAGR Mean?

CAGR (Compound Annual Growth Rate) is a common metric used in Investing/Financial Analysis to calculate an average growth rate. This “smoothing” removes the volatility that might be experienced over time to provide a more normalized/realistic growth rate.

Below is a common chart summary I’ve had to produce many times in Excel. This particular chart provides the company’s sales growth from 2020 to 2024 projections using the CAGR calculation.

You can see, that even though the company has had ups and downs in its sales over the years, they are overall growing at roughly a ~4% increase.

Excel sample chart of CAGR (Compound Annual Growth Rate).

Let’s work through how the CAGR percentage was produced and how you can easily build a chart like the above to show your CAGR metrics.


How To Calculate A CAGR

CAGR Formula Inputs

The CAGR formula requires 3 pieces of data in order to perform the calculation:

  • First/Starting Value
  • Last/Ending Value
  • Number of Periods (Growth Intervals)

CAGR Formula

Below is the formula for calculating the CAGR percentage:

CAGR (Compound Annual Growth Rate) Formula

Determining The Number of Periods In The CAGR Formula

This particular input gave me lots of confusion in my early years as a financial analyst. Largely, because there are some bad/poorly phrased articles out there on the internet that don’t explain the definition of Period.

I personally like to call these “Growth Periods” and it essentially equates to the number of data points you have minus 1. Alternatively, you can ask yourself how many period-over-period growth rates can you calculate within your period range.

Showing how to determine period count for CAGR in Excel.

In the above figure, we are working with 5 years of data, however, there are really only 4 periods of growth if you count it out manually. For this reason, your Period input needs to equal 4, NOT five.

CAGR Formula In Excel Spreadsheets

With the example data, the below screenshot shows the formula you could build in Excel to calculate the CAGR. Don’t worry, I’m going to show you a more simplistic way in the next section.

CAGR Formula example using Excel

Notice in the Excel formula above, for the Number of Periods input, I am counting the total number of years the data spans and subtracting 1 to get my final value of 4 periods.


CAGR Function In Excel

While Excel doesn’t have a function specifically called CAGR() in Excel, it does have a function we can utilize to calculate the CAGR metric.

We can utilize the Excel Function RRI() to perform our calculation very easily. The RRI function (which stands for Rate of Return on Investment) utilizes the same 3 pieces of data used in the CAGR Calculation. The function just calls them by different names.

  1. Nper - Number of Periods
  2. PV - Present/Current Value (First Period)
  3. FV - Future Value (Last Period)

Below is an image showing how you can use the RRI Function to calculate the CAGR %.

Example of Excel Function RRI (Rate of Return on Investment) to calculate the CAGR %

CAGR Number Format Rule In Excel

If you are creating a CAGR chart like the one I built for this article, you’ll likely be linking your CAGR percentage to a textbox within your chart object.

I like to append the CAGR label to the end of my percentage, so my audience knows what the percentage represents. You can incorporate a Custom Number Format rule to add the CAGR label in the cell without changing the value of the result.

Here is the number format rule I like to use: 0.0% "CAGR"; (0.0%) "CAGR"; 0.0% "CAGR"

If you are unsure how to add this formatting rule to your CAGR value, you can check out my introductory article on how to add Custom Number formats.

Excel example file with CAGR custom number formatting.

Download Example Chart Excel Spreadsheet File

If you would like to get a copy of the Excel file I used throughout this article (including the chart below), feel free to directly download the file by clicking the download button below.

Excel sample chart of CAGR (Compound Annual Growth Rate).

I Hope This Helped!

Hopefully, I was able to explain how you can calculate a CAGR in Excel. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.