With this article I want to show you how to create and price European options on an underlying that pays discrete dividends – such as European stock options - in Excel using the open source QuantLib analytics library.
In my previous article I presented an overview of the QuantLib models that can be used in Excel towards pricing the simplest non-linear derivative: European Option on a non-dividend paying underlying. Now I will consider the slightly more complex case where the underlying is known to pay dividends.
Nature apparently detests continuous flows, as the quantum view of the world currently proclaims. Since cash flows associated with derivative contracts are also part of nature, it follows that dividends are always discrete. Still mathematicians prefer to distinguish between discrete and continuous dividends because the mathematical treatment of idealized continuous dividend streams is much easier than that of discrete payment quanta.
In this article I will only consider deterministic discrete dividends, i.e. European Options on an underlying asset that is expected to pay with certainty known amounts at known discrete future times. A single denomination currency is involved, otherwise a highly non-trivial Quanto type of structure may arise!
So let me start with creating a Stock Option.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Creating an object of type Stock Option
First I select an empty cell where I want to have the formulas created by the wizard.
Then I click on the Type Selector, and choose the Stock Option type.
By default, Deriscope creates a European Call Option with one year expiry.
Finally I click on the Go button to have the generated formulas pasted in the spreadsheet.
This browser does not support the video element.
Generating the pricing formulas
I will let the wizard create the necessary formulas.
With the Stock Option object selected, I click on the Function Selector and choose the Price function.
I keep the default Model input because it uses the QuantLib AnalyticDividendEuropean pricing method, which is fine for my current task.
The default Yield Curve component of the Market input is a flat curve.
Because I want to see how the option price depends on the shape of the yield curve prior to option expiry, I choose to edit the Market input so that the Yield Curve is built out of two deposit rates.
When I am ready, I click on the Go button to have the generated formulas pasted in the spreadsheet.
This browser does not support the video element.
Move the Yield and Dividend Curve creation formulas
I bring them into view so that I can see how the option price changes as I edit the interest rates and dividends.
This browser does not support the video element.
Set the Yield Curve Date Pegs
Deriscope creates by default two deposit rates matured in one and two months respectively.
I change these dates to six months and one year in order to match the dividend payment date and the option expiry:
This browser does not support the video element.
Zero Dividend case: Irrelevance of intermediate Deposit Rates on Option Price
Note that dividend information is not part of the Stock object but part of the Market data through an object of type Dividend Curve:
The reason is that the shown dividend amounts and payment dates are not contractually bound to the Stock share contract but instead represent today's market estimates of future events.
By default, Deriscope creates a Dividend Curve with zero amounts.
Before setting a non-zero amount, I would like to verify that changing the 6-month zero rate has no effect on the option price:
This browser does not support the video element.
Indeed, the option price seems to not be affected.
This makes sense because a European Option only cares about the probability distribution of the stock price at expiry and the discount factor that applies on that date. The latter is fully determined by the one-year zero rate, which stays fixed as I change the 6-month rate.
Non-Zero Dividend case: Effect of Dividend Amount on Option Price
Let me know declare the dividend payment of 1 EUR in 6 months from now.
This browser does not support the video element.
I notice the option price went down.
One may wonder why, since a European Option should only care about the terminal state, i.e. the state at the time of expiry.
The answer is that the assumption of an intermediate dividend payment in 6 months has an immediate effect on the stock price, causing it to a downward jump of exactly the dividend amount. It follows that the terminal price of the stock must be also lower in relation to what it would have been if the jump had not taken place.
Non-Zero Dividend case: Effect of intermediate Deposit Rates on Option Price
If interest rates were zero, the stock jump size of 1 EUR on 16.02.18 would propagate unaltered in the future, causing a terminal stock distribution on 16.08.18 that would be left-shifted by 1 EUR too.
Under non-zero interest rates, time value of money applies and the initial stock jump size is brought forward compounded, just like a given amount – positive or negative – credited on your interest-bearing bank account on 16.02.18 would increase due to interest compounding as time moves forward to 16.08.18.
Therefore we expect the terminal jump size on 16.08.18 to be higher, the higher the deposit rate from 16.02.18 to 16.08.18 is. The latter is the Forward Rate between these two dates and goes up as the 6-month zero rate goes down.
So we expect the terminal jump size on 16.08.18 – and thus the price of the Call Option - to be higher, the lower the 6-month zero rate is.
The video below proves this to hold:
This browser does not support the video element.
Option Price dependence on Dividend Amount
Using Deriscope is very easy to build a table relating the Call Option price to the dividend amount. This is how:
This browser does not support the video element.
and this is the produced table with the corresponding chart:
You may download the spreadsheet produced with the above steps here.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com