Spreadsheet Practice

How to calculate CAGR in Excel

Formula (Excel and Google Sheets)

=(B6/B2)^(1/(A6-A2))-1

Divide the end value by the start value, raise it to 1 over the number of years and subtract 1: =(B6/B2)^(1/(A6-A2))-1.

Result on the example
11.62% in E1
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

Revenue grew by a factor of 1.552 over four years. CAGR is the steady yearly rate that gives the same growth: the fourth root of 1.552, minus 1, is 11.62%. The number of years is the gap between the first and last year, 4, not the count of rows, 5.

=(B6/B2)^(1/(A6-A2))-1

Result0.1161509397

  1. B6$1,940,000

    The value in B6.

  2. B2$1,250,000

    The value in B2.

  3. B6/B21.552

    The / operator divides.

  4. A62025

    The value in A6.

  5. A22021

    The value in A2.

  6. A6-A24

    The - operator subtracts.

  7. 1/(A6-A20.25

    The / operator divides.

  8. B6/B2)^(1/(A6-A21.11615094

    The ^ operator raises to a power.

  9. B6/B2)^(1/(A6-A2))-10.1161509397

    The - operator subtracts.

In Google Sheets

The same formula works in Google Sheets: =(B6/B2)^(1/(A6-A2))-1. We ran it in Google Sheets mode on the same data and it returns 11.62%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=RATE(A6-A2,0,-B2,B6)11.62%RATE finds the same yearly rate from the start and end values.
=(B6/B2)^(1/(ROWS(B2:B6)-1))-111.62%Counts the years from the rows, for a list without a year column.

Common mistakes

  • Using 5 years (the number of values) instead of 4 (the gaps between them) understates growth.
  • CAGR ignores the path: a big drop and recovery give the same CAGR as steady growth.
  • A negative or zero start value can't be raised to a power; CAGR doesn't apply.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate CAGR

In E1, calculate the compound annual growth rate of revenue from 2021 (row 2) to 2025 (row 6).

Questions

Is CAGR the same as the average growth rate?

No. The average of the yearly changes is higher than CAGR when growth varies. CAGR is the rate that actually reproduces the end value.

Is there a CAGR function?

RRI returns it in Excel: =RRI(A6-A2,B2,B6).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.