איך לחשב באקסל תשואה שנתית? פונקציית XIRR, נעים להכיר

המטרה: לבדוק לבד את התשואה השנתית האמיתית.

קהל היעד: משקיעים שרוצים להשוות בין אפיקי השקעה שונים.

האמצעי: פונקציית XIRR באקסל.

אם אתם סוגרים היום 100,000 ש"ח ומושכים בעוד שנה 105,000 ש"ח, מה התשואה השנתית? שאלה קלה, התשובה פשוטה: 5%.

ואם בעוד שנתיים מהיום תמשכו 110,000 ש"ח, מה התשואה השנתית? השאלה הזו פחות קלה אבל בהערכה גסה אפשר להגיד שקצת פחות מ-5%. למה קצת פחות ולא בדיוק? כי אם התשואה השנתית הייתה בדיוק 5%, אחרי השנה הראשונה ה-5,000 הנוספים היו בעצמם מייצרים עוד 250 ש"ח בזכות אפקט הריבית דריבית כך שהיינו מקבלים אחרי שנתיים 110,250 ש"ח. אגב, התשובה היא 4.88%.

ולשלב הבא: 100,000 ש"ח שהופכים בעוד 10 שנים ל-150,000 ש"ח. מה התשואה השנתית? הערכה גסה לא תעזור לנו כאן. למרות שמדובר בתוספת ממוצעת של 5,000 ש"ח בשנה, התשובה לא יכולה להיות 5% כי הריבית דריבית הייתה צריכה לפעול בכל הכוח לאורך עשור. למעשה, התשובה היא 4.14%.

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

ואיך אפשר בכלל להשוות בין כמה אפשרויות השקעה כשבכל אחת מהן יש הכנסות והוצאות בתאריכים שונים בסכומים שונים לאורך חיי ההשקעה?

בואו נפתח ביחד אקסל ונבדוק בעצמנו.

פונקציית XIRR, נעים להכיר

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

זו נוסחת XIRR:
XIRR(values,dates)‎

values – טור של סכומים.
dates – טור של תאריכים.

דוגמא: ביום 1.1.2017 נכנסו להשקעה 100,000 ש"ח. כעבור שנתיים, ביום 1.1.2019, נמשכו 110,000 ש"ח. נכניס את הנתונים לאקסל:

	A	B
1	01/01/2017	100,000
2	01/01/2019	-110,000
3	תשואה שנתית:	=XIRR(B1:B2,A1:A2)

תוצאה: 4.88%

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

שימושים אפשריים לפונקציית XIRR

תיק השקעות – אנחנו רוצים לבדוק מה התשואה השנתית שהשגנו בפועל בתיק שלנו. אפשר לבדוק על כל התיק ביחד: כל הפקדה לחשבון של התיק וכל משיכה ממנו תהיה שורה אחת באקסל. אפשר גם לבדוק על כל ני"ע בנפרד: כל קנייה או מכירה של אותו נייר תהיה שורה אחת באקסל.

דירה להשקעה – לפני שמתפתים לקנות דירה להשקעה "כדי שיהיה נכס ביד עם הכנסה חודשית", מומלץ לבדוק את המספרים: עלויות כולל הכל עם טווח ביטחון על שיפוצים ותיקונים, שכר דירה צפוי והמחיר שכנראה יהיה אפשר לקבל עליה בעתיד, נניח בעוד 10 שנים. לא לשכוח מס רכישה ומס שבח. אם אחרי כל החישובים מקבלים תשואה של אחוזים בודדים, הייתי שוקל דרכים יותר פשוטות להשקיע את הכסף.

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

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

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

לסיכום, לא תמיד פשוט לחשב לבד את התשואה השנתית, במיוחד כשיש הרבה פעילות של הכנסות והוצאות או כשיש עלויות מאחורי הקלעים. כדי לקחת החלטות נבונות, אפשר פשוט לפתוח אקסל, להכניס את הנתונים ולחשב תשואה שנתית עם פונקציית XIRR.

⇓ כתוב את התגובה שלך כאן ⇓

תגובות