אקסל צפי הכנסות מדיבידנדים

[סטרבו]

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

תודה רבה!

 

[סטרבו]

התחלתי קובץ.
הנחתי בו תשואת דיבידנד 2%, הפקדה חודשים של 700$ (2500 שקל בערך לפי שער נוכחי) וקצב צמיחה של 6% שנתי. עשיתי חישוב של דיבידנדים אחרי מס, ל20 שנה.
יצא קצת מבולגן. אשמח שתמצאו לי טעויות בחישוב או בחשיבה!

 

[daat99]

אני מקווה שאתה מסתדר עם אנגלית

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

תוסיף לקובץ שני גיליונות חדשים בשמות:

• Summary
• Dividends

נתחיל בגיליון Summary.
בתא A1 נרשום כותרת "Assumptions" שתייצג את הנחות המוצא שלנו.

בעמודות A ו-B נרשום את ההנחות הבאות כאשר הכותרות בעמודה A וההנחות בעמודה B:
לפי מספרי השורות:

1. 
   
2. Original Price: $100
3. Original Yield: 2%
4. Monthly Deposit: $700
5. Price Growth: 6%
6. Dividend Growth: 6%
7. Dividend Tax: 25%
8. Monthly Fee: $10
9. Commissions: $1.4

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


בעמודות F ו-G אנחנו נציג את התוצאות שלנו במתכונת הבאה:

1. Years Length: 20
2. Expected Yearly Dividend
3. Monthly
4. Expected Portfolio Value
5. Total Deposits
6. Total Costs

את הנוסחאות בתאים G2 עד G6 נמלא כך:

G2: =LOOKUP($G$1,Dividends!B:B,Dividends!G:G)*LOOKUP($G$1,Dividends!B:B,Dividends!E:E)
G3: =G2/12
G4: =LOOKUP($G$1,Dividends!B:B,Dividends!G:G)*LOOKUP($G$1,Dividends!B:B,Dividends!D:D)
G5: =LOOKUP($G$1,Dividends!B:B,Dividends!K:K)
G6: =LOOKUP($G$1,Dividends!B:B,Dividends!L:L)

כעת נעבור לגיליון "Dividends" ונמלא בוא את הנוסחאות שלנו.
בשורה 1 נרשום את הכותרות ובשורה 2 נרשום את הנוסחאות המתאימות:

A: Month
=IF(MOD(ROW($A1),12)=0,12,MOD(ROW($A1),12))

B: Year
=IF($A2<>"",ROUNDUP(ROW($A1)/12,0),"")

C: Start Cash
=IF(A2<>"",Summary!$B$4+IF(ISNUMBER(J1),J1,0),"")

D: Current Price
=IF(A2<>"",IF(ISNUMBER(D1),D1*POWER(1+Summary!$B$5,1/12),Summary!$B$2),"")

E: Current Dividend
=IF(A2<>"",IF(ROW(A2)=2,Summary!$B$2*Summary!$B$3,IF(A2=1,E1*(1+Summary!$B$6),E1)),"")

F: Purchased Stocks
=IF(A2<>"",(C2-Summary!$B$9-Summary!$B$8)/D2,"")

G: Total Stocks
=IF(A2<>"",F2+IF(ISNUMBER(G1),G1,0),"")

H: Dividends Received
=IF(A2<>"",IF(MOD(A2,4)=3,G2*E2/4,0),"")

I: Costs
=IF(A2<>"",Summary!$B$8+IF(F2<>0,Summary!$B$9,0)+IF(H2>0,H2*Summary!$B$7,0),"")

J: Cash End
=IF(A2<>"",C2+H2-I2-D2*F2,"")

K: Total Deposits
=IF(A2<>"",ROW(K1)*Summary!$B$4,"")

L: Total Costs
=IF(A2<>"",SUM(I$2:I2),"")

אחרי שרשמת את כל הנוסחאות בשורה 2 בעמודות A עד L תסמן את התאים A1 עד AL כקבוצה ותמרח את הנוסחאות על התאים שמתחתיהם (כשכל התאים בחורים צריך להיות לך ריבוע קטן בפינה השמאלית התחתונה של תא L2 אם הגיליון שלך מוכוון משמאל לימין ובפינה השמאלית התחתונה אם אתה בגיליון מימין לשמאל - תגרור את הריבועון הקטנטן הזה כלפי השורות שמתחתיו וזה יעתיק את הנוסחאות).

אם הצלחת למלא את הגיליון כמו הנתונים הללו אז אתה צריך לראות:

• בעמודה A את מספר החודש כאשר מתחילים בינואר בשורה 2
• בעמודה B את מספר שנת החיסכון כאשר מתחילים ב-1 והשנה מתחלפת בכל ינואר
• בעמודה C את סך הכספים שיש לך בתחילת כל חודש (כולל ההפקדות של אותו החודש והדיבידנדים שקיבלת בחודש הקודם - אם הייתה חלוקה שכזו)
• בעמודה D את מחיר המניה הנוכחי שצריך לעלות בכל חודש בצורה כזו שבמשך שנה הוא יעלה לפי המחיר הממוצע שהגדרת בגיליון ה-Summary
• בעמודה E את סך הדיבידנדים הרבעוניים כאשר מספר זה גדל בכל ינואר לפי מה שהגדרת
• בעמודה F את כמות המניות שרכשת בתחילת החודש עם הכספים שהפקדת והדיבידנדים שקיבלת
• בעמודה G את סך המניות שיש ברשותך כולל הרכישה האחרונה
• בעמודה H אתה תקבל דיבידנדים פעם ברבעון על כל המניות שברשותך כולל המניות שנרכשו בתחילת החודש
• בעמודה I אתה תראה את העלויות החודשיות שלך (עלות קבועה, עמלה במידה ורכשת מניות ומס דיבידנדים במידה וקיבלת דיבידנדים)
• בעמודה J אתה תראה כמה כסף נשאר לך בחשבון בסוף החודש לאחר הוצאות ולאחר קבלת דיבידנדים
• בעמודה K אתה תראה את הסכום המצטבר שהפקדת עד לאותו החודש כולל את ההפקדה של אותו החודש עצמו
• בעמודה L אתה תראה את סך ההוצאות שלך מרגע שהתחלת להשקיע (כל העמלות, עלויות קבועות ומיסוי דיבידנדים)

בגיליון ה-Summary אתה תוכל לרשום מספר שנה אחר בתא G1 ולראות בתאים G2-G6 את התוצאות של אותה השנה (במידה ואתה לא רואה תוצאות אז תבדוק בגיליון השני שיש לך מספיק שורות עד לדצמבר של אותה השנה - בשביל 20 שנה צריך לפחות 241 שורות בגיליון השני).

בהצלחה ותשאל אם משהו לא ברור.

נ.ב.
חישוב ה-YOC הוא חלוקת הדיבידנדים שאתה צפוי לקבל בסך ההפקדות שלך - תנסה להוסיף את זה בתאים F7-G7.

 

[סטרבו]

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

אתה רומז שהאקסל שלי בלאגן!?

חבל שאין אפשרות לסמן "אהבתי כל כך!". איזו השקעה מטורפת. תודה רבה רבה דעת.
בניתי את האקסל. נראה לי שיש טעות בעמודה C שהיא Start Cash. מוסיף את ההפקדה החודשית ומוסיף אליה את כל ההפקדות עד עכשיו.
במקום להוסיף את עמודה K להפקדה החודשית, צריך להוסיף את עמודה H.

 

[quesito]

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

 

[daat99]

אתה רומז שהאקסל שלי בלאגן!?

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

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

במקום להוסיף את עמודה K להפקדה החודשית, צריך להוסיף את עמודה H.

אתה צודק שישנה טעות אבל צריך להחליף את K ב-J ולא ב-H (תוקן במקור).

**זה מה שקורה כשמשנים דברים באקסל תוך כדי כתיבת הדרכה איך לבנות אותו**

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

למיטב הבנתי ( @StockDiv יכול להרחיב בנושא) התכנה עוזרת לבצע מעקב על התיק שלך/תיק וירטואלי.
האקסל הזה מנסה להריץ סימולציות שונות על סמך הנחות שלא בהכרח קשורות למציאות ההיסטורית שלנו.

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

 

[סטרבו]

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

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

תיקנתי גם אצלי, תודה רבה.
אני רוצה לשפר את הביצועים, האם כבודו יכול לארגן משבר כדי שנקנה במבצע?

 

[daat99]

האם כבודו יכול לארגן משבר כדי שנקנה במבצע?

הלוואי
קיוויתי שיהיה משבר בשבועות שאחרי שהקמתי את תיק דיבידעת אבל מר שוק לא הסכים

 

[ziv]

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

לא ירדת לסוף דעתי, לא נורא.

בניתי את האקסל מהשרשור הזה.

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

 

[daat99]

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

בשביל לעשות זאת בצורה גמישה רצוי להוסיף את ערך ההפקדה הראשונית לגיליון ה-summary.


נוסיף בשורה 10: (הכותרת בעמודה A והמספר בעמודה B עדיף בפורמט של מטבע בשביל הנוחות בעין)

Initial Deposit: 10000

לצורך הדוגמא רשמתי הפקדה ראשונית של 10 אלף, אתה תוכל לשנות את המספר הזה כאוות נפשך אחרי שנגיע לגיליון שעובד

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

C: Start Cash
=IF(A2<>"",if(row(c2)=2,Summary!$B$10,0)+Summary!$B$4+IF(ISNUMBER(J1),J1,0),"")

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

אין לי את האקסל זמין אז תגיד לי אם זה לא עובד לך.

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

 

[ziv]

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

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

File on MEGA

mega.nz

 

[daat99]

מה הבעיה בקובץ?
לי זה נראה תקין.

 

[ziv]

מה הבעיה בקובץ?
לי זה נראה תקין.

בגליון Summary :
F1 מראה נתונים על פי שנה ספיציפית או כמה סה"כ לפי הנתון שמוכנס?
אם אני מכניס 1 בG1 אני מקבל 7522 ב Expected Yearly Dividend
אם אני מכניס 30 בG1 אני מקבל 1722 ב Expected Yearly Dividend
לא הצלחתי להבין אם זה עובד או שלא הבנתי נכון.

בגליון Dividends :
בעמודה H הנתון פוחת עם הזמן זה לא אמור להיות הפוך ? (Dividends Received)
מתחיל לרדת משנה 7 והלאה

 

[daat99]

F1 מראה נתונים על פי שנה ספיציפית או כמה סה"כ לפי הנתון שמוכנס?

בגיליון הסיכום תא F1 זו כותרת ובתא G1 אתה רושם כמה שנים אתה מתכנן לחסוך עד לפרישה.
תא G2 מציג את צפי הדיבידנדים השנתי בשנת הפרישה הראשונה.

אם אני מכניס 1 בG1 אני מקבל 7522 ב Expected Yearly Dividend
אם אני מכניס 30 בG1 אני מקבל 1722 ב Expected Yearly Dividend
לא הצלחתי להבין אם זה עובד או שלא הבנתי נכון.

בעמודה H הנתון פוחת עם הזמן זה לא אמור להיות הפוך ? (Dividends Received)

שתי הבעיות הנ"ל נובעות מכך שבעמודה E יש לך את הנוסחה של עמודה F בטעות במוקם את הנוסחה שהוצגה בתחילת השרשור.
תחליף את הנוסחה שיש לך בעמודה E לנוסחה שהוצגה בתחילת השרשור.

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

 

[לופטי]

אני מקווה שאתה מסתדר עם אנגלית

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

תוסיף לקובץ שני גיליונות חדשים בשמות:

• Summary
• Dividends

נתחיל בגיליון Summary.
בתא A1 נרשום כותרת "Assumptions" שתייצג את הנחות המוצא שלנו.

בעמודות A ו-B נרשום את ההנחות הבאות כאשר הכותרות בעמודה A וההנחות בעמודה B:
לפי מספרי השורות:

1. 
   
2. Original Price: $100
3. Original Yield: 2%
4. Monthly Deposit: $700
5. Price Growth: 6%
6. Dividend Growth: 6%
7. Dividend Tax: 25%
8. Monthly Fee: $10
9. Commissions: $1.4

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


בעמודות F ו-G אנחנו נציג את התוצאות שלנו במתכונת הבאה:

1. Years Length: 20
2. Expected Yearly Dividend
3. Monthly
4. Expected Portfolio Value
5. Total Deposits
6. Total Costs

את הנוסחאות בתאים G2 עד G6 נמלא כך:

G2: =LOOKUP($G$1,Dividends!B:B,Dividends!G:G)*LOOKUP($G$1,Dividends!B:B,Dividends!E:E)
G3: =G2/12
G4: =LOOKUP($G$1,Dividends!B:B,Dividends!G:G)*LOOKUP($G$1,Dividends!B:B,Dividends!D:D)
G5: =LOOKUP($G$1,Dividends!B:B,Dividends!K:K)
G6: =LOOKUP($G$1,Dividends!B:B,Dividends!L:L)

כעת נעבור לגיליון "Dividends" ונמלא בוא את הנוסחאות שלנו.
בשורה 1 נרשום את הכותרות ובשורה 2 נרשום את הנוסחאות המתאימות:

A: Month
=IF(MOD(ROW($A1),12)=0,12,MOD(ROW($A1),12))

B: Year
=IF($A2<>"",ROUNDUP(ROW($A1)/12,0),"")

C: Start Cash
=IF(A2<>"",Summary!$B$4+IF(ISNUMBER(J1),J1,0),"")

D: Current Price
=IF(A2<>"",IF(ISNUMBER(D1),D1*POWER(1+Summary!$B$5,1/12),Summary!$B$2),"")

E: Current Dividend
=IF(A2<>"",IF(ROW(A2)=2,Summary!$B$2*Summary!$B$3,IF(A2=1,E1*(1+Summary!$B$6),E1)),"")

F: Purchased Stocks
=IF(A2<>"",(C2-Summary!$B$9-Summary!$B$8)/D2,"")

G: Total Stocks
=IF(A2<>"",F2+IF(ISNUMBER(G1),G1,0),"")

H: Dividends Received
=IF(A2<>"",IF(MOD(A2,4)=3,G2*E2/4,0),"")

I: Costs
=IF(A2<>"",Summary!$B$8+IF(F2<>0,Summary!$B$9,0)+IF(H2>0,H2*Summary!$B$7,0),"")

J: Cash End
=IF(A2<>"",C2+H2-I2-D2*F2,"")

K: Total Deposits
=IF(A2<>"",ROW(K1)*Summary!$B$4,"")

L: Total Costs
=IF(A2<>"",SUM(I$2:I2),"")

אחרי שרשמת את כל הנוסחאות בשורה 2 בעמודות A עד L תסמן את התאים A1 עד AL כקבוצה ותמרח את הנוסחאות על התאים שמתחתיהם (כשכל התאים בחורים צריך להיות לך ריבוע קטן בפינה השמאלית התחתונה של תא L2 אם הגיליון שלך מוכוון משמאל לימין ובפינה השמאלית התחתונה אם אתה בגיליון מימין לשמאל - תגרור את הריבועון הקטנטן הזה כלפי השורות שמתחתיו וזה יעתיק את הנוסחאות).

אם הצלחת למלא את הגיליון כמו הנתונים הללו אז אתה צריך לראות:

• בעמודה A את מספר החודש כאשר מתחילים בינואר בשורה 2
• בעמודה B את מספר שנת החיסכון כאשר מתחילים ב-1 והשנה מתחלפת בכל ינואר
• בעמודה C את סך הכספים שיש לך בתחילת כל חודש (כולל ההפקדות של אותו החודש והדיבידנדים שקיבלת בחודש הקודם - אם הייתה חלוקה שכזו)
• בעמודה D את מחיר המניה הנוכחי שצריך לעלות בכל חודש בצורה כזו שבמשך שנה הוא יעלה לפי המחיר הממוצע שהגדרת בגיליון ה-Summary
• בעמודה E את סך הדיבידנדים הרבעוניים כאשר מספר זה גדל בכל ינואר לפי מה שהגדרת
• בעמודה F את כמות המניות שרכשת בתחילת החודש עם הכספים שהפקדת והדיבידנדים שקיבלת
• בעמודה G את סך המניות שיש ברשותך כולל הרכישה האחרונה
• בעמודה H אתה תקבל דיבידנדים פעם ברבעון על כל המניות שברשותך כולל המניות שנרכשו בתחילת החודש
• בעמודה I אתה תראה את העלויות החודשיות שלך (עלות קבועה, עמלה במידה ורכשת מניות ומס דיבידנדים במידה וקיבלת דיבידנדים)
• בעמודה J אתה תראה כמה כסף נשאר לך בחשבון בסוף החודש לאחר הוצאות ולאחר קבלת דיבידנדים
• בעמודה K אתה תראה את הסכום המצטבר שהפקדת עד לאותו החודש כולל את ההפקדה של אותו החודש עצמו
• בעמודה L אתה תראה את סך ההוצאות שלך מרגע שהתחלת להשקיע (כל העמלות, עלויות קבועות ומיסוי דיבידנדים)

בגיליון ה-Summary אתה תוכל לרשום מספר שנה אחר בתא G1 ולראות בתאים G2-G6 את התוצאות של אותה השנה (במידה ואתה לא רואה תוצאות אז תבדוק בגיליון השני שיש לך מספיק שורות עד לדצמבר של אותה השנה - בשביל 20 שנה צריך לפחות 241 שורות בגיליון השני).

בהצלחה ותשאל אם משהו לא ברור.

נ.ב.
חישוב ה-YOC הוא חלוקת הדיבידנדים שאתה צפוי לקבל בסך ההפקדות שלך - תנסה להוסיף את זה בתאים F7-G7.

פשוט אדיר! הלכתי בדיוק לפי סד"פ הזה והכל עובד מעולה!

 

[daat99]

פשוט אדיר! הלכתי בדיוק לפי סד"פ הזה והכל עובד מעולה!

אני שמח לשמוע שההסבר עזר לך.

 

[אין כפית]

פשוט אדיר! הלכתי בדיוק לפי סד"פ הזה והכל עובד מעולה!

מה הסיכוי שאתה מעלה לכאן קישור לgoogle sheets עם האקסל הזה במצב read only כך שנוכל ליצור העתק שלו בקלות?

 

[daat99]

מה הסיכוי שאתה מעלה לכאן קישור לgoogle sheets עם האקסל הזה במצב read only כך שנוכל ליצור העתק שלו בקלות?

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

 

[Black Coffee]

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

היי. אשמח לקבל קישור ל google sheets עם האקסל הזה במצב read only כך שנוכל ליצור העתק שלו בקלות

תודה רבה!

 

[Black Coffee]

אמממ יש מצב בבקשה לקבל קישור לאקסל ?