With this article I want to show you how to create and price *American options* on a non-dividend-paying underlying – such as American stock options - in Excel using the open source QuantLib analytics library.

America has been traditionally touted as the "land of choice" and American Options honor their name by granting their holders an additional choice over their European counterparts: The time choice.

The choice of exercising the option - i.e. buying the underlying asset if *Call* and selling the underlying asset if *Put* – is present to both option styles. But an *American Option* also lets its holder decide when to carry out the exercise.

This extra degree of freedom wreaks havoc in the mathematical approach invented by Black and Scholes in 1973 for calculating the fair price of *European Options*. Their nice normal-distribution-based formulas simply do not hold in the case of *American Options*, with the single exception of *American Call Options* on underlyings that pay no dividends, which should have the same exact value with their *European* siblings because nobody in his right mind would ever forego the value of the time choice by exercising such options before their scheduled expiries.

Today there exist several mathematical techniques that may be employed to calculate *American Option *prices, but none of these techniques is exact. They are all approximate solutions to the SDE (*Stochastic Differential Equation*) that supposedly governs the evolution of the underlying price and I am going to use a few of them below.

Talking about SDE, one should bear in mind that *American Options* are sensitive to the path on which the underlying price treads from now until expiry, whereas *European Options* only worry about the endpoint of that path.

Take for example the case of options on Crude Oil. You all know that one of the parameters in the Black Scholes formula that must be specified is the σ, the volatility of the Oil price. But which volatility? The one right now or the one tomorrow?

Oil price volatility keeps changing just like oil price itself. Ignoring the details of the exact definition, the required volatility input is some sort of an average of the volatilities expected to hold at all future time points from now to expiry. This is an important point. Even if one expects a short lived spike in volatility - due perhaps to a foreseen war conflict in the Middle East followed by a long period of peace – the *European Option* price will turn out to be the same as when one expects a constant middle level volatility, for as long as the "total average" volatility from now to expiry stays the same.

The *American Option *price will instead be different between these two scenarios.

Inversely, while prices of market traded European Options imply only the "total average" expected volatility - often referred as *Implied Vol -* prices of *American Options* may be used at least in principle to infer the term structure of *Local Vol*, which is the volatility that is expected today to hold at some defined future time under the condition of a certain prevailing underlying price at that future time.

Let me know roll up my sleeves and do some actual spreadsheet calculations.

Unlike the case of *European Options*, one cannot in any reasonable way put together built-in Excel formulas that produce the price of an *American Option*.

One does need a dedicated software package for this task.

As usually, I will make use of the QuantLib open source financial library and ask Deriscope to generate the required spreadsheet formulas.

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.

Since I am interested in *American Options* I change the *Exercise Type* from *European* to *American*.

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.

Deriscope is smart enough to generate default *Model* and *Market* inputs that are suitable for pricing the selected option.

So I keep the defaults and click on the *Go* button to have the generated formulas pasted in the spreadsheet.

Before I take a look at the various formulas that have been pasted by Deriscope in the spreadsheet, I shift the data a bit around so that all are visible in the images below. I also make use of the *Trace Dependents/Precedents* feature of Excel that visualizes dependencies through blue arrows.

###
Pricing Output

Cell **D1** contains the formula **=ds(D2:E5) **that returns the price **9.925053717** as calculated by QuantLib.

The green color inputs display handle names of objects that are created elsewhere.

The one at the top called **&SIEB.DEOpt_A1:1.1** is the Option object of which the price is calculated.

The other two refer to the required Model and Market inputs as described next.

Models Input: Barone Adesi Whaley Approximation

Cell **D7** contains the handle name of the created *Model* object that is used as the value for the key *Models* in cell **E4**.

The exact type of that object is *Model[Vanilla Option]* which inherits from *Model[Tradable]*, which in turn inherits from *Model*.

The value associated with the *Pricing Method* key has been set by Deriscope to *BaroneAdesiWhaleyApprox,* which refers to a semi-analytical solution – meaning a fast algorithm based primarily on a closed form formula as opposed to the much slower numerical solutions that are of iterative nature – developed by Giovanni Barone-Adesi and Robert Whaley in 1987. You may download their original paper here

Markets Input: Collection of *Market* objects

Cell **D12** contains the handle name of the created *Market* object that is used as the value for the key *Markets* in cell **E5**.

It is of type *Market Set* which inherits from *Market*.

As its type name betrays, it contains a collection of *Market* objects.

Only the handle names of the constituent *Market* objects are needed in order to create the final object in cell **D12**. These handle names are listed in the four cells **D17:D20** and they are simple links to other cells further below, where the respective objects are created.

Yield Curve: Flat

Cell **G1** contains the handle name of an object of type *Yield Curve* which inherits from *Market*.

Deriscope creates a flat *Yield Curve* – i.e. with constant zero rates – for simplicity, but I can use the wizard to replace it with a more complex and realistic one.

Dividend Curve: Discrete Zero Dividends

Cell **G7** contains the handle name of an object of type *Dividend Curve* which inherits from *Market*.

Deriscope creates a *Dividend Curve* with zero discrete dividends because it is compatible with most pricing models.

I can edit it so that non-zero dividends are paid at specific times.

I can even use the wizard to replace it with a continuous dividend yield.

Volatility Curve: Flat

Cell **J1** contains the handle name of an object of type *Vol Curve* which inherits from *Market*.

Deriscope creates a flat *Vol Curve – *i.e. a constant volatility value - for simplicity, but I can use the wizard to replace it with a one-dimensional maturity-dependent volatility curve or a two-dimensional maturity-strike-dependent volatility surface.

Spot Underlying Price

Cell **J14** contains the handle name of an object of type *Stock Price Value *which inherits from *Market*.

It contains the spot market price of the underlying stock.

Deriscope sets this price to 100.

I can edit this value manually here or even link it to a real time feed so that it equals the actual current price of the referenced stock.

Displaying Extra Data

If I unhide the wizard and select the cell **D1** that contains the calculated option price, I see at the bottom of the taskpane the message: "*To display extra pricing data click here"*

If I then click on * the word here*, the following values are displayed inside the wizard:

These values are calculated by QuantLib in the course of the price valuation.

If desired, they may be also produced in the spreadsheet by adding the *Add Extras* key with an associated value of **TRUE** in the pricing formula in cell **D1**.

Changing the Pricing Method to Bjerksund-Stensland Approximation

First let me change the option's *Direction* in cell **B13** from *Call* to *Put*. Otherwise changing the *Pricing Method *would have no effect since the price of an *American Call Option *equals that of the respective *European Call Option*.

I notice the price in cell **D1** changes to **6.405098682**.

If I now select cell **E10** a validation dropdown appears. If not, I can easily create the dropdown by unhiding the wizard and reselecting the cell.

When I click on the dropdown, a list of methods appears:

Unfortunately the algorithm that generated the dropdown had no means of knowing that the selected cell is used to price an *American Option* and therefore all pricing methods of the default analytic approach are listed. I will show you later how you may list only the methods that apply to *American Options*. But now I can go ahead and choose the *BjerksundStenslandApprox* method that results to a new price of **6.298459662** in cell **D1**.

In a similar fashion, I can choose **JuQuadraticApprox** and get **6.382325053** for the calculated option price.

###
Accessing all applicable Pricing Methods

Only the wizard can provide me with the list of *Pricing Methods* that apply to my particular option.

So I unhide the wizard and select cell **A1** that contains the handle name of my *American Option*.

Next I click on the *Function Selector* and choose *Price*.

For my purposes here, I need to ensure that the optional key *Modelled Tradable* in the *Browse Area* is visible because this is the entry that lets the wizard know what the referenced tradable is as I keep browsing in deeper branches of the grid tree.

Then I can see which *Pricing Methods* are valid for each *Pricing Approach*:

Changing the Pricing Method to Binomial Vanilla

Now I will show you how to apply a *Pricing Method* that is not listed in the validation dropdown of cell **E10.**

I will choose the QuantLib implementation of the well-known Binomial Tree algorithm.

All I need to do is to create a new object of type *Model[Vanilla Option]* that has its *Pricing Method* set to *BinomialVanilla*.

So I select cell **A15** and search within the *Type Selector* of the wizard for that type.

Then I set the *Pricing Method* to *BinomialVanilla* and click on the *Go* button to paste the generated formulas in the spreadsheet.

Finally I link cell **E4** to my new *Model* object and voilà! The option price in cell **D1** changes to **6.422573603**.

Producing all applicable models

On a new sheet that I name Models, I use the wizard just like I did above in order to create all 13 Model objects supported by QuantLib.

The respective Pricing Methods are:

Pricing Approach |
Pricing Method |

Analytic | BaroneAdesiWhaleyApprox |

Analytic | BjerksundStenslandApprox |

Analytic | JuQuadraticApprox |

Finite Differences | FdBlackScholesVanilla |

Finite Differences | FDAmerican (time independent) |

Finite Differences | FDAmerican (time dependent) |

Finite Differences | FDDividendAmerican |

Finite Differences | FDBermudan |

Finite Differences | FdHestonVanilla |

Finite Differences | FdBatesVanilla |

Finite Differences | FdHestonHullWhiteVanilla |

Monte Carlo | MCAmerican |

Tree | BinomialVanilla |

### The final table

After I have created all *Model* objects, it is very simple to construct a table that will contain the option price for each model.

As you see below, cell **E7** contains the formula **=ds($D$2:$E$4;$D$6;D7)**, which is then pasted underneath.

Error Resolution

Cells E12 and E14 – corresponding to FDAmerican (Time Dependent) and FDBermudan - report errors.

I can investigate them by selecting the respective cells and reading the message at the *Info Area* of the wizard. It turns out the errors are due to a minor QuantLib bug that sometimes produces negative time intervals. In my case, this is caused from having selected the date 17 Feb 2018 – which is Saturday - as my trade date.

Changing the trade date in the wizard to 16 Feb 2018, all numbers come out right.

Note the two FDAmerican models produce the same price only because a flat vol is used.

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