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.
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|
| 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.
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