آموزش محاسبه بازده سود نقدی در گوگل شیت

محاسبه بازده سود نقدی در گوگل شیت

برای محاسبه بازده سود نقدی (dividend yield) در گوگل شیت، باید خودتان این عدد را استخراج کنید. تابع GOOGLEFINANCE قیمت، EPS، P/E، ارزش بازار و فهرست بلندبالایی از سایر فیلدهای معاملاتی را بازمی‌گرداند، اما بازده سود نقدی در میان آن‌ها نیست: =GOOGLEFINANCE("AAPL","yield") مقدار #N/A را برمی‌گرداند و هیچ نگارش دیگری از این واژه نیز نتیجه‌ای در بر ندارد. دو راهکار برای این مسئله وجود دارد. یا یک بازده دنباله‌دار (trailing yield) را از ستونی که برای سود نقدی در شیت خود نگهداری می‌کنید محاسبه کنید، یا نرخ سود نقدی پیش‌بینی‌شده (forward rate) اعلام‌شده را در یک سلول وارد کرده و بر قیمت تقسیم کنید.

چرا تابع GOOGLEFINANCE فاقد ویژگی بازده سود نقدی (Dividend Yield) است

ویژگی‌های این تابع در دو دسته قرار می‌گیرند. دسته اول، فیلدهای قیمت لحظه‌ای هستند که با دو آرگومان فراخوانی می‌شوند: 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

شرکت کوکاکولا در تاریخ Sep 14, 2023 مبلغ $0.46 و در تاریخ Jun 15, 2026 مبلغ $0.53 به ازای هر سهم پرداخت کرده است که مجموعاً 12 پرداخت در این بازه زمانی را شامل می‌شود. خط دوم در نمودار، حاصل‌ضرب هر پرداخت در عدد چهار است: نرخ سالانه‌ای که در صورت تکرار پرداخت آن فصل در طول کل سال، حاصل می‌شود. این نرخ سالی یک‌بار افزایش می‌یابد و در فواصل بین آن ثابت می‌ماند. همین پله‌ای بودن نرخ، منشأ خطای اکثر محاسبات بازده در صفحه‌گسترده‌هاست. سوابق کامل در تاریخچه سود نقدی کوکاکولا قابل مشاهده است.

دو روش برای محاسبه بازده سود نقدی در Google Sheets

روش اول، محاسبه بازده دوازده‌ماهه گذشته (trailing yield) بر اساس ستون‌های شخصی شماست. تاریخ‌های 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٪ است.

روش دوم، محاسبه بازده آتی (forward yield) بر اساس نرخ اعلام‌شده است. آخرین نرخ اعلام‌شده به ازای هر سهم را در 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 آن را در مجموع دوازده‌ماهه لحاظ می‌کند و سلول بازدهی جهش می‌یابد. دوازده ماه بعد، این رقم به‌آرامی به سطح قبلی بازمی‌گردد.

این پرداخت‌ها نادر نیستند. پنل ما هر پرداختی را در سراسر بازار ایالات متحده که در تقویم سود سهام به‌عنوان «موردی» (one-time) و نه بخشی از یک برنامه تکرارشونده علامت‌گذاری شده است، به‌صورت ماهانه شمارش می‌کند.

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 رسیدند. مجموع دوازده‌ماهه متحرک دیر یا زود با یکی از این موارد مواجه خواهد شد.

راه‌حل، استفاده از یک ستون پرچم (flag column) است. در ستون C کنار هر پرداخت، regular یا special را قرار دهید و سپس آن را به‌عنوان یک شرط اضافه کنید:

• =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% را نشان داد. سلول بازدهی که تغییر می‌کند در حالی که هیچ تغییری در ستون سود سهام ایجاد نشده، دقیقاً همان کاری را انجام می‌دهد که بازده دنباله‌دار باید انجام دهد: مخرج کسر تغییر کرده است.

هنگامی که یک تیکر (ticker) به‌درستی کار کرد، بلوک مربوطه را برای تمام دارایی‌ها کپی کنید و نتایج را به‌جای میانگین‌گیری ساده از بازده‌ها، بر اساس ارزش بازار وزن‌دهی کنید. این مرحله همان بازده سود سهام وزن‌دار پرتفوی است.

پرسش‌های متداول

آیا تابع GOOGLEFINANCE ویژگی نرخ سود نقدی (dividend yield) را دارد؟

خیر. این تابع داده‌های لحظه‌ای قیمت و قیمت‌های تاریخی را پوشش می‌دهد و هیچ‌یک از آن‌ها سود نقدی یا نرخ بازدهی نیستند. =GOOGLEFINANCE("KO","yield") مقدار #N/A را بازمی‌گرداند. نرخ بازدهی در یک کاربرگ از ترکیب رقم سود نقدی که شما وارد می‌کنید و قیمتی که تابع بازمی‌گرداند، محاسبه می‌شود.

چگونه نرخ سود نقدی دوازده‌ماهه (trailing dividend yield) را در Google Sheets محاسبه کنم؟

تاریخ‌های ex-dividend را در یک ستون و مبلغ نقدی هر سهم را در ستون بعدی قرار دهید، مجموع دوازده ماه اخیر را با =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()) محاسبه کنید، آن مجموع را بر =GOOGLEFINANCE("KO","price") تقسیم کرده و سلول نتیجه را با فرمت درصد نمایش دهید.

چرا نرخ سود نقدی من با نرخ نمایش‌داده‌شده در صفحه کارگزارم متفاوت است؟

دلیل آن اغلب صورت کسر است. صفحه‌ای که نرخ سود پیش‌رو (forward yield) را گزارش می‌کند، نرخ اعلام‌شده فعلی را سالانه می‌کند، در حالی که تابع SUMIFS دوازده‌ماهه، رقمی را بازمی‌گرداند که همچنان شامل پرداخت‌های انجام‌شده با نرخ قدیمی است. وجود یک سود نقدی ویژه (special dividend) در این بازه زمانی، این شکاف را بیشتر می‌کند.

چگونه سود نقدی ویژه را از محاسبه نرخ بازدهی کنار بگذارم؟

ستونی اضافه کنید که هر پرداخت را به‌عنوان عادی یا ویژه علامت‌گذاری کند، سپس آن ستون را به‌عنوان یک جفت معیار اضافی در تابع SUMIFS وارد کنید. پرداخت ویژه در کاربرگ باقی می‌ماند اما در محاسبه نرخ بازدهی لحاظ نمی‌شود.

آیا Google Sheets می‌تواند تاریخچه سود نقدی را به‌طور خودکار دریافت کند؟

نه از طریق GOOGLEFINANCE. ستون سود نقدی باید به‌صورت دستی نگهداری شود یا از منبعی که سوابق پرداخت را منتشر می‌کند، کپی شود. این تنها مرحله دستی در هر دو روش ارائه‌شده در این صفحه است.

هر پنل در اینجا به همراه کد SQL تولیدکننده آن ارائه شده است؛ بنابراین برای مشاهده دقیق نحوه محاسبه یک مجموع، یکی از آن‌ها را باز کنید. همین پرسش‌ها را می‌توان به زبان انگلیسی ساده در پایانه Strasmore مطرح کرد.