تعيد =(B2/A2)^(1/C2)-1 معدل النمو السنوي المركب (CAGR) من قيمة البداية في A2 إلى قيمة النهاية في B2 على مدى C2 سنة. وهو المعدل السنوي الثابت الواحد الذي يحوّل قيمة البداية إلى قيمة النهاية. وتعطي =RRI(C2,A2,B2) النتيجة نفسها.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Years | CAGR | RRI |
| 2 | $50,000 | $80,000 | 4 | 12.47% | 12.47% |
نمت الإيرادات من 50,000 إلى 80,000 في 4 سنوات، أي بمعدل CAGR قدره 12.47%. نسّق النتيجة نسبة مئوية (الصفحة الرئيسية > نمط النسبة المئوية، أو Ctrl+Shift+%، أو Control+Shift+% على Mac)، وإلا تظهر رقمًا عشريًا مثل 0.1247. غيّر C2 إلى 2 فيصبح النمو نفسه في نصف المدة 26.49% سنويًا.
صيغة CAGR خطوة بخطوة
CAGR = (end / start) ^ (1 / years) - 1
end/startهو عامل النمو الكلي: 80,000 / 50,000 = 1.6، أي أن الإيرادات صارت 1.6 ضعف ما كانت عليه.^(1/years)تأخذ الجذر الرابع لذلك العامل، أي العامل السنوي الذي إذا ضُرب في نفسه 4 مرات أعطى 1.6. والرفع إلى القوة 1/4 هو الجذر الرابع.-1تحوّل العامل إلى معدل: تصبح 1.1247 هي 12.47%.
الأقواس مهمة. ترفع =B2/A2^(1/C2)-1 الخلية A2 وحدها إلى القوة، لأن ^ تُحسب قبل /، فتعطي نتيجة لا معنى لها.
وتأخذ RRI (في Excel 2013 وما بعده، وفي Google Sheets) الأرقام الثلاثة نفسها بترتيب مختلف: =RRI(nper, pv, fv)، أي السنوات، ثم البداية، ثم النهاية.
CAGR من جدول قيم سنوية
مع صف لكل سنة، خذ القيمة الأولى والأخيرة واحسب الفترات من السنوات نفسها. ستة صفوف من 2021 إلى 2026 هي خمس فترات نمو، ومن الأخطاء الشائعة استخدام 6.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Year | Users | Growth | Measure | Rate | |
| 2 | 2021 | 12,000 | CAGR | 16.72% | ||
| 3 | 2022 | 18,000 | 50.0% | Average growth | 18.86% | |
| 4 | 2023 | 15,300 | -15.0% | |||
| 5 | 2024 | 19,900 | 30.1% | |||
| 6 | 2025 | 24,500 | 23.1% | |||
| 7 | 2026 | 26,000 | 6.1% |
نما المستخدمون بمعدل CAGR قدره 16.72%، لكن متوسط معدلات النمو السنوية الخمسة 18.86%. يرتفع المتوسط بسبب قفزة 50% في 2022 ولا يحتسب الهبوط في 2023 بالكامل. أما CAGR فهو الرقم الواحد الصادق: =B2*(1+F2)^5 تصل إلى 26,000 بالضبط. غيّر B3 إلى 8000: ينخفض المتوسط بشدة، بينما لا يتحرك CAGR إطلاقًا، لأنه لا يعتمد إلا على القيمة الأولى والأخيرة. ولتغير سنة واحدة وحدها، راجع نسبة التغير.
توقّع قيمة مستقبلية بمعدل CAGR
بالعكس، يخبرك معدل النمو أين ستكون القيمة: =start*(1+rate)^years. هنا تنمو 10,000 بمعدل 8% سنويًا:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Value | Rate | |
| 2 | 0 | $10,000 | 8% | |
| 3 | 1 | $10,800 | ||
| 4 | 2 | $11,664 | ||
| 5 | 3 | $12,597 | ||
| 6 | 4 | $13,605 | ||
| 7 | 5 | $14,693 |
بعد 5 سنوات بمعدل 8%، تصبح 10,000 هي 14,693. ضع قيمة النهاية هذه في صيغة CAGR مع 5 سنوات فتحصل على 8% مرة أخرى. والتركيب نفسه يحرك خطط الادخار والقروض في صفحة PMT.
CAGR بين تاريخين
عندما لا تكون الفترة عددًا صحيحًا من السنوات، استخدم الطول الدقيق بالسنوات أسًّا. تعيده YEARFRAC(start,end,1) من تاريخين؛ والرقم 1 يحتسب الأيام الفعلية، بينما يحتسب الافتراضي أشهرًا من 30 يومًا.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Bought | Sold | Paid | Sold for | Years | CAGR |
| 2 | 2022-03-15 | 2026-09-30 | $8,000 | $11,500 | 4.55 | 8.31% |
من مارس 2022 إلى سبتمبر 2026 نحو 4.55 سنوات، أي CAGR قدره 8.31%. ومع أموال تدخل وتخرج خلال المدة (إيداعات شهرية، أو بيع جزئي)، لا يعود CAGR مناسبًا: استخدم XIRR من صفحة NPV وIRR.
جرّب: نمو سكان مدينة
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Years | Pop 2016 | Pop 2026 | CAGR |
| 2 | 10 | 412,000 | 538,000 |
دورك: في D2، احسب معدل النمو السنوي المركب من عدد السكان في B2 إلى عدد السكان في C2، على مدى عدد السنوات في A2.
تلميح: (end/start)^(1/years)-1. أبقِ الأقواس حول القسمة وحول الأس.
CAGR مقابل متوسط النمو مقابل IRR
| ما لديك | استخدم | الصيغة |
|---|---|---|
| قيمة بداية وقيمة نهاية وعدد سنوات | CAGR | =(end/start)^(1/years)-1 أو =RRI(years,start,end) |
| التغير من سنة إلى التالية | نسبة التغير | =new/old-1 |
| قائمة معدلات سنوية تريد تلخيصها | المتوسط الهندسي | =GEOMEAN(1+C3:C7)-1، الذي يساوي CAGR |
| أموال تدخل وتخرج بمرور الوقت | IRR أو XIRR | =IRR(values)، =XIRR(values,dates) |
تجنّب عرض المتوسط البسيط AVERAGE لمعدلات النمو السنوية على أنه "متوسط النمو السنوي" لأي شيء يتراكم، مثل الإيرادات أو الأسعار أو الاستثمارات. فبعد +50% ثم -50% يكون المتوسط 0%، لكن القيمة انخفضت إلى 75% مما بدأت به.
الأسئلة الشائعة
ما صيغة CAGR في الاكسل؟
=(end/start)^(1/years)-1، مثل =(B2/A2)^(1/C2)-1. نسّق النتيجة نسبة مئوية. وتعطي =RRI(C2,A2,B2) النتيجة نفسها في Excel 2013 وما بعده.
كم سنة أستخدم في CAGR؟
عدد الفترات بين القيمة الأولى والأخيرة، لا عدد القيم. من 2021 إلى 2026 خمس سنوات، مع أن الجدول فيه ستة صفوف.
ما الفرق بين CAGR ومتوسط النمو السنوي؟
متوسط النمو هو المتوسط البسيط للتغير المئوي لكل سنة. أما CAGR فهو المعدل الثابت الواحد الذي يحوّل قيمة البداية إلى قيمة النهاية. وبعد هبوط ثم تعافٍ يبالغ المتوسط في تقدير النمو: +50% ثم -50% متوسطهما 0% لكنهما يتركانك عند 75% من البداية، أي CAGR بنحو -13.4%.
كيف أحسب CAGR بين تاريخين في الاكسل؟
استخدم الطول الدقيق بالسنوات أسًّا: =(B2/A2)^(1/YEARFRAC(C2,D2,1))-1، حيث C2 وD2 تاريخا البداية والنهاية. والرقم 1 يحتسب الأيام الفعلية.
هل يمكن حساب CAGR مع رقم سالب؟
ليس بشكل له معنى. قيمة البداية 0 تعطي #DIV/0!، وعندما تكون للبداية والنهاية إشارتان مختلفتان تعيد الصيغة #NUM! أو رقمًا لا معنى له. اعرض التغير بالقيم المطلقة بدلًا من ذلك.
