8 minutes reading time (1564 words)

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

cover

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

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
DirectionCall​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 TypePhysical​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 TypeEuropeanDetermines 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 volatility that results to a price that matches the provided target price.

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 + σdw
It 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 VasicekSame 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–RossSame 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–RossSame as above but using analytic formula for the calculation of zero bond prices.
​G2Based 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 GSRBased on a one factor gaussian interest rate model.
Both reversion and volatility are piecewise constant.
Calibration is also provided.
​Gaussian 1d Markov FunctionalBased 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.

File Name: Swaption1
File Size: 14 kb
Download File

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

Asian Option Pricing in Excel using QuantLib: Mont...
Credit Default Swap (CDS) Pricing in Excel using Q...