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.

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.

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.

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.

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:

### 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:

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.

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:

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:

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