Formula for Compound Annual Growth Rate in Excel – A Complete Guide
Calculating Compound Annual Growth Rate (CAGR) in Excel is easy once you know the formula. This guide walks you through manual and function‑based methods, common pitfalls, and real‑world examples for accurate growth analysis.
TL;DR
Use Excel’s CAGR formula:= (Ending_Value / Beginning_Value)^(1/Years) - 1
Or, with Excel functions:=RATE(Years, , -Beginning_Value, Ending_Value)
CAGR shows the smoothed annual growth rate of an investment or metric over time.
Introduction: Why CAGR Matters
When you want to compare the growth of two investments, sales lines, or any metric over different time spans, Compound Annual Growth Rate (CAGR) is your best friend. Unlike a simple average, CAGR smooths out the bumps, revealing the steady yearly rate that would get you from your starting value to your ending value. Excel makes it easy, if you know the right formula.
The Core CAGR Formula in Excel
Manual Formula
=(Ending_Value / Beginning_Value)^(1/Years) - 1Variables
- Beginning_Value (BV) – value at the start
- Ending_Value (EV) – value at the end
- Years (n) – number of full periods
Example
Investment grows from $10,000 to $18,000 in 5 years.
=(18000 / 10000)^(1/5) - 1 → 0.124 → 12.4 %
RATE Function Method
Excel’s RATE calculates the internal rate of return for equal‑length periods. If you leave the payment (PMT) blank, it doubles as a CAGR function:
=RATE(Years, , -Beginning_Value, Ending_Value)Why the minus sign? RATE expects the outflow (investment) to be negative and the inflow (future value) to be positive.
What Is Compound Annual Growth Rate?
CAGR answers one core question:
“If my investment grew at a constant yearly rate, what rate would turn my beginning value into my ending value over X years?”
Because it compounds, CAGR accounts for the time value of money, making it more accurate than a straight‑line average when returns fluctuate.
Step‑by‑Step CAGR Formula Example
| Step | Action | Excel Entry |
|---|---|---|
| 1 | Enter Start $12,000 in B2 | 12000 |
| 2 | Enter End $21,500 in B3 | 21500 |
| 3 | Enter Years 4 in B4 | 4 |
| 4 | In B6, type manual CAGR formula | =(B3/B2)^(1/B4)-1 |
| 5 | Format B6 as Percent | → 15.69 % |
| 6 | Optionally, in B7 use RATE |
=RATE(B4,,-B2,B3) |
Handling Uneven Time Periods
If your dates aren’t exactly whole years:
- Calculate number of days between dates.
- Divide by 365 (or 365.25) to get Years.
=(End_Date - Start_Date)/365.25
Then plug that decimal year count into the CAGR formula.
Common Mistakes & Troubleshooting
| Mistake | Fix |
|---|---|
| Swapping beginning and ending values | Ensure EV > BV, otherwise CAGR is negative. |
| Forgetting the “‑1” | Without it, you get the growth factor—not the percentage rate. |
Wrong sign in RATE |
Enter BV as negative and EV as positive. |
| Not formatting as percent | Format the cell or multiply by 100 to show %. |
| Using average annual return | CAGR already accounts for compounding; simple averages do not. |
Interpreting CAGR vs. Average Annual Return
| Metric | Best For | Limitation |
|---|---|---|
| CAGR | Smooth, compounded growth comparison | Needs start & end values only |
| Average Return | Quick snapshot of period by period gains | Ignores compounding effects |
Conclusion & Key Takeaways
- Formula:
(EV / BV)^(1/n) ‑ 1 - Shortcut:
RATEfunction does the same with proper signs. - Format as percent for clarity.
- Use CAGR for apples‑to‑apples growth comparisons across investments or business metrics.
FAQ – CAGR in Excel
What is the CAGR formula in Excel?
Use =(Ending_Value / Beginning_Value)^(1/Years) - 1
or RATE: =RATE(Years, , -Beginning_Value, Ending_Value)
How do I calculate CAGR for partial years?
Compute Years with =(End_Date-Start_Date)/365.25 , then plug that value into the CAGR formula.
Why does the RATE formula need a negative beginning value?
RATE treats cash outflows (investments) as negative and inflows as positive, so beginning value must be entered as a negative number.
How can I avoid common CAGR mistakes?
Ensure Ending Value is greater than Beginning Value, include the -1 in the formula, format results as a percentage, and subtract excess cash in RATE.
Is CAGR the same as average annual return?
No. CAGR accounts for compounding; a simple average ignores the effect of year‑to‑year fluctuations.
What if my beginning value is zero or negative?
You can’t compute CAGR with a zero start; use revenue growth techniques or exclude the period.
How do I calculate CAGR for monthly data?
Count months, divide by 12 to get years, then use the same formula.
Can I calculate CAGR for negative growth?
Yes, the formula returns a negative rate when EV < BV.
Is CAGR better than IRR?
For a single cash‑in, single cash‑out scenario, they’re identical. IRR handles multiple cash flows.