# Swaption Pricing in Excel: 14 Free QuantLib Models plus Implied Volatility Surface and Cube

Most people are unaware of the fact that free and open source QuantLib comes with a great variety of modelling approaches when it comes to pricing an *interest rate European swaption* in Excel that surpasses what is offered by expensive commercial products.

In fact, **14** different modelling approaches are implemented, whereby the *Black* approach does not support only a *flat volatility* as input, but also a *volatility surface* or a *volatility cube*.

But first a few words about *swaptions*.

There exist two types of *swaptions*: *receiver* and *payer*.

The (long) holder of a *European receiver* *swaption* with expiry at time **T** has the __right__ to decide at time **T** whether or not to enter into a *receiver* *fixed-to-floating interest rate swap* that starts at **T** and extends until a later time **T _{N}**.

You may refer here for an overview of *interest rate swaps*, but at this point it suffices to mention that the holder of a *receiver* *swap* receives fixed and pays floating.

Note that the counterparty of a long *swaption* holder is the *swaption writer* who faces an unlimited loss risk due to having the __obligation__ rather than the __right__ to enter into a *swap* at time T, if the other party wishes to exercise one's right.

In an analogous sense, the (long) holder of a *European payer swaption* has the right to enter into a *payer fixed-to-floating interest swap*.

The diagram below shows the timing of the various contingent cash flows:

Creating a Vanilla Swaption object in 22 seconds

I can use the wizard to produce a formula creating a *Vanilla Swaption* object in only **22** seconds, provided I am happy with the default assignments, as shown in the video below:

The following image indicates the structure of the pasted formulas.

Note that because cell **A1** is currently selected, the contents of the contained *swaption* object are visible in the wizard on the right.

Understanding the main formula

As you see, cell **A1** contains the formula *=ds(A2:B8),* which takes one input argument and returns the text *&VanSwaption_A1:1.2*

The prefix *&* indicates that *&VanSwaption_A1:1.2 *is the handle name of some object. In fact it points to an object of type *Vanilla Swaption*.

The input range *A2:B8* contains the input consisting of the following key/value pairs:

Key | Value | Description |

Vanilla IRS |
&VanIRS_A10:1.1 | The option's underlying interest rate swap.
Expects an object of type Vanilla IRS |

Direction | Call | Applies in relation to
the underlying swap into which the option holder enters at the time of
exercise. A Call direction means the option holder enters in that underlying swap. A Put direction means the option holder takes the opposite position, i.e. enters in the reversed swap. |

Settlement Type | Physical | Two choices: Cash when only an appropriately defined amount of cash is delivered from the seller to the buyer, whereas the tradable itself is not exchanged. Physical when the tradable itself is delivered from the seller to the buyer. |

Exercise Type | European | Determines when
exercise is allowed.
Possible values: European, Bermudan, American. |

Expiry | 27.03.2019 | The last date at which the option holder has the right to exercise the option. In the European case this is also the only date when exercise is possible. |

I can always access detailed information on any *key-value pair *by selecting the respective cell as this video shows:

###
Using the Vanilla Swaption object

Deriscope supplies the following three functions that can be called from a given *Vanilla* *Swaption* object:

** Price**: Returns primarily the

*swaption's*price for given

*model*and

*market*input.

Note that the *Price* function may also return several other values beyond the price of the *swaption* by setting the value for the *Output* key accordingly.

** Implied Vol**: Returns the

*flat Black*that results to a price that matches the provided target price.

*volatility*** Get Equivalent Tradable**: Returns the

*equivalent portfolio*constructed according to the

*put-call parity*relationship that owes to have the exact same price as the caller swaption.

The Price function

The following video shows how I use the wizard to generate in only **8** seconds the **7** spreadsheet formulas required by the *Price* function:

Below is the spreadsheet image with the pasted function in cell **D1** that returns a *Vanilla* *Swaption* price of **-0,018051973**. The **7** newly created blocks are clearly visible.

You notice the two blocks in columns **D** and **E** contain the wizard-generated default *yield* and *volatility* curves, both set with *flat rate* for simplicity.

I can obviously replace them with more realistic, market-calibrated non-flat curves as described in the next section.

You also notice the Model object created in cell G1 is of type *Black*.

Again this is the default model generated by the wizard. Deriscope allows you to choose a different model as will be explained below.

Market Volatility input

With Deriscope I am not restricted to using a *flat volatility* input with the *Black* pricing method.

I can also use the following:

a) a *volatility surface* that contains a market *vol* grid spanned by *swaption expiry* and *underlying swap tenor*. Then the appropriate *vol* is extracted through bilinear interpolation.

b) a *volatility cube* that contains a series of market *vol* grids spanned by *swaption strike, swaption expiry* and *underlying swap tenor*. Then the appropriate *vol* is extracted through a solver based on a SABR (stochastic volatility) evolution of the driftless forward swap rate.

I will show you how this is done in detail in some future post.

For now, the following video demonstrates how I can navigate through the various *vol* choices within the wizard.

Model input

Deriscope supports the following pricing methods:

Black | The swaption price is calculated by means of
the Black formula for swaptions, which relies on the 3 inputs
wrt the forward swap rate: atm forward,
standard deviation and annuity.The underlying assumption is that the applicable forward swap rate follows a driftless lognormal diffusion with
deterministic - perhaps time dependent vol
- in the annuity measure and
therefore is lognormally distributed
at expiry. |

Black Karasinski (Tree) | Based on short rate following a single-factor
stochastic process r according to the
SDE:
dln(r) = (θ - α ln(r))dt + σdw |

Hull White (Tree) | Based on short rate following a single-factor
stochastic process r according to the
SDE:
dr = (θ - αr)dt + σdwIt follows that the short rate follows a gaussian process with a mean reverting stochastic drift, a fact
that results in it being normally
distributed. |

Jamshidian Hull White (Semi-analytic) | Same as above but
using analytic formula for the calculation of zero bond prices. |

Vasicek (Tree) | Based on short rate following a single-factor
stochastic process r according to the
SDE:
dr = a(b - r)dt + σdw
A risk premium λ may also be specified. |

Jamshidian Vasicek | Same as above but
using analytic formula for the calculation of zero bond prices. |

Cox–Ingersoll–Ross (Tree) | Based on short rate following
a single-factor stochastic process r
according to the SDE:
dr = k(θ - r)dt + sqrt(r)σdw |

Jamshidian Cox–Ingersoll–Ross | Same as above but
using analytic formula for the calculation of zero bond prices. |

Extended Cox–Ingersoll–Ross (Tree) | Based on short rate r given by:
r(t) = μ(t) + r΄(t)
where r΄(t) as above and μ(t) is the deterministic time-dependent parameter used for term-structure fitting. |

Jamshidian Extended Cox–Ingersoll–Ross | Same as above but
using analytic formula for the calculation of zero bond prices. |

G2 | Based on short rate following
a two-factor short rate model according
to the formula:
r = φ(t) + x(t) + y(t) where dx = -axdt + σdw1
dy = -bydt + ηdw2 |

Gaussian 1d GSR | Based on a one factor gaussian interest
rate model.
Both reversion and volatility are piecewise
constant.
Calibration is also provided. |

Gaussian 1d Markov Functional | Based on a one factor gaussian interest
rate model driven by a one dimensional markovian state variable x.
Calibration is also provided. |

Market Model | The starting point is
the assumption that each forward interest
rate in a certain set of rates follows a specific diffusion with respect to
a respective measure. |

I will cover some of the above models in future posts.

For now, the following video demonstrates how I can navigate through the various *model* choices within the wizard.

Additional output data

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

When cell **D1** 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, beyond the cash flows I can also see the *Annuity*, *ATM forward, Spread Correction, Std Dev, Strike, Swap Length* and *Vega* as well.

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