Using the Bootstrapped Market SOFR Caplet Normal Vol Surface to Price in Excel Interest Rate Caps/Floors on Backward/Forward Looking SOFR Term Rates
In my earlier posts titled Overnight Index Swap (OIS): Pricing and Understanding using Excel and Overnight Index Swap (OIS): Observation Lags, Lookbacks, Rate Cutoffs and step-by-step Pricing in Excel I provided a detailed description of overnight index swaps known as OIS, of which the floating leg index in each payment period was defined as the average (either compounded or arithmetic) of observed overnight rates over that period. This index definition corresponded to what is known as a backward looking term rate, a name that alludes to the fact that the average is determined only after all overnight rates have been observed. In Deriscope this index is represented by the Oi Term Rate.
Due to the linear dependence of each period's payoff on the term rate, the present value of the respective payment only depends on the forward value of the term rate, which happens to be the same with the forward value of the corresponding forward looking term rate, where the latter is defined as the average of the forward overnight rates determined at the beginning of the payment period.
The situation is different with non-linear products, such as caps and floors that are defined over a sequence of times T₀, T₁, …, Tₙ.
A cap is a series of n European Call options – known as caplets - on some underlying rate. The iᵗʰ caplet references the time interval [Tᵢ₋₁ , Tᵢ] and pays N τᵢ max(Rᵢ - K , 0), where:
Rᵢ is the underlying rate observed during the time interval [Tᵢ₋₁ , Tᵢ]
K is a fixed number known as the cap's strike
τᵢ is the length of the time interval [Tᵢ₋₁ , Tᵢ] in annual units according to a specified daycount convention
N is the cap's notional
A floor is similar to a cap, but with the Call options being replaced by Put options that pay N τᵢ max(K - Rᵢ , 0).
The underlying rate R can in principle be any rate, such as a Libor rate, bond yield or inflation rate.
In interest rate caps and floors R has traditionally been a specific Libor rate, the tenor of which matched the interval between the successive times Tᵢ₋₁ , Tᵢ.
For example, the 3-month USD Libor would be the underlying rate of a 1-year cap with four quarterly periods defined by the time sequence T₀, T₁, T₂ , T₃ , T₄.
The first caplet would reference the interval [T₀, T₁] and pay N τ₁ max(R₁ - K , 0) where R₁ would be the 3m Libor observed at T₀ and τ₁ would be the length of [T₀, T₁].
Similarly for the remaining three caplets.
With the replacement of Libor by RFR-referencing term rates, caps and floors have emerged of which the underlying rate Rᵢ in the iᵗʰ caplet (or floorlet) is the corresponding term rate observed either at Tᵢ (if the term rate is backward looking) or Tᵢ₋₁ (if the term rate is forward looking).
While the forward value of the term rate does not depend on the term rate's "looking" direction, its overall variance and ascribed vol does! As a result, caps and floors are priced differently depending on whether their index is backward or forward looking.
Due to the European nature of the caplet and floorlet (collectively known as optionlet) payoffs, the dynamics (i.e. the stochastic evolution) of the underlying rate R is generally not of concern. All that is needed to price a cap or floor is knowledge of the implied volatilities σ₁, …, σₙ associated with the n optionlets, which the cap or floor consists of. This is similar to a European FX or Equity option, where knowledge of the respective implied vol suffices to calculate the option's price even when the terminal distribution of the underlying fx rate or equity price is unknown.
The market quotes the implied volatilities of caps in the form of a table for various tenors (left column) and strikes (top row).
Note that if a cap is quoted with implied volatility σ, its constituent caplets, were to be traded in isolation, would not have the same implied volatility σ. The meaning of a cap's implied volatility σ is that if σ were used to calculate the price of the cap's constituent caplets, the sum of these prices would equal the traded cap price.
To price bespoke caps and floors, the implied volatilities of the contained optionlets need to be known. The procedure by which the two-dimensional surface consisting of the implied volatilities of all conceivable optionlets (i.e. the optionlets with all possible expiry-strike combinations) is extracted by the quoted cap volatilities is known as vol bootstrapping.
In this post I will first create a term rate optionlet volatility surface bootstrapped out of market cap vols displayed in Bloomberg as of 30 Dec 2022. Then I will use that surface to price two bespoke caps, one that references a backward looking term rate and one that references a forward looking term rate.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Building the SOFR OIS Curve
The SOFR OIS curve will be built using the following market data from Bloomberg as of 31 Dec 2022 (which is the same as those for 30 Dec 2022 because the 31 Dec is a Saturday):
The image below shows all the input parameters to the Deriscope Function in cell C4 that constructs a corresponding Deriscope Object of Deriscope Type Yield Curve, uniquely identified by the returned handle name &SofrCrv.1.
The red-colored cell contains the Deriscope formula. Details on the meaning of the shown colors and the syntax of the Deriscope spreadsheet formulas can be found in this introductory post about the spreadsheet Deriscope function.
The formula =ds(C5:D14) in cell C4 takes as input a two-column range that contains all static data in the form of key-value pairs. This curve will be used below during the vol curve bootstrapping to a) provide all required discount factors and b) imply the forward overnight index term rates. It will therefore be used for both discounting and forecasting purposes.
Building the Backward Looking SOFR Term Rate
The image below shows all the input parameters to the Deriscope function in cell C4 that constructs a corresponding Deriscope object of type Oi Term Rate, uniquely identified by the returned handle name &BwdTrmRt.1.
Defining the Volatility type of the produced Optionlet Vol Surface
The result of the bootstrapping process is a two-dimensional surface of optionlet implied volatilities, with the two dimensions spanning the optionlet expiry and strike.
The implied volatilities reference a specific rate type (eg backward looking 3-month SOFR term rate) and a probability distribution. The latter can be lognormal, normal or something between, which is known as shifted lognormal.
Note this distributional assumption is only used for defining the optionlet implied vols. The optionlet's underlying rate may evolve under any stochastic process that may or may not imply a terminal distribution of the mentioned types.
Both the nature of the optionlet's underlying rate and its implied volatility type is captured in Deriscope by an object of type Vol Ref, constructed in cell H3 below:
According to the entry Normal in cell I8, I have chosen to treat the optionlet implied vols implied by the bootstrapped vol curve as normal.
The Bloomberg SOFR CAP Vol Data
Below is a screenshot with the SOFR cap normal volatilities reported in Bloomberg as of 31 Dec 2022 (which is the same as those for 30 Dec 2022 because the 31 Dec is a Saturday). They are quoted in basis points.
The type of compounded term rate is not shown, but for the purpose of this analysis I will assume that the caps reference a backward looking 3-month compounded term rate. This assumption can be easily modified in the spreadsheet if needed.
Building the SOFR Optionlet Vol Curve without the ATM Vols
Even though the above vol data include an ATM column, traders prefer to ignore the ATM vols mainly because the ATM strike at each row is not explicitly quoted but rather inferred by the corresponding cap tenor and the shape of the OIS curve at the time the vols are quoted. The ATM vols are used primarily in middle and back office to satisfy reporting requirements.
The image below shows the formula that creates the optionlet vol curve by bootstrapping the quoted cap normal vols, excluding the ATM column:
The formula =ds(C5:D12,F4,F5:W11) in cell C4 creates the object &VolCrv NoAtm.1 which is of Deriscope type VolCurve.
The key-value pair Vol Ref= &NormVolRef.1 supplies the definition of the implied optionlets' underlying rate as a normally distributed (for vol quoting purposes) backward looking 3-month term rate referencing the SOFR overnight index.
The input range F5:W11 contains the Bloomberg vols divided by 10,000 since they were originally quoted in basis points.
The key-value pair Forc Cvr= &SofrCrv.1 supplies the yield curve that is needed during bootstrapping to forecast the forward term rates, but also to discount the optionlet payoffs.
The key-value pair CapFlr Vol Spec= &Spec NoAtm.1 supplies the various assumptions that govern the bootstrapping algorithm. The entry &Spec NoAtm.1 in cell D11 is a link to cell C14 where the handle name &Spec NoAtm.1 is produced as shown below:
I will not go over the definitions of the several keys shown above. They are displayed in the Deriscope wizard's Info Area as soon as a respective cell is selected, as shown below with the cell C19 being selected, with the blue-colored text items being hyperlinks to more information:
But the key CapFlr Vol Type= deserves some explanation here.
While it was mentioned above that the type of the produced optionlet implied volatilities is specified by the object &NormVolRef.1 to be normal, the supplied cap vol quotes do not need to be also normal. They may be quoted in normal, lognormal, or shifted lognormal terms. The key here specifies the vol type as Normal since in this case the market cap vols are quoted in normal terms.
Using the SOFR Optionlet Vol Curve Object
I may now use the object &VolCrv NoAtm.1 to get the implied optionlet volatility for any desired expiry and strike by using the Deriscope Local Function Implied Value, as seen below:
Further on, I can ask for the ATM strikes for any desired set of cap tenors by using the local function Implied ATM Strike:
If Deriscope is used as a proofing tool in model validation or audit projects, it is often desirable to delve into the mechanics of the bootstrapping process and display details concerning the intermediate steps.
In the case dealt with here, the optionlet vol curve has been built out of a cap vol table consisting of 6 rows and 17 columns. It is often important to know the exact details of the 6 x 17 = 102 involved caps and compare them with those used in the system being validated, since any discrepancy in the cash flow dates of those caps could perhaps explain any observed differences in the optionlet volatilities implied by the system versus Deriscope and lead to concrete recommendations regarding the corrective measures that need to be taken.
The 102 caps can be displayed in the Deriscope wizard's Browse Area by simply selecting the cell C4 while the wizard is open:
Highlighted above is shown the key _Cash Flows=, with the associated value being the object named $Set#5.
Clicking on the lens shown in red circle leads to the following screen:
This table consists of 102 rows, with each row corresponding to one of the caps associated with the supplied vol quotes.
For easier access to these cap details, the whole table can be also displayed on the spreadsheet as the array formula output of the Show Function so that these data are dynamically linked to the rest of the spreadsheet:
The column titled #CashFlows contains handle names of objects that provide information down to the caplet level on the corresponding caps.
As usually, this information can be displayed in the wizard by selecting the cell that contains the corresponding handle name.
For example, selecting the cell AP5 shown in red circle results to the following screen with 4 rows that correspond to the 4 caplets contained in the first cap with maturity 29 Dec 2023 and strike -0.015:
In a red rectangle are shown the details of the first caplet.
The 10ᵗʰ column titled #Type supplies the type of each caplet's cash flow. All four cash flows are of type CapFlrCompAvgΟΙ, which is an acronym for Capped/Floored Compounded Average of an Overnight Index.
Reading the first row's data from left to right:
#AccrStart = 30-Dec-2022 is the start date of the caplet's accrual period, which period is defined as the interval [Tₛ , Tₑ] in Oi Term Rate.
#AccrEnd = 29-Mar-2023 is the end date of the caplet's accrual period
#Fixing = 28-Mar-2023 is the date when the term rate is fixed. Since the term rate is backward looking, this is one business day prior to the end of the accrual period.
#FixingStart = 30-Dec-2022 is the start date of the calculation period over which the SOFR index is compounded to produce the term rate. Note the calculation period is defined as the interval [T₀ , Tₙ] in Oi Term Rate.
#FixingEnd = 29-Mar-2023 is the end date of the calculation period over which the SOFR index is compounded to produce the term rate. More precisely, the last SOFR fixing date is the 28-Mar-2023, but, since SOFR is an overnight rate, its underlying spanning interval extends until the 29-Mar-2023 and this is the reason the latter date appears as the end of the term rate's overall fixing period.
#Index = 0.04684 is the corresponding forecasted term rate, i.e. the compounded average of the forward SOFR values over the period between 30-Dec-2022 and 29-Mar-2023.
#AdjIndex = 0.06184 is the adjusted index, i.e. the effective forward rate of this caplet that has a strike of -0.015. This rate has been calculated using the forward rate Bachelier formula, but in this case it is very close to the difference 0.04684 – (-0.015) because the caplet is deep in the money.
#PmtDate = 29-Mar-2023 is the payment date.
#Amount = 0.01529 is the amount in USD paid by this caplet, of which the notional is assumed to be 1. It equals the product of the adjusted index times the length of the accrual period. The latter equals 0.2472, as seen below.
Scrolling the table to the right, the following becomes visible:
Reading the first row's data from left to right:
#ValueStart = 30-Dec-2022 is the start date of the reference period, which period is defined as the interval [T΄₀ , T΄ₙ] in Oi Term Rate.
#ValueEnd = 29-Mar-2023 is the end date of the reference period.
#AccrDC = the daycount convention used in the determination of the accrual period.
#AccrDays = the number of days in the accrual period.
#AccrTime = the length of the accrual period in annual units according to the daycount convention in the #AccrDC column.
The column titled #Details contains handle names of objects that provide information about the compounded overnight index of the corresponding caplets.
For example, clicking on the $Variant#6 leads to a quite big set of data, a part of which is dedicated to the daily fixings of the SOFR rate, as shown below:
The date gap between 11-Jan-2023 and 28-Mar-2023 is due to the application of the Telescopic property.
Building the SOFR Optionlet Vol Curve using Sparse Data
Sometimes, reliable vol quotes are not available for certain tenor / strike combinations.
In such cases the vol table will contain blank cells, but it can still be processed by setting Sparse CapFlr Vols= TRUE, as seen below:
Building the SOFR Optionlet Vol Curve with the ATM Vols
If the ATM vols need to be considered, they can be supplied as an extra input range as seen below. Note in this case the key Include ATM= must be set to TRUE.
Setting up a Bespoke Cap on Backward Looking Term Rates
I want to price a 3-year cap on the backward looking 3-month compounded SOFR term rate as of 30 Dec 2022. The cap has a notional of 100,000,000 USD, a strike of 4% and has started in the past, specifically on 12 Dec 2022.
Below are the two spreadsheet formulas that construct the cap:
Setting up the required Market and Model Objects
The cap's price will be calculated by the Deriscope function Price, which expects as input an object of type Market that supplies market-related information and an object of type Model that supplies pricing methodological assumptions.
Since a cap is sensitive on interest rates and vols, the Market object owes to contain the SOFR yield curve and the optionlet vol curve.
Below is the construction of the object &MktSetNoHist.1 of type Market Set in cell C4 that is simply a collection of these already constructed curve objects:
Regarding the modelling assumptions, I would need two separate model objects, one that applies the Bachelier pricing formula for normally distributed rates and one that employs the Telescopic method for calculating the compounded averages of the SOFR index.
Below is the construction of the object &MdlSet.1 of type Model Set in cell C4 that is simply a collection of these two objects:
Failed Cap Pricing due to Missing Historical Fixings
The screenshot below shows that the Deriscope formula in cell H4 has returned an error.
The wizard's diagnostic message complains about a missing SOFR fixing as of December 12th, 2022.
Thinking about it, the pricing failure is justified since the cap starts on 12 Dec 2022, but the provided yield curve can supply only forecasted forward fixings on or after 30 Dec 2022.
Succeeded Cap Pricing after Incorporating the Required Historical Fixings
It is quite obvious that I will need to supply the pricing formula with all historical SOFR fixings from 12 Dec 2022 to 29 Dec 2022.
The Deriscope type that acts as a collector of all sorts of historical data is called Historical Values and below is the construction of the corresponding object &SofrHist.1 in cell H4:
Next, I collect all objects together into the new Market Set object &MktSetWithSofr.1 in cell M4:
With this new Market object, the pricing formula =ds(H5:I12) below succeeds and returns the object &CapBwdLookingVal.1 in cell H4, as shown below.
The formula returns an object rather than a number because of the entry Output= Full, shown highlighted in red.
Nevertheless, the returned object's contents can be seen inside the wizard, where the cap's price of 2,020,102 USD is visible.
The price can be also displayed on the spreadsheet using the Show function.
Similarly, the cap's cash flows can be displayed on the spreadsheet, as shown below split in two parts:
Based on these cash flows, one may calculate the intrinsic value of the cap by adding together the intrinsic values of the caplets, with the following result:
Setting up the corresponding Cap on Forward Looking Term Rates
To construct a cap on forward looking term rates, I must first create the corresponding object of type Oi Term Rate that has Looking= Forward, as seen below in cell C17. I have named this object &FwdTrmRt.1 for easy reference.
Now the construction of the cap object is straightforward, as I can reuse the existing object &Sched.1 that defines the cap's time schedule.
Below is the formula with the key-value pair Index= &FwdTrmRt.1 highlighted.
Pricing the Cap on Forward Looking Term Rates
Considering any caplet that starts in the future, the vol of its forward looking term rate is the same as the vol of its backward looking term rate for the very simple reason that both rates are modelled by effectively the "same" stochastic process according to a paper by Andrei Lyashenko and Fabio Mercurio in 2019.
To be more precise, both rates are represented by the same stochastic variable R(t) representing a forward term rate, with the only difference being that for any specific caplet time interval (T₁,T₂), the stochastic process of R(t) associated with the forward looking term rate stops evolving after T₁, while the stochastic process of R(t) associated with the backward looking term rate stops evolving after T₂.
Therefore, both rates share necessarily the same vol σ, i.e. the vol of R(t), which is actually time-dependent. It turns out that σ(t) is flat for t < T₁ and falls linearly down to 0 between T₁ and T₂.
The terminal variance of the forward looking term rate equals the integral ₀ᵀ¹∫ σ²(t) dt while the terminal variance of the backward looking term rate equals the integral ₀ᵀ²∫ σ²(t) dt.
Deriscope takes this into account and adjusts the variance of the terminal term rate as needed.
An attempt to price the cap on forward looking term rates using the previous Market object will result to failure since the historical SOFR fixings are not relevant in this context.
According to the screenshot below, Deriscope complains that the historical fixing for the 3M term rate is missing as of 12 Dec 2022:
This is logical and the only solution is to set up the Market object so that it includes the required fixing.
In fact, contrary to the backward looking case, only one fixing is required, as seen below:
Below is the creation in cell W4 of the Market Set object &MktSetWithTerm.1 that will be used as input to the cap pricing.
Putting all together, here is the final pricing result:
The cap's price is 1,745,513 USD, which is less than the previous result of 2,020,103 USD.
There are two factors that cause this difference.
First is the historical term rate fixing of 3.8% that affects the payout of the first caplet. This is significantly less than the forecasted fixing of the compounded SOFR rate during the first caplet period in the backward looking case, which was computed as 4.82372%.
Second is the fact that forward looking term rates are known (fixed) at the beginning of each caplet period and therefore have a smaller variance than the backward looking term rates that are resolved only at the end of the same period. This smaller variance pushes the price of caps (and floors) on forward looking term rates to lower levels compared to the prices of their counterpart instruments on backward looking term rates.
Finally, below is the table with this cap's cash flows:
Effect on Price from Forward Looking versus Backward Looking
It would be interesting to isolate the effect coming only from the looking direction of the term rate.
This can be achieved by repricing the cap on forward looking term rates using a new Market object that contains the historical term rate fixing set to the value of 4.82372%, which is term rate shown above at the first row of the cash flow table associated with the cap on backward looking term rates.
Then the price of the cap on forward looking term rates becomes 1,948,833, which is 7 bp lower than the price 2,020,103 of the cap on backward looking term rates.
Below is the table with the comparative results:
Click on IntRateCapSofr.xlsx to download the spreadsheet produced with the above steps.
At https://www.deriscope.com you can find more information about Deriscope and download the Excel Add-In required to run some of the formulas in the spreadsheet.
