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

*normal volatility surface*of the above data as of

**10 Apr 2018**:

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:

Key | Value | Description |

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

Expiry | 12.04.2019 | The date at which the option holder has the right to exercise the option. |

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.027 674145**. 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 **2 76.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.**and therefore within the expected

**73503**bps*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.0 11870723**, which is

**118.**and still does not match the respective

**bps****70723***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 **11 8.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