בניית אקסל סימולציית כדאיות קרן השתלמות

[חוקרת]

אני לא שכחתי אותך - החיים מצריכים תשומת לב יתרה ואני נאלץ להפנות את תשומת לבי למקומות אחרים בימים הקרובים.
אני אמשיך את ההדרכה בהקדם.

כמובן

 

[daat99]

עדיין לא שכחתי אותך!

 

[daat99]

את יכולה בבקשה לשלוח לי את הקובץ העדכני שלך בשביל שנוכל להמשיך מאותה הנקודה?

 

[חוקרת]

את יכולה בבקשה לשלוח לי את הקובץ העדכני שלך בשביל שנוכל להמשיך מאותה הנקודה?

שלחתי לך במייל

 

[daat99]

קיבלתי את המייל ואני מאוד מתנצל שאני לא מצליח להגיע לזה עדיין.
הלחץ בעבודה אמור לרדת בצורה דרסטית במהלך אמצע/סוף מרץ - אני מקווה שזה עדיין יהיה רלוונטי מבחינתך.

 

[חוקרת]

@daat99 בלי לחץ, אני אחכה בסבלנות. תודה

 

[חוקרת]

בגליון parsed הוספנו עמודות:
D דבידנד devidend
שינינו את הדבידנד מדבידנד שנתי לדבידנד רבעוני

=IF(A3<>"",IF(MOD(B3,3)=0,IF(Data!C9>0,Data!C9/4,D2),0),"")

E מחיר מתואם Normalized Stock Price

=IF(A3<>"",C3*input!$H$3,"")

F דבידנד מתואם Normalized Dividend

=IF(A3<>"",D3*input!$H$3,"")

G הפקדה חודשית Deposits

=IF(A3<>"",IF(AND(A2<>input!$B$2,A3=input!$B$2),input!$B$6,0)+IF(AND(A3>=input!$B$2,A3<=input!$B$3),input!$B$5,0),"")

H עמלה חודשית Monthly Percentage Cost

=IF($A3<>"",IF(ISNUMBER(R2),R2*input!$E$5,0),"")

I הפקדה + שארית Starting Cash

=IF(A3<>"",IF(ISNUMBER(Q2),Q2,0)+G3-H3,"")

J יחידות לקנייה Can Purchase

=IF(A3<>"",IF(I3>input!$E$2,(I3-input!$E$2)/E3,0),"")

K יחידות מעוגל Purchases

=IF(A3<>"",IF(J3>input!$E$3,ROUNDDOWN(J3,0),0),"")

L עמלת קנייה Comissions

=IF(A3<>"",IF(K3>0,input!$E$2,0)+IF(AND(A3>=input!$B$2,A3<=input!$B$3),input!$E$4,0),"")

M סכום רכישה Cash Change From Purchases

=IF($A3<>"",K3*E3,"")

N כמות יחידות שניקנו בפועל Stocks Quantity

IF($A3<>"",IF(ISNUMBER(N2),N2,0)+K3,"")

O דבידנד שהתקבל Dividends Received

=IF($A3<>"",N3*F3,"")

P מס דבידנדים Dividend Tax

=IF($A3<>"",O3*input!$E$6,"")

Q שארית מזומן End Cash

=IF(A3<>"",I3-L3-M3+O3-P3,"")

R שווי תיק Portflio Value

=IF($A3<>"",N3*E3+Q3,"")

S כסף שהושקע Total invested Cash

=IF($A3<>"",G3+IF(ISNUMBER(S2),S2,0),"")

בגליון input הוספנו סיכום
A8 סיכום
A9 סך הפקדות

=LOOKUP(2,1/(Parsed!A:A=B3),Parsed!S:S)

A10 שווי תיק בסוף תקופה

=LOOKUP(2,1/(Parsed!A:A=B3),Parsed!R:R)

A11 תשואה מצטברת

=B10/B9

 

[חוקרת]

אוקי, אז מה הצעד הבא?

 

[daat99]

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

כהתחלה צריך להוסיף לגיליון ההגדרות תא חדש שמייצג את אחוז החשיפה הרצוי לאג"ח ותא מחושב שמייצג את אחוז החשיפה למניות ויהיה המשלים של החשיפה לאג"ח ל-100%.

• בתא D8 נרשום "אחוז חשיפה לאג"ח"
• בתא D9 נרשום "אחוז חשיפה למניות"
• את תא E8 נצבע בצהוב ונרשום בו לצורך הדוגמה 40%
• בתא E9 נרשום את הנוסחה:
  

  =1-E8

נעשה טיפה סדר בקובץ:

• נשכפל את גיליון parsed לגיליון חדש בשם Stocks.

• נחזור לגיליון ההגדרות (input) ונשנה את תאים B9 ו-B10 כך שבכל מקום שישנה הפניה לגיליון Parsed אנחנו נחליף אותה להפניה לגיליון Stocks (ישנם 4 אזכורים שצריך להחליף).
  אל תשכחי לוודא ששני התאים מתייחסים לגיליון Stocks החדש לפני שאת ממשיכה!

• נמחק מגיליון parsed המקורי את כל העמודות אחרי עמודה G כולל (נשאיר את עמודות A-F).

בגיליון Stocks החדש נשנה את הנוסחאות הבאות:

• בתא E3 נרשום:

=IF(A3<>"",Parsed!E3,"")

• בתא F3 נרשום:

=IF(A3<>"",Parsed!F3,"")

• כעת נשכפל את הנוסחאות על כל התאים בעמודות E ו-F.
  אם הנוסחאות נכונות אז הערכים שבתאים צריכים להיות זהים לערכים ממקודם!

• אחרי שביצענו זאת נמחק את עמודות C ו-D מגיליון Stocks.
  כל הערכים צריכים להישאר אותו הדבר!

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

 

[daat99]

נוסחה נוספת שכדאי להוסיף לגיליון ההגדרות היא נוסחה של "תשואה שנתית ממוצעת" CAGR:
בתא A12 נכתוב את הכותרת ובתא B12 את הנוסחה הבאה:

=(B11^(1/(B3-B2+1))-1)

אם הנוסחה נכונה אז כאשר תבדקי השקעה חד פעמית של 10 אלף שח ללא הפקדות שוטפות עם העלויות של אינטראקטיב ישראל בין השנים 1972-2016 אז את תקבלי:

• תיק בשווי של 517 אלף שח
• תשואה מצטברת של 5711.43%
• תשואה שנתית של 9.41%.

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

• תיק בשווי 785 אלף שח
• תשואה מצטברת של 7580.93%
• תשואה שנתית של 10.18%

בשל עלויות ומיסוי אנחנו מקבלים תשואה שהיא פחותה ב-1% בשנה שגורמת לתיק שלנו להתכווץ ברבע מול התיק התיאורטי.

 

[jones]

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

שאלה,
איך מייצרים באקסל שינוי רטרואקטיבי של כמות המניות הזמינות, הרי אם מורידים את ה 3% המניות עם חבות המס המינימלית אז צריך להפחית את כמות היחידות הזמינות לחישוב הבא (בשנה הבאה) , איך ניתן לבצע את זה באקסל ?

 

[daat99]

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

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

לצורך מענה לשאלתך כרגע (ואנא המנע מלהמשיך את הדיון בשרשור הזה):
לא ניתן לסמלץ סדר מכירת ניירות בצורה אוטומטית באקסל - זו פעולה שאתה צריך לבצע ידנית.

זו הסיבה שהבדיקה הזו כל כך קשה, ארוכה ומסובכת.

 

[חוקרת]

=1-E9

האם הכוונה 1 מינוס E8? כי זה תא E9

G כולל (נשאיר את עמודות A-E).

את עמודה F להשאיר או למחוק?

נחזור לגיליון ההגדרות (input) ונשנה את תאים B9 ו-B10 כך שבכל מקום שישנה הפניה לגיליון Parsed אנחנו נחליף אותה להפניה לגיליון Stocks (ישנם 4 אזכורים שצריך להחליף).
אל תשכחי לוודא ששלושת התאים מתייחסים לגיליון Stocks החדש לפני שאת ממשיכה!

B9 B10 ומה התא השלישי?

תשואה שנתית של 9.41%.

יצא לי 0.094 האם להשאיר את הנוסחא ככה או לכפול במאה?

בשל עלויות ומיסוי אנחנו מקבלים תשואה שהיא פחותה ב-1% בשנה שגורמת לתיק שלנו להתכווץ ברבע מול התיק התיאורטי.

לא הבנתי את המשפט הזה.

 

[daat99]

האם הכוונה 1 מינוס E8? כי זה תא E9

אכן.

את עמודה F להשאיר או למחוק?

משאירים את F.

B9 B10 ומה התא השלישי?

טעות סופר - רק שני תאים.

יצא לי 0.094 האם להשאיר את הנוסחא ככה או לכפול במאה?

תשני את הפורמט של התא לאחוזים עם שתי ספרות אחרי הנקודה.
0.094=9.4%.

לא הבנתי את המשפט הזה.

התשואה השנתית הממוצעת ירדה מ-10.18% ל-9.41% שזה מהווה פער תשואת של פחות מ-1% בממוצע שנתי.
התשואה הכוללת שלנו לעומת זאת ירדה מ-7580.93% ל-5711.43% שזה מהווה הפסד של רבע מהתשואה המצטברת לכל התקופה.

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


*כל הטעויות תוקנו בהודעה המקורית - תודה.

 

[חוקרת]

@daat99 אוקי, הבנתי.
מה השלב הבא?