כיצד לחשב IRR באקסל: 4 השיטות הטובות ביותר

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

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

נניח שתשקיע סכום כסף בתוכנית השקעות ל-20 השנים הבאות. התוכנית אומרת שתקבל תמורה כל רבעון. כעת, אם זה חכם להשקיע בתוכנית כזו או לא ניתן להחליט משווי ה-IRR של ה-SIP. ככל שה-IRR גבוה יותר תוכנית ההשקעה משתלמת יותר. הדבר נכון לגבי כל שאר ההשקעות עתירות הון בעסקים, חשבונות חיסכון בנקאיים, קרנות נאמנות, 401(k) וכו'.

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

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

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

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

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

קרא גם:תבניות התקציב הטובות ביותר של Excel

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

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

=IRR(ערכים או טווחי תאים, [ניחוש ארגומנט])

• ערכים הם קלט נדרש עבור פונקציית IRR. זה יכול להיות סדרה של מספרים, מערך של מספרים או טווח תאי ייחוס המורכב תזרימי מזומנים.
• ניחוש טיעון הוא אופציונלי. אם אינך מספק ערך Guess, אזי Excel מחשיב אותו לשיעור התשואה הפנימי של 0.1, 10% או 10 נקודות בסיס הנדרש על ידי תוכנית ההשקעה. אם תספק ארגומנט של Guess, Excel יפעיל את החישובים למשך עד 20 הפעלות איטרטיביות כדי למצוא ערך IRR קרוב לערך Guess.

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

=XIRR(ערכים או טווחי תאים, תאריכים, [ניחוש ארגומנט])

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

=MIRR(ערכים או טווחי תאים, שיעור_פיננסים, שיעור_השקעה מחדש)

• ערכים הוא דוח תזרים המזומנים המורכב מאחד לפחות של תזרימי מזומנים חיוביים ושליליים.
• Finance_rate הוא הריבית שאתה משלם למלווה כנגד הכסף שנלווה עבור פרויקט עסקי.
• Reinvest_rate הוא שיעור הריבית שהעסק או קרן ההשקעות שלך מקבלים על תזרימי המזומנים בעת השקעה מחדש.

קרא גם:כיצד להשתמש בנוסחת החיסור של Excel

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

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

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

• כעת, השתמש בנוסחה הבאה בתא שבו תרצה ש-Excel יחשב את ערך ה-IRR:

=IRR(C2:C8)

• מכה להיכנס להשיג החזר ROI על השקעה פוטנציאלית.

חישוב IRR זה מבוסס על ארגומנט ניחוש ברירת המחדל של 10%. נניח שאתה מחפש תשואה של 12%. כך עליך להזין את ערך הניחוש:

=IRR(C2:C8,30%)

• בטבלה לעיל, אתה רואה שתזרים המזומנים האחרון הוא שלילי. לפיכך, תזרים המזומנים חייב להשתנות מחיובי לשלילי או שלילי לחיובי.

• בתרחיש זה, השתמש ב-a לְנַחֵשׁ כדי למצוא את התוצאה המדויקת שכן IRR יפיק שתי תוצאות.

• ה לְנַחֵשׁ הטיעון צריך להיות ההחזר על ההשקעה שאתה מצפה לו.

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

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

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

=XIRR(C2:C8,D2:D8)‎

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

• בגליון העבודה הנוכחי, הוסף שתי שורות מידע כפי שמוצג בתמונה.

• אלו הם ה עלות הון ו שיעור השקעה חוזרת.

• בתאים שלהם, הזן גם את הערכים.
• כעת, השתמש בנוסחה הבאה כדי לקבל שיעור תשואה פנימי ריאליסטי:

=MIRR(C2:C8,G2,G3)‎

ה-IRR והנוסחאות המשוננות שלו מבצעים 20 חישובים איטרטיביים לפני הצגת תוצאה. אם אתה צריך דיוק גדול יותר מזה, אתה יכול להשתמש בפונקציה של Excel Goal Seek כדי לחשב IRR. בשיטה זו, Excel מבצע עד 32,000 חישובים איטרטיביים לפני יצירת ערך החזרה. להלן ההוראות לשימוש ב-Goal Seek וחישוב IRR ב-Excel:

• הגדר את IRR ל-11% או כל דבר אחר באופן ידני.

• חשב את ה- NPV מתחת ל-IRR באמצעות נוסחה זו:

=NPV(C9,C3:C8)+C2

• כעת, לחץ על נתונים הכרטיסייה ולאחר מכן בחר את מה-אם ניתוח בתוך ה תַחֲזִית קבוצת פקודות בתפריט רצועת Excel.
• בחר חיפוש מטרה מתפריט ההקשר שצץ.
• הנה ה הגדר תא צריך להיות ערך NPV. אז ה להעריך צריך להיות 0.

• מַעֲרֶכֶת על ידי החלפת תא לערך IRR.
• אתה רק צריך להזין את הפניות התא כפי שמוצג בתמונה.

• עכשיו, לחץ בסדר כדי לחשב IRR באמצעות Goal Seek.
• לפי מערך הנתונים הנוכחי, ה-NPV יהיה 0.00 אם ה-IRR הוא 12%.

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

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

• הארגומנט עבור החלק ערכים חייב להכיל הכנסה (תזרים מזומנים חיובי) והוצאה (תזרים מזומנים שלילי) כדי שהפונקציה תעבוד בצורה מדויקת.
• הפונקציה IRR תעבד רק מספרים בארגומנט הערכים. זה יתעלם מכל אלמנט אחר כמו ערכים לוגיים, תאים ריקים וטקסטים.
• חייבים להיות מרווחי תזרים מזומנים קבועים כמו שבועי, חודשי, רבעוני, שנתי וכו'. זה לא חובה שתזרימי המזומנים יהיו שווים בשווי הדולר.
• ודא שאתה מכין את טבלת תזרים המזומנים בסדר כרונולוגי. לדוגמה, אם אתה משלם $100 בכל חודש, אז סדר את ערכי תזרים המזומנים ואת התאריכים שלהם מינואר, פברואר, מרץ וכו'.
• בחישובי IRR פשוטים, אין חובה להזין ארגומנט של Guess.
• בעת שימוש בערך Guess, נוסחת IRR תציג ערך הקרוב ביותר לארגומנט Guess. אם זה מראה #NUM! שגיאה, שנה את ערך הארגומנט Guess.

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

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

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

הבא בתור, כיצד להשתמש בנוסחה של Excel IF-THEN.