# 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 _{1}, T_{2}, …, T_{N}** occurring before expiry

**T**, over which the average

**A**– either arithmetic or geometric – of the realized underlying prices

**X**is calculated according to the formula:

_{i}*Arithmetic Average*: **A = (X _{1} + … + X_{N})/N**

*Geometric Average*: **A = (X _{1}…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.

If you are not familiar with Deriscope, visit the Quick Guide for an introductory tour to the most important Deriscope features.

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:

Key | Value | Description |

Reference Option |
&FXOpt_A9:2.1 | The option object before
its modification through the exotic features pertinent to the Asian character.
In particular, this object defines the underlying and the payoff type. |

Asian Type | Fixed Strike | As described above.
Available values: Fixed Strike and Floating
Strike |

Average Type | Arithmetic | As described above.
Available values: Arithmetic and Geometric |

Average Schedule | &Schedule_D9:2.1 | The schedule of dates, over which the underlying prices are collected for the purpose of calculating the respective average numerical value. |

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:

Average Type | Strike Type | Valuation Model |

Arithmetic | Fixed | 1) Finite Differences
2) Monte Carlo |

Arithmetic | Floating | Monte Carlo |

Geometric | Fixed | 1) Analytic – Discrete Sampling
2) Analytic – Continuous
Sampling
3) Monte Carlo |

Geometric | Floating | Analytic – Discrete Sampling |

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