CAGR (Compound Annual Growth Rate)
Measure the smoothed annual return of an investment, portfolio, or asset over any time period using Google Sheets formulas.
=(ending_value/beginning_value)^(1/years)-1 How It Works
CAGR calculates the steady annual rate that would take an investment from its starting value to its ending value. It smooths out the ups and downs of actual yearly returns into a single comparable number - useful for evaluating performance across different time periods and investments.
Syntax
=(ending_value / beginning_value) ^ (1 / years) - 1Or using the built-in RRI function:
=RRI(years, beginning_value, ending_value)Example
Portfolio Performance:
- Invested: $25,000 in 2019
- Current Value: $41,000 in 2024
- Time Period: 5 years
Formula: =(41000/25000)^(1/5)-1
Result: 10.4% CAGR
This means the portfolio grew as if it earned a steady 10.4% each year, even though actual returns varied.
CAGR for Common Scenarios
| Scenario | Start | End | Years | CAGR |
|---|---|---|---|---|
| 401(k) growth | $50,000 | $120,000 | 10 | 9.1% |
| Home appreciation | $300,000 | $420,000 | 8 | 4.3% |
| Side business | $5,000 | $18,000 | 3 | 53.3% |
| College fund | $10,000 | $22,000 | 12 | 6.8% |
| Crypto position | $2,000 | $8,500 | 2 | 106.2% |
Variations
Using POWER Function
=POWER(ending/beginning, 1/years) - 1Identical result, some people find it more readable.
Monthly Data to Annual CAGR
When you have months instead of years:
=(ending/beginning)^(12/months) - 1Display as Formatted Percentage
=TEXT((ending/beginning)^(1/years)-1, "0.00%")Returns “10.40%” instead of 0.104.
Real (Inflation-Adjusted) CAGR
=((1 + nominal_cagr) / (1 + inflation_rate)) - 1Example: 10.4% nominal, 3% inflation:
=((1.104)/(1.03))-1 = 7.2% real CAGRCAGR vs. Simple Average Return
| Year | Actual Return | Portfolio ($25,000 start) |
|---|---|---|
| 1 | +22% | $30,500 |
| 2 | -12% | $26,840 |
| 3 | +18% | $31,671 |
| 4 | +8% | $34,205 |
| 5 | +20% | $41,046 |
- Simple average: (22 - 12 + 18 + 8 + 20) / 5 = 11.2%
- CAGR: (41046/25000)^(1/5) - 1 = 10.4%
CAGR is lower because losses have a disproportionate effect on compounding. It’s the more accurate measure of actual growth.
Setting Up a CAGR Calculator
| A | B |
|---|---|
| Beginning Value | $25,000 |
| Ending Value | $41,000 |
| Start Date | 1/1/2019 |
| End Date | 1/1/2024 |
| Years | =(B4-B3)/365.25 |
| CAGR | =(B2/B1)^(1/B5)-1 |
Comparing Investments Side by Side
| Investment | Start | End | Period | CAGR |
|---|---|---|---|---|
| Fund A | $10,000 | $18,500 | 5 yrs | 13.1% |
| Fund B | $10,000 | $26,000 | 8 yrs | 12.7% |
| Fund C | $10,000 | $14,200 | 3 yrs | 12.4% |
CAGR makes these directly comparable despite different time periods.
Pro Tips
-
CAGR hides volatility - two investments with identical CAGR can have wildly different risk profiles
-
Use for benchmarking - compare your portfolio CAGR against the S&P 500’s historical ~10%
-
Longer periods are more meaningful - a 1-year CAGR is just that year’s return; 5+ years smooths noise
-
Doesn’t work with contributions - if you added money over time, use XIRR instead of CAGR
-
Include dividends - use total return (price appreciation + dividends reinvested) for accurate CAGR
Common Errors
- Ignoring additional contributions - CAGR assumes no money was added or withdrawn; use XIRR for portfolios with cash flows
- Using price-only returns - excluding dividends understates total return by 1-2% annually for typical stock portfolios
- Too short a period - a 1-year CAGR for a volatile asset is misleading
- Confusing with average return - CAGR is always less than or equal to the simple average due to volatility drag