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

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

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:

### Pricing the Vanilla Swaption in 9 seconds

The easiest and simplest possible way to get the price of a *swaption* that already exists as a Deriscope object in Excel is by calling its *Price Simple* function, as the following 9-second video demonstrates:

The screenshot below shows the two formulas pasted by the wizard in cells **D1** and **D6**.

The formula in **D1** is =ds(D2:E4) and returns the swaption price calculated as **0.009889125**. It references the swaption object **&VanSwaption_A1:1.1** that was created earlier in cell **A1** and a new object **&VanSwaptionMkt_D6:1.1** that is created by the wizard below in cell **D6**. This last object serves as a simple placeholder of the necessary market data, which in this case are: **Int Rate= 0.04** and **Vol= 0.2**.

You may notice that no modelling assumptions are specified in the input data.

This is due to the default wizard behavior to generate only those input data that are deemed as mandatory. It is possible though to instruct the wizard to include the optional input data, part of which is the specification of the pricing model.

The next video shows how I may choose the **Bachelier** model that assumes the forward swap rate is normally distributed:

Below you see the pricing formula =ds(D13:E16) pasted in cell **D12** that returns the swaption price of **0.277367541**, which is much higher than the previous result due to the fact that the volatility input of **0.2** in cell **E28** is now interpreted as normal vol.

In addition to the market object **&VanSwaptionMkt_D23:1.1** in cell **E16**, the additional object **&VanSwaptionMdl_D18:1.1** in cell **E15** supplies the modelling assumptions.

###
Using the Vanilla Swaption object

Deriscope supplies various functions that can be called from a given *Vanilla* *Swaption* object, of which the most important are:

** 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. In particular, by setting **Output**= **Price+Input** or **Output**= **Full+Input**, an object of type *Valuation* is returned, which may be used to either **a)** solve for the fair value of some input parameter, or **b)** run a repricing based on selected modified input parameters.

** Price Simple**: A light version of the

*Price*function, whereby market data are supplied as part of a single object. It encompasses

**a)**a fair value solver and

**b)**an optional execution based on selected modified input parameters.

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

Working with Complex Market Input

So far, both interest rate and vol have been assumed to be single numbers.

While this may be acceptable in certain contexts, most often a fully built yield curve will be preferred over a single interest rate number because the swaption cash flows occur at different times when different interest rates generally apply.

With regard to volatility, even if the European nature of the swaption relies on a single volatility number, one often has a volatility multi-dimensional structure that supplies the vols on a given grid of maturities and strikes.

Thankfully Deriscope allows me to replace both the interest rate and vol numbers with handles of appropriate objects the type of which inherits from *Market*.

The fastest way to generate these *Market* objects is by calling the *Price* function, which differs from *Price Simple* in that these *Market* objects are explicitly generated and used as input to the pricing formula. Afterwards I have the option to use these *Market* objects as input to the *Price Simple* function as well.

The following video shows how I use the wizard to generate in only **8** seconds the **7** spreadsheet formulas required by the *Price* function. I have previously shifted the *Price Simple* formulas downwards so that the new formulas appear on the top rows.

Below is the spreadsheet image with the pasted function in cell **D1** that returns a *Vanilla* *Swaption* price of **0,018051973**, which differs slightly from the earlier **0.009889125** due to the **Compounding**= **Simple** entry. 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.

###
How to Build a Table of Prices that Depend on Varying Input Values

One often wants to investigate the dependence of an output quantity – such as the swaption price – on one or more input parameters. For example, imagine I want to build a table of swaption prices implied by varying expiries and strikes.

Deriscope provides the interface that allows me to do that using a single spreadsheet formula.

One way would be to use the *Price Simple* function with the extra optional *Edit Input* key.

A more efficient method is to use the *Revalue* function that applies on any object of type *Valuation*.

I can easily create a Valuation object that holds the all my Market and Model inputs by calling the *Price* function with the extra optional input **Output**= **Price+Input**, as shown below:

Next, I prepare the coordinates of my two-dimensional expiry-strike table, as shown below.

The top date is the original swaption expiry.

The remaining dates are generated using the function **Date::Add Period** with a **Period=** input of **3 months**, as you see in the formula bar while cell **J8** is selected.

The challenging job is to come up with a formula in the top/left cell **K7** that produces the swaption price implied by its corresponding coordinates (expiry of **27.03.2019** and strike of **0.03**) and then paste this formula over the whole range **K7:M15**.

I know the function *Revalue* of the object **&VanSwaptionVal_J2:1.3** in cell **J2** is capable of doing this. Rather than typing the formula by hand, I prefer to let the wizard to paste a prototype of this function, which I can then edit in the spreadsheet.

The next video shows the undertaken steps. Note I choose the third **Paste** menu item because it creates a more compact formula that I can easier edit afterwards.

Below is the spreadsheet formula as pasted by the wizard in cell **K7** after I have edited its parameters to reference the expiry date in cell **J7**. It returns a swaption price of **0.01805**, which is the same as the one I had earlier in cell **D1** since the modified strike of **0.03** is not yet referenced by the formula. So far, only the key **Expiry=** of the object in cell **A1** is referenced.

As the plural in the name of the keys "**Ref Objects**=", "**Ref keys**=" and "**Ref Values**=" implies, I may set their associated values to arrays consisting of more than one single element. So, in order to have the formula reference the swaption strike in addition to the expiry, I extend the formula parameters with the object in cell $**A$10**, its associated key "**fixed rate**=" and the strike value from cell **K$6** as shown below:

This formula does what I wanted: It returns the swaption price for the expiry on the left column and the strike on the top row.

I may now paste it across the whole range **K7:M15** to get the final table of swaption prices:

### Solving for the Fair Value of any Input Parameter

The object **&VanSwaptionVal_J2:1.3** in cell **J2** is of type *Valuation*.

All *Valuation* objects contain a function called *Fair Value* that solves for the number that a specified input parameter must attain so that a specified output number matches a given target.

As an example, I can use the *Fair Value* function to find out the strike that will make the swaption price to equal – let's say – **0.02**. The following video shows the required wizard steps:

The screenshot below the formula in cell **J18** that returns the fair swaption strike, which is represented here as the fair value associated with the **Ref Key**= **Fixed Rate** of the object **&VanIRS_A10:1.3** that causes our swaption to be revaluated to the target price of **0.02**.

By default, the specified target value of **0.02** refers to the swaption price, but I can make it refer to a different quantity that is part of the output of the calling *Valuation* object.

For example, I can create a Valuation object that will include extra results made available by QuantLib and Deriscope by changing the **Output**= **Price+Input** to **Output**= **Full+Input**, as shown below:

Now the object in cell **J26** does not contain only the swaption price, but also other results, such as the swaption vega. The following video shows how I can calculate the fair swaption strike that results in a swaption vega of **0.04**:

The image below shows the fair strike output of **0.042884765** as calculated by the formula =ds(J31:K36) in cell **J30**:

Technical 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