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 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:
Recommended for Deriscope starters: The Overview and Quick Guide pages.
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 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.
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 + σ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 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
