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

[daat99]

שם טיפה ארוך

שמות של גיליונות בעברית יסבכו טיפה את הנוסחאות (שילוב של אנגלית ועברית תמיד מתבלבל).

יש לך התנגדות לשנות את השם של הגיליון עם הנתונים ההיסטוריים לשם קצר באנגלית כמו "Data"?

 

[חוקרת]

שיניתי.
(לידע כללי- אנגלית ומספרים זה גם מבלבל? נגיד Data 1928)

 

[daat99]

לידע כללי- אנגלית ומספרים זה גם מבלבל?

תגידי לי את איזו נוסחה מהנוסחאות הבאות יותר קריאה בשבילך:
בלי המספר:

=VLOOKUP(A2,Data!1:1048576,2,FALSE)

או עם המספר:

=VLOOKUP(A2,'Data 1928'!1:1048576,2,FALSE)

תשימי לב לצורך לתחום את שם הגיליון בתוך גרשיים בגלל שיש בו רווח.


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

 

[חוקרת]

עם המספר (שיניתי את שם הגליון לData 1871)
עמודה B בקובץ של רוברט שילר זה מחיר שנתי, איפה נמצא מחיר חודשי? (חיפשתי בגליון אבל לא מצאתי/הבנתי).
בקובץ שלו מופיע לי גליון אחד. אומרת את זה כדי לוודא שזה אכן כך ואין לי בעיה עם הצגת הקובץ.

 

[daat99]

עמודה B בקובץ של רוברט שילר זה מחיר שנתי, איפה נמצא מחיר חודשי?

בקישור שנתתי לך יש קובץ נוסף שמציג נתונים חודשיים תחת הכותרת:

U.S. Stock Markets 1871-Present and CAPE Ratio

בקובץ הזה יש גיליון בשם Data כאשר:

• בעמודה A יש תאריך חודשי (שנה וחודש)
• בעמודה B יש את מחיר המדד
• בעמודה C יש את הדיבידנד שחולק מוכפל ב-12 (אנחנו צריכים לחלק את המספר הזה ב-12)
• בעמודה G יש את תושאת אג"ח ממשלתי אמריקאי 10 שנים

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

 

[חוקרת]

1. טיפה קטנוני אבל איפה רשום השם של המדד? אני לא רואה שכתוב s&p 500
2.

בעמודה C יש את הדיבידנד שחולק מוכפל ב-12 (אנחנו צריכים לחלק את המספר הזה ב-12)

לעשות עמודה ליד שמחלקת ב12?
3. הוספתי גליון בשם 100% Stock
4. הקובץ מתעדכן אוטומטי כשרוברט מעדכן? הרי הורדתי אותו למחשב.

 

[daat99]

טיפה קטנוני אבל איפה רשום השם של המדד? אני לא רואה שכתוב s&p 500

בעמודה B בקובץ למעלה רשום S&P Composite שזה מייצג את השוק האמריקאי (דומה מאוד ל-SP500 ומבחינת הסימולציות שלנו אין הבדל מהותי בין השניים).

לעשות עמודה ליד שמחלקת ב12?

עדיף שלא בשביל שתוכלי לעדכן את הקובץ בעתיד בקלות.

3. הוספתי גליון בשם 100% Stock

בגיליון הזה את רוצה את הסימולציה?
השארת את הגיליון Data כמו שהוא?

4. הקובץ מתעדכן אוטומטי כשרוברט מעדכן? הרי הורדתי אותו למחשב.

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


בהנחה שאת משתמשת בקובץ המקורי של רוברט שילר עם הנתונים החודשיים בלי לשנות בו כלום (למעט הוספת הגיליון שציינת למעלה) תייצרי גיליון חדש בשם "Parsed".
בגיליון החדש תוסיפי בשורה #1 את העמודות הבאות:

• A: Year
• B: Month
  
• C: Stock Price
• D: Dividend

בשורה #2 תרשמי את הנוסחאות הבאות:
A:

=IF(Data!A9<>"",ROUNDDOWN(Data!A9,0),"")

B:

=IF(A2<>"",MOD(Data!A9*100,100),"")

C:

=IF(A2<>"",Data!B9,"")

D:

=IF(A2<>"",IF(Data!C9>0,Data!C9/12,D1),"")

אחרי שרשמת את הנוסחאות את צריכה לראות את המספרים הבאים בשורה #2:

1871, 1, 4.44, 0.021667

תסמני את ארבעת התאים משורה #2 שהכנסת אליהם מספרים ובלחיצה על הפינה הימנית התחתונה של התא הימני (אני משער שהגיליון הוא משמאל לימין כאן) תגררי את הנוסחאות כך שהן יימרחו על שורות נוספות שיתמלאו בנתונים נוספים.
תגררי את הנוסחאות עד לשורה 1800 בערך (הנתונים הנוכחיים של דצמבר 2016 צריכים להגיע לשורה 1753).

מה עשינו כאן?

• בעמודה A רשמנו נוסחה שאומרת שאם יש נתונים בעמודה A בגיליון ה-Data המקורי אז אנחנו רוצים להעתיק משם את המספר אבל לעגל אותו כלפי מטה למספר השלם הקרוב - בצורה הזו אנחנו מקבלים את השנה מהמספר 1871.01 למשל.
  תשימי לב שהנתונים לא נלקחים משורה 2 אלא משורה 9 וזה בגלל שאנחנו התחלנו את הנתונים בגיליון שלנו בשורה 2 אבל שילר התחיל בשורה 9.
  
• בעמודה B רשמנו נוסחה שאומרת שאם יש לנו שנה בעמודה הראשונה של אותה השורה אז אנחנו רוצים להכפיל את המספר המקורי מהקובץ של שילר ב-100 ואז לקחת את השארית של החלוקה ב-100
  בצורה הזו אנחנו מקבלים את החודש שמופיע אחרי הנקודה העשרונית.
• בעמודה C אנחנו שוב מתנים את השורה בקיום שנה בעמודה A של אותה השנה ולוקחים את מחיר המדד מעמודה B בגיליון המקורי כאשר אנחנו מתחילים גם כאן בשורה 9.
• בעמודה D אנחנו עושים משהו דומה למה שעשינו ב-C רק שהפעם אנחנו מחלקים את המספר מהקובץ המקורי ב-12 בשביל לקבל דיבידנד חודשי.
  כמו כן בגלל ששילר לא עדכן נתונים של הרבעון האחרון של 2016 עדיין אז כאשר אין נתונים על דיבידנדים בחודש מסוים אני משתמש בנתון של חלוקת הדיבידנדים מהחודש הקודם.

נעצב טיפה את הגיליון בשביל שהוא יהיה לנו יותר קריא:

• בעמודות A ו-B נבחר עיצוב של מספר ללא ספרות עשרוניות.
• בעמודות C ו-D נבחר עיצוב של "מטבע דולרי" עם שתי ספרות אחרי הנקודה.

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


אחרי שתייצרי את הגיליון הזה נמשיך לייצר גיליון נוסף לפני שנתחיל עם הסימולציה.

 

[חוקרת]

@daat99 נעשה.
+מחקתי את הגליון 100% stock

 

[daat99]

נעשה.

תייצרי גיליון נוסף עם השם "Input".
תמלאי בו את התאים הבאים:

A1: General
A2: First Investment Year
A3: Last Investment Year
A4: Initial Desired Price
A5: Monthly Investment

D1: Costs
D2: Purchase Commission
D3: Minimum Units Per Purchast
D4: Monthly Fee
D5: Dividend Tax

G1: Calculated Values
G2: Initial Stock Price
G3: Normalized Initial Stock Price

בנוסף צריך למלא ערכים ו/או נוסחאות בתאים הבאים (את הערכים את בוחרת בעצמך - את הנוסחאות תעתיקי):

B2:
שנת התחלת השקעה שאת רוצה לבדוק
B3:
שנת סיום ההשקעה שאת רוצה לבדוק - שנת הפרישה
B4:
המחיר של המדד שאת רוצה להגדיר ביום ההשקעה הראשון שלך - מספר נוח הוא 10.
B5:
הסכום החודשי שאת רוצה להפקיד לתיק ההשקעות במהלך שנות החיסכון עד לפרישה.

E2:
העמלה לפעולה שאת משלמת - אני  משלם 1.4 דולר.
E3:
כמות היחידות המינימלית שאת מוכנה לרכוש בכל פעולה - אם לא יהיה סכום מספק לרכוש כמות כזו של יחידות אז לא תבוצע רכישה באותו החודש והכסף יישאר בחשבון לחודש העוקב.
E4:
עלות חודשית למימון החשבון - במקרה שלי זה 0.
E5:
מס דיבידנדים שגובים ממך על אחזקה במדד - לתושבי ישראל שאינם אזרחים אמריקאיים זה 25% שמנוכה במקור לרשות המס האמריקאית.

H6: =VLOOKUP($B$2, Parsed!A:C,3,FALSE)
H7: =$B$4/H2

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

 

[חוקרת]

האם אני יכולה לרשום בעמודות A D G בעברית או שזה יהווה בעיה?
לגבי costs מה עם עמלות נוספות כמו המרה?
לגבי מס במקור.. אם אני אזרחית אמריקאית לרשום 0?
מה המשמעות של E6 וE7?

 

[FIYaacov]

לגבי מס במקור.. אם אני אזרחית אמריקאית לרשום 0?

הניכוי במקור באמת יהיה 0, אבל בפועל תשלמי 0, 15 או 20 לארה"ב (תלוי בסטטוס שלך ובמדרגת מס שלך שם), ותשלימי ל-25% לישראל, כך שבפועל תשלמי 25% אבל תיהני מדחיית מס של בין כמה חודשים לשנה (תלוי של איזה רבעון הדיבידנד)

 

[daat99]

האם אני יכולה לרשום בעמודות A D G בעברית או שזה יהווה בעיה?

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

לגבי costs מה עם עמלות נוספות כמו המרה?

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

לגבי מס במקור.. אם אני אזרחית אמריקאית לרשום 0?

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

מה המשמעות של E6 וE7?

מכיוון שאת הסימולציות שלנו אנחנו לא הולכים להתחיל ב-1871 אלא בשנים אחרות (כמו למשל ב-1987 אם אנחנו רוצים לדמות השקעה של 30 שנה עד היום) אז אנחנו צריכים לנרמל את מחיר המדד בצורה שאנחנו נוכל להשקיע בו.
ללא נירמול כזה אנחנו צריכים יותר מ-2000 דולר בשביל לרכוש יחידה אחת של המדד היום שזה אומר שבמשך כמה חודשים טובים אנחנו נחסוך כספים בלי להשקיע אותם בפועל במדד.
הנירמול הזה בא ואומר שאנחנו רוצים לדמות מחיר יחידת מדד התחלתי כלשהו ולהשקיע ביחידות שלמות לפי המחיר הזה.
תחשבי על זה כמו שהמחיר של יחידת SPY שמייצג את מדד SP500 הוא 1/10 לערך ממחיר המדד עצמו כך אנחנו למעשה "מנפיקים לעצמנו" ETF חדש שמחירו הוא 10 דולר ליחידה ביום הראשון לחיסכון שלנו (אם רשמת בתא B4 את המחיר 10 דולר למשל).

בתא E6 אנחנו מקבלים את המחיר האמתי של המדד בתאריך תחילת החיסכון (ינואר של השנה שרשומה בתא B2 למשל) ובתא E7 אנחנו מקבלים את המכפיל שלנו שבאמצעותו אנחנו יכולים להגיע ממחיר המדד בפועל למחיר של יחידת ה-ETF החדש שהמצאנו לעצמנו למעלה.

 

[חוקרת]

@FIYaacov התלבטתי אם לתייג אותך, תודה

@daat99 לא הבנתי את E6 וE7.

בוא נבדוק רגע שמבחינה טכנית הכל נכון.
אם אני רושמת 1871 בB2
בE6 מקבלת 4.44
כמו בעמודה C בגליון INPUT

אם אני רושמת 1916 אני מקבלת בE6
9.33.
בעמודה C בגליון INPUT כתוב 9.80
והפער גדל ככל שמתקרבים ל2016..

בEF כתוב #DIV/0! כי אין עדייןן נתון בH2

האם זה בסדר?

הבנתי את עניין הנירמול, שנתחיל מ10$ ולא ממחיר אמיתי שיכול להיות "מפלצתי" לצורך הסימולציה.

 

[daat99]

בוא נבדוק רגע שמבחינה טכנית הכל נכון.
אם אני רושמת 1871 בB2

מה רשמת ב-B4?

כשאצלי רשום 10 ב-B4 ו-1871 ב-B2 אז אני מקבל 4.44 ב-H2 ו-2.2522 ב-H3 וזה תקין.
תא H2 אומר שהמחיר בינואר 1871 היה 4.44 ואם תסתכלי בגיליון ה-Parsed שלנו אז את תראי שבינואר 1871 המחיר באמת היה 4.44.
תא H3 אומר שצריך להכפיל את המחיר של המדד ב-2.2522 בשביל שהמחיר של ינואר 1871 יגיע למחיר המנורמל של 10 דולר שבחרנו בתא B4.

כשאני משנה את תא B2 ל-1916 ומשאיר את B4 על 10 אז אני מקבל 9.33 בתא H2 ו-1.0718 בתא H3.
כשאני מתבונן בנתונים מגיליון ה-Parsed אני רואה שבינואר 1926 מחיר המדד היה 9.33 כמצופה.
אם נכפיל את מחיר המדד (9.33) ב-1.0718 (המספר שרשום בתא H3) אז נקבל את מחיר היעד שלנו שהוא 10 (שרשמנו בתא B4).

בEF כתוב #DIV/0! כי אין עדייןן נתון בH2

לאיזה תא הכוונה ב-EF?
לא צריכה להיות שגיאה בגיליון בשלב הזה.

 

[חוקרת]

טעיתי וכתבתי את הנסוחאות בE6 וE7 במקום בH6 וH7 מתנצלת.

אני מקבל 4.44 ב-H2 ו-2.2522 ב-H3 וזה תקין.

אבל בH2 וH3 אין כלום, לא נוסחא ולא מספר כלומר הם ריקות.
בהודעה שהנחית אותי איך למלא את התאים לא היתה התיחסות לH2 וH3

 

[daat99]

אבל בH2 וH3 אין כלום

התכוונתי ל-H6 ו-H7 בהתאמה - טעות סופר שלי.

 

[חוקרת]

התכוונתי ל-H6 ו-H7 בהתאמה - טעות סופר שלי.

אז את הנוסחא בH7 לתקן ל =$B$4/H6

אם כן אז זה יצא לי גם

כשאצלי רשום 10 ב-B4 ו-1871 ב-B2 אז אני מקבל 4.44 ב-H2 ו-2.2522 ב-H3 וזה תקין.

אוקי עכשיו הבנתי שH6 זה מחיר המדד באותה שנה ןH7 זה המכפיל כדי לקבל 10$. לא ברור לי עדיין במה נשתמש במכפיל.

 

[daat99]

לא ברור לי עדיין במה נשתמש במכפיל.

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

 

[חוקרת]

@daat99 הבנתי
מה השלב הבא?

 

[daat99]

מה השלב הבא?

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