How To Calculate A CAGR Formula In Excel (Correctly)
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.
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:
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.
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.
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.
- Nper – Number of Periods
- PV – Present/Current Value (First Period)
- FV – Future Value (Last Period)
Below is an image showing how you can use the RRI Function 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.
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.
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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![Which Excel Versions Have XLOOKUP Function? [Solved]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/DoIHaveXLOOKUP.png)
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.