Google Sheets-এ ডিভিডেন্ড ইল্ড বের করার উপায়

Google Sheets-এ ডিভিডেন্ড ইল্ড গণনা করা

Google Sheets-এ ডিভিডেন্ড ইল্ড গণনা করার জন্য আপনাকে নিজেই সংখ্যাটি তৈরি করতে হবে। GOOGLEFINANCE ফাংশনটি প্রাইস, EPS, PE, মার্কেট ক্যাপ এবং অন্যান্য কোট ফিল্ডের একটি দীর্ঘ তালিকা প্রদান করে, কিন্তু ডিভিডেন্ড ইল্ড সেগুলোর মধ্যে অন্তর্ভুক্ত নয়: =GOOGLEFINANCE("AAPL","yield") ব্যবহার করলে #N/A ফলাফল আসে এবং এই শব্দের অন্য কোনো বানানও কাজ করে না। এক্ষেত্রে দুটি সমাধান কার্যকর। আপনি আপনার শিটে সংরক্ষিত একটি ডিভিডেন্ড কলাম থেকে ট্রেলিং ইল্ড গণনা করতে পারেন, অথবা ঘোষিত ফরোয়ার্ড রেট একটি সেলে লিখে তা দিয়ে ভাগ করতে পারেন।

কেন GOOGLEFINANCE-এ ডিভিডেন্ড ইল্ড অ্যাট্রিবিউট নেই

এই ফাংশনের অ্যাট্রিবিউটগুলো দুটি শ্রেণিতে বিভক্ত। প্রথমটি হলো লাইভ কোট ফিল্ড, যা দুটি আর্গুমেন্ট ব্যবহার করে আনা হয়: price, volume, pe, eps, marketcap, high52, changepct এবং আরও প্রায় এক ডজন। দ্বিতীয়টি হলো ঐতিহাসিক মূল্যের ফিল্ড, যা একটি নির্দিষ্ট তারিখ বা তারিখের পরিসীমা ব্যবহার করে আনা হয়: open, high, low, close, volume। ডিভিডেন্ডের রেকর্ড এই দুই শ্রেণির কোনোটির মধ্যেই পড়ে না। কোনো অ্যাট্রিবিউটই পেমেন্ট বা পেআউটের ইতিহাস প্রদান করে না, যার ফলে ইল্ডের লব (numerator) বের করার জন্য ফাংশনের বাইরের তথ্যের ওপর নির্ভর করতে হয়।

ডিভিডেন্ড ইল্ড হলো প্রতি শেয়ারের বার্ষিক লভ্যাংশকে শেয়ারের দাম দিয়ে ভাগ করে প্রাপ্ত শতাংশ। এর দামের অংশটি একটি সেলের মাধ্যমে পাওয়া যায়। লভ্যাংশের অংশটি বের করাই মূল কাজ। যদি এই অনুপাতটি আপনার কাছে নতুন হয়, তবে ডিভিডেন্ড ইল্ড আসলে কী পরিমাপ করে অংশে স্প্রেডশিটের হিসাবের আগে এর মূল ধারণা আলোচনা করা হয়েছে এবং কীভাবে ডিভিডেন্ড ইল্ড গণনা করবেন অংশে গাণিতিক প্রক্রিয়াটি ধাপে ধাপে দেখানো হয়েছে।

এখানে কাঁচামাল হিসেবে দেওয়া হলো: গত প্রায় তিন বছরে একটি প্রধান লভ্যাংশ প্রদানকারী কোম্পানির প্রতিটি পেমেন্টের তথ্য। প্রতিটি সারিতে রয়েছে 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 Sep 14, 2023 তারিখে প্রতি শেয়ারে $0.46 এবং Jun 15, 2026 তারিখে $0.53 লভ্যাংশ প্রদান করেছে, যা এই সময়ের মধ্যে মোট 12টি পেমেন্ট। চার্টের দ্বিতীয় লাইনটি হলো প্রতিটি পেমেন্টকে চার দিয়ে গুণ করে প্রাপ্ত মান: অর্থাৎ সেই ত্রৈমাসিকের পেমেন্ট যদি সারা বছর অব্যাহত থাকে, তবে বার্ষিক হার কত হবে। এটি বছরে একবার বৃদ্ধি পায় এবং মাঝের সময়গুলোতে স্থির থাকে। এই বৃদ্ধি বা ধাপের হিসাবটিই যেখানে বেশিরভাগ স্প্রেডশিট ইল্ড গণনায় ভুল হয়। সম্পূর্ণ রেকর্ডটি Coca-Cola-এর ডিভিডেন্ড ইতিহাস-এ পাওয়া যাবে।

Google Sheets-এ ডিভিডেন্ড ইল্ড বের করার দুটি উপায়

প্রথম পদ্ধতিটি হলো আপনার নিজস্ব কলাম থেকে তৈরি করা ট্রেইলিং ইল্ড। কলাম A-তে ex-dividend তারিখ এবং কলাম 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 দিয়ে গুণ না করে সেলটিকে পার্সেন্টেজ ফরম্যাটে সেট করুন। যে শিট দুটিই করে, সেটি 2.9%-এর জায়গায় 290% দেখাবে।

দ্বিতীয় পদ্ধতিটি হলো ঘোষিত হার থেকে ফরোয়ার্ড ইল্ড বের করা। 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 date প্রতি বছর কয়েক দিন করে সরে যায়, ফলে একটি উইন্ডোতে তিনটি বা পাঁচটি পেমেন্টও পড়ে যেতে পারে। মোট অংকের ওপর আস্থা রাখার আগে 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 সংখ্যক পেমেন্টকে এককালীন হিসেবে চিহ্নিত করা হয়েছে, যা ওই মাসে ex-dividend হওয়া মোট পেমেন্টের 2.91% অংশ। বারো মাসের রোলিং সাম (rolling sum) হিসাব করলে কোনো না কোনো সময় এই ধরনের পেমেন্ট তাতে যুক্ত হবেই।

এর সমাধান হলো একটি ফ্ল্যাগ কলাম ব্যবহার করা। প্রতিটি পেমেন্টের পাশে কলাম C-তে regular অথবা special লিখুন এবং সেটিকে একটি শর্ত (criterion) হিসেবে যোগ করুন:

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

স্পেশাল ডিভিডেন্ডের সারিগুলো শিটে রেখে দিন। এগুলো প্রকৃত নগদ অর্থ এবং প্রাপ্ত আয়ের রেকর্ডে এগুলো থাকা জরুরি। তবে চলমান আয়ের হার বা রান রেট (run rate) নির্ধারণের জন্য যে সংখ্যা ব্যবহার করা হয়, সেখানে এগুলো অন্তর্ভুক্ত করা উচিত নয়।

ফিনিশড সেল যা প্রদর্শন করে

এই পদ্ধতিতে তৈরি ট্রেইলিং ইল্ডের দুটি চলক অংশ থাকে এবং তারা ভিন্ন ভিন্ন সময়ে পরিবর্তিত হয়। লভ্যাংশের মোট পরিমাণ বছরে কয়েকবার পরিবর্তিত হয়, যখন কোনো পেমেন্ট নির্দিষ্ট সময়সীমার মধ্যে আসে বা বের হয়ে যায় অথবা লভ্যাংশ বৃদ্ধি পায়। অন্যদিকে, দাম প্রতিটি সেশনে পরিবর্তিত হয়। নিচের প্যানেলটি একই পেয়ারের জন্য গত দুই বছরের মাসিক ভিত্তিতে চূড়ান্ত হিসাবটি প্রদর্শন করে।

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%। একটি ইল্ড সেল যখন লভ্যাংশের কলামে কোনো পরিবর্তন ছাড়াই পরিবর্তিত হয়, তখন এটি ট্রেইলিং ইল্ডের স্বাভাবিক কাজই করে: অর্থাৎ এর হর (denominator) পরিবর্তিত হয়েছে।

একবার একটি টিকারের জন্য এটি কাজ করলে, প্রতিটি হোল্ডিংয়ের জন্য ব্লকটি কপি করুন এবং ইল্ডের গড় না করে বাজার মূল্যের ভিত্তিতে ফলাফলগুলোকে ওয়েট (weight) করুন। এই ধাপটি হলো পোর্টফোলিও ওয়েটেড ডিভিডেন্ড ইল্ড।

সচরাচর জিজ্ঞাসিত প্রশ্নাবলী

GOOGLEFINANCE-এ কি ডিভিডেন্ড ইল্ড অ্যাট্রিবিউট আছে?

না। এই ফাংশনটি শুধুমাত্র লাইভ কোট ফিল্ড এবং ঐতিহাসিক দামের তথ্য প্রদান করে, যার মধ্যে ডিভিডেন্ড বা ইল্ডের কোনোটিই অন্তর্ভুক্ত নেই। =GOOGLEFINANCE("KO","yield") প্রদান করে #N/A। স্প্রেডশিটে ইল্ড বের করার জন্য আপনাকে ডিভিডেন্ডের পরিমাণ সরবরাহ করতে হবে এবং ফাংশন থেকে প্রাপ্ত দামের সাথে তা সমন্বয় করতে হবে।

গুগল শিটে ট্রেইলিং ডিভিডেন্ড ইল্ড কীভাবে গণনা করব?

একটি কলামে এক্স-ডিভিডেন্ড তারিখ এবং অন্যটিতে শেয়ার প্রতি নগদ লভ্যাংশের পরিমাণ রাখুন। =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()) ব্যবহার করে গত বারো মাসের যোগফল বের করুন, সেই মোট পরিমাণকে =GOOGLEFINANCE("KO","price") দিয়ে ভাগ করুন এবং ফলাফল সেলটিকে পার্সেন্টেজ ফরম্যাটে সেট করুন।

আমার ব্রোকারের পেজে দেখানো ডিভিডেন্ড ইল্ডের সাথে আমার গণনার পার্থক্য কেন?

এর প্রধান কারণ হলো লব (numerator)। ব্রোকারের পেজে সাধারণত ফরওয়ার্ড ইল্ড দেখানো হয়, যা বর্তমান ঘোষিত লভ্যাংশকে বার্ষিক হিসেবে গণ্য করে। অন্যদিকে, বারো মাসের SUMIFS একটি ট্রেইলিং ফিগার প্রদান করে, যার মধ্যে পুরনো হারে দেওয়া লভ্যাংশও অন্তর্ভুক্ত থাকতে পারে। যদি ওই সময়ের মধ্যে কোনো স্পেশাল ডিভিডেন্ড দেওয়া হয়ে থাকে, তবে এই পার্থক্য আরও বেড়ে যায়।

ইল্ড গণনার সময় স্পেশাল ডিভিডেন্ড কীভাবে বাদ দেব?

প্রতিটি পেমেন্ট নিয়মিত নাকি বিশেষ, তা চিহ্নিত করার জন্য একটি আলাদা কলাম তৈরি করুন। এরপর SUMIFS ফাংশনে ওই কলামটিকে অতিরিক্ত ক্রাইটেরিয়া হিসেবে যুক্ত করুন। এতে স্পেশাল পেমেন্টটি শিটে থাকলেও ইল্ড গণনার বাইরে থাকবে।

গুগল শিট কি স্বয়ংক্রিয়ভাবে ডিভিডেন্ড হিস্ট্রি সংগ্রহ করতে পারে?

GOOGLEFINANCE-এর মাধ্যমে এটি সম্ভব নয়। ডিভিডেন্ড কলামটি হাতে আপডেট করতে হয় অথবা এমন কোনো উৎস থেকে কপি করতে হয় যেখানে পেমেন্টের রেকর্ড প্রকাশিত হয়। এই পৃষ্ঠায় বর্ণিত উভয় পদ্ধতির ক্ষেত্রেই এটিই একমাত্র ম্যানুয়াল ধাপ।

এখানে প্রতিটি প্যানেলের সাথে সেই SQL কোড দেওয়া আছে যা থেকে ডেটা তৈরি হয়েছে। কোনো টোটাল কীভাবে গণনা করা হয়েছে তা দেখতে যেকোনো একটি প্যানেল ওপেন করুন। Strasmore টার্মিনালে সাধারণ ইংরেজিতে একই প্রশ্ন জিজ্ঞাসা করা যাবে।