USD Swaption Pricing in Excel using SABR Stochastic Volatility and Market Vol Cube from CME

The pricing of exotic interest rate products cannot ignore the so called market volatility cube that is made daily available by several swaption brokers.

Traders often use the SABR Stochastic volatility model in order to estimate vols off the provided grid.

In this article I will show you how to price an out-of-the-money swaption by applying SABR calibration on the volatility cube I received from the Chicago Mercantile Exchange (CME) containing cleared European swaption trades on 3-month USD LIBOR as of 16 April 2018.

It may be helpful if you have already read my previous article dealing with swaption pricing based on an at-the-money volatility surface received from CME.

CME clears European swaption trades with 5 different expiries - 1M, 3M, 6M, 1Y, 2Y – and 7 underlying swap tenors - 1Y, 2Y, 5Y, 10Y, 15Y, 20Y, 30Y.

Below you see the at-the-money Black vols quoted as of 16 Apr 2018.

The next image contains the out-of-the-money Black vols for the following strike offsets (in basis points) from the respective atm strike: ±200, ±100, ±50, ±25. Note that a few strike-expiry-tenor combinations are so illiquid that no data are available.

You may download the spreadsheet with the full raw data, including the forward rates, black vols, normal vols and annuities here. This spreadsheet also contains the chart you see at the cover image above, which is an overlay of three Black volatility surfaces corresponding to strike offsets of -100, 0 and 100 as of 16 Apr 2018:

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

About the SABR model

The SABR(Stochastic Alpha Beta Rho) volatility model (2002) describes the time evolution of a single forward F - such as a forward swap rate with a given maturity and tenor or a forward stock price with a given maturity - as a two-factor diffusion process that follows the SDE:

dF = σ(F^β)dw

where w is a Wiener process, β is the Beta constant and σ is the forward's stochastic volatility, which itself follows the SDE:

dσ = νσdω

where v (Nu) is constant and ω is another Wiener process having correlation ρ (Rho) with w .Web reference available here.

We refer to the initial value of σ as α (Alpha), i.e. α = σ(0)

Typically one uses the SABR model not only for a single forward F but rather for the simultaneous description of the evolution of a collection of forwards, like – for example - the forward swap rates F(T1,T2) spanned by several combinations of forward swap start dates T1 and forward swap maturities T2.

In the latter case, for each pair (T1,T2) the respective forward swap rate F(T1,T2) is assigned its own quartet of SABR parameters α(T1,T2), β(T1,T2), ν(T1,T2) and ρ(T1,T2).

The following image illustrates this situation by picking up three different forward rates defined through their start date interval τ1 and swap tenor τ2, both expressed as time intervals. These three (τ1,τ2) pairs are (1Y,1Y), (2Y,3Y), (4Y,3Y) and correspond to three points on the bottom floor spanned by the x axis (start date interval) and y axis (swap tenor). The vertical axis measures the time t in annual units so that the origin t = 0 corresponds to today and a positive t corresponds to a date in the future.

Each immersed path is plotted against its own 2-dimensional coordinate system, of which the vertical axis coincides with the global time axis and the horizontal axis measures the level of the forward rate realized at time t. 

For simplicity I assume a flat yield curve so that all three forward rates start at 2% at time 0.

My initial modelling assumption is that the (1Y,1Y) forward rate evolves with SABR parameters α₁, β₁, ν₁, ρ₁, the (2Y,3Y) rate with α₂, β₂, ν₂, ρ₂, and the (4Y,3Y) rate with α₃, β₃, ν₃, ρ₃.

The parameters are so chosen that the "overall volatility" increases as we move from (1Y,1Y) to (2Y,3Y) to (4Y,3Y).

The image shows one sample future path for each of these rates, as generated from the respective SABR diffusion equations. 

The SABR-implied volatility cube

For any given set of parameters α, β, ν, ρ applying on a pair (T1,T2), the diffusion of the respective forward rate F(T1,T2) is known and therefore one is able to calculate the price of every European option that relies on that particular forward rate F(T1,T2).

This means, the prices of all European options differing on their strike K but sharing the same expiry T1 and underlying tenor T2 – T1 can be calculated.

In practice one considers only the pairs (T1,T2) that correspond to the European swaptions actually traded in the market, collects the available strikes K and finally calculates the SABR-implied theoretical prices SABR(T1,T2,K).

In practice again, one works with a reparametrization of T1, T2, K in terms of τ1, τ2, k, where k is defined as the spread K – ATM, where ATM is the at-the-money strike, i.e. the known value of F(T1,T2) at time 0.

The reason for this reparametrization is twofold.

First, whereas T2 takes several different values as the maturity dates of the various underlying swaps are mostly different from each other, τ2 takes only a few values since several swaptions share the same tenor.

Second, no two swaptions share the same strike K, but several share the same strike spread k.

The conclusion is that plotting the points (τ1,τ2,k) is far easier than plotting the points (T1,T2,K), involves fewer coordinates and results in a symmetrical 3-dimensional lattice.

In the following image I deploy a 3-D coordinate system, where the x axis represents the expiry interval τ1, the y axis the swap tenor τ2 and the vertical z axis the strike spread k. I consider three different values of k measured in basis points, namely -100, 0 and 100 and use the same combinations of τ1, τ2 as in the example above.

The result is nine points that represent 9 different swaptions, arranged as three triplets, where each triplet lies on the same vertical line corresponding to a fixed pair (τ1,τ2).

Each triplet is then priced using the corresponding SABR parameters that I had chosen in my earlier example and the thus calculated price SABR(τ1,τ2,k) - quoted in terms of the equivalent Black vol or normal vol - is displayed on the side of each point.

The resulting grid along with the calculated prices is known as the SABR-implied volatility cube. 

The market volatility cube

The market volatility cube is constructed by plotting the points (τ1,τ2,k) and then assigning to each point the actual market price Market(τ1,τ2,k) of the corresponding European swaption, quoted in terms of the equivalent Black or normal vol.

Continuing with the three swaptions example, the resulting diagram looks like that: 

Estimating the SABR parameters 

The two cubes (SABR and market) span the same grid of points but carry different vols on those points.

The next task is to find the numbers α, β, ν, ρ for each pair (τ1,τ2) so that the discrepancy between the two sets of vols becomes minimum in some well-defined collective sense.

This is achieved by running a multi-variate optimization routine, whereby several sets of α, β, ν, ρ parameters are tried out until the optimal set is found.

Deriscope runs this routine when I create an object of type Vol Curve and give as input a market volatility cube.

The resulting Vol Curve object will then contain the optimal set of SABR parameters.

Deriscope also allows me to optionally supply initial guess values for the constant parameters α, β, ν, ρ for each pair (τ1,τ2), which amounts to supplying a total of 4*N1*N2 values, where N1 is the number of different expiries and N2 the number of different swap tenors.

Alternatively, I may also supply four flat guess values α, β, ν, ρ that apply to all (τ1,τ2) pairs. 

Using the optimal set of SABR parameters 

If I am interested in pricing a single European swaption for which the triplet (K,T1,T2) already corresponds to one of the grid points, then I should obviously use the appropriate vol read off the market volatility cube.

But if I need to price a European swaption for which the triplet (K,T1,T2) corresponds to a point (τ1,τ2,k) that falls between the grid points, then I am better off interpolating the α, β, ν, ρ parameters of the neighboring points in the SABR cube and then extracting the corresponding vol than interpolating directly the vols of the neighboring points in the market volatility cube.

In practice, I achieve that by creating a Vol Curve object using a market volatility cube input as described above and feed that object to the Deriscope pricing formula as part of the Market input.

I should also follow this approach when I price interest rate products the values of which depend on several forward rates, such as Bermudan swaptions and constant maturity swaps. 

Running a real world example

I will try to price a 1Y into 5Y European swaption on the 3-month USD LIBOR

First I set my trading date to 16 April 2018 because I am going to use the CME market data as of that date.

Then I use the wizard to create the respective Vanilla Swaption object.

Because I reside in Germany, the wizard sets the index to the 6 month Euribor by default so that I need to change it to the desired 3 month USD Libor. If I had resided in the US, the wizard would have created a USD Libor by default and I would only need to tweak its tenor from the default 6 months to my target of 3 months.

Also the wizard creates by default an underlying spot-starting swap with a 5 years length.

Since I am interested in a 1Y into 5Y European swaption, the underlying spot-starting swap must have a length of 6 years.

I could have kept the wizard's 5 years default and make the required change in the spreadsheet after the wizard had finished its job.

Nevertheless as you see in the video below, I choose to adjust the tenors of both legs of the underlying swap inside the wizard, so that the correct structure is generated before it gets pasted in the spreadsheet. 

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.

So I go ahead and create a yield curve, which I build for simplicity with a flat zero rate of 2.5%.

Then I use the Deriscope function ATM Rate to calculate the relevant forward rate off this yield curve and set the result 2.5166732% as the strike of my swaption.

You can see the details of these steps in the respective section of my previous article.

The final result is an at-the-money European swaption as shown below:

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 set the Vol Input parameter of the Vol Curve object to Swaption Cube.

I do that so that the wizard generates a volatility cube as input market data to the Price function.

Of course I will have to manually edit this default volatility cube later on in the spreadsheet so that it reflects my CME market data.

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 "littering" my spreadsheet real estate with unnecessary yield curve creation formulas. 

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

Understanding the volatility cube structure generated by Deriscope

The above price of 0.010071093 assumes the volatility cube created by the wizard as the object named &Tbl3DNum_G40:1.1 in cell J9.

This object is of type HyperTable and contains three two-dimensional sub-tables.

For convenience, I display below the Vol Curve object with its volatility cube input in isolation, with the sub-tables shifted to the right for a better viewing: 

I have selected the cell J9 so that you can see the formula in the formula bar that creates the HyperTable object.

In spite of the apparent complexity, the input that creates a HyperTable object is very simple.

It only requires two keys, called Coords and Tables.

The value expected by the key Coords is an object of type Set, but Deriscope allows me as a shortcut to enter directly a range containing the expected coordinates, without the need to create yet another object.

This is exactly what is done here, where the input range J14:J17 contains the 3 coordinates -0.01, 0, 0.01 labeled as #AtmSpread. The # sign is important and indicates that #AtmSpread serves as the identifier of the respective coordinates.

You notice that these are the same as atm spreads of -100, 0, 100 basis points expressed in natural rate units.

The value associated with the second key Tables is an array of ranges that correspond to the two-dimensional sub-tables carrying the volatility data that correspond to the #AtmSpread coordinates.

So the first sub-table in range M10:P12 corresponds to the first coordinate and so on.

Deriscope expects that the middle sub-table, i.e. the one corresponding to the zero atm spread, contains the at-the-money market swaption vols, whereas the remaining sub-tables contain the respective vol spreads, i.e. the differences between the respective market vols and their at-the-money counterparts.

As you see, Deriscope has by default created an at-the-money sub-table with flat volatilities of 20%, a sub-table corresponding to the -0.01 atm spread with flat vol spreads of 2% and a sub-table corresponding to the 0.01 atm spread with flat vol spreads of 4%. 

Linking to my CME market data

All I need to do now, is replacing the contents of the HyperTable object with the atm spreads and volatility sub-tables available from CME.

The following image shows the Vol Curve object with its volatility cube input created using the data from CME: 

The important object here is the HyperTable named &Tbl3DNum_J9:1.1 created in cell J9.

Note that some of the sub-table entries are left blank due to the lack of respective data from CME.

In that case, Deriscope fills these entries internally with values derived by applying bilinear interpolation on the neighboring data. 

Error resolution

Unfortunately, after creating my volatility curve using the CME data, cell G1 returns Error!

Before giving in to panic, I select cell G1 while having the wizard open.

Then I see the following error message: 

This message comes straight from QuantLib and tells me that the provided market vols of the various 1M to 1Y swaptions could not be matched after all allowed variations of the SABR parameters α, β, ν, ρ for the pair (1M,1Y) have been tried.

It also tells me that the root-mean-square discrepancy (across the set of different swaptions) between market and SABR has been 0.0128637 and that one particular swaption has suffered the highest discrepancy of 0.0191815.

This failure is actually not that surprising!

If you see the sub-tables with the CME vol spreads, you will notice that the 1M to 1Y vol for the -200 atm spread is almost 90% and that the respective value for the 200 atm spread is completely missing!

It is indeed likely that either a few CME data are stale due to illiquid market conditions or that the bilinear interpolation applied by Deriscope on missing CME data introduces unrealistic vol values.

In both cases, it turns out that some market values cannot be matched by the SABR model within the specified tolerance.

The last remark provides the solution to this problem.

All I need to do is increase the tolerance so that success is reported even if a high discrepancy is reached for a few (τ1,τ2) pairs. 

Adjusting the SABR tolerance

If I select the cell J1 with the wizard open, I see the following contents of the Vol Curve object:  

I notice an element called SABR Model, but no such input to the Vol Curve object is visible in the spreadsheet! Where does it come from?

The answer is that the SABR Model is an optional input to the Create function of the Vol Curve type. Since I had chosen to paste only the mandatory inputs in the spreadsheet, the SABR Model input assumes its default value seen in the wizard, but does not exist anywhere in the spreadsheet.

If I click on the lens sign, I can see the contents of that default SABR Model object: 

The highlighted Error Tolerance of 0.01 is obviously the culprit as it is set to less than the reported rms error of 0.0128637.

All I need to do is change the Error Tolerance to something higher than 0.0128637 in order to get through this safety barrier.

But then a different (τ1,τ2) pair could perhaps break once more the optimization task, so it is prudent to set it to something much higher, such as 1.0.

Since I cannot change the Error Tolerance value inside the wizard, I will have to first create a SABR Model object in the spreadsheet.

But how can I locate the SABR Model type in the wizard's Type Browser?

If I click on the lens sign in the cell containing the SABR Model type (see previous image), I am informed that SABR Model derives from Model[Quotable], which derives from Model.

So I can easily now create a SABR Model object as the following video demonstrates: 

Next I edit the cell next to the Error Tolerance key so that it contains 1.0 instead of 0.01 and then insert the appropriate key-value pair in the input range of my Vol Curve object.

The next video shows these two simple steps: 

As you noticed in the above video, after I completed the insertion of the SABR Model key-value pair in the Vol Curve creation formula, I browsed through the object's contents in order to verify that the Error Tolerance had now indeed the new value of 1.0.

But I also saw the ¬Calibration Failure Info entry that reported the QuantLib message that "Max Iterations reached".

I can easily resolve this error by setting 60,000 for the Max Iterations entry in cell K53.

This is actually a Deriscope bug (!), since the wizard owed to know of that high Max Iterations requirement while it was generating the inputs for the SABR Model creation formula! You may … send your complains to the developer.

Anyway, now the calibration succeeds and cell G1 reports a swaption price of 0,01020462 as the next screenshot betrays: 

Evaluating the quality of the SABR calibration

The Deriscope wizard reports several data about the SABR calibration.

I only need to browse through the contents of my Vol Curve object and inspect inside the Sparse SABR Params field, where I see the following: 

Each row corresponds to a fixed (expiry, tenor) pair.

The first two columns define the tenor and expiry respectively in annual units.

The next four columns report the calculated SABR parameters α, β, ν, ρ in that sequence.

The next column reports the atm forward swap rate.

The next two columns report the root-mean-square and maximum approximation error achieved during the respective optimization routine run over the available swaptions with the respective (expiry, tenor) pair.

The error is in the same units as the market volatility being used.

So if Black vols are used, a maximum approximation error of 0.03 means that the calculated SABR parameters α, β, ν, ρ are such that the SABR-implied swaption Black vols differ from the supplied market Black vols by amounts that do not exceed 3% (absolute difference).

Obviously the smaller the error, the better the market fit.

Note that a very high maximum approximation error may coexist with a low root-mean-square error, which would indicate that most strikes have been well matched, but at least one strike had a bad fit.

The final column contains an integer that indicates the kind of criteria used by the optimization routine in order to reach the solution, according to the map:

1 <-> Max Iterations

2 <-> Stationary Point

3 <-> Stationary Function Value

4 <-> Stationary Function Accuracy

5 <-> Zero Gradient Norm 

Sanity check: Reproducing the CME vol input

As a final check, I can calculate the implied Black vol of the produced swaption price 0.01020462 and see how close it comes to the corresponding CME vol of 22.16373622%.

I can use the Deriscope Implied Vol function as this video shows: 

The result is 22.300487% in cell G32, which is higher by around 0.14% from the CME 22.16373622% in absolute terms.

This proximity can be further improved by setting Vega Weighted = TRUE in cell K38, which runs the optimization with higher weight on the at-the-money 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