Cellwise Try it free

Excel guide · 3 min read

How to calculate CAGR in Excel

Quick answerCAGR (compound annual growth rate) is =(end/start)^(1/years)-1. Revenue that grew from $250,000 in 2020 to $412,000 in 2025 has a CAGR of =(B7/B2)^(1/5)-1, about 10.5% a year. Years means periods between the two values (2025 - 2020 = 5), not the number of rows (6).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1YearRevenueStart dateEnd dateStart valueEnd value
22020250000Mar 15, 2022Oct 31, 20251200017350
32021281000
42022318500
52023302000
62024365000
72025412000

The CAGR formula

Divide the ending value by the starting value to get the total growth multiple (1.648 here), take the root for the number of years with ^(1/years), and subtract 1 to turn the multiple into a rate. Format the cell as Percentage.

CAGR answers one question: what steady yearly rate would take the start value to the end value? It smooths out the dip in 2023, which is the point, but it also means it tells you nothing about how bumpy the ride was.

=(B7/B2)^(1/5)-1

Result: 0.10507420471 shows as 10.5% with Percent format

About 0.105: revenue grew at a compound 10.5% a year.

Count the years from the cells

The most common CAGR mistake is counting 6 years because there are 6 rows. From 2020 to 2025 there are 5 growth periods. Calculate the number from the year column instead of typing it, so the formula stays right when you add 2026.

POWER(number, power) is the same as the ^ operator if you find it easier to read.

=POWER(B7/B2,1/(A7-A2))-1

Result: 0.10507420471 shows as 10.5% with Percent format

Again about 10.5%, with the 5 periods worked out from the years.

RRI and RATE: built-in alternatives

Excel has a function made for this. =RRI(5,B2,B7) returns the same 10.5%. Its arguments are number of periods, start value and end value. RRI is in Excel 2013 and later.

RATE also works, but it treats money as cash flows, so the start value must be negative: =RATE(5,,-B2,B7). It is handy if you are already using PMT, PV and FV. For a quick, readable formula that works in any spreadsheet app, the ^ version is the safest choice.

CAGR between two exact dates

For an investment held from 15 March 2022 to 31 October 2025, the period is not a whole number of years. Count the days with DAYS and divide by 365 to get the years (365.25 if you want to allow for leap years). Then use them as the exponent.

In full Excel, YEARFRAC(start,end) is another way to get the fraction of years. The value grew from $12,000 to $17,350.

=(G2/F2)^(365/DAYS(E2,D2))-1

Result: 0.1068143408 shows as 10.7% with Percent format

About 0.106: 10.6% a year over roughly 3.6 years.

Why the average growth rate is not CAGR

Averaging the five year-on-year growth rates gives a higher number, because percentage swings do not cancel out: a 10% fall followed by a 10% rise leaves you below where you started. The average below divides each year by the one before, subtracts 1, and averages the five results.

Use the average growth rate to describe typical yearly changes. Use CAGR to compare investments or to project forward, because only CAGR reproduces the real ending value: =B2*(1+CAGR)^5 gives back $412,000.

=SUMPRODUCT(B3:B7/B2:B6-1)/5

Result: 0.10860460292 shows as 10.9% with Percent format

About 0.109 (10.9%), higher than the 10.5% CAGR.

Common mistakes

Using the number of rows as the years

Six yearly values span five years. Use end year minus start year, or COUNT(values)-1.

Forgetting the brackets around 1/years

=(B7/B2)^1/5-1 raises to the power 1 and then divides by 5. Write ^(1/5).

#NUM! with a negative or zero start value

CAGR is not defined when the start is zero or negative (for example a loss year), because you cannot take a root of a negative ratio. Describe the change in dollars instead, or start from a positive year.

Calling the average growth rate CAGR

The arithmetic average of yearly growth is not compound growth. If you need CAGR, use the end, the start and the number of periods only.

FAQ

What is the CAGR formula in Excel?

=(end/start)^(1/years)-1, formatted as a percentage. In Excel 2013 and later, =RRI(years,start,end) gives the same result.

How do I calculate CAGR for months?

Use the number of months divided by 12 as the years: =(end/start)^(12/months)-1. For a monthly rate instead, use =(end/start)^(1/months)-1.

Does CAGR use the number of years or the number of data points?

The number of years between the first and last value. Five years of growth have six data points, and CAGR uses 5.

How do I project future values with CAGR?

Multiply the latest value by (1+CAGR) raised to the number of years ahead: =B7*(1+rate)^3 for three years out. Remember it assumes steady growth, which real data rarely has.

Functions used

Related guides

Updated