חישוב תשואת דיבידנד ב-Google Sheets: שני פתרונות

חישוב תשואת דיבידנד ב-Google Sheets

כדי לחשב את תשואת הדיבידנד ב-Google Sheets, עליך לבנות את הנתון בעצמך. הפונקציה GOOGLEFINANCE מחזירה מחיר, EPS, P/E, שווי שוק ורשימה ארוכה של שדות ציטוט אחרים, אך תשואת דיבידנד אינה נמנית עמם: =GOOGLEFINANCE("AAPL","yield") מחזירה #N/A, וכל איות אחר של המילה אינו מניב תוצאה. קיימים שני פתרונות עוקפים מקובלים. ניתן לחשב תשואה עוקבת (trailing yield) מתוך עמודת דיבידנדים שאתה מנהל בגיליון, או להקליד את שיעור הדיבידנד העתידי המוצהר (forward rate) לתא ולבצע חילוק.

מדוע ל-GOOGLEFINANCE אין מאפיין של תשואת דיבידנד

המאפיינים של הפונקציה מתחלקים לשתי משפחות. קיימים שדות של ציטוטים בזמן אמת, הנמשכים באמצעות שני ארגומנטים: price, volume, pe, eps, marketcap, high52, changepct ועוד כעשרה נוספים. לאחר מכן קיימים שדות של מחירים היסטוריים, הנמשכים באמצעות תאריך או טווח תאריכים: open, high, low, close, volume. נתוני דיבידנד אינם שייכים לאף אחת מהמשפחות הללו. אין מאפיין שמחזיר תשלום או היסטוריית חלוקות, מה שמחייב את השגת המונה של התשואה ממקור חיצוני לפונקציה.

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

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

SELECT
    toString(ex_dividend_date)                     AS ex_date,
    formatDateTime(ex_dividend_date, '%b %e, %Y')  AS ex_date_label,
    round(toFloat64(payment), 4)                   AS cash_amount,
    round(toFloat64(payment) * 4, 4)               AS annualized_run_rate
FROM
(
    SELECT
        ex_dividend_date,
        max(cash_amount) AS payment
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND ex_dividend_date >= today() - 1120
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
)
ORDER BY ex_dividend_date

קוקה-קולה שילמה $0.46 למניה ב-Sep 14, 2023 ו-$0.53 ב-Jun 15, 2026, סך של 12 תשלומים לאורך התקופה. השורה השנייה בתרשים היא כל תשלום כפול ארבע: השיעור השנתי המשתמע אם התשלום של אותו רבעון היה חוזר על עצמו לאורך כל השנה. הוא עולה פעם בשנה ונשאר קבוע ביניהן. העלייה המדרגתית הזו היא המקום שבו רוב התשואות בגיליונות אלקטרוניים משתבשות. התיעוד המלא נמצא ב-היסטוריית הדיבידנדים של קוקה-קולה.

שתי דרכים לקבלת תשואת דיבידנד ב-Google Sheets

השיטה הראשונה היא תשואה עוקבת (trailing yield) המחושבת מטור עצמאי. הזינו את תאריכי ה-ex-dividend date בעמודה A ואת המזומן למניה בעמודה B, שורה אחת לכל תשלום, ולאחר מכן:

• סך מצטבר ל-12 חודשים: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• מחיר חי: =GOOGLEFINANCE("KO","price")
• תשואה עוקבת, כאשר התא מעוצב כאחוזים: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• ספירת תשלומים באותו חלון זמן, לבקרה: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) הוא אותו יום בלוח השנה לפני שנים-עשר חודשים, כך שחלון הזמן מתגלגל מעצמו. עצבו את תא התשואה כאחוזים במקום להכפיל במאה בתוך הנוסחה. גיליון שמבצע את שתי הפעולות יציג 290% כאשר הכוונה היא ל-2.9%.

השיטה השנייה היא תשואה עתידית (forward yield) המבוססת על שיעור מוכרז. הזינו את השיעור האחרון שהוכרז למניה בתא D2 ואת מספר התשלומים בשנה בתא E2:

• תשואה עתידית: =D2*E2/GOOGLEFINANCE("KO","price")

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

שתי הערות קטנות על הפונקציה. הציטוטים מתעכבים בעד עשרים דקות, והעיכוב בכל תא נתון ניתן לקריאה באמצעות =GOOGLEFINANCE("KO","datadelay"). כמו כן, שליפה היסטורית מחזירה מערך של שניים על שניים במקום מספר בודד, לכן יש לעטוף אותה: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) מחזיר את מחיר הסגירה בלבד.

שלב השנתי והטעות שהוא מסתיר

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

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

SELECT
    ticker,
    round(toFloat64(sum(cash_amount)), 4)                          AS paid_last_12m,
    round(toFloat64(argMax(cash_amount, ex_dividend_date)) * 4, 4) AS latest_x4,
    round((toFloat64(argMax(cash_amount, ex_dividend_date)) * 4
           / toFloat64(sum(cash_amount)) - 1) * 100, 2)            AS gap_pct,
    count()                                                        AS payment_count
FROM
(
    SELECT
        ticker,
        ex_dividend_date,
        max(cash_amount) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'JNJ', 'PG', 'AAPL', 'MSFT', 'CVX', 'ABBV', 'IBM')
      AND ex_dividend_date >  today() - 365
      AND ex_dividend_date <= today()
    GROUP BY ticker, ex_dividend_date
)
GROUP BY ticker
ORDER BY gap_pct DESC

במיון לפי גודל הפער, JNJ נמצאת בראש. היא ביצעה 4 תשלומים בסך כולל של $5.24 למניה במהלך התקופה, בעוד שפי ארבעה מהתשלום האחרון שלה מגיע ל-$5.36, גבוה ב-2.29%. במניה עם תשואה הקרובה ל-3%, טעות בסדר גודל כזה משנה את הנתון המודפס בעשירית האחוז או יותר, וזה מספיק כדי לשנות את סדר הרשימה הממוינת.

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

דיבידנדים מיוחדים אינם חלק מקצב החלוקה השוטף

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

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

SELECT
    toString(month_start)                           AS month,
    formatDateTime(month_start, '%b %Y')            AS month_label,
    countIf(freq = '0')                             AS one_time_payments,
    round(countIf(freq = '0') / count() * 100, 2)   AS one_time_share_pct
FROM
(
    SELECT
        toStartOfMonth(ex_dividend_date)        AS month_start,
        ticker,
        ex_dividend_date,
        ifNull(toString(any(frequency)), 'na')  AS freq
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= toStartOfMonth(today() - 730)
      AND ex_dividend_date <  toStartOfMonth(today())
    GROUP BY month_start, ticker, ex_dividend_date
)
GROUP BY month_start
ORDER BY month

ב-Jul 2026, החודש המלא האחרון, ל-111 תשלומים היה סימון של תשלום חד-פעמי, המהווים 2.91% מכלל הניירות שעברו את תאריך ה-ex-dividend באותו חודש. סכום מצטבר של שנים-עשר חודשים ייתקל באחד מהם במוקדם או במאוחר.

הפתרון הוא עמודת סימון. הציבו regular או special בעמודה C לצד כל תשלום, ולאחר מכן הוסיפו זאת כקריטריון:

• =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY(),$C$2:$C,"regular")

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

מה מציג התא המוגמר

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

WITH
    month_close AS
    (
        SELECT
            toLastDayOfMonth(date) AS month_end,
            argMax(close, date)    AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= toStartOfMonth(today() - 730)
          AND date <  toStartOfMonth(today())
        GROUP BY month_end
    ),
    payouts AS
    (
        SELECT
            ex_dividend_date,
            max(cash_amount) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND ex_dividend_date >= today() - 1160
          AND ex_dividend_date <= today()
        GROUP BY ex_dividend_date
    )
SELECT
    toString(month_end)                       AS month,
    formatDateTime(month_end, '%b %Y')        AS month_label,
    round(ttm, 4)                             AS ttm_dividends,
    round(ttm / close_px * 100, 2)            AS trailing_yield_pct
FROM
(
    SELECT
        m.month_end                                   AS month_end,
        toFloat64(any(m.close_px))                    AS close_px,
        toFloat64(sumIf(p.cash_amount,
            (p.ex_dividend_date >  subtractYears(m.month_end, 1))
            AND (p.ex_dividend_date <= m.month_end))) AS ttm
    FROM month_close AS m
    CROSS JOIN payouts AS p
    GROUP BY m.month_end
)
ORDER BY month

קו הדיבידנד נע במדרגות, ולאחר מכן נותר שטוח. קו התשואה משתנה בכל חודש לאורך אותה תקופה. ב-Aug 2024 עמד הסך העוקב על $1.89 למניה מול תשואה של 2.61%; עד Jul 2026 עמד הסך על $2.08 והתשואה הראתה 2.37%. תא תשואה שמשתנה בעוד איש לא נגע בעמודת הדיבידנד עושה את מה שתשואה עוקבת אמורה לעשות: המכנה השתנה.

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

שאלות נפוצות

האם ל-GOOGLEFINANCE יש מאפיין של תשואת דיבידנד?

לא. הפונקציה מכסה שדות של ציטוטים בזמן אמת ומחירים היסטוריים, ואף אחד מהם אינו דיבידנד או תשואה. =GOOGLEFINANCE("KO","yield") מחזיר #N/A. תשואה בגיליון מחושבת מנתון דיבידנד שאתה מספק וממחיר שהפונקציה מחזירה.

כיצד אוכל לחשב תשואת דיבידנד עוקבת (trailing) ב-Google Sheets?

שמור תאריכי אקס-דיבידנד בעמודה אחת ואת המזומן למניה בעמודה הבאה, סכם את שנים-עשר החודשים האחרונים באמצעות =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()), חלק את הסכום ב-=GOOGLEFINANCE("KO","price"), ועצב את תא התוצאה כאחוז.

מדוע תשואת הדיבידנד שלי שונה מזו שמופיעה בדף של הברוקר שלי?

לרוב מדובר במונה. דף המצטט תשואה עתידית (forward yield) מבצע שנתיות לשיעור המוצהר הנוכחי, בעוד שחישוב SUMIFS של שנים-עשר חודשים מחזיר נתון עוקב שעדיין כולל תשלומים שבוצעו לפי השיעור הישן. דיבידנד מיוחד בתוך התקופה מרחיב את הפער עוד יותר.

כיצד אוכל להוציא דיבידנד מיוחד מחישוב התשואה?

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

האם Google Sheets יכול למשוך היסטוריית דיבידנדים באופן אוטומטי?

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

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