วิธีคำนวณ Dividend Yield ใน Google Sheets

การคำนวณ Dividend Yield ใน Google Sheets

การคำนวณ Dividend Yield ใน Google Sheets จำเป็นต้องสร้างสูตรขึ้นมาเอง เนื่องจากฟังก์ชัน GOOGLEFINANCE สามารถดึงข้อมูลราคา, EPS, P/E, Market Cap และข้อมูลอื่น ๆ ได้อีกมากมาย แต่ไม่มีฟังก์ชันสำหรับ Dividend Yield โดยตรง โดยที่ =GOOGLEFINANCE("AAPL","yield") จะแสดงผลเป็น #N/A และการสะกดคำในรูปแบบอื่นก็ไม่สามารถดึงข้อมูลนี้ได้เช่นกัน มีวิธีแก้ปัญหาหลักสองวิธี คือการคำนวณ Trailing Yield จากคอลัมน์เงินปันผลที่คุณจัดทำขึ้นเองในชีต หรือการพิมพ์อัตราเงินปันผลล่วงหน้า (Forward Rate) ที่ประกาศไว้ลงในเซลล์แล้วนำมาหาร

เหตุใด GOOGLEFINANCE จึงไม่มี attribute สำหรับ dividend yield

Attribute ของฟังก์ชันนี้แบ่งออกเป็นสองกลุ่ม กลุ่มแรกคือข้อมูลราคาแบบเรียลไทม์ ซึ่งดึงข้อมูลโดยใช้สองอาร์กิวเมนต์ ได้แก่ price, volume, pe, eps, marketcap, high52, changepct และอื่น ๆ อีกประมาณหนึ่งโหล กลุ่มที่สองคือข้อมูลราคาในอดีต ซึ่งดึงข้อมูลโดยระบุวันที่หรือช่วงเวลา ได้แก่ open, high, low, close, volume ข้อมูลการจ่ายเงินปันผลไม่ได้อยู่ในทั้งสองกลุ่มนี้ ไม่มี attribute ใดที่แสดงประวัติการจ่ายเงินหรือประวัติการจ่ายปันผล ทำให้ตัวเศษของสูตรคำนวณอัตราผลตอบแทนต้องมาจากแหล่งข้อมูลภายนอกฟังก์ชัน

Dividend yield คือเงินปันผลต่อหุ้นรายปีหารด้วยราคาต่อหุ้น แสดงผลเป็นเปอร์เซ็นต์ ส่วนของราคาเป็นเพียงหนึ่งเซลล์ แต่ส่วนของเงินปันผลต้องอาศัยการคำนวณ หากอัตราส่วนนี้เป็นเรื่องใหม่สำหรับคุณ สิ่งที่ dividend yield วัดผลจริง ๆ จะอธิบายเนื้อหาก่อนเข้าสู่กลไกของสเปรดชีต และ วิธีการคำนวณ dividend yield จะอธิบายขั้นตอนทางคณิตศาสตร์

นี่คือข้อมูลดิบ: การจ่ายเงินทุกครั้งที่บริษัทจ่ายเงินปันผลรายใหญ่แห่งหนึ่งจ่ายออกมาในช่วงเวลาประมาณสามปีที่ผ่านมา แต่ละแถวคือวัน ex-dividend date ซึ่งเป็นวันที่ผู้ซื้อหุ้นตั้งแต่วันนั้นเป็นต้นไปจะไม่มีสิทธิได้รับเงินปันผลรอบที่จะถึงนี้ โดยแสดงคู่กับจำนวนเงินสดที่จ่ายต่อหุ้น

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 ครั้งในช่วงเวลาดังกล่าว เส้นที่สองบนแผนภูมิคือจำนวนเงินที่จ่ายแต่ละครั้งคูณด้วยสี่ ซึ่งเป็นอัตราต่อปีที่อนุมานว่าหากการจ่ายเงินในไตรมาสนั้นเกิดขึ้นซ้ำตลอดทั้งปี ตัวเลขจะปรับขึ้นปีละหนึ่งครั้งและคงที่ในช่วงระหว่างนั้น การปรับขึ้นเป็นขั้นบันไดนี้เองที่เป็นจุดที่การคำนวณ yield ในสเปรดชีตส่วนใหญ่ผิดพลาด ข้อมูลบันทึกทั้งหมดอยู่ใน ประวัติการจ่ายเงินปันผลของ Coca-Cola

สองวิธีในการคำนวณอัตราเงินปันผลตอบแทนใน Google Sheets

วิธีแรกคือการคำนวณ Trailing yield จากคอลัมน์ที่คุณสร้างขึ้นเอง ให้ระบุวัน ex-dividend date ไว้ในคอลัมน์ A และจำนวนเงินต่อหุ้นในคอลัมน์ B โดยให้หนึ่งแถวต่อหนึ่งการจ่ายเงิน จากนั้น:

• ผลรวมย้อนหลังสิบสองเดือน: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• ราคาตลาดปัจจุบัน: =GOOGLEFINANCE("KO","price")
• Trailing yield โดยจัดรูปแบบเซลล์เป็นเปอร์เซ็นต์: =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:

• Forward yield: =D2*E2/GOOGLEFINANCE("KO","price")

ไม่มีระบบอัตโนมัติสำหรับช่อง D2 คณะกรรมการบริษัทจะเป็นผู้ประกาศอัตรา และคุณต้องอ่านจากประกาศนั้นแล้วพิมพ์ลงไป นี่คือวิธีที่ถูกต้องในการคำนวณ Forward yield และการเลือกระหว่างตัวเศษทั้งสองแบบนี้คือประเด็นหลักของ trailing versus forward dividend yield

ข้อควรทราบเพิ่มเติมสองประการเกี่ยวกับฟังก์ชันนี้ ข้อมูลราคาจะล่าช้าสูงสุดยี่สิบนาที และสามารถตรวจสอบความล่าช้าของแต่ละเซลล์ได้ด้วย =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% สำหรับหุ้นที่ให้ Yield ใกล้เคียงสามเปอร์เซ็นต์ ความผิดพลาดระดับนี้ส่งผลให้ตัวเลขที่แสดงผลคลาดเคลื่อนไปหนึ่งในสิบของเปอร์เซ็นต์หรือมากกว่า ซึ่งเพียงพอที่จะทำให้ลำดับในรายการที่จัดเรียงไว้เปลี่ยนแปลงไป

คอลัมน์จำนวนงวดการจ่ายเงินเป็นเหตุผลสนับสนุนประการที่สองในการใช้ฟังก์ชัน SUMIFS แทนการคูณ ช่วงเวลาสิบสองเดือนแบบหมุนเวียนไม่ได้มีเงินปันผลรายไตรมาสครบสี่งวดเสมอไป วัน ex-dividend date มักเลื่อนออกไปเล็กน้อยในแต่ละปี ทำให้บางช่วงเวลาอาจมีเงินปันผลเข้ามาสามหรือห้างวด COUNTIFS จะระบุให้คุณทราบว่าเกิดเหตุการณ์ใดขึ้นก่อนที่คุณจะเชื่อถือตัวเลขรวมนั้น

Special dividends are not a run rate

The other classic error treats a one-off payment as part of the schedule. A company clears surplus cash with a single large distribution, the SUMIFS sweeps it into the trailing total, and the yield cell jumps. Twelve months later it quietly drops back.

These payments are not rare. The panel counts every payment across the US market that the dividend calendar marks as one-time rather than part of a repeating schedule, month by month.

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

In Jul 2026, the most recent complete month, 111 payments carried a one-time marking, 2.91% of everything that went ex-dividend that month. A rolling twelve month sum will meet one of them sooner or later.

The fix is a flag column. Put regular or special in column C beside each payment, then add it as a criterion:

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

Keep the special rows in the sheet. They are real cash and they belong in a record of what was received. They do not belong in a number meant to describe an ongoing rate.

ผลลัพธ์ที่ได้จากเซลล์คำนวณ

อัตราผลตอบแทนย้อนหลัง (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% เซลล์แสดงอัตราผลตอบแทนที่เปลี่ยนแปลงไปทั้งที่ไม่มีการแก้ไขข้อมูลในคอลัมน์เงินปันผลนั้น กำลังแสดงพฤติกรรมตามปกติของ trailing yield ซึ่งเกิดจากการที่ตัวหาร (ราคาหุ้น) มีการเปลี่ยนแปลง

เมื่อคำนวณสำหรับหุ้นหนึ่งตัวได้แล้ว ให้คัดลอกบล็อกคำนวณลงมาสำหรับหุ้นทุกตัวในพอร์ต และใช้วิธีถ่วงน้ำหนักด้วยมูลค่าตลาดแทนการหาค่าเฉลี่ยของอัตราผลตอบแทน ขั้นตอนนี้คือ อัตราผลตอบแทนเงินปันผลถ่วงน้ำหนักของพอร์ตการลงทุน

คำถามที่พบบ่อย

GOOGLEFINANCE มี attribute สำหรับ dividend yield หรือไม่

ไม่มี ฟังก์ชันนี้ครอบคลุมเฉพาะข้อมูลราคาแบบเรียลไทม์และราคาในอดีต โดยไม่มีข้อมูลเงินปันผลหรืออัตราผลตอบแทน =GOOGLEFINANCE("KO","yield") จะส่งค่ากลับเป็น #N/A การคำนวณ yield ในสเปรดชีตต้องอาศัยตัวเลขเงินปันผลที่คุณระบุเองร่วมกับราคาที่ฟังก์ชันดึงมาให้

ฉันจะคำนวณ trailing dividend yield ใน Google Sheets ได้อย่างไร

ให้เก็บข้อมูลวัน ex-dividend date ไว้ในคอลัมน์หนึ่งและจำนวนเงินต่อหุ้นในอีกคอลัมน์หนึ่ง จากนั้นรวมยอดเงินปันผลย้อนหลังสิบสองเดือนด้วย =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()) แล้วหารด้วย =GOOGLEFINANCE("KO","price") พร้อมจัดรูปแบบเซลล์ผลลัพธ์ให้เป็นเปอร์เซ็นต์

ทำไม dividend yield ของฉันจึงต่างจากที่แสดงบนหน้าเว็บของโบรกเกอร์

สาเหตุส่วนใหญ่อยู่ที่ตัวตั้งหาร หน้าเว็บที่แสดง forward yield มักจะคำนวณจากอัตราที่ประกาศล่าสุดแบบเต็มปี ในขณะที่การใช้ SUMIFS ย้อนหลังสิบสองเดือนจะได้ค่า trailing ซึ่งอาจยังมีเงินปันผลที่จ่ายในอัตราเดิมรวมอยู่ด้วย นอกจากนี้ หากมีเงินปันผลพิเศษ (special dividend) อยู่ในช่วงเวลาดังกล่าวก็จะยิ่งทำให้ตัวเลขมีความแตกต่างกันมากขึ้น

ฉันจะแยกเงินปันผลพิเศษออกจาก yield ได้อย่างไร

ให้เพิ่มคอลัมน์เพื่อระบุว่าการจ่ายเงินแต่ละครั้งเป็นแบบปกติหรือพิเศษ จากนั้นเพิ่มคอลัมน์ดังกล่าวเป็นเงื่อนไขเพิ่มเติมในสูตร SUMIFS เงินปันผลพิเศษจะยังคงอยู่ในสเปรดชีตแต่จะไม่ถูกนำมาคำนวณรวมใน yield

Google Sheets สามารถดึงประวัติการจ่ายเงินปันผลโดยอัตโนมัติได้หรือไม่

ไม่สามารถทำได้ผ่าน GOOGLEFINANCE ข้อมูลคอลัมน์เงินปันผลต้องบันทึกด้วยตนเองหรือคัดลอกมาจากแหล่งข้อมูลที่เผยแพร่บันทึกการจ่ายเงิน นี่เป็นขั้นตอนเดียวที่ต้องทำด้วยมือสำหรับทั้งสองวิธีที่ระบุในหน้านี้

ข้อมูลทุกส่วนในที่นี้มาพร้อมกับ SQL ที่ใช้ประมวลผล คุณสามารถเปิดดูเพื่อตรวจสอบวิธีการนับยอดรวมได้อย่างละเอียด คุณสามารถสอบถามคำถามเดียวกันนี้ด้วยภาษาอังกฤษทั่วไปได้ที่ Strasmore terminal