כיצד ליצור נוסחת CAGR באקסל

למד כיצד ניתן לחשב קצב צמיחה שנתי מורכב (CAGR) ב-Excel באמצעות אופרטורים או פונקציות שונות של Excel.

CAGR ראשי תיבות של Compound Annual Growth Rate, המודד את קצב הצמיחה השנתי (שיעור החלק) של השקעה בכל שנה במהלך מרווח נתון. לדוגמה, נניח שקנית מניה בשווי 100 דולר ב-2011 והיא שווה 400 דולר ב-2021, CAGR יהיה הקצב שבו ההשקעה שלך במניה זו גדלה מדי שנה.

שוקי המניות תנודתיים, הצמיחה של השקעה עשויה להשתנות משנה לשנה. ההחזר על ההשקעה עשוי לעלות או לרדת. CAGR עוזר להחליק תשואות של השקעה כמו שהיא גדלה בקצב מאוזן מדי שנה.

למרות שאין פונקציית CAGR באקסל, ישנן מספר דרכים בהן תוכל לחשב CAGR באקסל. מאמר זה מראה לך כיצד ליצור נוסחאות CAGR ברורות וקלות להבנה באמצעות פונקציות שונות של Excel.

כיצד ליצור א קצב צמיחה שנתי מורכב נוסחה (CAGR) באקסל

קצב צמיחה שנתי מורכב (CAGR) עוזר מאוד לתכנון עסקי, פיננסי, מודלים פיננסיים וניתוח השקעות. נוסחת CAGR מחשבת את הצמיחה השנתית של השקעה שניתן להשתמש בה כדי להשוות להשקעות אחרות.

כדי לחשב CAGR אתה צריך שלוש תשומות עיקריות: ערך התחלתי של השקעה, ערך סיום ומספר התקופות (שנים).

נוסחת CAGR

התחביר של נוסחת CARG הוא:

CAGR =(ערך סיום/ערך התחלה)1/n - 1

איפה:

• ערך סיום – יתרת סיום של ההשקעה בתום תקופת ההשקעה.
• ערך התחלתי – יתרת התחלה של ההשקעה בתחילת תקופת ההשקעה.
• נ – מספר השנים שהשקעת.

חישוב CAGR באקסל

עכשיו שלמדת את החשבון מאחורי ריבית דריבית, בוא נראה איך אתה יכול לחשב CAGR באקסל. ישנן 5 דרכים ליצור נוסחת Excel לחישוב CAGR:

• שימוש באופרטורים אריתמטיים
• שימוש בפונקציית RRI
• שימוש בפונקציית POWER.
• שימוש בפונקציית RATE.
• שימוש בפונקציית IRR.

חישוב CAGR באקסל באמצעות אופרטורים

הדרך הישירה לחישוב CAGR היא באמצעות אופרטורים. השתמש בנוסחה הגנרית לעיל כדי לחשב CAGR.

נניח שיש לנו נתוני מכירות עבור חברה מסוימת בגיליון האלקטרוני למטה.

בעמודה א' יש את השנים שבהן ההכנסות מתקבלות. בעמודה B יש את ההכנסות של החברה בשנה המתאימה. עם נוסחת CAGR באקסל, אתה יכול לחשב את קצב הצמיחה השנתי של ההכנסות.

הזן את הנוסחה שלהלן כדי לחשב CAGR בשיטה הישירה:

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

בדוגמה למטה, הערך ההתחלתי של ההשקעה נמצא בתא B2 וערך הסיום בתא B11. מספר השנים (תקופה) בין תחילת תקופת ההשקעה לסיום היא 9. בדרך כלל, כל תקופת מחזור השקעה מתחילה שנה אחת ומסתיימת בשנה הבאה, מכאן שהתקופה הראשונה של המחזור, במקרה זה, היא 2011 -2012 והמחזור האחרון הוא 2019-2020. אז המספר הכולל של השנים שהושקעו הוא '9'

התוצאה תהיה במספרים עשרוניים ולא באחוזים כפי שמוצג לעיל. כדי להמיר אותו לאחוז, עבור ללשונית 'בית', לחץ על התפריט הנפתח שאומר 'כללי' בקבוצת מספרים, ובחר באפשרות '% אחוז'.

כעת, קיבלנו את שיעור הצמיחה השנתי המורכב שהוא '10.77%' בתא B13. זהו קצב הגדילה המוחלק עבור כל תקופת הזמן.

חישוב CAGR באקסל באמצעות פונקציית RRI

פונקציית RRI מודדת ריבית שווה ערך תקופתית להחזר של השקעה או הלוואה על פני תקופה מסוימת.

שיעור הריבית מחושב על סמך הערך הנוכחי והעתידי של ההשקעה ומשך הזמן.

התחביר:

=RRI(nper,pv,fv) 

• nper - המספר הכולל של תקופות (שנים)
• pv - זה מציין את הערך הנוכחי של השקעה או הלוואה (זהה לערך ההתחלתי)
• fv - זה מציין את הערך העתידי של השקעה או הלוואה (זהה לערך הסיום)

יש לנו את הערך של nper בתא A11, pv בתא C2 ו-fv בתא C11. השתמש בנוסחה זו:

=RRI(A11,C2,C11)‎

CAGR הוא '10.77%', שנמצא בתא B13.

חישוב CAGR באקסל באמצעות פונקציית POWER

שיטה קלה נוספת לחישוב קצב צמיחה שנתי מורכב (CAGR) באקסל היא באמצעות פונקציית POWER. פונקציית POWER מחליפה את ^ מפעיל בפונקציית CAGR.

התחביר של פונקציית POWER:

=POWER(מספר,הספק)

טיעונים של פונקציית POWER:

• מספר – זהו מספר הבסיס שנמצא על ידי חלוקת ערך הסיום (EV) בערך ההתחלה (BV) (EV/BV).
• כּוֹחַ – זה להעלות את התוצאה למעריך של אחד חלקי משך הזמן (1/מספר תקופות (n)).

כעת, הארגומנטים מוגדרים כך כדי למצוא ערך CAGR:

=POWER(EV/BV,1/n)-1

בוא ניישם את הנוסחה על דוגמה:

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

התוצאה:

חישוב CAGR באקסל באמצעות פונקציית RATE

פונקציית שיעור היא פונקציה נוספת שבה אתה יכול להשתמש כדי למצוא CAGR ב- Excel. כשמסתכלים על התחביר של הפונקציה RATE, זה עשוי להיראות קצת מסובך עם 6 ארגומנטים לא פחות, אבל לאחר שתבינו את הפונקציה, אולי תעדיפו את השיטה הזו למציאת ערך CAGR.

הפונקציה תחביר של RATE:

=RATE(nper,pmt,pv,[fv],[type],[נחש]) 

איפה:

• nper – המספר הכולל של תקופת התשלומים (תקופת ההלוואה).
• Pmt (אופציונלי) – סכום התשלום שבוצע בכל תקופה.
• Pv – זה מציין את הערך הנוכחי של ההלוואה/ההשקעה (ערך התחלתי (BV))
• [Fv]- זה מציין את הערך העתידי של ההלוואה/ההשקעה, בתשלום האחרון (ערך סיום (EV))
• [סוּג] – זה מציין מתי התשלומים עבור ההלוואה/השקעה מגיעים, הוא 0 או 1. הארגומנט 0 פירושו שהתשלומים מגיעים בתחילת התקופה ו-1 פירושו שהתשלומים מגיעים בסוף התקופה (ברירת המחדל היא 0).
• [לְנַחֵשׁ] - הניחוש שלך לגבי שיעור. אם מושמט, ברירת המחדל היא 10%.

הסיבה שלפונקציה RATE יש שישה ארגומנטים היא שניתן להשתמש בה עבור חישובים פיננסיים רבים אחרים. אבל אנחנו יכולים להמיר את הפונקציה RATE לנוסחת CAGR, עם שלושה ארגומנטים 1st (nper), 3rd (pv) ו-4th (fv) בלבד:

=RATE(nper,,-BV,EV)‎

מכיוון שאיננו מבצעים תשלומים קבועים (חודשי, רבעוני, שנתי), אנו משאירים את הטיעון השני ריק.

כדי לחשב את קצב הצמיחה השנתי המורכב באמצעות פונקציית RATE, השתמש בנוסחה זו:

=RATE(A11,,-C2,C11)‎

אם אינך רוצה לחשב את מספר התקופות באופן ידני, השתמש בפונקציית ROW כארגומנט הראשון של ה-RATE fromula. זה יחשב את ה NPR בשבילך.

=RATE(ROW(A11)-ROW(A2),,-C2,C11)‎

חישוב CAGR באקסל באמצעות פונקציית IRR

הקיצור של IRR של 'Internal Rate Return' הוא פונקציית Excel שמחשבת את IRR עבור תשלומים והכנסה המתרחשים במרווחי זמן קבועים (כלומר חודשי, שנתי).

שיטת IRR מועילה כאשר אתה צריך לחשב את ערך ה-CAGR עבור תזרימי מזומנים תקופתיים.

התחביר הוא:

=IRR(ערכים,[נחש])

איפה:

• ערכים - מגוון תשלומים. טווח התשלומים צריך להיות לפחות תזרים מזומנים אחד שלילי ואחד חיובי.
• [לְנַחֵשׁ] (אופציונלי) - זה מייצג את הניחוש שלך לגבי ערך התעריף. אם מתעלמים, ברירת המחדל היא 10%.

פונקציית Excel IRR מחייבת אותך להתאים מחדש את מערך הנתונים בדרך זו:

יש להכניס את ערך ההתחלה כמספר שלילי, ערך הסיום הוא מספר חיובי, וכל שאר הערכים כאפסים.

הנה הנוסחה לדוגמא:

=IRR(C2:C11)‎

ובכן, אלו הדרכים בהן תוכל לחשב שיעור צמיחה שנתי מורכב (CGAR) באקסל.