كيفية إنشاء صيغة CAGR في Excel

تعرف على كيفية حساب معدل النمو السنوي المركب (CAGR) في Excel باستخدام عوامل التشغيل أو وظائف Excel المختلفة.

CAGR تعني معدل النمو السنوي المركب ، والذي يقيس معدل النمو السنوي (المعدل السلس) للاستثمار كل عام خلال فترة زمنية معينة. على سبيل المثال ، لنفترض أنك اشتريت سهمًا بقيمة 100 دولار في عام 2011 وقيمته 400 دولار في عام 2021 ، فإن معدل النمو السنوي المركب سيكون هو المعدل الذي نما به استثمارك في هذا السهم كل عام.

أسواق الأسهم متقلبة ، وقد يختلف نمو الاستثمار من سنة إلى أخرى. قد يزيد عائد الاستثمار أو ينقص. يساعد معدل النمو السنوي المركب CAGR على تحقيق عوائد سلسة لاستثمار مثل نموه بمعدل متوازن كل عام.

على الرغم من عدم وجود وظيفة CAGR في Excel ، إلا أن هناك عدة طرق يمكنك من خلالها حساب معدل النمو السنوي المركب في Excel. توضح لك هذه المقالة كيفية إنشاء صيغ CAGR واضحة وسهلة الفهم باستخدام وظائف Excel المختلفة.

كيفية إنشاء ملف معدل النمو السنوى الاجمالى صيغة (CAGR) في Excel

معدل النمو السنوي المركب (CAGR) مفيد جدًا للأعمال والتخطيط المالي والنمذجة المالية وتحليل الاستثمار. تحسب صيغة CAGR النمو السنوي للاستثمار الذي يمكن استخدامه للمقارنة بالاستثمارات الأخرى.

لحساب معدل النمو السنوي المركب ، تحتاج إلى ثلاثة مدخلات أساسية: القيمة الأولية للاستثمار ، والقيمة النهائية ، وعدد الفترات (السنوات).

صيغة CAGR

صيغة صيغة CARG هي:

معدل نمو سنوي مركب = (قيمة النهاية / قيمة البداية) 1 / ن - 1

أين:

• القيمة النهائية – الرصيد الختامي للاستثمار في نهاية فترة الاستثمار.
• قيمة البداية – رصيد بداية الاستثمار في بداية فترة الاستثمار.
• ن – عدد السنوات التي استثمرتها.

حساب معدل النمو السنوي المركب في Excel

الآن بعد أن تعلمت الحساب الكامن وراء الفائدة المركبة ، دعنا نرى كيف يمكنك حساب معدل النمو السنوي المركب في Excel. هناك 5 طرق لإنشاء صيغة Excel لحساب معدل النمو السنوي المركب:

• باستخدام العوامل الحسابية
• استخدام وظيفة RRI
• استخدام وظيفة الطاقة.
• باستخدام وظيفة RATE.
• استخدام وظيفة IRR.

حساب معدل النمو السنوي المركب في Excel باستخدام عوامل التشغيل

الطريقة المباشرة لحساب معدل النمو السنوي المركب هي باستخدام عوامل التشغيل. استخدم الصيغة العامة أعلاه لحساب معدل النمو السنوي المركب.

لنفترض أن لدينا بيانات مبيعات لشركة معينة في جدول البيانات أدناه.

يحتوي العمود A على السنوات التي يتم فيها كسب الإيرادات. يحتوي العمود B على إيرادات الشركة في العام المعني. باستخدام صيغة CAGR في Excel ، يمكنك حساب معدل النمو السنوي للإيرادات.

أدخل الصيغة أدناه لحساب CAGR بالطريقة المباشرة:

= (B11 / B2) ^ (1/9) -1

في المثال أدناه ، تكون قيمة بداية الاستثمار في الخلية B2 والقيمة النهائية في الخلية B11. عدد السنوات (الفترة) بين بداية ونهاية فترة الاستثمار هو 9. عادة ، تبدأ كل فترة دورة استثمار سنة واحدة وتنتهي في العام التالي ، وبالتالي ، فإن الفترة الأولى من الدورة ، في هذه الحالة ، هي 2011 2012 والدورة الاخيرة 2019-2020. لذا فإن إجمالي عدد السنوات المستثمرة هو "9"

ستكون النتيجة بأرقام عشرية وليست نسبة مئوية كما هو موضح أعلاه. لتحويلها إلى نسبة مئوية ، انتقل إلى علامة التبويب "الصفحة الرئيسية" ، وانقر على القائمة المنسدلة التي تقول "عام" في مجموعة الأرقام ، وحدد خيار "النسبة المئوية".

الآن ، حصلنا على معدل النمو السنوي المركب وهو "10 .77٪" في الخلية B13. هذا هو معدل النمو الفردي السلس لكامل الفترة الزمنية.

حساب معدل النمو السنوي المركب في Excel باستخدام دالة RRI

تقيس وظيفة RRI معدل فائدة دوري مكافئ لعائد استثمار أو قرض خلال فترة زمنية معينة.

يتم احتساب معدل الفائدة على أساس القيمة الحالية والمستقبلية للاستثمار والمدة.

بناء الجملة:

= RRI (nper، pv، fv) 

• nper - إجمالي عدد الفترات (سنوات)
• الكهروضوئية - تحدد القيمة الحالية للاستثمار أو القرض (مثل القيمة الأولية)
• ص - تحدد القيمة المستقبلية للاستثمار أو القرض (مثل القيمة النهائية)

لدينا قيمة nper في الخلية A11 و pv في الخلية C2 و fv في الخلية C11. استخدم هذه الصيغة:

= RRI (A11، C2، C11)

معدل النمو السنوي المركب هو "10 .77٪" ، وهو موجود في الخلية B13.

حساب معدل النمو السنوي المركب في Excel باستخدام وظيفة الطاقة

طريقة أخرى سهلة لحساب معدل النمو السنوي المركب (CAGR) في Excel هي باستخدام وظيفة الطاقة. تحل وظيفة POWER محل ملف ^ عامل في وظيفة CAGR.

صيغة الدالة POWER:

= POWER (العدد ، القوة)

حجج وظيفة POWER:

• عدد - هو الرقم الأساسي الذي يتم إيجاده بقسمة القيمة النهائية (EV) على القيمة الأولية (BV) (EV / BV).
• قوة - هو رفع النتيجة لأس واحد مقسومًا على المدة (1 / عدد النقاط (ن)).

الآن ، يتم تعريف الوسيطات بهذه الطريقة للعثور على قيمة CAGR:

= POWER (EV / BV، 1 / ​​n) -1

لنطبق الصيغة على مثال:

= POWER (C11 / C2،1 / A11) -1

النتائج:

حساب معدل النمو السنوي المركب في Excel باستخدام دالة RATE

وظيفة المعدل هي وظيفة أخرى يمكنك استخدامها للعثور على معدل نمو سنوي مركب في Excel. عندما تنظر إلى صيغة الدالة RATE ، قد تبدو معقدة بعض الشيء مع 6 وسيطات لا تقل عن ذلك ، ولكن بمجرد فهم الوظيفة ، قد تفضل هذه الطريقة للعثور على قيمة CAGR.

صيغة الدالة RATE:

= RATE (nper، pmt، pv، [fv]، [type]، [guess]) 

أين:

• nper – إجمالي عدد الدفعات (مدة القرض).
• بمت (اختياري) - مبلغ الدفعة المسددة في كل فترة.
• بف - يحدد هذا القيمة الحالية للقرض / الاستثمار (القيمة الأولية (BV))
• [و]- يحدد هذا القيمة المستقبلية للقرض / الاستثمار ، عند الدفعة الأخيرة (القيمة النهائية (EV))
• [نوع] - يحدد هذا عندما تكون مدفوعات القرض / الاستثمار مستحقة ، إما 0 أو 1. تعني الوسيطة 0 أن المدفوعات مستحقة في بداية الفترة وتعني 1 أن المدفوعات مستحقة في نهاية الفترة (الافتراضي هو 0).
• [خمن] - تخمينك للمعدل. إذا تم حذفه ، يتم تعيينه افتراضيًا إلى 10٪.

سبب احتواء الدالة RATE على ست وسيطات هو أنه يمكن استخدامها في العديد من العمليات الحسابية المالية الأخرى. لكن يمكننا تحويل الدالة RATE إلى صيغة CAGR ، بثلاث وسيطات فقط (nper) ، و 3 (pv) ، و 4 (fv):

= RATE (nper ،، - BV، EV)

نظرًا لأننا لا نقوم بدفعات منتظمة (شهرية ، ربع سنوية ، سنوية) ، فإننا نترك الوسيطة الثانية فارغة.

لحساب معدل النمو السنوي المركب باستخدام دالة RATE ، استخدم هذه الصيغة:

= RATE (A11 ،، - C2، C11)

إذا كنت لا تريد حساب عدد الفترات يدويًا ، فاستخدم الدالة ROW كأول وسيطة في RATE fromula. سوف يحسب الإذاعة الوطنية العامة لك.

= RATE (ROW (A11) -ROW (A2) ،، - C2، C11)

حساب معدل النمو السنوي المركب في Excel باستخدام وظيفة IRR

إن IRR المختصر لـ "عائد السعر الداخلي" هو إحدى وظائف Excel التي تحسب معدل العائد الداخلي للدفعات والدخل الذي يحدث على فترات منتظمة (أي شهريًا ، سنويًا).

تعتبر طريقة IRR مفيدة عندما تحتاج إلى حساب قيمة معدل النمو السنوي المركب للتدفقات النقدية الدورية.

الصيغة هي:

= IRR (القيم ، [تخمين])

أين:

• القيم - مجموعة من المدفوعات. يجب أن يحتوي نطاق المدفوعات على تدفقات نقدية سلبية واحدة على الأقل وأخرى موجبة.
• [خمن] (اختياري) - يمثل هذا تخمينك لقيمة السعر. إذا تم تجاهله ، يتم تعيينه افتراضيًا إلى 10٪.

تتطلب منك وظيفة Excel IRR إعادة ضبط مجموعة البيانات بهذه الطريقة:

يجب إدخال قيمة البداية كرقم سالب ، وقيمة النهاية هي رقم موجب ، وجميع القيم الأخرى كأصفار.

هذه هي الصيغة للمثال:

= IRR (C2: C11)

حسنًا ، هذه هي الطرق التي يمكنك من خلالها حساب معدل النمو السنوي المركب (CGAR) في Excel.