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 the 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. In the option pricing model, the most accurate dividend yield 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.

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 the implied dividend yield can be extracted easily by using it. This method has been implemented in an Excel spreadsheet.