How Do I Calculate CAGR in Excel?

Written by josh victor | 13/05/2017
How Do I Calculate CAGR in Excel?
CAGR can be found in Excel by lining up the numbers in a row and entering a simple calculation. (Stockbyte/Stockbyte/Getty Images)

Compound Annual Growth Rate (CAGR) is used to determine the average yearly monetary change. It is most often used in finance and business to determine the average change over time of revenue, profit or other business metric. Finding the CAGR in Excel can be done using a simple formula.

Lay out the list of your data on a line either vertically or horizontally. Do not skip any spaces because the spreadsheet will interpret them as zero values.

Arrange the numbers in exactly the chronological order that they occurred. If the data list is short, you can even identify each year next to the figure. For example,

$5,000 2007

$6,250 2008

$7,000 2009

$7,900 2010

Use the formula to calculate CAGR using the numbers in you data set.

(Last number/first number)^(1/n)-1

n is the difference of the years. In this case 2010 - 2007 = 3.

Plug in the numbers to find the solution.

CAGR = (7,900/5000)^(1/3)-1

CAGR = .164 = 16.4 per cent annually

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.