Calculate forward dividend yield

You can use underlying SheetsFinance data to calculate a forward dividend yield. Build it from the latest declared dividend, payment frequency, and current stock price.

Quick method: use companyInfo

For a fast estimate, use the lastDiv field from company info and assume a frequency based on what you know about the stock.

=(SF("AAPL", "companyInfo", "lastDiv") * 4) / SF("AAPL")

Multiply by 4 for quarterly, 12 for monthly, or 1 for annual. This works if the company has a consistent schedule and you already know the frequency.

Precise method: pull the latest dividend and frequency from one SF_DIVIDEND call

For a more accurate figure, use SF_DIVIDEND with explicit fields to return only the dividend and frequency. Then use INDEX to extract the latest value from the first row.

Step 1: request only the fields you need

Chain dividend&frequency in the fields argument and add NH to remove the header row.

Put the current date in a cell, for example $A$1, and reference it from the formula. This keeps the sheet fast because Google Sheets recalculates TODAY() every time anything changes.

=SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH")

This returns a two-column array with the dividend in the first column and the frequency in the second. Because you explicitly requested the fields, the column positions are predictable.

Step 2: extract the latest dividend and frequency

With NH applied, the first row is data. Use INDEX(..., 1, 1) for the dividend and INDEX(..., 1, 2) for the frequency.

=INDEX(SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH"), 1, 1)

=INDEX(SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH"), 1, 2)

SheetsFinance returns frequency as text: Quarterly, Annual, Irregular, or Monthly.

Step 3: convert the frequency text to a number and calculate

Because the frequency is text, you need to convert it to a numeric multiplier before using it in the yield formula.

=(INDEX(SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH"), 1, 1) *
  IF(INDEX(SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH"), 1, 2)="Quarterly", 4,
  IF(INDEX(SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH"), 1, 2)="Monthly", 12,
  IF(INDEX(SF_DIVIDEND("AAPL", $A$1-365, $A$1, "dividend&frequency", "NH"), 1, 2)="Annual", 1, 1))))
  / SF("AAPL")

This is hard to read. Use LET to call SF_DIVIDEND once and name the dividend and frequency for reuse.

Keeping the formula readable with LET

Use LET to call SF_DIVIDEND once, extract the two values, and handle the text-to-number conversion in one place.

=LET(
  today, $A$1,
  divData, SF_DIVIDEND("AAPL", today-365, today, "dividend&frequency", "NH"),
  latestDiv, INDEX(divData, 1, 1),
  freqText, INDEX(divData, 1, 2),
  mult, IF(freqText="Quarterly", 4, IF(freqText="Monthly", 12, IF(freqText="Annual", 1, 1))),
  (latestDiv * mult) / SF("AAPL")
)

The formula above defaults to a multiplier of 1 for any frequency that is not Quarterly, Monthly, or Annual. If the frequency is Irregular, the forward yield is unreliable because the payment schedule is unpredictable. In that case, use the trailing dividend yield instead.

If no dividend is found in your date range

Some stocks pay dividends infrequently or have stopped. If SF_DIVIDEND returns no rows, widen the start date to cover a longer period.

=INDEX(SF_DIVIDEND("AAPL", $A$1-730, $A$1, "dividend&frequency", "NH"), 1, 1)

Pre-built alternative

If you only need the trailing dividend yield, SheetsFinance provides it directly:

=SF("AAPL", "ratios", "dividendYield", "ttm")

Or from company info:

=SF("AAPL", "companyInfo", "divYield")

For an annual dividend yield, replace ttm with a year:

=SF("AAPL", "ratios", "dividendYield", "2023")

For a quarterly dividend yield, use ratiosQ1, ratiosQ2, ratiosQ3, or ratiosQ4 as the type and pass a year:

=SF("AAPL", "ratiosQ4", "dividendYield", "2023")

What is next

For more on dividend data, read Dividends. For other custom calculations, see Financial Formulas.