The CAGR formula in Excel is a powerful way to measure the average annual growth rate of an investment, business metric, or financial data set over a specific period. CAGR, or Compound Annual Growth Rate, gives you a smoothed rate of return, helping eliminate the volatility between the starting and ending values. This tutorial will show you how to calculate CAGR in Excel on Windows, macOS, and Ubuntu/Linux, including formula syntax and keyboard shortcuts.
📊 What Is CAGR?
CAGR stands for Compound Annual Growth Rate. It tells you how much something has grown (or shrunk) per year on average over a defined time period.
CAGR Formula:
= (Ending Value / Beginning Value)^(1 / Number of Years) - 1
You’ll often use CAGR in:
- Investment growth analysis
- Business revenue forecasting
- Customer base or sales performance
- Financial modeling
📘 Example CAGR Calculation in Excel
Suppose:
- Beginning Value (B1) = 10,000
- Ending Value (B2) = 20,000
- Number of Years (B3) = 3
Your Excel formula:
=(B2/B1)^(1/B3)-1
💡 Format the result cell as percentage for clarity:
- Windows:
Ctrl + Shift + % - macOS:
Cmd + Shift + % - Ubuntu (LibreOffice Calc):
Ctrl + Shift + 5
Result: 25.99% CAGR
⌨️ Keyboard Shortcuts for Excel CAGR Workflows
| Action | Windows Excel | macOS Excel | Ubuntu (LibreOffice Calc) |
|---|---|---|---|
| Format as Percentage | Ctrl + Shift + % | Cmd + Shift + % | Ctrl + Shift + 5 |
| Insert formula | = (equal sign) | = (equal sign) | = (equal sign) |
| Confirm formula | Enter | Return | Enter |
| Edit cell | F2 | Ctrl + U | F2 |
🧠 Tips for Using CAGR Formula in Excel
- Always wrap your CAGR formula in parentheses to maintain order of operations.
- Use absolute references (e.g.,
$B$1) to keep cells fixed when copying formulas. - For tables or dashboards, format the CAGR cell with a green font to highlight positive growth and red for negative values.
🪟 CAGR Calculation on Windows
- Select an empty cell.
- Type:
=(EndValue/StartValue)^(1/Years)-1 - Press Enter.
- Format as Percentage using
Ctrl + Shift + %.
You can also use AutoSum > More Functions > Power() for advanced control.
🍎 CAGR Formula on macOS Excel
- Select a target cell.
- Use the formula:
=(C2/C1)^(1/C3)-1 - Press Return.
- Format as percentage via Cmd + Shift + % or the Format tab.
Excel on macOS supports the exact same formula as Windows.
🐧 CAGR in Ubuntu (LibreOffice Calc)
LibreOffice uses identical logic but slightly different interface:
- Select a cell and type:
=(B2/B1)^(1/B3)-1 - Press Enter.
- Go to Format > Cells > Number > Percentage or use
Ctrl + Shift + 5.
📉 Bonus: CAGR with Dates
You can even calculate CAGR based on dates:
=(B2/B1)^(1/(YEAR(DATE_END)-YEAR(DATE_START)))-1
Just replace DATE_START and DATE_END with your actual date cells.
❓ FAQs About CAGR in Excel
Can I calculate CAGR with missing years?
No. CAGR assumes consistent time periods. Use linear growth or other models if gaps exist.
Can CAGR be negative?
Yes. If the end value is less than the start, CAGR will be negative—indicating decline.
What if I want to calculate CAGR for monthly growth?
Convert years to months:
=(B2/B1)^(1/Months)-1
Is there a built-in CAGR function in Excel?
No, but you can build one using the formula above or via VBA.
✅ Conclusion
The CAGR formula in Excel helps you analyze long-term performance by providing a clear annualized growth rate. Whether you’re on Windows, macOS, or Ubuntu, it’s a must-have tool in your spreadsheet arsenal. Combine it with keyboard shortcuts for formatting, and you’ll have fast, accurate, and insightful growth analysis at your fingertips.
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment