Excel
All Top 50 Excel & Business Modeling Questions#45
Calculating Compound Annual Growth Rate (CAGR) Correctly in Excel
EasyMcKinseyInterview Question #45
Asked at McKinseyA company's revenue grew from $10.0M in 2021 (cell `B2`) to $22.5M in 2026 (cell `B7`). Write a formula in cell `D2` to calculate the 5-year Compound Annual Growth Rate (CAGR).
Input Table: HistoricalRevenue (A1:B7)
6 rows preview| Year (Col A) | Revenue ($M) (Col B) |
|---|---|
| 2021 | 10 |
| 2022 | 12 |
| 2023 | 14.5 |
| 2024 | 16.8 |
| 2025 | 19.2 |
| 2026 | 22.5 |
Expected Output Structure1 rows
| Metric | Calculated CAGR (D2) |
|---|---|
| 5-Year CAGR (2021-2026) | 17.61% |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
D2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Year (Col A) | Revenue ($M) (Col B) | ||||||||
| 2 | 2021 | 10 | Target [Enter Formula] | |||||||
| 3 | 2022 | 12 | ||||||||
| 4 | 2023 | 14.5 | ||||||||
| 5 | 2024 | 16.8 | ||||||||
| 6 | 2025 | 19.2 | ||||||||
| 7 | 2026 | 22.5 | ||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell D2 and enter your formula above.Shortcut: Click "Load Solution" to inspect