# Fully Functional Excel Spreadsheet for SOFR Swaption Trading with Lognormal, Normal and Shifted Lognormal SABR Vol Calibration

Setting up an interest rate swaption trading desk is a complex endeavour. First, the type of the underlying interest rate should be defined, and it can be either a still existing ibor rate such as EURIBOR or JPY TIBOR or – in the case of LIBOR that has been discontinued as of June 30, 2023 - an equivalent ARR (Alternative Reference Rate) such as a forward-looking Term SOFR or backward-looking compounded SOFR average. Then the available market spot swap rates and swaption volatilities must be correctly interpreted and bootstrapped to build the implied discounting and forecasting yield curves and the implied volatility cube respectively. Finally, the trader must be able to construct any number of bespoke trades and calculate their price and risk.

In this post I will present the details of an Excel spreadsheet I put together for one of my clients who was interested in setting up a swaption trading desk. The emphasis is on the systematic presentation of the building blocks that are essential for market calibration and portfolio pricing. For the sake of simplicity, market vol data are restricted on the three expiries 6M, 1Y, 2Y and four swap tenors 6M, 1Y, 2Y, 3Y. Actual SOFR OIS rates are used as of December 29, 2023. The market vols are introduced in three independent forms - lognormal, normal and shifted lognormal – with made up values to facilitate the comparison of their corresponding implied swaption prices.

The underlying swap rate is the rate of a SOFR OIS that pays ACT/360 annual compounded SOFR against ACT/360 annual fixed, but this may be easily replaced by a less-than-annual Term SOFR (such as a 3M Term SOFR) or a conventional ibor. The compounded SOFR is treated in a forward-looking manner, which is not a problem if the calibrated swaptions reference a term SOFR as well. If this is not the case, the market vols must be – in principle - suitably adjusted. In practice though, no adjustment should be necessary if both the calibrated swaptions and the to-be-priced swaptions reference a backward-looking compounded SOFR, since the effect from mispricing the backward-looking feature ought to be absorbed largely by the calibrated volatilities.

For a thorough understanding of the SABR model and its calibration using lognormal Libor swaption vols, you are referred to my earlier post on USD Swaption Pricing in Excel using SABR Stochastic Volatility and Market Vol Cube from CME.

Some insight on the volatility impact of forward- versus backward-looking compounded SOFR rates in the context of cap/floor pricing is offered at my post on Using the Bootstrapped Market SOFR Caplet Normal Vol Surface to Price in Excel Interest Rate Caps/Floors on Backward/Forward Looking SOFR Term Rates.

### Table Of Contents

- The SOFR OIS Yield Curve
- Defining the Swap Rate Referenced by the Market Swaptions
- Setting up the SABR Calibration of Market Lognormal Vols
- Viewing the Results of the SABR Calibration of Market Lognormal Vols
- Working with frozen SABR Parameters calibrated to Market Lognormal Vols
- Extracting Implied Swaption Vols from the calibrated Volatility Cube
- Viewing the Market Swaptions
- Viewing the Dollar Prices of the Market Swaptions
- Viewing the Black Vols of the Market Swaptions
- Viewing the Normal Vols of the Market Swaptions
- Viewing the Shifted Lognormal Vols of the Market Swaptions
- Calibrating the SABR Model to Normal Market Vols
- Calibrating the SABR Model to Shifted Lognormal Market Vols
- Building a Single Custom Swaption
- Preparing the Pricing Models
- Pricing the Custom Swaption using the Lognormal Vol Cube
- Displaying the Cash Flows of the Underlying Swap referenced by the Custom Swaption
- Pricing the Custom Swaption using the Normal Vol Cube
- Pricing the Custom Swaption using the Shifted Lognormal Vol Cube
- Building and Pricing a Portfolio of Swaptions
- Calculating the Implied Vols of all Swaptions within a Portfolio
- Displaying the Cash Flows of the Underlying Swaps referenced by the Portfolio Swaptions
- Displaying the SABR Risk of the whole Portfolio of Swaptions
- Download Spreadsheet

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

### The SOFR OIS Yield Curve

The *Yield Curve* used for forecasting the reference floating rates and discounting the swaption cash flows is produced in **cell C4** below with the trade date assumed to be the January 3, 2024. The shown SOFR OIS market rates are actual rates observed on December 29, 2023, but are assumed here to apply on the trade date of January 3, 2024.

More details on building a Yield Curve out of SOFR OIS are at my post How to build a SOFR Yield Curve in Excel using QuantLib and Deriscope.

### Defining the Swap Rate Referenced by the Market Swaptions

Each swaption in the market volatility cube is defined by its expiry date, strike and the structure of the swap index that corresponds to the underlying swap that commences on the expiry date.

The swap index is represented in Deriscope by a Deriscope Object of Deriscope Type that of *Swap Rate*, which must be defined before the market volatility cube can be constructed.

Below, the *Swap Rate* object is created in **cell C4** by the formula =ds(C5:D14) that returns the handle name **&RefSwpRate.1**.

Note, the tenor of 1Y is arbitrary and has no significance for the construction of the volatility cube since the tenor for each vol quote will be reset to match the tenor of the corresponding swaption. All other quantities are important and will apply on all vol quotes.

### Setting up the SABR Calibration of Market Lognormal Vols

The SABR calibration is performed by the formula =ds(H5:I13) in **cell H4** below.

The red-colored cells contain the Deriscope formulas. Details on the meaning of the shown colors and the syntax of the Deriscope spreadsheet formulas can be found in this introductory post about using Deriscope in Excel.

The produced object identified by the handle name **&LogCalibVolCrv.1** is of Deriscope type *Vol Curve* and contains the calibration results, as we will see shortly.

The important modelling input is through the *key-value pair* (<= https://www.deriscope.com/products/Key-Value_pair.html) Sabr Model= **&PreCalibSabrMdl.1** in the **range H12:I12**. The object **&PreCalibSabrMdl.1** has been previously created in **cell C4** through the formula =ds(C5:D25) and is of type *SABR Model*.

The Boolean *value** *in **cell D8** next to the *key* **Is Array=** must be **FALSE** to be able to treat the initial guess values of the SABR parameters α, β, ν and ρ as single numbers.

It is possible to set it to **TRUE**, but then the guess values would need to be entered as arrays, which probably makes little sense since it is difficult to "guess" a very large number of values.

The shown guess values are arbitrary.

The 4 *keys* **Alpha fixed=**, **Beta fixed=**, **Nu fixed=** and **Rho fixed=** expect Boolean values that control the SABR parameters α, β, νand ρ during calibration.

When TRUE, the respective SABR parameter X is kept fixed during calibration with its value set equal to the value entered for the key "X Guess=".

In the current usage, at least one of the Booleans must be **FALSE** to run a meaningful calibration against the given market vols.

Otherwise, if all Booleans are **TRUE**, the market vols are ignored and effectively no calibration takes place.

I have chosen to treat the initial guess value 0.5 of the β parameter as fixed and thus calibrate only the remaining 3 parameters α, νand ρ.

The market vols are entered in the form of tables in the **columns K, L, M, N** and **O** and quoted in lognormal terms because of the entry **Black** in **cell I35** next to the *key* **Vol Type=**.

Each matrix corresponds to a strike taken from the 7 strikes entered as spreads over the ATM (At-The-Money) rate in the **range H22:H28.**

The table in the middle – highlighted in light blue and shown once more below – corresponds to the strike-spread 0 in **cell H25** and holds the ATM lognormal vols:

The top table – shown once more below - corresponds to the strike-spread **-0.75%** in **cell H22** and holds the lognormal vols, albeit in spread notation, meaning that – for example - the **10.15%** quote for the 6M into 6M swaption represents the vol spread relative to the corresponding ATM vol of **12.46%** so that the absolute vol value equals 12.46% + 10.15% = 22.61%.

Similar holds for the remaining non-ATM vol tables.

In this example the ATM vol table spans the same grid of expiries and tenors as the non-ATM vol tables. It is nevertheless possible for the ATM vol table to contain a denser grid with expiries and tenors that are not present in the non-ATM vol tables.

### Viewing the Results of the SABR Calibration of Market Lognormal Vols

The object **&LogCalibVolCrv.1** contains the results of the vol calibration, which can be viewed in the Deriscope wizard's Browse Area by simply selecting the **cell H4** while the wizard is open.

Below is displayed a small portion of the object's contents.

In red rectangle is shown the key **_Sparse SABR Params=** of which the associated value **$set_#5** is an object of type *Set* that contains the calibrated SABR parameters. Clicking on the lens icon, the set's contents are as below:

As described at How to Display any Object Contents in the Spreadsheet, these data can be dynamically inserted in the spreadsheet with the help of the *Show* function, as below:

Coming back to the contents of the object **&LogCalibVolCrv.1**, the *key* **_Dense SABR Params=** is of interest only when the ATM vol table spans a denser grid of expiries and tenors compared to the non-ATM vol tables.

Of practical interest are the *keys* **_Sparse Alpha=, _Sparse Beta=, _Sparse Nu**, and **_Sparse Rho=**, of which the associated *values* contain the respective calibrated SABR parameters in a convenient tabular form. Below are the corresponding four tables. Each table makes use of the *Show* function to return the data associated with the *key* shown in blue font in the cell besides the table's top left corner:

The *key* **_Sparse ATM Fwd =** contains the ATM strikes, i.e. the forward swap rates, for each expiry-tenor combination in tabular form, as shown below:

###
Working with frozen SABR Parameters calibrated to Market Lognormal Vols

Traders calibrate the SABR model infrequently, once daily or a bit more often if the market is volatile. Deriscope can flag any supplied initial guess SABR parameters as "frozen" - and thus skip the calibration routine – by setting the *keys* **Alpha fixed=**, **Beta fixed=**, **Nu fixed=** and **Rho fixed=** in the *SABR Model* object to **TRUE**. Doing so, it is sensible to also set **Is Array=** **TRUE** that allows the array specification of the initial guess SABR parameters.

The calibrated SABR parameters found above are copied and pasted as values in the ranges shown below. The blue font serves as a visual indicator that the numbers are stored in the respective cells as constant values rather than as outputs of spreadsheet formulas. This ensures the numbers do not change if the spreadsheet is recalculated.

The next step involves the creation of the new object **&LogSabrMdl.1** of type *SABR Model* in **cell AV4** that uses the above values as input for the initial guess SABR parameters along with setting the above-mentioned Booleans to **TRUE**:

The final object of type *Vol Curve* is then created in **cell AV15** with the *handle name* **&LogVolCrv.1**:

This object contains the calibrated vol cube and will be used as input to all functions that calculate the price and risk of any bespoke swaptions.

Since the latter functions also require the input of the yield curve, it is convenient to create the object **&LogMktCol.1** that represents the collection containing the vol curve and the yield curve, as below:

### Extracting Implied Swaption Vols from the calibrated Volatility Cube

The object **&LogVolCrv.1** comes with several Deriscope Functions that are listed in the Deriscope wizard's Function Selector, as shown below:

Highlighted in yellow is the function *Implied Value*, which can be used to return vols implied by the selected *Vol Curve* object.

Clicking on the wizard's Go button results to the spreadsheet formula shown below that returns the implied swaption vol - calculated as **20.857%** - for the indicated strike, expiry (corresponds to the **Maturity=** *key*) and tenor (corresponds to the **Swap Maturity=** *key*)

It is interesting here to see how this value of **20.857%** compares with the corresponding market vol.

The two relevant market vol tables are those associated with the strike spreads of **-0.25%** and **0%**, which are those below:

In red circle are the vols associated with the 6M expiry and the 2Y tenor.

Their sum equals **21.17%**, which is the absolute market vol for the 6M into 2Y swaption striked at ATM – 0.25%.

We conclude that the implied vol of **20.857%** misses the market vol of **21.17%** by **0.313%**.

How does this mismatch size of **0.313%** compares with the table displaying the calibration results?

The latter table is shown again below, with the yellow highlighted row corresponding to the 6M expiry and the 2Y tenor.

The two numbers in red circles are the Root Mean Square error of **0.33%** and the maximum error of **0.59%**.

It turns out, the calculated mismatch of **0.313%** is well within the error margins reported by the calibration routine.

In the above example, the *Implied Value* function was invoked with a single triplet (strike, expiry, tenor) input and returned the corresponding implied vol.

But the function also supports the input of several triplets that may be either explicitly or implicitly defined.

Below, the *key-value pair* **All Combs= FALSE** means the triplets are specified explicitly, with each row in the **range H15:J17** defining the corresponding triplet.

So, the output value of 36.061% is the implied vol for the triplet (2%, 3M, 2Y) at the first row of the mentioned range.

The blank strike cell at the second row indicates the ATM strike, whereas the blank tenor (Swap Maturity) cell defaults to the value 2Y above it. Therefore, the output value of 19.183% is the implied vol for the triplet (ATM, 6M, 2Y) at the second row.

Finally, the output value of 23.363% is the implied vol for the triplet (3%, 6M, 2Y) at the third row.

Below is the same example with the only difference that **All Combs= TRUE**.

In this case, any blank cells below **Maturity=** and **Swap Maturity=** are ignored, which means the input data define three strikes, two expiries and one tenor. It turns out all combinations give rise to a two-dimensional matrix of which the two axes span the three strikes and the two expiries. The *Implied Value* function returns in **cell N9** an object of type *Table 2D*.

The table's contents can be easily displayed in tabular form on the spreadsheet with the help of the *Show* function, as shown below in the **range N4:P7**.

Below is an example that results to a 3-dim table of implied vols due to the fact there exist more than one values for each of Strike, Maturity and Swap Maturity.

The 3-dim table consists of the array of the two strikes and two 2-dim sub-tables, the contents of which can be displayed as shown below.

Using the function *Rearrange*, the above 3-dim table can be easily rotated and/or transformed to a 2-dim or 1-dim table.

For example, the object in **cell AL12** below is a 2-dim table of which the vertical axis spans the combined *Strike* and *Maturity* (i.e. expiry) pairs and the horizontal axis spans the *Swap Maturities*. This structure is encoded in the input text "Str+Mat,SwapMat" in **cell AM15**. The + symbol indicates the merging of the two axes identified by the operands. In this case the operands are the texts "Str" and "Mat" that uniquely identify the axes related to the *titles* **#Strike** and **#Maturity** respectively.

The contents of the produced 2-dim table can be displayed with the help of the *Show* function as below:

### Viewing the Market Swaptions

Deriscope supports the generation of several objects and quantities linked to the calibrated *Vol Curve* object, provided one takes care to set a few Boolean flags to **TRUE**.

In **cell C4** below, a new *Vol Curve* object is created as a clone of the original **&LogVolCrv.1**, albeit with the mentioned Boolean flags set to **TRUE**.

The new object is named **&LogVolCrvImplData-Black.1** and contains several interesting datasets, which can be seen in the wizard as soon as the **cell C4** is selected:

The element associated with the *key* **_Sparse Mkt Swaptions=** contains the market swaption instruments used in the calibration. This dataset can be of interest when comparing the Deriscope results against those from a different system, in which case one would wish to verify that the calibration instruments are constructed correctly.

Clicking on the corresponding lens icon, the following table is displayed in the wizard:

The swaptions are laid out as a 2-dim table with the vertical axis spanning all #AtmSpread and #Expiry combinations and the horizontal axis spanning all #SwapMat values. This configuration is the result of the definition:

**Implied Tables Config= AtmSpread+Expiry,SwapMat**

inside the **&LogVolCrvImplData-Black.1** object and can be changed by changing that definition.

The details of each of the 21x4 = 84 swaptions can be inspected by clicking on the corresponding lens icon.

For example, doing so on the first instrument identified as $VanSwaption, the following data are displayed:

One may also view the precise cash flows of this swaption's underlying swap entered upon its exercise (or the swap rate's conventions if the swaption is cash-settled) by clicking on the lens icon of the _**CashFlows** element shown in red rectangle at the bottom:

###
Viewing the Dollar Prices of the Market Swaptions

The element associated with the *key* **_Sparse Mkt Prices=** inside the **&LogVolCrvImplData-Black.1** object contains the actual dollar prices of the market swaption instruments inferred from their market vols, whereby all swaptions are assumed to have a notional of 1$.

Below is the table on the spreadsheet with these prices as returned by the Deriscope *Show* function:

The element associated with the *key* **_Sparse Impl Prices=** inside the **&LogVolCrvImplData-Black.1** object contains the theoretical dollar prices of the market swaption instruments implied by the calibrated SABR model.

Below is the table on the spreadsheet with these prices as returned by the Deriscope *Show* function.

As expected, these prices differ slightly from the actual prices since the calibrated vols do not reproduce exactly the market vols.

### Viewing the Black Vols of the Market Swaptions

The element associated with the *key* **_Sparse Conv Mkt Vols=** inside the **&LogVolCrvImplData-Black.1** object contains the actual Black vols of the market swaption instruments in absolute terms, i.e. the non-ATM vols are displayed as absolute vols rather than as spreads, which was the case with the vols that were fed as input to the calibration routine.

Below is the table on the spreadsheet with these absolute vols as returned by the Deriscope *Show* function:

The element associated with the *key* **_Sparse Impl Vols=** inside the **&LogVolCrvImplData-Black.1** object contains the calibrated market Black vols, i.e. the theoretical Black vols of the market swaption instruments implied by the calibrated SABR model.

Below is the table on the spreadsheet with these vols as returned by the Deriscope *Show* function:

### Viewing the Normal Vols of the Market Swaptions

The Vol Curve object **&LogVolCrvImplData-Black.1** only contains implied Black vols because it was constructed using the input:

**Impl Vol Spec= &ImplVolSpec-Black.1**

where the rhs element is an object of type *Impl Vol Spec* that was constructed to contain **Vol Type= Black**.

To generate the implied normal vols, the *key* **Impl Vol Spec= **should be set to a different *Impl Vol Spec* object that has **Vol Type= Normal**.

Below, the required *Vol Curve* object is created in **cell C31** and carries the *handle name* **&LogVolCrvImplData-Norm.1**.

Now the element associated with the *key* **_Sparse Conv Mkt Vols=** inside the **&LogVolCrvImplData-Norm.1** object contains the actual normal vols of the market swaption instruments in absolute terms.

Below is the table on the spreadsheet with these vols as returned by the Deriscope *Show* function:

The element associated with the *key* **_Sparse Impl Vols=** inside the **&LogVolCrvImplData-Norm.1** object contains the calibrated market normal vols, i.e. the theoretical normal vols of the market swaption instruments implied by the calibrated SABR model.

Below is the table on the spreadsheet with these vols as returned by the Deriscope *Show* function:

### Viewing the Shifted Lognormal Vols of the Market Swaptions

Like how the implied normal vols were generated, doing so for the shifted lognormal vols requires the creation of the appropriate *Vol Curve* object.

This time, the *Impl Vol Spec* object must contain **Vol Type= Shifted Lognormal**.

Below, the required *Vol Curve* object is created in **cell C46** and carries the handle name **&LogVolCrvImplData-Shift.1**.

Note the shift size is set at **1%**.

As before, the element associated with the *key* **_Sparse Conv Mkt Vols=** inside the **&LogVolCrvImplData-Shift.1** object contains the actual shifted lognormal vols of the market swaption instruments in absolute terms.

Below is the table on the spreadsheet with these vols as returned by the Deriscope *Show* function:

The element associated with the *key* **_Sparse Impl Vols=** inside the **&LogVolCrvImplData-Shift.1** object contains the calibrated market shifted lognormal vols, i.e. the theoretical shifted lognormal vols of the market swaption instruments implied by the calibrated SABR model.

Below is the table on the spreadsheet with these vols as returned by the Deriscope *Show* function:

### Calibrating the SABR Model to Normal Market Vols

If the market vols are quoted by the broker in normal terms, the *Vol Curve* can still be generated almost as was done in the case where the market vols were quoted in Black terms.

For example, let us assume the normal market vols are tabulated in a 2-dim form as below:

As usually, the non-ATM quotes are represented as spreads over their corresponding ATM values.

I have chosen these numbers so that their corresponding absolute (i.e. non-spread) values exactly equal the values shown above for the Actual Normal Market Vols so that they convey the exact same volatility information as the already seen Black vols.

The screenshot below shows how these quotes are fed as input to the formula that creates the *Vol Curve* object **&NormCalibVolCrv.1** in **cell C4**.

The only difference from the previous construction is the entry **Normal** in **cell D20**, which tells the calibration routine that the input vols are quoted in normal terms.

As was done earlier, below is the table with the calibration results:

As was done with the Black vols earlier, so here a final *Vol Curve *object must be created that skips the calibration step by keeping the initial guess SABR parameters constants and equal to the calibrated values shown above.

Skipping the intermediate steps, below are the two final important objects that will be used as input to all functions that calculate the price and risk of any bespoke swaptions with respect to the volatility cube implied by the normal market quotes.

### Calibrating the SABR Model to Shifted Lognormal Market Vols

If the market vols are quoted by the broker in shifted lognormal terms, the *Vol Curve* can still be generated as was done above with the normal vols.

For example, let us assume the shifted lognormal market vols are tabulated in a 2-dim form as below:

As usually, the non-ATM quotes are represented as spreads over their corresponding ATM values.

Like how I chose the market normal vols, I have chosen these numbers so that their corresponding absolute (i.e. non-spread) values exactly equal the values shown earlier for the Actual Lognormally Shifted by 1% Market Vols so that they convey the exact same volatility information as the already seen Black vols.

The screenshot below shows how these quotes are fed as input to the formula that creates the *Vol Curve* object **&ShiftedCalibVolCrv.1** in **cell C4**.

The only difference from the previous construction is the entry **Shifted Lognormal** in **cell D20**, which tells the calibration routine that the input vols are quoted in shifted lognormal terms.

As was done earlier, below is the table with the calibration results:

As was done with the normal vols above, so here a final *Vol Curve *object must be created that skips the calibration step by keeping the initial guess SABR parameters constants and equal to the calibrated values shown above.

Skipping the intermediate steps, below are the two final important objects that will be used as input to all functions that calculate the price and risk of any bespoke swaptions with respect to the volatility cube implied by the shifted lognormal market quotes.

### Building a Single Custom Swaption

The function that creates a swaption takes as input a vanilla interest rate swap that defines the cash flow schedules and conventions that will apply when the swaption is exercised, either by physical or cash settlement.

This means my first task is to build that swap.

Note the swap's fixed rate, effective date and tenor will not be important because the final function that creates the swaption is going to reset these two elements as needed.

Below, the vanilla swap is created in **cell H4** as the object **&RefSwap.1** of type *Vanilla IRS*.

Note the *key-value pair* **Ibor Index= %Sofr|1Y **that defines the floating rate index as a forward-looking compounded over 1-year SOFR term rate.

Also, the blank **I9 cell** next to the *key* **Fxd Rate=** makes this swap At-The-Money, although this will not affect the final swaption as already mentioned above.

A first prototype instance of a swaption is created in **cell M4** below as the object **&RefSwaption.1** of type *Vanilla Swaption*.

While this last formula creates a perfectly legitimate swaption, it does not offer the most convenient way for creating a custom swaption with arbitrary expiry, tenor and strike. It is much better to create the final custom swaption by using the **&RefSwaption.1** as a reference swaption input to the function *Shift*, as seen below.

The pair **Code= 6m2y** means the expiry is in 6 months and the tenor of the underlying swap upon exercise is 2 years.

As with the *Vanilla IRS* above, the blank **S11 cell** next to the *key* **Strike=** leaves the exact strike level undefined and would have made this swaption At-The-Money if it were not for the entry **ATM Shift= -0.25%** that sets the strike to the lower level of ATM – 0.25%.

These settings are intentional so that the custom swaption matches one of the market swaptions of which the vols were used in the calibration of the volatility cube, which in turn allows me to verify to what extent the pricing results are going to replicate the market data.

### Preparing the Pricing Models

When it comes to pricing European swaptions, Deriscope supports three basic models.

Their corresponding objects are all of type *Model[Vanilla Swaption]* and will be required as input to the pricing functions later.

Below are the spreadsheet functions that create these three objects:

The three models differ only with respect to the value associated with the *key* **Pricing Method=**, which is set respectively to **Black**, **Bachelier** and **Black Displaced**.

###
Pricing the Custom Swaption using the Lognormal Vol Cube

Now all the components for pricing the custom swaption are in place. The first pricing approach will make use of the volatility curve calibrated against the lognormal market vols that is represented by the object **&LogVolCrv.1**. This is accomplished with the formula =ds(C7:D13) in **cell C6** below that returns an object identified through the *handle name* **&BlackPrice-LogVolMkt.1**.

The object **&BlackPrice-LogVolMkt.1** contains the pricing results. Its contents can be seen in wizard, as below:

As usually, these contents can be transferred to the spreadsheet with the help of the wizard's Go button, with the result as below:

Since all objects of type *Vanilla Swaption* support the function *Implied Vol*, it would be interesting to apply that function on our custom swaption to see if the corresponding market vol can be replicated.

Since the function *Implied Vol* supports the *Black*, *Normal* and *Shifted* *Lognormal* quotation types, I choose to apply it three times to get the implied vol expressed in all three quotation types. Below is the result:

If everything is done correctly and the custom swaption exactly matches the 6M into 2Y market swaption, the implied Black vol of **20.857%** should match the corresponding element of the table containing the *Theoretical Black Market Vols* seen earlier.

Indeed, below is the latter element in red circle and the anticipated match is verified:

Similarly, the implied normal vol of **0.707%** should match the corresponding element of the table containing the *Theoretical Normal Market Vols* seen earlier.

As before with the Black vols, below is the latter element in red circle and the anticipated match is again verified:

Finally, the implied shifted lognormal vol of **16.104%** should match the corresponding element of the table containing the *Theoretical Lognormally Shifted by 1% Market Vols* seen earlier.

Below is the latter element in red circle and the anticipated match is also verified:

One final check could involve the equality between the At-The-Money rate of the custom swaption with the corresponding element of the *ATM Forward Swap Rates* table seen earlier.

The former can be calculated with the help of the function *ATM Rate* that applies on all objects of type *Vanilla IRS*.

Below is the application of this function on the object **&Custom Swaption.1**:

The returned value of **3.521%** exactly the element of the table seen earlier and shown in red circle below:

So far, I have priced the custom swaption using the input *key-value pair* **Models= &BlackMdl.1**, which indicates usage of the Black pricing formula, which in turn relies on lognormal vols implied by SABR parameters calibrated to the given lognormally quoted market vols.

It is important to note that the **&LogVolCrv.1** object only stores the calibrated SABR parameters. The lognormal vol required by the Black pricing formula is calculated on the fly by the pricing routine using the formula below published by Hagan, Kumar, Lesniewski and Woodward in Risk Magazine in 2002:

But it is also possible to imply a normal volatility out of the SABR parameters through the formula below:

This fact provides the possibility to price the custom swaption using the **&LogVolCrv.1** object and incorporating the Bachelier pricing formula, as shown below:

The slight difference in NPV (**65,227** vs **65,233**) most likely is due to the approximating nature of the above shown formulas that are used to calculate the implied Black and normal vols out of the calibrated SABR parameters.

Below is an overview of this part of the spreadsheet where the price and implied vols of the custom swaption are calculated by relying on the SABR calibrated volatility cube **&LogVolCrv.1**.

### Displaying the Cash Flows of the Underlying Swap referenced by the Custom Swaption

The spreadsheet formula that returns the NPV of the custom swaption can be slightly amended so that it also returns the sensitivities of the swaption's price with respect to the SABR parameters.

Below is the amended formula that returns the result in the form of the object **&LogVolRisk.1** in **cell C4**:

The object **&LogVolRisk.1** contains the sensitivities in the form of tables corresponding to the SABR parameters. Below these tables are displayed on the spreadsheet with the help of the *Show* function. As expected, due to the custom swaption's expiry of 6M and tenor of 2Y, only the sensitivities with coordinates 6M and 2Y are non-zero.

### Pricing the Custom Swaption using the Normal Vol Cube

I will now attempt to price the custom swaption by relying on the volatility curve calibrated against the normal market vols that is represented by the object **&NormVolCrv.1**. This is accomplished with the formula =ds(C7:D13) in **cell C6** below that returns an object identified through the handle name **&BachelierPrice-NormVolMkt.1.1**. Note the usage here of the object **&BachelierMdl.1** which indicates the application of the Bachelier pricing formula, as this is more appropriate when the SABR model has been calibrated against normally quoted market vols.

As was done earlier, the output object **&BachelierPrice-NormVolMkt.1.1** contains the numerical results, which can be extracted with the help of the Show function as below:

The NPV of **65,080** differs slightly from the **65,227** previously calculated off the Black vols calibrated SABR model. This might look surprising given the fact that the normal market vols were chosen to be exactly equivalent to the Black market vols but can be explained by observing that the calibrated SABR parameters differ slightly in the two approaches. The reason lies on the fact that the mathematical formulas returning the Black and normal vols out of given SABR parameters differ and therefore "guide" the minimization routines along different paths that end in slightly different solutions for the SABR parameters that minimize the error function.

As was explained in the previous case, it is also possible to calculate the NPV using the Black pricing formula. Also, the implied volatilities expressed in all vol types can be calculated.

Below is the complete section of the spreadsheet where the price and implied vols of the custom swaption are calculated by relying on the SABR calibrated volatility cube **&NormVolCrv.1**.

### Pricing the Custom Swaption using the Shifted Lognormal Vol Cube

I will now attempt to price the custom swaption by relying on the volatility curve calibrated against the shifted lognormal market vols that is represented by the object **&ShiftedVolCrv.1**. The result is shown below:

The NPV of **65,024** is very close to the previous results as expected since the market vol quotes were chosen to be equivalent with those used in the previous cases.

### Building and Pricing a Portfolio of Swaptions

We saw earlier how the function *Shift* could be applied on a reference swaption to create a new swaption with desired expiry, tenor, and strike.

The same function may be also used to create several such swaptions at once.

Below, such a portfolio containing 20 swaptions is created in **cell D4** as an object of type *Portfolio* with the handle name **&Pfolio.1**:

Once the *Portfolio* object has been created, can be used just like any other single instrument. For example, its NPV can be calculated by means of the *Price* function.

Here is the result:

### Calculating the Implied Vols of all Swaptions within a Portfolio

The function *Implied Vol* applies only on a single object of type *Vanilla Swaption* and therefore cannot be used on a *Portfolio* object directly.

But Deriscope supports a generic facility centered around a type called *Task* that allows the execution of a function such as *Implied Vol* on each constituent of a *Portfolio*.

Below is the whole setup that produces the implied vols of all the swaptions inside the *Portfolio*:

### Displaying the Cash Flows of the Underlying Swaps referenced by the Portfolio Swaptions

By using an object of type *CF Model*, the collection of all cash flows of the swaps entered upon each swaption exercise may be displayed in tabular form with the desired number and ordering of columns. An example is presented below:

### Displaying the SABR Risk of the whole Portfolio of Swaptions

The generation of the sensitivities of the whole portfolio of swaptions is not different than the one concerning a single swaption.

Below is the result:

As expected, a non-zero risk now appears on most expiry-tenor combinations since the portfolio contains swaptions with varying expiry-tenor values.

Click on **SwaptionTermSofrCube.xlsx** to download the spreadsheet produced with the above steps.