CAGR
The exponent, not the formula, is what goes wrong here: five annual figures enclose four years of growth, so a five-year table takes 1/4. The arithmetic itself is =(End/Start)^(1/Years)-1, or =RRI(4, B2, B6) if you would rather Excel held it. What decides the rest of this page is which question you are asking: CAGR measures what a series already did between two endpoints and smooths away everything in between, compound interest projects a balance forward from a rate you supply, and percentage change covers a single step from one value to the next. Format the answer as Percentage rather than multiplying it by 100.
The formula
=(B6/B2)^(1/4)-1 start value in B2, end value in B6, four years between them
=(B6/B2)^(1/(A6-A2))-1 the period count taken from the year column, so it cannot drift
=RRI(4, B2, B6) the built-in equivalent: periods, present value, future value (Excel 2013+)
=RRI(COUNT(B2:B6)-1, B2, B6) the values-minus-one rule written into the formula rather than remembered
=(B6/B2)^(365/(A6-A2))-1 real dates in A2 and A6: annualise on the day count instead of whole years
=B2*(1+$C$1)^4 the same relationship run forwards, projecting from a known rateA worked example
A2:A6 holds the years 2020 to 2024 and B2:B6 the revenue for each of them: 40,000, 46,000, 44,500, 58,000 and 61,000.
=(B6/B2)^(1/4)-1
0.1113, which reads as 11.13% once the cell is formatted as Percentage. Five figures enclose four growth periods — 2020 to 2021, 2021 to 2022, 2022 to 2023, 2023 to 2024 — so the exponent is 1/4. Write 1/5 and the answer becomes 8.81%, entirely believable and never flagged. Note too that 2022 fell to 44,500: CAGR conceals that completely, which is simultaneously the point of the measure and its main limitation.
Which one do I need?
| If you want to… | Use |
|---|---|
| The average yearly rate implied by a start value and an end value | =(End/Start)^(1/Years)-1, where Years is the gap between the endpoints and not the count of figures |
| You would rather not write the exponent by hand | =RRI(nper, pv, fv) returns exactly the same number; it arrived in Excel 2013 |
| A year column sits beside the values | Derive the period count from it — =(B6/B2)^(1/(A6-A2))-1 — so adding a year to the table cannot leave a stale exponent behind |
| The endpoints are real dates rather than whole years | Annualise on days: =(B6/B2)^(365/(A6-A2))-1, which handles a 17-month span as readily as a 5-year one |
| Growth from one period to the next, not an average across many | That is percentage change, =(new-old)/old, and it answers a different question |
| The starting value is zero or negative | CAGR does not exist there — a ratio to zero has no root and a sign change makes the result meaningless. Quote the absolute change instead |
| Growing a balance forward at a known rate | That is compounding rather than measurement: =P*(1+r/n)^(n*t), or FV |
| A stream of cash flows in and out, not two endpoints | IRR is the growth-rate analogue for an uneven series, and NPV values it at a rate you choose |
| The cell shows 0.1113 instead of 11.13% | Apply the Percentage format; multiplying by 100 as well is what turns it into 1113% |
| #NUM! comes back from the formula | A negative base under a fractional exponent. Check the start value before suspecting the syntax |
Frequently asked questions
What is the CAGR formula in Excel?
There is no CAGR function; you type =(End/Start)^(1/Years)-1 into a cell. With the first value in B2, the last in B6 and four years between them that is =(B6/B2)^(1/4)-1. RRI is the built-in shorthand for the identical calculation, so =RRI(4,B2,B6) returns the same rate to the last decimal.
Do five years of data mean 5 or 4 in the formula?
Four. The exponent counts the growth periods between the endpoints, and five annual figures have four gaps between them. Counting the values instead understates the rate every time: on revenue rising from 40,000 to 61,000 across 2020 to 2024 it turns a true 11.13% into 8.81%, with no error to give it away. Deriving the number as =A6-A2 from the year column takes the judgement out of it.
What is the difference between CAGR and average annual growth?
Averaging the yearly percentage changes ignores compounding and is biased upwards — a year of +50% followed by a year of -50% averages to 0% while the money is actually down 25%. CAGR is derived from the two endpoints alone, so it always reproduces the real start-to-finish outcome. That is why it is the figure quoted in accounts and prospectuses, and why it can never be checked against the intermediate years.
Why does my growth rate return #NUM! or a rate that makes no sense?
A zero or negative starting value. The formula raises End/Start to a fractional power, a negative base under a root has no real answer, and Excel reports #NUM!. Worse, when both ends are negative the ratio comes out positive and Excel returns a perfectly formatted number that means nothing at all. A business moving from a loss to a profit has no growth rate — report the absolute change.
New guides and tools, once a month
DE + EN · double opt-in · no spam