חישוב IRR באקסל | כיצד להשתמש בפונקציית IRR? (עם דוגמאות)

חישוב IRR באקסל

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

IRR באקסל מייצג I Nternal R ate of R eturn. IRR ב- Excel היא אחת הפונקציות המובנות הזמינות ב- Microsoft Excel. IRR על Excel נכלל בקטגוריה של פונקציות פיננסיות ב.

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

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

נוסחה המשמשת לחישוב IRR ב- Excel

נוסחת ה- IRR של Excel היא כדלקמן:

איפה,

• טווח = זהו פרמטר חובה. טווח תאים המציין את סדרת תזרימי המזומנים שלגביהם יחושב שיעור ההחזר הפנימי.
• guess = זהו פרמטר אופציונלי. זה מציין מספר שאתה מניח שהוא קרוב לתוצאה של שיעור התשואה הפנימי. אם לא מוזכר שום ערך, ערך ברירת המחדל נקבע כ- 0.1 או 10%
• "[]" מציין פרמטרים אופציונליים.

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

כיצד לחשב IRR ב- Excel? (עם דוגמאות)

אתה יכול להוריד תבנית Excel זו של פונקציית IRR כאן - תבנית Excel של פונקציית IRR

חישובי ה- IRR ב- Excel יכולים להתבצע בשתי דרכים כלומר פונקציית גליון עבודה (WS) או פונקציית VBA. כפונקציה WS, ניתן להזין אותה כחלק מהנוסחה בתא של גליון עבודה. כפונקציה VBA, ניתן להשתמש בה בקוד מאקרו אשר מוזן באמצעות עורך Visual Basic של Microsoft המשולב ב- MS Excel. עיין בחישוב ה- IRR בדוגמה המצוינת להלן כדי להבין טוב יותר.

דוגמה מס '1 - חישוב IRR בגליון העבודה של Excel

בחישוב לדוגמא של IRR, -3000 מציין את סכום ההון שהושקע. המספרים הבאים ו -5000 מציינים הכנסה. פונקציית IRR ב- Excel מחושבת על ערכים -3000, 2000 ו- 5000. התוצאה היא 67%.

דוגמה מס '2

בחישוב לדוגמא IRR זה, #NUM! מוצג כתוצאה מכיוון שאין ערך שלילי יחיד במכלול הערכים. לכן, אין ערך המציין תשלום, אך כולם הכנסה.

דוגמה מס '3

בחישוב לדוגמא IRR, כל הערכים שליליים. זה מצביע על כך שלא נוצרו הכנסות. לפיכך, התוצאה שנוצרה היא #NUM! 

דוגמה מס '4

בחישוב דוגמת IRR זה, הערכים הכוללים הם חמישה. -10000 מציין את התשלום שבוצע. הערכים הבאים הם לשנה כלומר הכנסה שנצברה בשנה 1 היא 2000, שנה 2 היא 5000, שנה 3 היא 4000, שנה 5 היא 6000.

כדי לחשב את ההכנסה שנוצרה בסוף שלוש שנים, נוסחת ה- IRR מוחלת על תאים הנעים בין B2: B4. התוצאה היא -19% מכיוון שסכום התשלום בשנה הראשונית גבוה מההכנסה שנוצרה בשנים שלאחר מכן.

כדי לחשב את ההכנסה שנוצרה בתום חמש שנים, נוסחת IRR מוחלת על תאים הנעים בין B2: B6. התוצאה היא 22%. התוצאה חיובית שכן ההכנסה שנוצרה בשנים הבאות גבוהה מסכום התשלום שהושקע בשנה הראשונית.

דוגמה מס '2 - חישוב IRR ב- Excel VBA

ניתן להשתמש בפונקציית IRR בקוד VBA ב- Microsoft Excel באופן הבא:

נוסחת IRR ב- Excel VBA :

נוסחת Irr ב- VBA היא כדלקמן.

איפה,

ערכים = תזרים מזומנים

IRRVal = שיעור תשואה פנימי

IRR בדוגמת VBA של Excel

בוא נעבור את הדוגמה עבור איר ב- VBA.

בחישוב דוגמה זו של IRR, ה- IRRVal הוא משתנה מסוג כפול. הוא מחזיק בתוצאה שהוחזרה על ידי הפונקציה Irr. הערכים מיוצגים בצורה של מערך של סוג נתונים כפול שבו הערך ההתחלתי הוא -10000 המציין את התשלום שבוצע בשנה הראשונה. הערכים הבאים הם 2000, 5000, 4000 ו- 6000 המציינים את ההכנסה שנוצרה בשנה בארבע השנים הבאות.

דברים שיש לזכור

1. מערך הערכים ( טווח ) חייב להכיל מספר חיובי מינימלי ומספר שלילי אחד.
2. הערכים צריכים להיות בסדר רציף. IRR משתמש בסדר הערכים כדי לפרש את סדר תזרימי המזומנים. אנא דאג להזין את ערכי התשלום וההכנסה ברצף כפי שהם קרו או שאתה רוצה לגרום לזה לקרות.
3. אם מערך של טיעון הפניה מכיל טקסט, ערכים לוגיים או תאים ריקים, מתעלמים מערכים אלה.
4. MS Excel משתמש בטכניקה איטרטיבית לחישוב IRR באמצעות Excel. החל מניחוש, IRR עובר את החישוב עד שהתוצאה מדויקת בתוך 0.00001 אחוז. אם IRR לא יכול למצוא תוצאה שעובדת אחרי 20 ניסיונות, ה- #NUM! ערך השגיאה מוחזר.
5. אם פונקציית IRR ב- excel מחזירה את המספר #NUM! או אם התוצאה אינה סביב מה שציפית שתהיה, נסה עם ערך אחר עבור
6. ברוב המקרים, אינך צריך לספק ערך ניחוש לחישוב ה- IRR ב- Excel. אם מדלגים על פרמטר הניחוש, התוכנה מניחה שהוא 0.1 כלומר 10%.
7. IRR קשור קשר הדוק ל- NPV, כלומר פונקציית Net Present Value. שיעור התשואה המחושב על ידי IRR עם Excel הוא שיעור הריבית המקביל לערך נוכחי אפס נטו.