عائد التوزيعات في Google Sheets: حلّان عمليان

لحساب عائد التوزيعات في Google Sheets، عليك إنشاء الرقم بنفسك. تعرض GOOGLEFINANCE السعر، وربحية السهم، ونسبة السعر إلى الربحية، والقيمة السوقية، وقائمة طويلة من بيانات الأسعار الأخرى، لكن عائد التوزيعات ليس من بينها: =GOOGLEFINANCE("AAPL","yield") تُرجع #N/A، ولا تؤدي أي صياغة أخرى للكلمة إلى نتيجة. هناك حلّان عمليان. احسب العائد السابق من عمود توزيعات تحتفظ به في الجدول، أو أدخل معدل التوزيع المستقبلي المعلن في خلية ثم اقسمه.

لماذا لا تتضمن GOOGLEFINANCE سمة لعائد توزيعات الأرباح

تنقسم سمات الدالة إلى مجموعتين. هناك حقول الأسعار الفورية، وتُستخرج باستخدام وسيطين: price وvolume وpe وeps وmarketcap وhigh52 وchangepct ونحو عشرة حقول أخرى. وهناك حقول الأسعار التاريخية، وتُستخرج باستخدام تاريخ أو نطاق زمني: open وhigh وlow وclose وvolume. أما سجلات توزيعات الأرباح فلا تنتمي إلى أي من المجموعتين. فلا توجد سمة تُرجع سجل المدفوعات أو التوزيعات، ولذلك يجب الحصول على بسط العائد من خارج الدالة.

يُحسب عائد توزيعات الأرباح بقسمة التوزيعات السنوية لكل سهم على سعر السهم، وتُكتب النتيجة كنسبة مئوية. ويكون السعر في خلية واحدة. أما التوزيعات فتتطلب العمل الفعلي. إذا كانت هذه النسبة جديدة عليك، يشرح لك ما الذي يقيسه عائد توزيعات الأرباح فعلياً المفهوم قبل آليات جداول البيانات، بينما يوضح كيفية حساب عائد توزيعات الأرباح خطوات الحساب.

إليك البيانات الأساسية: كل دفعة أجراها مُصدِر واحد خلال نحو السنوات الثلاث الماضية. يعرض كل صف تاريخ ex-dividend، أي التاريخ الذي لا يحصل فيه مشتري السهم في ذلك التاريخ أو بعده على الدفعة المقبلة، إلى جانب المبلغ النقدي المدفوع لكل سهم.

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

دفعت Coca-Cola مبلغ $0.46 لكل سهم في Sep 14, 2023، ومبلغ $0.53 في Jun 15, 2026، بإجمالي 12 دفعات خلال الفترة. ويمثل الخط الثاني في الرسم كل دفعة مضروبة في أربعة، أي المعدل السنوي المفترض إذا تكررت دفعة ذلك الربع طوال العام. يرتفع هذا المعدل مرة واحدة سنوياً، ويبقى ثابتاً بين الزيادات. وهنا تحديداً تحدث معظم أخطاء حساب عائد التوزيعات في جداول البيانات. ويظهر السجل الكامل في سجل توزيعات أرباح Coca-Cola.

طريقتان لحساب عائد التوزيعات في Google Sheets

الطريقة الأولى هي عائد لاحق محسوب من عمود تنشئه بنفسك. ضع تواريخ ex-dividend في العمود A، والمبلغ النقدي لكل سهم في العمود B، مع تخصيص صف لكل دفعة، ثم استخدم ما يلي:

• إجمالي آخر اثني عشر شهراً: =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) اليوم نفسه من السنة السابقة، ولذلك تتحرك الفترة تلقائياً إلى الأمام. نسّق خلية العائد كنسبة مئوية، بدلاً من ضربها في 100 داخل الصيغة. فقد تعرض الورقة 290% عندما تكون النتيجة المقصودة 2.9%.

الطريقة الثانية هي عائد مستقبلي محسوب من معدل التوزيع المعلن. ضع أحدث معدل معلن لكل سهم في D2، وعدد الدفعات السنوية في E2:

• العائد المستقبلي: =D2*E2/GOOGLEFINANCE("KO","price")

لا توجد أتمتة للخانة D2. يعلن مجلس الإدارة المعدل، ثم تقرأه من إعلان التوزيع وتدخله يدوياً. هذه هي الطريقة الصحيحة لحساب العائد المستقبلي، والاختيار بين البسطين هو جوهر موضوع العائد اللاحق مقابل العائد المستقبلي للتوزيعات.

ملاحظتان قصيرتان بشأن الدالة. تتأخر الأسعار لمدة تصل إلى 20 دقيقة، ويمكن معرفة فترة التأخير في أي خلية باستخدام =GOOGLEFINANCE("KO","datadelay"). كما أن جلب البيانات التاريخية يعيد مصفوفة من صفين وعمودين، لا رقماً واحداً، لذا استخدمها ضمن صيغة تغليف: يعطيك =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) سعر الإغلاق وحده.

خطوة التسنين السنوي والخطأ الذي تخفيه

تضرب أكثر الصيغ المعطوبة شيوعاً في جداول التوزيعات أحدث دفعة في أربعة. وتظل النتيجة صحيحة إلى أن ترفع الشركة توزيعاتها. بعد الزيادة، دُفعت ثلاث من الدفعات الأربع ضمن السنة السابقة بالمعدل القديم، ولذلك فإن ضرب الدفعة الجديدة في أربعة يبالغ في تقدير ما حصل عليه المساهم فعلياً.

يوضح الجدول أدناه حجم الفارق لدى ثماني شركات كبيرة موزعة للتوزيعات: ما دفعه كل سهم خلال آخر اثني عشر شهراً، إلى جانب أربعة أمثال أحدث دفعة.

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 بدلاً من الضرب. فلا تحتوي دائماً فترة الاثني عشر شهراً المتحركة على أربع دفعات فصلية بالضبط. إذ قد تتقدم تواريخ ex-dividend أو تتأخر بضعة أيام كل عام، وقد تضم الفترة ثلاث دفعات أو خمساً. يوضح لك COUNTIFS أي دفعة حدثت قبل أن تثق في الإجمالي.

التوزيعات الخاصة ليست وتيرة مستمرة

يتعلق الخطأ الكلاسيكي الآخر باعتبار دفعة تُدفع مرة واحدة جزءاً من الجدول المعتاد. تستخدم الشركة فائض النقد لديها في توزيع كبير لمرة واحدة، فتُدرجها دالة SUMIFS ضمن الإجمالي المتحرك، وتقفز خلية العائد. وبعد اثني عشر شهراً، ينخفض العائد مجدداً بهدوء.

هذه الدفعات ليست نادرة. تحصي اللوحة كل دفعة في السوق الأمريكية يحددها تقويم التوزيعات على أنها دفعة لمرة واحدة، لا جزءاً من جدول متكرر، وذلك شهراً بعد شهر.

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")

أبقِ الصفوف الخاصة في ورقة العمل. فهي تمثل نقداً حقيقياً، وينبغي أن تظهر في سجل ما تم استلامه. لكنها لا تنتمي إلى رقم يُفترض أن يصف وتيرة مستمرة.

ما تعرضه الخلية المكتملة

يتكوّن العائد المتأخر المحسوب بهذه الطريقة من عنصرين متحركين، ويتحرك كل منهما وفق وتيرة مختلفة. يتغير إجمالي التوزيعات عدة مرات سنوياً، عندما يدخل أحد التوزيعات أو يخرج من الفترة، أو عندما تُعلن الشركة زيادة في التوزيع. أما السعر فيتغير في كل جلسة. يعرض الجزء أدناه الحساب المكتمل شهراً بعد شهر خلال العامين الماضيين للدافع نفسه.

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. ويُحتسب العائد في جدول البيانات باستخدام قيمة توزيع تُدخلها أنت، وسعر تُرجعه الدالة.

كيف أحسب عائد التوزيعات اللاحق في Google Sheets؟

احتفظ بتواريخ ex-dividend في عمود، وبالمبلغ النقدي لكل سهم في العمود التالي. اجمع توزيعات الأشهر الاثني عشر الأخيرة باستخدام =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())، ثم اقسم الإجمالي على =GOOGLEFINANCE("KO","price")، ونسّق خلية النتيجة كنسبة مئوية.

لماذا يختلف عائد التوزيعات لديّ عن العائد المعروض في صفحة الوسيط؟

غالباً ما يكون السبب هو البسط. فالصفحة التي تعرض عائداً مستقبلياً تحوّل معدل التوزيع المعلن حالياً إلى معدل سنوي، بينما تُرجع دالة SUMIFS لفترة اثني عشر شهراً عائداً لاحقاً لا يزال يتضمن توزيعات دُفعت بالمعدل السابق. ويزداد الفرق إذا تضمنت الفترة توزيعاً استثنائياً.

كيف أستبعد توزيعاً استثنائياً من العائد؟

أضف عموداً يحدد ما إذا كان كل توزيع عادياً أو استثنائياً. ثم مرّر هذا العمود كزوج معايير إضافياً في SUMIFS. سيبقى التوزيع الاستثنائي في جدول البيانات، لكنه لن يدخل في احتساب العائد.

هل يمكن لـ Google Sheets جلب سجل التوزيعات تلقائياً؟

ليس عبر GOOGLEFINANCE. يُحدَّث عمود التوزيعات يدوياً، أو يُلصق من مصدر ينشر سجلات المدفوعات. وهذه هي الخطوة اليدوية الوحيدة في كلتا الطريقتين المعروضتين في هذه الصفحة.

يأتي كل جدول هنا مرفقاً بـ SQL الذي أنشأه، لذا افتح أحدها لترى بالضبط كيف حُسب الإجمالي. ويمكن طرح الأسئلة نفسها باللغة الإنجليزية البسيطة على منصة Strasmore.