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
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect