不同时间段下整体CAGR的正确计算方法及Excel公式咨询
2026-6-21
问题背景与数据表格
| 产品名称 | 2023 | 2024 | 2025 | 2026 | CAGR公式 | 验证公式 | |||
|---|---|---|---|---|---|---|---|---|---|
| 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 | 总计 | 500 | 450 | 2100 | 2070 | ||||
| 6 | |||||||||
| 7 | 整体CAGR | 验证整体CAGR | |||||||
| 8 | 11.3% | 2070 |
我需要在单元格G8计算2023-2026年的整体CAGR，目前用的公式是：
=(SUM(E2:E4)/SUM(B2,C3,D4))^(1/3)-1
通过单元格I8的公式验证结果看似正确：
=SUM(B2,C3,D4)*(1+G8)^3
但这个方法存在逻辑问题：它默认Product B和C也存续了3年，而G2:G4区域是按各产品实际存续年限计算的CAGR。想知道有没有更合理的整体CAGR计算方法，或者适配这类场景的Excel专用公式？
解决方案
1. 基于年度总营收的直接计算（最直观）
直接用每年的总营收数据计算，公式逻辑完全贴合整体业务的时间跨度：
=(E5/B5)^(1/(2026-2023))-1
代入数据即=(2070/500)^(1/3)-1，结果约为11.3%。这个方法不纠结单个产品的启动时间，只反映整体业务从2023到2026年的复合增长趋势，逻辑完全自洽。
2. 加权平均CAGR（结合单个产品贡献）
如果需要体现各产品增长对整体的影响，可按各产品初始值占比做加权平均：
=SUMPRODUCT(G2:G4, B2:B4/SUM(B2,C3,D4))
这里的权重是各产品初始值占所有产品初始值总和的比例，计算结果会更贴合不同产品的增长贡献差异。
3. 用XIRR函数精确计算（最严谨）
若要考虑资金的时间价值（比如不同产品的阶段性投入），可以用XIRR函数：
- 整理现金流与对应日期：
- 2023年：-500（Product A的初始营收，作为期初投入）
- 2024年：-150（Product B的启动投入）
- 2025年：-850（Product C的启动投入）
- 2026年：2070（总营收，作为期末收回）
- 输入公式：
=XIRR(现金流区域, 日期区域)
这个结果是考虑资金分批投入的内部收益率，和CAGR逻辑一致，适用于需要精准体现时间价值的场景。
内容的提问来源于stack exchange，提问作者Michi
相关产品推荐
相关产品推荐
