32 minutes reading time (6433 words)

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

cover

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 


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.  

Pricing Currency Swaps with different Collateral C...