Asian Option Pricing in Excel using QuantLib: Monte Carlo, Finite Differences, Analytic models for Arithmetic and Geometric Average. Example with live EUR/USD rate

Asian options come in different flavors as described below, but to the extent they have European exercise rights they can be priced by QuantLib using primarily Monte Carlo, but under certain circumstances using also Finite Differences or even analytic formulas.

The main feature of an Asian option is that it involves the average of the realized prices of the option's underlying over a time period before the option's expiry T.

The Asian option contract specifies N observation times T₁, T₂, …, T_N occurring before expiry T, over which the average A – either arithmetic or geometric – of the realized underlying prices X_i is calculated according to the formula:

Arithmetic Average: A = (X₁ + … + X_N)/N

Geometric Average: A = (X₁…X_N)^1/N

There exist two variations:

Fixed Strike:

At time T the long option holder on a notional of one currency unit – the denomination currency of the prices X_i - has the right to exercise the option and receive a cash amount equaling A currency units in exchange of a pre-agreed fixed strike K.

Floating Strike:

At time T the long option holder has the right to exercise the option and receive the underlying asset in exchange of a strike set at that time T to equal the by then realized average A.

It turns out the Fixed Strike variant can be only cash settled, whereas the Floating Strike variant supports both cash and physical settlement.

It is intuitively expected that the average A must be much less volatile than the underlying X. The next chart shows a simulated daily evolution of the underlying price versus the arithmetic and geometric average produced from an assumed lognormal diffusion with a drift of 4% and volatility of 20%. You may download the very simple spreadsheet that produced this chart here.

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Creating all four types of Asian Option objects

Given the two Average definitions and the two Strike types, there exist four different combinations.

I can use the wizard to produce the formulas creating all four different Asian Option types on the USD/EUR FX rate by an intelligent use of the special Clone function, as shown in the video below: 

The produced formulas are shown in the following screenshot: 

Understanding the main formula

As you see, cell A1 contains the formula =ds(A2:B7), which takes one input argument and returns the text &AsianOpt_A1:2.1

The prefix & indicates that &AsianOpt_A1:2.1 is the handle name of some object. In fact it points to an object of type Asian Option.

The input range A2: B7 contains the input with the following keys:

Pricing an Asian option

Each one of the four types of Asian options supports different valuation models.

The following video shows how I use the wizard to find out which valuation models apply to each Asian option: 

This is the table of applicable valuation models: 

Monte Carlo pricing of the arithmetic average, fixed strike Asian Option

This video shows how I can price my first Asian option in cell G2 using the Monte Carlo approach: 

You must have noticed that for brevity in the exhibition I have pasted the formulas with the mandatory parameters only.

Due to this choice, the Model[Simulation] object in cell I13 in the image below contains only the number of samples input. 

But if I select the cell I13 and unhide the wizard I see the following contents:

In particular, you see that I have the option of defining the following input parameters:

Antithetic Variate

Brownian Bridge

Control Variate

Seed 

Linking to live FX rate

Deriscope can retrieve true real time FX rates and stock prices from various live feed providers as described in detail here.

I can therefore use in cell M26 the simple formula =dsLiveGetSync("TrueFX";"EUR/USD") in order to get the actual EUR/USD rate without time delay every time I recalculate my spreadsheet by pressing F9.

Note the function dsLiveGetSync places a synchronous live feed request when the cell gets calculated.

As explained in the article in the above link, asynchronous requests and automatic cell updates - even when the spreadsheet is not calculated - are also supported.

As you see in the screenshot below, I am using the built-in Excel IF(…) function to easily switch between manual and live input FX rate.

For pure cosmetic reasons I wrap the IF function inside the Deriscope dsPipe function as this causes the containing cell to flash green or red as its value goes up or down, provided Deriscope flashing mode is switched on. 

Additional output data

During the price calculation, QuantLib also computes certain values that can be of interest.

When cell I1 is selected, the wizard displays the following in its Info Area: 

If I click on the here hyperlink of the top sentence "To display extra pricing data click here", I see the following:  

As you see, the following data concerning the Monte Carlo pricing are reported:

Error Estimate

Samples

Data

Weight Sum

Mean

Variance

Standard Deviation

Skewness

Kurtosis

Min

Max

Gaussian Downside Variance

Gaussian Downside Deviation

Processing Time

I have already explained here how I may transfer these data in the spreadsheet. 

Advanced Topic: Following the pricing algorithm step-by-step

It is also possible to track the pricing algorithm that has been run in order to produce the observed output and also see the actually used intermediate QuantLib structures and numerical outputs as described in the respective section of my Interest Rate Swap article. 

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