| 0 | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | 2023 | 2024 | 2025 | 2026 | CAGR | Check | ||
| 2 | Product A | 500 | 300 | 800 | 600 | 6.3% =(E2/B2)^(1/3)-1 | 600 =B2*(1+G2)^3 | ||
| 3 | Product B | 150 | 450 | 570 | 94.9% =(E3/C3)^(1/2)-1 | 570 =C3*(1+G3)^2 | |||
| 4 | Product C | 850 | 900 | 5.9% =(E4/D4)^(1/1)-1 | 900 =D4*(1+G4)^1 | ||||
| 5 | Total | 500 | 450 | 2100 | 2070 | ||||
| 6 | |||||||||
| 7 | Overall CAGR | Check Overall CAGR | |||||||
| 8 | 11.3% | 2070 |
In Cell G8 I want to calculate the overall CAGR from 2023 to 2026.
Therefore, I used the following formula for it:
=(SUM(E2:E4)/SUM(B2,C3,D4))^(1/3)-1
When I check this formula in Cell I8 it seems to give the right result:
=SUM(B2,C3,D4)*(1+G8)^3
It all seems to work correctly. However, when I am thinking about it I am not sure if it is really correct because when I look at the formula in Cell G8 it kind of assumes that also Product B and Product C have been existing for 3 years.
In Range G2:G4 I always put the specific number of years to calculate the CAGR.
Maybe I am overthinking this but is there another way to calculate the overall CAGR or even a specific Excel-Formula for this kind of situation?