=(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+%, ב-Mac Control+Shift+%), אחרת היא מוצגת כמספר עשרוני כמו 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 הן 5 תקופות של צמיחה, וטעות נפוצה היא להשתמש ב-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 יש 5 שנים, אף שבטבלה יש 6 שורות.
מה ההבדל בין 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! או מספר חסר משמעות. דווחו במקום זאת על השינוי במונחים מוחלטים.
