How to calculate compound annual growth rate in Excel three ways (the power formula, RRI and POWER), worked on six years of revenue. Includes the year-over-year sales growth formula, why the average of yearly growth rates overstates growth, the check that proves the CAGR, and how to handle partial years and monthly data.
The CAGR calculation in Excel is one cell: =(B7/B2)^(1/5)-1, where B2 is the start value, B7 the end value and 5 the number of years between them. =RRI(5,B2,B7) returns the same rate. On six years of revenue below, both give 8.0%, while the average of the yearly growth rates says 9.3%, and the difference is not rounding.
CAGR = (End value / Start value)^(1 / number of periods) − 1
CAGR is the one constant yearly rate that, compounded, turns the start value into the end value. It ignores the path in between: a series that rose steadily and one that swung up and down have the same CAGR if they start and end at the same values. That makes it a summary of where the series went, not a description of how, and it is why a CAGR should always be quoted with its window: "8.0% a year, 2021 to 2026."
The number of periods is the number of years between the two values, not the number of values. Six year-end figures span five years of growth.
A1 and B1 are headers. A2:A7 hold the years 2021 to 2026 and B2:B7 the revenue, so A7 − A2 = 5.
| A | B | C | |
|---|---|---|---|
| 1 | Year | Revenue (USD) | Year-over-year growth |
| 2 | 2021 | 3,200,000 | |
| 3 | 2022 | 4,100,000 | +28.1% |
| 4 | 2023 | 3,500,000 | −14.6% |
| 5 | 2024 | 4,300,000 | +22.9% |
| 6 | 2025 | 4,050,000 | −5.8% |
| 7 | 2026 | 4,700,000 | +16.0% |
Revenue grew from $3,200,000 to $4,700,000, with two down years on the way.
The power formula, with the period count taken from the years so it updates if the range changes:
=(B7/B2)^(1/(A7-A2))-1
RRI, Excel's built-in rate function. Its syntax is RRI(nper, pv, fv): the number of periods, the start value and the end value, in that order:
=RRI(5,B2,B7)
POWER, the same arithmetic written as a function:
=POWER(B7/B2,1/5)-1
All three return 0.0799, which is 7.99% or 8.0% formatted to one decimal. The arithmetic: 4,700,000 / 3,200,000 = 1.46875, and 1.46875^0.2 = 1.0799.
The sales growth formula for one year is:
Growth = (This year − Last year) / Last year
In C3, filled down to C7:
=B3/B2-1
That is the column in the table: +28.1%, −14.6%, +22.9%, −5.8%, +16.0%. Year-over-year growth answers "how did this year do against last"; CAGR answers "what steady rate would have got us from 2021 to 2026". For a percentage change between any two figures rather than consecutive years, the formula is the same subtraction and division.
The average of the five yearly rates is:
=AVERAGE(C3:C7)
which returns 9.32%. Compound 2021 revenue at that rate, unrounded (9.3167%), for five years and you get 3,200,000 × 1.093167^5 = 4,995,538. The real 2026 figure is 4,700,000, so the average overstates the end value by $295,538.
The reason is that a fall and a rise of the same percentage do not cancel. A 14.6% fall needs a 17.1% rise to recover, but the average treats −14.6% and +14.6% as netting to zero. The more the yearly rates swing, the further the average sits above the CAGR.
The extreme case makes it plain. Revenue of $1,000,000 falls to $500,000 and then returns to $1,000,000. The yearly rates are −50% and +100%, so their average is +25% a year. The company ended where it started, and its CAGR is 0%.
The check that proves the CAGR. Put the CAGR in D2 and compound the start value by it:
=B2*(1+D2)^(A7-A2)
It returns 4,700,000, exactly B7. The average fails the same test. A second check: the CAGR is the geometric mean of the growth factors, so =GEOMEAN(1+C3:C7)-1 also returns 7.99% (in Excel 2019 or earlier, enter it with Ctrl+Shift+Enter).
When the start and end are dates rather than whole years, use YEARFRAC for the period count:
=(End/Start)^(1/YEARFRAC(StartDate,EndDate,1))-1
The third argument matters. YEARFRAC's default basis is the US (NASD) 30/360 day count; basis 1 uses actual days, which is what a growth rate between two reporting dates should use.
With monthly data, count months and annualize in one step:
=(End/Start)^(12/Months)-1
Revenue that went from $300,000 a month to $390,000 a month over 36 months grew at (1.3)^(12/36) − 1 = 9.1% a year. A compound monthly rate converts the same way: 0.65% a month is 1.0065^12 − 1 = 8.1% a year, not 0.65 × 12 = 7.8%. Take monthly start and end points from the same month of the year, or remove seasonality first, or the rate measures the season rather than the growth.
=(B7/B2)^(1/6)-1 gives 6.6%, understating the rate.A company CAGR of 8.0% says nothing about who grew. It can be every customer growing 8%, or a handful growing 30% while the rest shrink, and those are different businesses. For growth among existing customers, net revenue retention in Excel splits expansion from churn; for projecting a rate forward, see run rate.
title: "CAGR in Excel: the formula, RRI, and why it differs from average growth" description: "How to calculate compound annual growth rate in Excel three ways (the power formula, RRI and POWER), worked on six years of revenue. Includes the year-over-year sales growth formula, why the average of yearly growth rates overstates growth, the check that proves the CAGR, and how to handle partial years and monthly data." seoTitle: "CAGR calculation in Excel: formula and growth examples" metaDescription: "CAGR = (end value / start value)^(1/years) - 1. In Excel use =(B7/B2)^(1/5)-1 or =RRI(5,B2,B7). Worked on six years of revenue, with year-over-year growth." date: 2026-09-30 category: guides solution: revenue-driver-analysis keywords: ["cagr calculation excel", "growth rate formula excel", "compound annual growth rate excel", "sales growth formula", "rri function excel", "cagr formula", "average annual growth rate excel"] answer: "CAGR, compound annual growth rate, is the constant yearly rate that takes a start value to an end value: (end / start)^(1 / years) - 1. In Excel, with 2021 revenue in B2 and 2026 in B7, use =(B7/B2)^(1/5)-1 or =RRI(5,B2,B7). Years is the number of periods between the values, not the number of values. Year-over-year growth is (this year - last year) / last year." faq:
The CAGR calculation in Excel is one cell: =(B7/B2)^(1/5)-1, where B2 is the start value, B7 the end value and 5 the number of years between them. =RRI(5,B2,B7) returns the same rate. On six years of revenue below, both give 8.0%, while the average of the yearly growth rates says 9.3%, and the difference is not rounding.
CAGR = (End value / Start value)^(1 / number of periods) − 1
CAGR is the one constant yearly rate that, compounded, turns the start value into the end value. It ignores the path in between: a series that rose steadily and one that swung up and down have the same CAGR if they start and end at the same values. That makes it a summary of where the series went, not a description of how, and it is why a CAGR should always be quoted with its window: "8.0% a year, 2021 to 2026."
The number of periods is the number of years between the two values, not the number of values. Six year-end figures span five years of growth.
A1 and B1 are headers. A2:A7 hold the years 2021 to 2026 and B2:B7 the revenue, so A7 − A2 = 5.
| A | B | C | |
|---|---|---|---|
| 1 | Year | Revenue (USD) | Year-over-year growth |
| 2 | 2021 | 3,200,000 | |
| 3 | 2022 | 4,100,000 | +28.1% |
| 4 | 2023 | 3,500,000 | −14.6% |
| 5 | 2024 | 4,300,000 | +22.9% |
| 6 | 2025 | 4,050,000 | −5.8% |
| 7 | 2026 | 4,700,000 | +16.0% |
Revenue grew from $3,200,000 to $4,700,000, with two down years on the way.
The power formula, with the period count taken from the years so it updates if the range changes:
=(B7/B2)^(1/(A7-A2))-1
RRI, Excel's built-in rate function. Its syntax is RRI(nper, pv, fv): the number of periods, the start value and the end value, in that order:
=RRI(5,B2,B7)
POWER, the same arithmetic written as a function:
=POWER(B7/B2,1/5)-1
All three return 0.0799, which is 7.99% or 8.0% formatted to one decimal. The arithmetic: 4,700,000 / 3,200,000 = 1.46875, and 1.46875^0.2 = 1.0799.
The sales growth formula for one year is:
Growth = (This year − Last year) / Last year
In C3, filled down to C7:
=B3/B2-1
That is the column in the table: +28.1%, −14.6%, +22.9%, −5.8%, +16.0%. Year-over-year growth answers "how did this year do against last"; CAGR answers "what steady rate would have got us from 2021 to 2026". For a percentage change between any two figures rather than consecutive years, the formula is the same subtraction and division.
The average of the five yearly rates is:
=AVERAGE(C3:C7)
which returns 9.32%. Compound 2021 revenue at that rate, unrounded (9.3167%), for five years and you get 3,200,000 × 1.093167^5 = 4,995,538. The real 2026 figure is 4,700,000, so the average overstates the end value by $295,538.
The reason is that a fall and a rise of the same percentage do not cancel. A 14.6% fall needs a 17.1% rise to recover, but the average treats −14.6% and +14.6% as netting to zero. The more the yearly rates swing, the further the average sits above the CAGR.
The extreme case makes it plain. Revenue of $1,000,000 falls to $500,000 and then returns to $1,000,000. The yearly rates are −50% and +100%, so their average is +25% a year. The company ended where it started, and its CAGR is 0%.
The check that proves the CAGR. Put the CAGR in D2 and compound the start value by it:
=B2*(1+D2)^(A7-A2)
It returns 4,700,000, exactly B7. The average fails the same test. A second check: the CAGR is the geometric mean of the growth factors, so =GEOMEAN(1+C3:C7)-1 also returns 7.99% (in Excel 2019 or earlier, enter it with Ctrl+Shift+Enter).
When the start and end are dates rather than whole years, use YEARFRAC for the period count:
=(End/Start)^(1/YEARFRAC(StartDate,EndDate,1))-1
The third argument matters. YEARFRAC's default basis is the US (NASD) 30/360 day count; basis 1 uses actual days, which is what a growth rate between two reporting dates should use.
With monthly data, count months and annualize in one step:
=(End/Start)^(12/Months)-1
Revenue that went from $300,000 a month to $390,000 a month over 36 months grew at (1.3)^(12/36) − 1 = 9.1% a year. A compound monthly rate converts the same way: 0.65% a month is 1.0065^12 − 1 = 8.1% a year, not 0.65 × 12 = 7.8%. Take monthly start and end points from the same month of the year, or remove seasonality first, or the rate measures the season rather than the growth.
=(B7/B2)^(1/6)-1 gives 6.6%, understating the rate.A company CAGR of 8.0% says nothing about who grew. It can be every customer growing 8%, or a handful growing 30% while the rest shrink, and those are different businesses. For growth among existing customers, net revenue retention in Excel splits expansion from churn; for projecting a rate forward, see run rate.
Covirage computes growth per customer, product and region from the uploaded ledger with deterministic tools and shows which ones account for the company rate; the external AI model writes the explanation and never does the arithmetic. See revenue driver analysis. To carry the rate forward, see sales projection and the FORECAST function in Excel.
=(end/start)^(1/periods)-1, for example =(B7/B2)^(1/5)-1 for 2021 to 2026. The RRI function gives the same result: =RRI(5,B2,B7). Format the result as a percentage; the exponent is the number of years between the two values.
Average annual growth is the arithmetic mean of each year's rate. CAGR is the single compound rate that joins the start and end values. When growth is uneven, the average is higher than CAGR and overstates what compounding actually produced.
Sales growth = (this period's sales - last period's sales) / last period's sales. In Excel with last year in B2 and this year in B3, use =B3/B2-1 and format as a percentage.
Not meaningfully. The formula takes a root of a negative ratio, which returns an error or a misleading figure. For a series that starts at a loss or zero, report absolute change or growth from the first positive year.