How to Determine Implied Dividend Yield-Derivative Valuation in Excel

Dividend yield is an input into the option valuation model that often receives little attention from practitioners. This is probably because the majority of companies do not pay dividends. And for those that pay, an inaccuracy in the estimation of the dividend yield often has a small impact on the fair value of the financial instrument, especially if the tenor of the instrument is short.

However, under some circumstances, dividend can become an important input in the valuation model, and an inaccurate estimation can lead to a severe financial loss. This is what happened with a French bank during the pandemic,

The bank also lost about 100 million euros on dividend futures for the quarter, the people said. The losses had surged at one point to about 300 million euros before improving, according to the people. Dividend futures are derivatives that investors use to speculate on the payouts that companies make to shareholders. They have tumbled to historic lows in recent weeks as some of the world’s biggest corporations shred their awards in response to the coronavirus and, in some industries, pressure from regulators. Read more

In this post, we discuss ways to determine the dividend yield accurately. The most accurate dividend yield to be used in an option pricing model is the implied one that can be calculated from

  1. Dividend futures,
  2. Traded single stock futures, or
  3. Traded options.

We’re not going to discuss dividend futures here as they are over-the-counter instruments and not traded frequently. Single stock futures, on the other hand, have been traded more frequently than dividend futures, but they are illiquid and the number of available single stock futures is still small.

This leaves us with the last choice, i.e. using traded options to determine the implied dividend yield. Specifically, if the options are of European-style exercise, then we can use the put-call parity to create a synthetic single stock future, i.e.

Implied dividend in Excel

where C(K,t) is the call price at time t,

            P(K,t) denotes the put price,

            K is the strike price,

            S is the stock price,

            r is the risk-free rate,

            T is the time to maturity, and

            q is the continuous dividend yield.

Note that equation (1) is model-free, and can be used to extract the implied dividend yield.  This method has been implemented in an Excel spreadsheet.

implied dividend yield

As an example, we are going to calculate the implied dividend for Microsoft (MSFT) as of Feb-26-2021. The picture above shows the price of the 6-month call option with a strike price of $240. The price of the put option, as well as the risk-free rate, are provided in the Excel spreadsheet.

The picture below shows the calculated implied dividend yield from MSFT traded option prices. We obtain an implied dividend yield of 0.72%.

implied dividend in Excel

Note that in the above example, for illustration purposes we assumed that the options are European style, but in fact they are American style, as is the case for most equity options. This means that the put-call parity no longer holds, and the implied dividend yield calculated using eq (1)  is not accurate.

Fortunately, there is a viable solution. Reference [1] presents a method that generalizes the put-call parity for the case of American options. Essentially, the author introduces a so-called early exercise premium into equation (1), thus allowing the generalized put-call parity relationship continues to be used in the calculation of the implied dividend yield.

A novel element in this methodology is that it clears the hurdle of dealing with the early exercise premium that is not present in index options but is an element of value in American style options of US stocks. The Cox, Ross and Rubinstein (1979) binomial tree takes account of this premium and the tree is constructed simultaneously for a pair of put and call options with otherwise equal characteristics by guessing the same values for implied dividends and implied volatilities for the put and call pricing models. Analyzing dividends from stock options thus implied reveals their predictive power and adds to the understanding of cross-sectional stock returns.

References

[1] J, Kragt, Option Implied Dividends, https://ssrn.com/abstract=2980275

Further questions

What's your question? Ask it in the discussion forum

Have an answer to the questions below? Post it here or in the forum

LATEST NEWSFrench strikers maintain pressure to reject pension plan
French strikers maintain pressure to reject pension plan

PARIS (AP) — French train and metro drivers, refinery workers, garbage collectors and others were holding further strikes on Wednesday against President Emmanuel Macron’s plan to raise the retirement age to 64, in efforts to keep up pressure on the government amid the ongoing parliamentary…

Stay up-to-date with the latest news - click here
LATEST NEWSDollar hits 3-mth high as Powell flags higher rates
Dollar hits 3-mth high as Powell flags higher rates

LONDON — The dollar scaled multi-month highs against most other major currencies on Wednesday, after Federal Reserve Chair Jerome Powell warned that U.S. interest rates might need to go up even faster and higher than expected to rein in stubborn inflation. Higher rates benefit the…

Stay up-to-date with the latest news - click here
LATEST NEWSGold near one-week low as Fed’s Powell strikes hawkish tone
Gold near one-week low as Fed’s Powell strikes hawkish tone

Gold prices hovered near a one-week low on Wednesday after Federal Reserve Chair Jerome Powell said that U.S. interest rates might need to go higher than expected to control sticky inflation. Spot gold was flat at $1,813.79 per ounce by 0907 GMT, after hitting its…

Stay up-to-date with the latest news - click here
LATEST NEWSChina accuses Washington of trying to block its development
China accuses Washington of trying to block its development

BEIJING (AP) — Is the United States out to sabotage China? Chinese leaders think so. President Xi Jinping accused Washington this week of trying to isolate his country and hold back its development. That reflects the ruling Communist Party’s growing frustration that its pursuit of…

Stay up-to-date with the latest news - click here
LATEST NEWSStocks slump, dollar flies on hawkish Powell
Stocks slump, dollar flies on hawkish Powell

LONDON — Stocks fell, the dollar hit multi-month highs and U.S. and German short-term bond yields were parked at their highest levels since at least 2008 as Federal Reserve chair Jerome Powell put big rate hikes back on the table to tame inflation. Ahead of…

Stay up-to-date with the latest news - click here

Leave a Reply