Excel CAGR formula

Which function to use to equate to an Excel CAGR formula as there is no in build compound annual growth rate function.

• 2:01
• Skill Level: 4
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Excel CAGR formula

In this example we have the sales figures for a five year period, and what we want to know is what constant growth rate, results in the sales figure going from about 8700 to 18700. We would like to us the Excel CAGR formula.

We can use the RATE function to find this out, so

• if I click in a cell,
• activate the Function Wizard
• and find the RATE function,
• I now have to fill in this area, the most important section is the number of periods, even though they say 5 year period, we are interested in the end of period 1 to the end of period of 5, which is actually a period of 1,2,3,4, so the number of periods is 4,
• the regular payment there is none,
• the present value we can say is this value,
• the future value will be negative, the last value we have,
• and the type we can omit it or put a 0 in,
• and when we say ok it gives us a constant growth rate of 20.98%

It is always useful to prove your calculations, so what we want to see is whether our interest rate grows steadily to the end amount, so what we can say is

• this cell must equal the start value,
• and the next door cell must be equal to the start value multiplied by 1+ the interest rate we determined,
• we just make it absolute
• close it
• enter,
• and now when we copy this across, well see that although the numbers differ during the years, this percentage grows to the correct amount