Cara Kira Dividend Yield dalam Google Sheets

Mengira Hasil Dividen dalam Google Sheets

Untuk mengira hasil dividen dalam Google Sheets, anda perlu membina angka tersebut sendiri. GOOGLEFINANCE mengembalikan harga, EPS, P/E, permodalan pasaran dan senarai panjang medan sebut harga lain, namun hasil dividen bukanlah salah satu daripadanya: =GOOGLEFINANCE("AAPL","yield") mengembalikan #N/A, dan tiada ejaan lain bagi perkataan tersebut yang berfungsi. Dua penyelesaian sementara boleh digunakan. Kira hasil trailing daripada lajur dividen yang anda selenggara dalam helaian tersebut, atau taip kadar forward yang diisytiharkan ke dalam sel dan lakukan pembahagian.

Mengapa GOOGLEFINANCE tidak mempunyai atribut hasil dividen

Atribut fungsi ini terbahagi kepada dua keluarga. Terdapat medan sebut harga langsung yang diperoleh dengan dua argumen: price, volume, pe, eps, marketcap, high52, changepct dan kira-kira sedozen lagi. Kemudian terdapat medan harga sejarah yang diperoleh dengan tarikh atau julat tarikh: open, high, low, close, volume. Rekod dividen tidak tergolong dalam mana-mana keluarga tersebut. Tiada atribut yang mengembalikan sejarah pembayaran atau agihan, yang menyebabkan pengangka bagi hasil tersebut perlu diperoleh dari luar fungsi.

Hasil dividen ialah dividen tahunan sesaham dibahagikan dengan harga sesaham, yang ditulis sebagai peratusan. Bahagian harga bagi pengiraan itu hanyalah satu sel. Bahagian dividen pula memerlukan usaha. Jika nisbah itu sendiri baharu bagi anda, apa yang sebenarnya diukur oleh hasil dividen membincangkannya sebelum sebarang mekanik hamparan kerja, dan cara mengira hasil dividen memperincikan aritmetiknya.

Berikut adalah bahan mentah: setiap pembayaran yang dibuat oleh satu pembayar isi rumah sepanjang kira-kira tiga tahun yang lalu. Setiap baris ialah tarikh ex-dividend, iaitu tarikh pada dan selepas itu pembeli saham tidak menerima pembayaran yang akan datang, bersebelahan dengan tunai yang dibayar sesaham.

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 membayar $0.46 sesaham pada Sep 14, 2023 dan $0.53 pada Jun 15, 2026, iaitu 12 pembayaran sepanjang tempoh tersebut. Baris kedua pada carta ialah setiap pembayaran didarab dengan empat: kadar tahunan yang tersirat jika pembayaran suku tersebut diulang sepanjang tahun. Ia meningkat sekali setahun dan kekal mendatar di antaranya. Peningkatan itulah punca kebanyakan hasil dalam hamparan kerja menjadi salah. Rekod penuh terdapat dalam sejarah dividen Coca-Cola.

Dua cara untuk mendapatkan hasil dividen dalam Google Sheets

Kaedah pertama ialah hasil trailing yang dibina daripada lajur anda sendiri. Masukkan tarikh ex-dividend dalam lajur A dan tunai sesaham dalam lajur B, satu baris bagi setiap pembayaran, kemudian:

• Jumlah dua belas bulan trailing: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• Harga semasa: =GOOGLEFINANCE("KO","price")
• Hasil trailing, dengan sel diformatkan sebagai peratusan: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• Kiraan pembayaran dalam tetingkap yang sama, sebagai semakan: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) ialah hari kalendar yang sama dua belas bulan yang lalu, jadi tetingkap tersebut bergerak ke hadapan secara automatik. Formatkan sel hasil sebagai peratusan dan bukannya mendarab dengan 100 di dalam formula. Helaian yang melakukan kedua-duanya akan memaparkan 290% sedangkan maksudnya ialah 2.9%.

Kaedah kedua ialah hasil forward daripada kadar yang diisytiharkan. Masukkan kadar sesaham yang paling baru diisytiharkan dalam D2 dan bilangan pembayaran setahun dalam E2:

• Hasil forward: =D2*E2/GOOGLEFINANCE("KO","price")

Tiada apa yang mengautomasikan D2. Lembaga pengarah mengisytiharkan kadar, dan anda membacanya daripada pengisytiharan tersebut lalu menaipnya masuk. Itulah versi hasil forward yang jujur, dan pilihan antara kedua-dua pengangka tersebut merupakan keseluruhan topik bagi hasil dividen trailing berbanding forward.

Dua nota kecil mengenai fungsi tersebut. Sebut harga adalah tertunda sehingga 20 minit, dan kelewatan pada mana-mana sel boleh dibaca dengan =GOOGLEFINANCE("KO","datadelay"). Dan pengambilan data sejarah mengembalikan tatasusunan dua kali dua dan bukannya satu nombor, jadi bungkuskan ia: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) memberikan harga penutup sahaja.

Langkah pengtahunan dan kesilapan yang disembunyikannya

Formula yang paling kerap rosak dalam helaian dividen ialah mendarabkan bayaran terkini dengan empat. Ia berfungsi dengan tepat sehingga saat syarikat menaikkan kadar. Selepas kenaikan, tiga daripada empat bayaran dalam tahun terdahulu dibuat pada kadar lama, dan mendarabkan bayaran baharu dengan empat akan melebihkan jumlah yang sebenarnya diterima oleh pemegang saham.

Panel di bawah mengukur jurang tersebut bagi lapan pembayar dividen besar: jumlah yang dibayar oleh setiap nama bagi setiap saham sepanjang dua belas bulan terdahulu, bersebelahan dengan empat kali ganda bayaran terkininya.

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

Disusun mengikut jurang tersebut, JNJ berada di kedudukan teratas. Ia membuat 4 bayaran berjumlah $5.24 sesaham sepanjang tempoh tersebut, manakala empat kali ganda bayaran terkininya berjumlah $5.36, iaitu 2.29% lebih tinggi. Bagi saham yang memberikan hasil hampir 3%, ralat sebesar itu mengubah angka yang dipaparkan sebanyak satu persepuluh mata peratusan atau lebih, yang mencukupi untuk menyusun semula senarai yang telah diisih.

Lajur kiraan bayaran merupakan hujah kedua bagi penggunaan SUMIFS berbanding pendaraban. Tempoh dua belas bulan bergerak tidak selalunya mengandungi tepat empat bayaran suku tahunan. Tarikh ex-dividend berganjak beberapa hari setiap tahun, dan sesuatu tempoh boleh merangkumi tiga atau lima bayaran. COUNTIFS memberitahu anda yang mana satu berlaku sebelum anda mempercayai jumlah tersebut.

Dividen khas bukanlah kadar jangkaan

Satu lagi kesilapan klasik adalah menganggap bayaran sekali sahaja sebagai sebahagian daripada jadual tetap. Syarikat melupuskan lebihan tunai melalui satu agihan besar, fungsi SUMIFS menyerapnya ke dalam jumlah terkumpul, dan sel hasil (yield) melonjak. Dua belas bulan kemudian, angka itu jatuh semula secara senyap.

Bayaran sebegini tidak jarang berlaku. Panel ini mengira setiap bayaran di seluruh pasaran AS yang ditandakan dalam kalendar dividen sebagai bayaran sekali sahaja dan bukannya sebahagian daripada jadual berulang, bulan demi bulan.

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

Pada Jul 2026, bulan lengkap yang paling terkini, 111 bayaran membawa tanda sekali sahaja, iaitu 2.91% daripada semua yang menjadi ex-dividend pada bulan tersebut. Jumlah dua belas bulan bergolek akan menemui salah satu daripadanya lambat laun.

Penyelesaiannya adalah dengan menggunakan lajur penanda. Letakkan regular atau special dalam lajur C di sebelah setiap bayaran, kemudian tambahkannya sebagai kriteria:

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

Kekalkan baris khas tersebut dalam helaian anda. Ia adalah tunai sebenar dan perlu ada dalam rekod penerimaan. Ia tidak sepatutnya dimasukkan ke dalam angka yang bertujuan untuk menggambarkan kadar yang berterusan.

Hasil akhir paparan sel

Hasil dividen trailing yang dibina dengan cara ini mempunyai dua komponen bergerak, dan ia bergerak mengikut jadual yang berbeza. Jumlah dividen berubah beberapa kali setahun, apabila pembayaran masuk atau keluar daripada tempoh pengiraan atau apabila terdapat kenaikan dividen. Harga pula berubah pada setiap sesi dagangan. Panel di bawah memaparkan pengiraan akhir bulan demi bulan sepanjang dua tahun lepas bagi pembayar dividen yang sama.

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

Garisan dividen bergerak secara berperingkat, kemudian mendatar. Garisan hasil bergerak setiap bulan dalam tempoh yang sama. Pada Aug 2024, jumlah trailing berada pada $1.89 sesaham berbanding hasil sebanyak 2.61%; menjelang Jul 2026, jumlah tersebut berada pada $2.08 dan hasilnya mencatatkan 2.37%. Sel hasil yang berubah walaupun tiada sesiapa menyentuh lajur dividen menunjukkan fungsi sebenar hasil trailing: penyebutnya telah berubah.

Setelah satu ticker berfungsi, salin blok tersebut ke bawah untuk setiap pegangan dan wajarkan keputusan mengikut nilai pasaran dan bukannya mengambil purata hasil. Langkah tersebut adalah hasil dividen wajaran portfolio.

Soalan Lazim

Adakah GOOGLEFINANCE mempunyai atribut hasil dividen?

Tidak. Fungsi ini meliputi medan sebut harga langsung dan harga sejarah, dan tiada satu pun daripadanya merupakan dividen atau hasil. =GOOGLEFINANCE("KO","yield") mengembalikan #N/A. Hasil dalam helaian dibina daripada angka dividen yang anda bekalkan dan harga yang dikembalikan oleh fungsi tersebut.

Bagaimanakah cara saya mengira hasil dividen terkumpul (trailing) dalam Google Sheets?

Simpan tarikh ex-dividend dalam satu lajur dan tunai sesaham dalam lajur seterusnya, jumlahkan dua belas bulan terakhir dengan =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()), bahagikan jumlah tersebut dengan =GOOGLEFINANCE("KO","price"), dan format sel hasil sebagai peratusan.

Mengapakah hasil dividen saya berbeza daripada yang terdapat pada halaman broker saya?

Kebiasaannya disebabkan oleh pengangka. Halaman yang memetik hasil hadapan (forward yield) akan mengtahunkan kadar semasa yang diisytiharkan, manakala SUMIFS dua belas bulan mengembalikan angka terkumpul yang masih mengandungi pembayaran yang dibuat pada kadar lama. Dividen khas dalam tempoh tersebut meluaskan lagi jurang perbezaan.

Bagaimanakah cara saya mengecualikan dividen khas daripada hasil?

Tambah satu lajur yang menandakan setiap pembayaran sebagai biasa atau khas, kemudian masukkan lajur tersebut sebagai pasangan kriteria tambahan dalam SUMIFS. Pembayaran khas itu kekal dalam helaian tetapi dikecualikan daripada pengiraan hasil.

Bolehkah Google Sheets menarik sejarah dividen secara automatik?

Tidak melalui GOOGLEFINANCE. Lajur dividen diselenggara secara manual atau ditampal masuk daripada sumber yang menerbitkan rekod pembayaran. Itu adalah satu-satunya langkah manual dalam kedua-dua kaedah di halaman ini.

Setiap panel di sini disertakan dengan SQL yang menghasilkannya, jadi buka salah satu daripadanya untuk melihat dengan tepat bagaimana jumlah dikira. Soalan yang sama boleh ditanya dalam bahasa Inggeris biasa pada terminal Strasmore.