בניית אקסל סימולציית כדאיות קרן השתלמות
- פותח הנושא חוקרת
- פורסם בתאריך
הבדל בינינו בעמודה C הוא שאני ביצעתי רכישה של יחידות שלמות בלבד
- אבל עמודה C זה מחיר ליחידה איך זה יכול להיות מושפע מכמות היחידות? כמות היחידות מושפעת מהמחיר.
בעמודה C עשיתי את הנוסחא:
מחיר יחידה בשנה הקודמת + (מחיר יחידות בשנה הקודמת כפול אחוז השינוי במחיר שבעמודה B), האם עשית זאת בדרך אחרת?
- כמות היחידות בעמודה D, אתה מתכוון שבעמודה D צריך לעגל למטה
ולטפל בשארית?
ובמקביל זכרתי כמה מזומן נשאר לי בשביל להשתמש בו ברכישה הבאה
בשנת 1970 למשל אני רכשתי 1098 יחידות ונשארתי עם עודף בעוד את רכשת 1089.8 יחידות ולא נשארת עם עודף.
בשנת 1971 אני רכשתי 1053 יחידות כי השתמשתי בעודף משנת 1970 ואת רכשת 1052.3 יחידות.
- אז להוסיף עמודה של שארית יחידות?
- אז ב1971 רכשת 1053 ונשאר לך עודף של 1.
האם אתה עושה את זה ידני או משתמש בנוסחא שמתחשבת בשארית של שנה קודמת, מעגלת את כמות היחידות,
ואת השארית הנוכחית שמה בצד?
(בשלב הזה את יכולה להזין את עמלות הפעולה שלך בשביל לקבל סימולציה מציאותית שכוללת גם עמלות).
תוכל בבקשה לפרט טכנית איך עושים את זה?
- אבל עמודה C זה מחיר ליחידה איך זה יכול להיות מושפע מכמות היחידות? כמות היחידות מושפעת מהמחיר.
בעמודה C עשיתי את הנוסחא:
מחיר יחידה בשנה הקודמת + (מחיר יחידות בשנה הקודמת כפול אחוז השינוי במחיר שבעמודה B), האם עשית זאת בדרך אחרת?
- כמות היחידות בעמודה D, אתה מתכוון שבעמודה D צריך לעגל למטה
ולטפל בשארית?
ובמקביל זכרתי כמה מזומן נשאר לי בשביל להשתמש בו ברכישה הבאה
בשנת 1970 למשל אני רכשתי 1098 יחידות ונשארתי עם עודף בעוד את רכשת 1089.8 יחידות ולא נשארת עם עודף.
בשנת 1971 אני רכשתי 1053 יחידות כי השתמשתי בעודף משנת 1970 ואת רכשת 1052.3 יחידות.
- אז להוסיף עמודה של שארית יחידות?
- אז ב1971 רכשת 1053 ונשאר לך עודף של 1.
האם אתה עושה את זה ידני או משתמש בנוסחא שמתחשבת בשארית של שנה קודמת, מעגלת את כמות היחידות,
ואת השארית הנוכחית שמה בצד?
(בשלב הזה את יכולה להזין את עמלות הפעולה שלך בשביל לקבל סימולציה מציאותית שכוללת גם עמלות).
תוכל בבקשה לפרט טכנית איך עושים את זה?
נערך לאחרונה ב:
אז השלב הבא זה להשוות את שני התרחישים, אחד שמנזילים קרן השתלמות ואחד שלא?
לפני שאת קופצת להשוואת שני תרחישים מורכבים שמצריכים ניהול במקביל ואיזונים של שני תיקים בכל חלק (בשניהם יש גם תיק ממוסה וגם תיק חייב וצריך לבצע פעולות איזון/משיכה שמתחשבות בשניהם)
כדאי שתבצעי בדיקה מלאה של מה קורה אם את מחליטה להתחיל למשוך כספים לפי כלל ה-3% בשנה כלשהי בדרך.
כלומר שבמקום למשוך 10,000 קבוע, לשנות את זה ל3% מגודל התיק כן?
אז ליד עמודה D שמחשבת כמה יחידות לקנות אם מוסיפים 10000 שח לתיק, לעשות עמודה חדשה של גודל התיק?
וצריך לחשב גם ריבית דריבית וזה a whole new level
אני מציע שתייצרי באותו הקובץ גיליון חדש ותכניסי אליו את הנתונים משנת 1928כפי שהם מוצגים כאן.
אחרי שתייצרי לעצמך קובץ אקסל שמשווה תיק של 100% מניות לפי חבויות מס שונות (FIFO מול חבות מס מינימלית)
לעשות בעצם את ההשוואה שעשינו במשיכה ב2015 על כל שנה מ1928?
יהיה לך יותר קל להתקדם לתיק שמצריך איזון בין מניות לאג"חים עם אותן חבויות מס שונות ושם הפער יגדל בצורה שקשה יהיה להאמין שאין לנו טעויות.
לא הבנתי את הכוונה באותן חבויות מס שונות ושם הפער יגדל, אבל כנראה שיש כמה שלבים לפני שלא הבנתי,אז אניח לזה כרגע.
לפני שאת קופצת להשוואת שני תרחישים מורכבים שמצריכים ניהול במקביל ואיזונים של שני תיקים בכל חלק (בשניהם יש גם תיק ממוסה וגם תיק חייב וצריך לבצע פעולות איזון/משיכה שמתחשבות בשניהם)
כדאי שתבצעי בדיקה מלאה של מה קורה אם את מחליטה להתחיל למשוך כספים לפי כלל ה-3% בשנה כלשהי בדרך.
כלומר שבמקום למשוך 10,000 קבוע, לשנות את זה ל3% מגודל התיק כן?
אז ליד עמודה D שמחשבת כמה יחידות לקנות אם מוסיפים 10000 שח לתיק, לעשות עמודה חדשה של גודל התיק?
וצריך לחשב גם ריבית דריבית וזה a whole new level
אני מציע שתייצרי באותו הקובץ גיליון חדש ותכניסי אליו את הנתונים משנת 1928כפי שהם מוצגים כאן.
אחרי שתייצרי לעצמך קובץ אקסל שמשווה תיק של 100% מניות לפי חבויות מס שונות (FIFO מול חבות מס מינימלית)
לעשות בעצם את ההשוואה שעשינו במשיכה ב2015 על כל שנה מ1928?
יהיה לך יותר קל להתקדם לתיק שמצריך איזון בין מניות לאג"חים עם אותן חבויות מס שונות ושם הפער יגדל בצורה שקשה יהיה להאמין שאין לנו טעויות.
לא הבנתי את הכוונה באותן חבויות מס שונות ושם הפער יגדל, אבל כנראה שיש כמה שלבים לפני שלא הבנתי,אז אניח לזה כרגע.
- הצטרף
- 22/11/15
את צודקת - התייחסתי לעמודה D ולא לעמודה C -טעות שלי.
בכל אופן המספרים של התשואה הם אצלי מספרים מחושבים (כלומר עם רמת דיוק גבוהה יותר מ-2 ספרות אחרי הנקודה) ולכן התוצאות אצלי טיפה שונות עד כדי עיגול.
אם ניקח למשל את התשואה של שנת 1969 אז אצלך רשום -8.24% בעוד שאצלי זה מחושב ע"י הנתונים הבאים:
- מחיר בסוף 1968: 103.86
- מחיר בסוף 1969: 92.06
- דיבידנד שחולק במהלך 1969: 3.24
- מחיר כולל דיבידנד (ללא מס) בסוף 1969: 92.06+3.24=95.3
- שינוי שווי במהלך שנת 1969: 95.3 חלקי 103.86 = 0.91758136 (לצורך התגובה המספר הזה מעוגל ברמת דיוק של 8 ספרות אחרי הנקודה העשרונית - באקסל הוא לא מעוגל בצורה יזומה אלא מוגבל ליכולות התוכנה עצמה).
- 1 פחות שינוי שווי כפול 100: 8.241864% הפסד בשנה.
כפי שציינתי אני מעדיף לבצע חישובים מדוייקים שכוללים גם מיסוי דיבידנדים במקור ולכן אני מפריד את הנתונים הגולמיים לדיבידנד ותשואה כך שאני יכול לחשב את מה שאני צריך באותו רגע נתון ברמת דיוק מקסימלית.
זה מה שהתייחסתי אליו קודם - כן.
לא של שארית יחידות אלא של שארית מזומן.
עודף של 1.217325 דולר - כן.
ברכישות אני משתמש בנוסחה כי אני לא צריך לבצע בדיקות שונות בהתאם לספר הפקודות.
בפעולות המשיכה צריך לעבוד יותר קשה כי שם צריך להחליט מה מחיר הבסיס של היחידות שמוכרים לפי סדר המכירה הנבחר ובשביל זה אנחנו צריכים ספר אחזקות שאותו קשה מאוד עד כדי בלתי אפשרי להכיל בנוסחה רגילה.
כן - אני סוכם אותה עם ההפקדה של החודש הבא בשביל להחליט אם אני יכול לבצע פעולת רכישה בחודש העוקב או לא וכמה יחידות שלמות אני יכול לרכוש.
זה מסבך טיפה את הנוסחה אז בשלב הזה הייתי מתעלם מהעלות הזו (או פשוט מאפשר לחשבון להיכנס למינוס עד כדי עלות פעולה).
כשהקובץ יהיה מוכן (לפני שנתחיל לנהל ספר פקודות) נוכל לחזור לנקודה הזו ולשדרג את הנוסחאות שלך בשביל לתמוך בעמלות פעולה גם כן (אצלי אני דורש גם סכום מינימלי לביצוע פעולה בנוסף לקיום העמלה שזו דרישה יותר מציאותית שגם משפיעה על התוצאות).
כשתהיה לך סימולציה מוכנה שמייצגת את הגדילה של התיק (נוסיף לה את עמלות הפעולה למשל) אז תחשבי מה המספר שמייצג 3% משווי התיק.
המספר הזה זה המספר שאת צריכה למשוך מתיק ההשקעות שלך בכל שנה כאשר בגיליון אחד את מבצעת את המשיכה לפי FIFO ובשני לפי חבות מס מינימלית.
עד כאן זו ההשוואה המופשטת של חשבון שמורכב מ-100% מניות ללא איזונים בדרך, ללא מיסוי דיבידנדים בכלל וללא דמי ניהול.
מכאן אנחנו נצטרך לחזור על ההשוואה (עם הידע שנצבור עד אז) ולהתחיל להשוות את החשבונות השונים עם איזונים.
כשנסיים את ההשוואה של החשבונות שמאזנים את כבר תוכלי לראות פער אפילו יותר גדול ממה שתקבלי בהשוואה הראשונה.
לא בהכרח - זה נתון שאת יכולה לחשב בסוף הסימולציה בלי לשמור אותו בעמודה נפרדת (אני כן שומר אותו בעמודה נפרדת אבל זה לצורך ויזואלי ולא לצורך חישובי).
זה לא קשור לסימולציה הזו.
לא - תזיני את הנתונים מ-1928 ותבחרי שרירותית "שנת פרישה" כלשהי.
כשיש לך את כל הנתונים הללו עם הנוסחאות את יכולה להתחיל את החישוב של ההשקעות בכל שנה שאת רוצה בלי לייצר גיליון חדש.
את יכולה למשל להגדיר שאת מתחילה להשקיע מ-1950 ופורשת ב-1990.
בצורה הזו את תתחילי לרכוש מניות ב-1950 (למרות שיהיו לך מחירי מניות מ-1928) וכשתגיעי ל-1990 תוכלי להתחיל ולמשוך מתיק ההשקעות שלך תוך כדי תנודות השוק והתחשבות במנגנוני המיסוי השונים (FIFO מול חבות מס מינימלית).
חבויות מס שונות: מכירה לפי FIFO או חבות מס מינימלית.
הפער יגדל בצורה כל כך משמעותית כך שאת לא תביני מה קורה מול העיניים שלך ותהיי בטוחה שיש לך טעות בחישוב.
זו הסיבה המרכזית שישנה חשיבות גדולה מאוד לכך שאת תייצרי את האקסל בעצמך בשביל שתוכלי להבין שאין לך טעויות וזה באמת הפער בפועל.
כדי לא להתבלבל ולדעת שזה מינוס 8.241864 ולא פלוס איזו פעולה נדרשת?
אני מבינה שאי אפשר לשלם בשארית יחידות אלא במזומן, אז איך הופכים את שארית היחידה למזומן?
נגיד, בשנת 1970 רכשנו 1089 יחידות עם שארית יחידה של 0.8.
אז כדי להפוך את שווי השארית למזומן עושים 0.8 כפול מחיר יחידה שהיא 9.176 יוצא 7.3 דולר?
לפי זה
מניחה שלא כך עושים..
נערך לאחרונה ב:
ספר האחזקות זה התיעוד של הפעולות שלנו בתיק? כדי שנדע איזו יחידה בדיוק מתי קנינו ומתי למכור אותה?
ומה זה
אבל בדוגמא שלנו זה לא חודשי אלא שנתי
כי מתיחסים לא ללשווי התיק כולל רווחים אלא רק כמה כסף יש בתיק?
- הצטרף
- 22/11/15
מחסירים מהמספר 0.91758136 את המספר 1 ומקבלים (מינוס) 0.08241864.
את יכולה להכפיל את השארית במחיר המניה או לחלופין:
למשל אם היו לך 10,000 שח ורכשת 1089 יחידות עם מחיר X.
מחסירים את 1089 כפול X מהמספר 10,000 ואז מקבלים את השארית כמזומן.
למה לא?
מה את חושבת שצריך לעשות במקום?
כן.
במקרה שלנו יהיו לנו 1000 יחידות שנרכשו ב-1969 עם מחיר של 10 ו-1089 שנרכשו ב-1970 עם מחיר של 9.175863 למשל.
בדוגמה הראשונית זה באמת שנתי.
כשתגיעי להשוואה של קרן השתלמות מול חשבון ממוסה את תצטרכי לחשב לפי הפקדות חודשיות ולא שנתיות.
זה בגלל שאנחנו סוכמים את כמות המניות שמוחזקות בתיק (ובגלל שהנחת שהדיבידנדים מושקעים בחזרה במדד ללא מיסוי במקור).
רווחי ריבית דריבית רלוונטיים כאשר את מקבלת דיבידנד כמזומן ואז את צריכה להשקיע אותו מחדש אבל אפילו במקרה הזה את לא באמת צריכה להתייחס אליו בצורה מפורשת כי זה מתקבל בצורה אוטומטית מדרך החישוב של התשואות וההשקעה.
כי ב1971 יצא לי 3.2 דולר ולא 1.2 כמו אצלך.
אז בשנה הבאה עושים 10,000 + השארית במזומן חלקי מחיר למניה?
כלומר מקודם לא התיחסתי לשארית, לעדכן את המסמך כן להתייחס לזה?
נערך לאחרונה ב:
- הצטרף
- 22/11/15
ב-1970 רכשת 1089 יחידות ונשארת עם עודף של 7.48531?
בשנת 1971 יש לי 10007.48531 דולר ומחיר יחידה עומד על 9.502629 כך שאני רוכש 1053 יחידות.
1053 כפול 9.502629 זה 10006.268337.
10007.48531 פחות 10006.268337 יוצא לי עודף של 1.216973.
הפקדה חודשית/שנתית (10,000) ועוד שארית מזומן מהחודש/השנה הקודמת - הסכום של שניהם חלקי מחיר מניה נוכחי מעוגל כלפי מטה.
כן.
צעד צעד אנחנו נבנה את המסמך המלא.
עד כמה זה מפריע כרגע שהמספרים שלי לא מדויקים כמו שלך כי השתמשתי באחוז המעוגל של הממוצע של הS&P?
ב1971
מחיר יחידה אצלי - 9.5026656 קנינו 1053 עם שארית 1.
אם אני עושה 1 * 9.5... יוצא לי 1 דולר.
אם אני עושה 10,000 פחות (1053 * 9.5..) יוצא לי מינוס 6.3 דולר.
*עריכה*
זה לא 10000 פחות זה 10007~ פחות.
ב1971
מחיר יחידה אצלי - 9.5026656 קנינו 1053 עם שארית 1.
אם אני עושה 1 * 9.5... יוצא לי 1 דולר.
אם אני עושה 10,000 פחות (1053 * 9.5..) יוצא לי מינוס 6.3 דולר.
*עריכה*
זה לא 10000 פחות זה 10007~ פחות.
נערך לאחרונה ב:
- הצטרף
- 22/11/15
זה לא - פשוט יהיו לנו תוצאות טיפה שונות אבל ממש לא בצורה מהותית.
מה שיותר חשוב אלו הנוסחאות עצמן ודרכי החישוב.
נכון:
אז השינויים שעשיתי עד כה בקובץ:
1. את השורה של השוואת צורות מיסוי העברתי ליד השנה של 2015 (שורה 51)
2. נעלתי את שורות 1-3 כדי שהכותרות לא יעלמו כשמגוללים למטה
3. הוספתי עמודה של כמות יחידות מעוגלת, עמודה של שארית יחידה ועמודה של הערך המזומן של שארית היחידה.
4. את שארית המזומן הוספתי ל10,000 שח לקנייה של שנה הבאה
5. בשלב זה שיניתי את הנוסחאות בעמודה D שהיה 10,000 כערך קבוע ושיניתי את זה ל10,000 כערך קבוע + שארית מזומן משנה קודמת.
6.הוספתי גיליון עם דוגמא לחישוב מדויק של שינוי אחוז מדד הS&P בשנה כדי שיהיה לשלב מאוחר יותר
7. הוספתי גיליון מ1928
כל שאר העמודות, סכום רכישה וכמות יחידות זה יהיה לפי הבחירה השרירותית של שנת תחילת השקעה ושנת פרישה?
1. את השורה של השוואת צורות מיסוי העברתי ליד השנה של 2015 (שורה 51)
2. נעלתי את שורות 1-3 כדי שהכותרות לא יעלמו כשמגוללים למטה
3. הוספתי עמודה של כמות יחידות מעוגלת, עמודה של שארית יחידה ועמודה של הערך המזומן של שארית היחידה.
4. את שארית המזומן הוספתי ל10,000 שח לקנייה של שנה הבאה
5. בשלב זה שיניתי את הנוסחאות בעמודה D שהיה 10,000 כערך קבוע ושיניתי את זה ל10,000 כערך קבוע + שארית מזומן משנה קודמת.
6.הוספתי גיליון עם דוגמא לחישוב מדויק של שינוי אחוז מדד הS&P בשנה כדי שיהיה לשלב מאוחר יותר
7. הוספתי גיליון מ1928
צריך שמ 1928-2016 למלא בתור בסיס רק את עמודות שנה, שינוי באחוזים ומחיר למניה?
כל שאר העמודות, סכום רכישה וכמות יחידות זה יהיה לפי הבחירה השרירותית של שנת תחילת השקעה ושנת פרישה?
- הצטרף
- 22/11/15
כן.
אם את רוצה את יכולה לקחת את הסימולציה צעד אחד קדימה ולהתחשב אפילו בדיבידנד שמחולק (כך שתוכלי להתייחס למס דיבידנדים במקור בעתיד גם בתוך החשבון הפטור ממס כי המיסוי הולך ל-IRS ואין עליו פתור לאזרח ישראלי שאינו אזרח אמריקאי).
אם את רוצה לעשות זאת את יכולה לגשת לקישור הזה וללחוץ שם על הקישור הבא בשביל להוריד קובץ אקסל של רוברט שילר עם מידע שנתי על ה-SP500 ועל אג"ח 10 שנים
כן.
התוכן שלהן יהיה אותן הנוסחאות שמשתמשות במידע "מהשורה הקודמת" אבל בשורה הראשונה תזיני 0 ולכן זה יהיה ריק.
בשורה שמייצגת את השנה שאת רוצה להתחיל "להשקיע" בה את מזינה את ההשקעה הראשונה ואז כל הנוסחאות ימלאו את שאר התאים בנתונים.
ברור לי שמה שאמרתי עכשיו נראה מפחיד אבל את תגיעי לשם.
בשלב ראשון נבנה את האקסל עם רמת הפירוט של נוסחאות שאת רוצה להגיע אליה כך שאנחנו מתחילים בשנה הראשונה שיש לנו עליה מידע ואחר כך נשדרג את היכולות שתיארתי בשביל שתוכלי לשנות את הבדיקה שלך בקלות.
בגליון שמתחיל מ1969 עשינו שהמחיר הראשון הוא 10 דולר ליחידה.
בגליון מ1928 לשנות את זה ולכתוב למחירים האמיתיים?
הכוונה, שבנוסף "לבסיס הנתונים" (שנה, מחיר יחידה, דבידנד ותשואה) שכתבנו, להוסיף את הנוסחאות של העמודות 'סכום לרכישה', 'כמות יחידות לקניה' (ואת ההרחבות על השארית), אבל להשאיר אותן ריקות כלומר ללא ערכים?
מה צריך לעשות עם הנתון של הדבידנד, להוסיף אותו למחיר המניה וזה ישפיע על התשואה?
- הצטרף
- 22/11/15
כן.
לחלופין את יכולה להשתמש בנתונים של רוברט שילר שמפרטים גם דיבידנד שנתי ולא רק תשואה (הם מתחילים לפני 1928).
כן אבל לדעתי כדאי לך לעשות זאת בגיליונות נפרדים באותו הקובץ.
בגיליון אחד יהיו לך הנתונים ההיסטוריים (מחירי המדד, דיבידנדים שחולקו וכו') ובגיליון אחר נעשה שימוש במידע הנ"ל ע"י נוסחאות VLOOKUP נדבר עליהן בהמשך ושם נוסיף את העמודות של סכום לרכישה, כמות יחידות וכו'.
אם את רוצה לדמות את המציאות אז את צריכה להוסיף אותו ל-"הפקדות" של המשקיע בניכוי מס דיבידנדים במקור ולא לתשואה עצמה.
אוקי, עשיתי גליון של נתונים היסטורים מ1928 שכולל שנה, מחיר יחידה, ודבידנד שחולק, וגליון נוסף עם אותם נתונים בשביל סימולציות.
הפקדות הכוונה הסכום בו אני קונה בשנה הבאה? (10,000 + שארית מזומן + דבידנד בניכוי מס)?
להוסיף גם עמודה על נתוני אגח?
הסתכלתי מה זה הנוסחא הזאת, אם הבנתי נכון חיפוש נתונים ספציפים מתוך מאגר?
אני לא יודעת מה רמת הפירוט שאני רוצה להגיע אליה עדיין, תוך כדי הבנייה אני לומדת דברים חדשים. בוא נניח כרגע שכמה שיותר מדויק
- הצטרף
- 22/11/15
בואי ונתמקד רק בגיליון הזה.
האם את יכולה לתאר אילו עמודות יש לך ובאיזו שורה נמצאים הנתונים של השנה הראשונה?
כן.
צריכה להיות עמודה כזו בגיליון עם הנתונים ההיסטוריים.
כן.
במקום לשכפל את הנתונים הההיסטוריים אנחנו נמשוך אותם בצורה אוטומטית.
My Favoriteבוא נניח כרגע שכמה שיותר מדויק
A- שנה
B- תשואה שנתית של S&P 500
C- מחיר יחידה
D- דבידנד שנתי
E- אגח ממשלתי ארוך
הנתונים מתחילים משנת 1928 בשורה 4
1928 נתונים היסטורים