USD Swaption Pricing in Excel using the Bachelier Model and Market Normal Vols from CME

The Chicago Mercantile Exchange (CME) clears European swaption trades on 3-month USD LIBOR since April 2016 and has thus become the first major exchange that lists Over-The-Counter (OTC) interest rate products with optionality.

The standardized swaption contracts have 5 different expiries - 1M, 3M, 6M, 1Y, 2Y – and 7 underlying swap tenors - 1Y, 2Y, 5Y, 10Y, 15Y, 20Y, 30Y.

CME publishes daily data that include at-the-money volatility surface (both normal and lognormal), at-the-money strikes, swaption prices and annuities, all of which may be freely downloaded here.

Below you see the at-the-money strikes and normal vols quoted as of 10 Apr 2018. 

You may download the spreadsheet with the full raw data here.

Note the normal vol for a particular swaption is simply the annualized standard deviation that an assumed normal probability distribution of the swap rate at the time of the option's expiry should have in order for the calculated swaption price to match the market quotation.

For example, you see that the normal vol of the 1M into 1Y swaption is 31.93374 in units called "basis points". The respective at-the-money strike is 2.4855%, which is the forward swap rate, observed today for a swap that starts in one month and extends for one year. In basis points, 2.4855% is expressed as 248.55 bps. The vol 31.93374 simply means that if the swap rate were stochastic so that in a year from now it would be normally distributed with a standard deviation of 31.93374, the implied swaption price would exactly match the actual market price.

Normal vols represent only a convenient convention for quoting swaption prices and have nothing to do with the actual stochastic dynamics of the time evolution of the swap rate, which can be anything and even include jumps.

The quotation of swaption prices through normal vols has a significant advantage over the direct quotation of the prices themselves: As time passes by, the shortening of the expiry time and the change of interest rates bear a dramatic effect on the price of the swaption but not as such on its normal vol.

In other words, several swaptions that differ slightly from each other on expiry and tenor will exhibit different prices but almost the same vol even when interest rates keep changing.

Let's now pretend I want to buy a 1Y into 4Y swaption over the counter and I would like to know to what extent the price quoted by my broker is at par with the theoretical "fair" price implied by the CME traded products above.

Unfortunately I cannot directly compare the broker's quote with the CME quotations, as these do not include a 4Y swap tenor.

So I must calculate the swaption price myself by applying some mathematical model.

One such model is the so called Bachelier model, which assumes that the stochastic hypothesis for the normal vol quotation described above, is not only a measuring artifact but represents the reality in the sense that the forward swap rate evolves in such a way that at any future time is normally distributed.

Another often used model is the so called Black model, which assumes a lognormal distribution of the underlying forward swap rate.

Since 2012 when the bank of Denmark introduced negative deposit interest rates, it has become apparent that the Black model cannot represent the reality and banks have started shifting their analytics towards models that could cope with negative rates, such as the displaced Lognormal or the Bachelier.

In what follows, I will apply the Bachelier model as implemented by QuantLib for pricing my European 1Y into 4Y swaption in Excel. 

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Creating the Vanilla Swaption object

I will use the wizard to produce the formula that creates a Vanilla Swaption object representing a 1Y into 4Y European swaption on the 3-month USD LIBOR, as shown in the video below:  

The following image indicates the structure of the pasted formulas.  

Understanding the main formula

As you see, cell A1 contains the formula =ds(A2:B5), 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:B5 contains the following key/value pairs:

These are only the mandatory input data because I instructed the wizard to refrain from pasting the optional input data.

Setting the strike

You may have noticed that the wizard has set the strike equal to 4%. This is a hard-coded default value because no interest rate information was supplied to the wizard during the construction of the swaption object.

In order to set up an at-the-money swaption, I should set the strike equal to the forward swap rate with maturity equal to the option's expiry.

Deriscope provides a function that calculates the at-the-money rate for a given swaption. It even conveniently generates a default flat yield curve, which I can edit to reflect the actual market interest rates.

I will set the flat zero rate to 2.5% which is in the range suggested by the CME at-the-money strike data. This is of course different from the interest rates CME used to derive the reported data, but it helps me to prove the point that the important ingredient that I use from CME is the normal vols and not the interest rates that keep changing anyway.

In reality, traders do not recalculate the vol surface all the time, but they do take into account the actual interest rates.

Setting a flat zero rate of 2.5% is convenient for demonstration purposes and also implies time-dependent forward rates because the compounding convention is set to Simple.

The following video shows how I do all this to generate the spreadsheet formula that calculates the at-the-money swap rate for my already created swaption, using a yield curve with a flat zero rate of 2.5%: 

For the given flat yield curve, the at-the-money rate has been calculated to equal  2.5157542%.

Now I set the strike of my swaption to equal that value so that I end up with an at-the-money swaption (with respect to my yield curve), which I can afterwards price using the at-the-money normal volatility surface from CME.

This is how my spreadsheet looks like at this stage: 

Pricing the swaption

The following video shows how I use the wizard to generate the spreadsheet formulas required by the Price function.

Please pay attention on how I choose the Bachelier pricing method as model input and the at-the-money volatility surface as market input.

All other input data are automatically created by the wizard in an intelligent fashion so that they are compatible with my existing objects.

I also set the yield curve flat rate to 2.5% for convenience in order to force the wizard to reuse the existing spreadsheet yield curve object rather than creating a new one.

If I had kept instead the default flat rate of 4%, the wizard would have created a new yield curve object because it would have failed to find an existing object anywhere in the spreadsheet with a flat rate of 4%!

Below is the spreadsheet image with the pasted function in cell G1 that returns a European swaption price of 0.027674145. I have only shifted the VolCurve box upwards to make it visible.

Why is the price wrong?

I can translate the price into basis points by multiplying it by 10,000.

I thus get 276.74145 bps, which is very different from the prices 45.49152 and 119.21837 quoted by CME for the nearby 1Y into 2Y and 1Y into 5Y swaptions, as you can see in the table with the CME option prices below: 

Well, the reason is that I am still using the default volatility surface that has been generated by the wizard as the value for the Vol Table key in range J8:M10.

Let me replace this table with the one from CME shown on the top of this article, after I have divided the normal vols by 10,000 so that they are reported in natural units.

The swaption price in cell G1 (screenshot below) is now recalculated to 0.009373503, which is 93.73503 bps and therefore within the expected CME range. 

Can I replicate the CME price of the 1Y into 5Y swaption?

All I need to do is to increase the tenor of the underlying swap from 5Y to 6Y by setting 6Y as the new value for the Tenor key of the two Schedule objects in cells E5 and E17.

In order to keep my existing calculations intact, I create a duplicate sheet by using the Excel's right-click menu option for creating a copy of an existing sheet while keeping the original.

In my new cloned sheet, I go ahead and change the cells E5 and E17 as described and get the following: 

The new calculated price in cell G1 is 0.011870723, which is 118.70723 bps and still does not match the respective CME price of 119.21837 bps.

The deviation is due to the CME option having a strike of 2.8575% (see table on top of this article), whereas my option has a different strike of about 2.3423% as you see in cell B11.

The solution is not to set my strike equal to the CME strike!

If I do that, I will indeed have the exact same swaption, albeit a swaption that ceases to be at-the-money under my current yield curve!

Remember that the CME vol surface I have been using as input to my Bachelier pricing is supposed to be the at-the-money vol and thus designed to reproduce the prices of at-the-money swaptions.

So in order to reproduce the CME 1Y into 5Y swaption price, I should make it into an at-the-money swaption by setting its strike equal to the atm rate computed in cell A16 as 2.5166955%.

If I do this, I get a price of 118.92147 bps in cell G1, which is still closer to the CME price of 119.21837 bps.

The proximity is indeed impressive given the fact that I am using a different yield curve than the one implied in the CME quoted market price and attests to my earlier point that moneyness (zero in this at-the-money case) and vol are the important ingredients in the pricing of swaptions.

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