CAGR Formula in Excel: Compound Annual Growth Rate
By ExcelReady TeamUpdated 6 min read
The short answer
To calculate CAGR in Excel, divide the ending value by the starting value, raise the result to the power of one over the number of years, and subtract 1: =(B6/B2)^(1/4)-1. Growth from 50,000 to 80,000 over four years gives 12.47%. The number of years is the number of periods between the values, not the number of rows.
Key takeaways
- CAGR = (End / Start)^(1 / Years) - 1, formatted as a percentage.
- Count the gaps between values, not the values: 2020 to 2024 is 4 years.
- =RRI(Years, Start, End) and =POWER(End/Start, 1/Years)-1 give the same result.
- CAGR is the steady yearly rate that links start to end. It is not the average of the yearly changes.
- CAGR fails with a zero or negative starting value, so check your data first.
On this page+
The CAGR formula in Excel is =(End/Start)^(1/Years)-1. CAGR, short for compound annual growth rate, is the steady yearly rate that would grow your starting value into your ending value. It smooths out good and bad years into one number you can compare. This guide shows the formula on a revenue table, the built-in RRI shortcut, and the two mistakes that give wrong answers.
What is CAGR?#
CAGR is the single growth rate that, applied every year, gets you from the first value to the last. If revenue went from 50,000 to 80,000 in four years, CAGR tells you the constant rate that does the same job: about 12.47% a year.
It is used for revenue, customers, investments and anything else that compounds. "Compound" means each year's growth is applied to the new, larger total, not to the original starting value.
The examples use this table in A1:B6:
| A | B | |
|---|---|---|
| 1 | Year | Revenue |
| 2 | 2020 | 50,000 |
| 3 | 2021 | 54,000 |
| 4 | 2022 | 61,500 |
| 5 | 2023 | 70,200 |
| 6 | 2024 | 80,000 |
How to calculate CAGR in Excel#
Divide the end value by the start value, raise it to one over the number of years, then subtract 1.
=(B6/B2)^(1/4)-1| Part | What it does | Value |
|---|---|---|
B6/B2 | Total growth as a ratio | 1.6 |
^(1/4) | Takes the 4th root, one step per year | 1.1247 |
-1 | Turns the ratio into a rate | 0.1247 |
Format the cell as a percentage (Ctrl+Shift+% on Windows, ⌃⇧% on Mac) to see 12.47%. The ^ symbol means "to the power of". You type it with Shift+6 on most keyboards.
Every bracket counts. Excel works out powers before division, so =(B6/B2)^1/4-1 raises 1.6 to the power of 1, then divides by 4. That returns -0.6, which is nonsense.
How to count the number of years#
Use the number of gaps between the first and last value, not the number of values. 2020 to 2024 has five values but only four years of growth.
To avoid counting by hand, let Excel subtract the years:
=(B6/B2)^(1/(A6-A2))-1A6-A2 is 2024-2020, which is 4. Now the formula still works if you add a 2025 row and point it at that row instead.
If you only have a list of values without years, =ROWS(B2:B6)-1 also gives 4.
Using RRI or POWER instead#
Excel has two other ways to write the same calculation. Both return 12.47% on this data.
- RRI (Excel 2013 and later):
=RRI(4,B2,B6). The arguments are the number of periods, the start value and the end value. It is the easiest to read. - POWER:
=POWER(B6/B2,1/4)-1. POWER(number, power) does the same as the^operator.
The ^ version works in every version of Excel and in other spreadsheet apps, so it's the safest choice for shared files and job tests.
Why CAGR is not the average growth rate#
The average of the yearly percent changes is not the same as CAGR, and with uneven growth it can be badly misleading. Look at an investment that goes from 100 to 150 and back to 100:
| Year | Value | Yearly change |
|---|---|---|
| 0 | 100 | |
| 1 | 150 | +50% |
| 2 | 100 | -33.3% |
The average of +50% and -33.3% is +8.3%. But you ended where you started, so the real growth is zero. CAGR gets this right: =(100/100)^(1/2)-1 is 0%.
That's why analysts quote CAGR for multi-year growth. To work out each single year's change, use the percent change formula, and practise it in the lesson on percentages and % change.
How to check a CAGR result#
Grow the starting value by the CAGR for the right number of years. You should land on the ending value.
=B2*(1+D2)^4If D2 holds the CAGR, this returns 80,000. It's a quick way to prove your answer in an interview task. The same "multiply by 1 plus the rate" idea is explained in how to increase a number by a percentage.
How to calculate CAGR between two dates#
When the start and end don't fall exactly a whole number of years apart, let YEARFRAC count the years for you. YEARFRAC returns the number of years between two dates, including the fraction.
Say an investment was worth 120,000 on 15 January 2021 (date in A2, value in B2) and 150,000 on 15 July 2024 (date in A3, value in B3):
=(B3/B2)^(1/YEARFRAC(A2,A3))-1YEARFRAC returns 3.5 here, so the formula returns about 6.58% a year. Using 3 or 4 years by hand would give a noticeably wrong rate. YEARFRAC works in every version of Excel, and its optional third argument lets you choose how days are counted; the default works well for most growth reports.
Common CAGR mistakes and errors#
Watch for these when your result looks wrong:
- Wrong number of years. Using 5 instead of 4 for 2020 to 2024 gives 9.86%, which is too low.
- Missing brackets around 1/Years. Write
^(1/4), not^1/4. - Start and end swapped.
=(B2/B6)^(1/4)-1gives a negative rate for growing data. - Zero starting value. Dividing by zero returns #DIV/0!. CAGR has no meaning when you start from nothing.
- Negative values. A negative ratio raised to a fractional power returns #NUM!. CAGR only works when both values have the same sign, usually both positive.
For months or quarters, change the power. Monthly data over 30 months uses =(End/Start)^(12/30)-1 to give a yearly rate. Percentages also work the other way round: to find how much one part contributes to a total, see percentage of total, and for the basics of every percentage formula, see the Excel percentage formula.
Practise it#
CAGR combines three skills that tests love: brackets, powers and percentages. Practise percentages and % change in a live spreadsheet: you type the formula and it checks it instantly. Then try the exercise on this page, which asks you to count the years with a formula instead of by hand.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Revenue grew from 2020 to 2024. Click D2 and calculate the CAGR from B2 (start) to B6 (end), using the years in A2 and A6 to count the periods.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Revenue | ||||
| 2 | CAGR | ||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 |
Tip: End divided by start, to the power of 1 over (A6-A2), minus 1. Mind the brackets.
Practise this in a lesson
Frequently asked questions
What is the CAGR formula in Excel?
Use =(End/Start)^(1/Years)-1, for example =(B6/B2)^(1/4)-1. Format the cell as a percentage. Excel 2013 and later also have =RRI(Years, Start, End), which returns the same rate.
How many years do I use in CAGR?
Use the number of periods between the first and last value. From 2020 to 2024 there are five values but four years of growth, so the number of years is 4.
What is the difference between CAGR and average annual growth?
Average growth adds the yearly percent changes and divides by the count. CAGR is the single steady rate that turns the start value into the end value. When growth is uneven, the average overstates it.
Can CAGR be negative?
Yes. If the ending value is smaller than the starting value, CAGR is negative. A fall from 100 to 81 over two years gives -10% per year.
How do I calculate CAGR for months instead of years?
Raise the ratio to 12 divided by the number of months: =(End/Start)^(12/Months)-1. Over 30 months, use 12/30.