FORMULAS & FUNCTIONS
CAGR Formula in Excel (RRI, Months and Negative Values)
CAGR, the compound annual growth rate, is the steady yearly rate that takes a value from where it started to where it ended. It is the fair way to say how fast revenue, customers or an investment grew over several years. This guide shows the CAGR formula in Excel, the RRI and POWER versions, the off-by-one mistake most people make, why the average growth rate is wrong, how to annualize growth over months or between dates, and what to do with negative values, with a free calculator.
The CAGR formula
=(end/start)^(1/years)-1- end / start: the total growth as a multiple (685,000 / 420,000 = 1.63).
- ^(1/years): the yearly root of that multiple, the rate that compounds to it.
- -1: turns the multiple into a percentage. Format the cell as a percentage.
Step 1: Start with the values and the number of years
The CAGR sheet lists six years of revenue, 2021 to 2026, with year-over-year growth in column C (=B5/B4-1). The calculation needs three cells:
| Cell | Formula | Value |
|---|---|---|
| F4 Start value | =B4 | 420,000.00 |
| F5 End value | =B9 | 685,000.00 |
| F6 Years | =A9-A4 | 5 |
Years is the number of periods between the first and last value: 2026 – 2021 = 5. Six yearly values span five years of growth. Getting this count right is the most important part of the formula.
Step 2: The CAGR formula in Excel
=(F5/F4)^(1/F6)-1The result is 10.28%. Revenue did not grow 10.28% every year: it rose 10.83%, then fell 3.72% in 2023, then grew 18.65%, 13.28% and 13.71%. CAGR is the single constant rate that would have produced the same start and end. It smooths the path, which is why it is good for comparing periods or businesses and useless for spotting a bad year.
For the change between just two years, use percentage change instead; see percentage change in Excel with negative numbers.
Step 3: RRI, POWER and the off-by-one mistake
Excel has a function for CAGR: RRI returns the equivalent interest rate for growth over a number of periods. The POWER function is another way to write the ^:
=RRI(F6,F4,F5)=POWER(F5/F4,1/F6)-1Both return 10.28%. Note the order of RRI’s arguments: number of periods, start value, end value. RRI needs Excel 2013 or later; the ^ version works everywhere.
Step 4: Prove it, and why not the average growth rate
Check a CAGR by growing the start value at that rate for the same number of years:
=F4*(1+F7)^F6It returns exactly 685,000.00: Ties. Now try the same with the simple average of the five yearly growth rates, =AVERAGE(C5:C9) = 10.55%. Growing 420,000.00 at 10.55% for 5 years gives 693,537.00, overstating the real end value by 8,537.00. The arithmetic average ignores compounding and the effect of the down year, so it always overstates (or at best equals) the real growth.
The bottom of the sheet projects revenue forward at the CAGR: =B9*(1+F7) for 2027 (755,403.21) and =B9*(1+F7)^2 for 2028 (833,042.36). Treat it as a trend line, not a forecast, and compare it with your plan in an actual vs target combo chart.
Step 5: CAGR over months or between dates
For a period that is not a whole number of years, scale the exponent. Monthly recurring revenue grew from 12,400.00 on Jan 31, 2025 to 18,900.00 on Oct 31, 2026, 21 months:
=(C7/C6)^(12/C8)-1That is a 27.23% annualized growth rate. The monthly rate, =(C7/C6)^(1/C8)-1, is 2.03%, and compounding it 12 times, =(1+C9)^12-1, gives the same 27.23%. The number of months comes from =DATEDIF(C4,C5,"M") (see DATEDIF in Excel). With exact dates, use fractional years from YEARFRAC instead: =(C7/C6)^(1/YEARFRAC(C4,C5,1))-1 gives 27.27%, slightly different because it counts days rather than whole months.
Step 6: Negative or zero values
CAGR only makes sense when both the start and end values are positive. With a loss in either year, the formula returns an error or a number that means nothing:
| Case | Start | End | Plain formula (3 years) |
|---|---|---|---|
| Loss turning into a profit | (15,000.00) | 48,000.00 | -247.36% |
| Smaller loss | (20,000.00) | (5,000.00) | -37.00% |
| Start value zero | – | 12,000.00 | error (division by zero) |
The smaller loss is an improvement, yet the formula reports a 37.00% decline. Guard the formula so it returns n/a instead:
=IF(OR(B17<=0,C17<=0),"n/a",(C17/B17)^(1/$B$21)-1)
For profit that swings between loss and gain, report the change in amount, or explain the movement with a waterfall chart. And if money was added or withdrawn along the way (new investment, owner drawings), CAGR on the start and end values is misleading: use IRR or XIRR on the cash flows instead. To go the other way, from a rate to a future value, see the compound interest formula.
Common CAGR mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Years = number of values | CAGR too low (8.49% instead of 10.28%) | Use last year – first year |
| Average of yearly growth rates | Growth overstated (10.55%) | Use the CAGR formula and check it |
| Missing the -1 | Shows 110.28% instead of 10.28% | End the formula with -1 |
| Rate cell formatted as a number | Shows 0.10 | Format as a percentage |
| Partial years treated as whole years | Growth over 21 months reported as 2 years | Use 12 / months or YEARFRAC |
| Start value is a loss | Error or a misleading rate | Return n/a with IF |
When you compare several products or locations, put the start value, end value and years for each in its own row and copy the formula down, with the years in a locked cell if every row covers the same period. A CAGR column sorted from highest to lowest shows at once which part of the business is growing fastest.
Frequently asked questions
What is the formula for CAGR in Excel?
=(end value/start value)^(1/years)-1, for example =(B9/B4)^(1/5)-1. =RRI(years,start,end) gives the same result.
How many years do I use for CAGR?
The number of periods between the first and last value: the last year minus the first year. Six yearly values cover five years.
How do I calculate CAGR for months?
=(end/start)^(12/months)-1 annualizes growth over any number of months.
Can CAGR be calculated with negative numbers?
No. With a zero or negative start or end value, the result is an error or meaningless. Return n/a with IF and report the change in amount instead.
What is the difference between CAGR and average growth rate?
The average of yearly growth rates ignores compounding and overstates growth when results vary. CAGR is the constant rate that actually connects the start and end values.
Final checklist
- Start and end values are both positive.
- Years = last year – first year, not the number of values.
- Periods in months or quarters use 12 / months or 4 / quarters.
- The result is formatted as a percentage.
- Start x (1 + CAGR) ^ years returns the end value.
- With money added or withdrawn, use IRR or XIRR instead.
Track your growth over time
Browse the ready-to-use Excel templates for business reporting, visit the OneXcel Tutorial Hub for step-by-step guides, or turn your growth KPIs into a dashboard with the OneXcel Dashboard Builder.