How to build a SOFR Yield Curve in Excel using QuantLib and Deriscope
The markets expect that Libor will be gradually phased out as more investors and lenders prefer financial products tied to the Secured Overnight Financing Rate (SOFR) rather than the USD London Inter-bank Offered Rate (LIBOR).
The main reason for this ongoing exodus is the expected cessation of the daily announcements of the interbank lending rates by the panel banks on which the daily LIBOR publication by the ICE Benchmark Administration (IBA) in London is based. The panel banks have only agreed to continue announcing such rates until the end of 2021. It is therefore likely that no LIBOR rate will be published beyond that date.
Until recently, only a few sell side institutions have realized the importance of using SOFR-based curves as risk-free discounting curves.
Several sell side and almost all buy side enterprises are still using discounting curves stripped out of Fed Funds overnight index swaps (OIS), provided they undertake the effort – not all of them do - to distinguish between OIS and LIBOR curves for discounting purposes.
One can hardly blame them for not rushing to adopt SOFR as the new Risk Free Rate (RFR), given the fact that Chicago Mercantile Exchange (CME) and London Clearing House (LCH) only very recently – on October 16, 2020 - converted away from Effective Federal Funds Rate (EFFR) to using SOFR as the reference index for their discounting RFR on all the USD-denominated swaps cleared by them.
Today, the sum of the notional amounts of all financial products referencing the USD LIBOR is estimated to $400 trillion. These are diverse products ranging from derivatives such as interest rate swaps to consumer products such as student loans and home mortgages. About $170 trillion are in outstanding swaps, of which one third have maturities beyond 2021 according to the Wall Street Journal.
By the end of 2021 all business holders of such products will have to switch to SOFR for evaluating and risk managing their portfolios.
While the basic task of portfolio Mark-To-Market (MTM) can be achieved by relying on SOFR discount factors seen on Bloomberg or published by exchanges such as CME, other tasks - such as hedge accounting – need the capability of building these discount factors out of market rates, since only then can one measure the effect of varying market rates on the portfolio MTM.
I will show you below how simple it is to generate a SOFR curve with the Excel Deriscope Add-In that uses the financial analytics of the well-known QuantLib software library and the innovative Deriscope Excel user interface.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Using the Wizard to setup the Formulas that create a Yield Curve out of SOFR OIS
The bulk of the industry – especially the buy side – would be content with a SOFR curve built out of market SOFR OIS rates.
I will thus start with this simpler case and leave the more complex futures-plus-swaps case for the next section.
The type of swaps I will be dealing with are Overnight Index Swaps (OIS). If you are not familiar with their structure and pricing, you are advised to look at my earlier post about OIS where I describe the cash flows of this type of swaps, derive the pricing formulas and use the Deriscope wizard to generate all relevant spreadsheet functions for a specific example of an OIS based on the European Overnight Index Average (EONIA) index.
A simple and brief introduction on how a yield curve may be built out of OIS using EONIA OIS as an example are mentioned in my post titled Risk Free Yield Curve Building in Excel using Negative Overnight Index Swap (OIS) Rates.
A more detailed treatment of how FedFunds based OIS rates published by Bloomberg can be used in trading floor applications to build risk-free curves is available in my post titled OIS Discounted USD Libor Curve Production in Excel for Front Office Trading.
Below I will focus exclusively on OIS based on the SOFR index.
These are swaps with an annual frequency on both their fixed and floating leg.
The amount paid at the end of the kth accrual period of the floating leg is based on the effective floating rate Fk of that period and given by the usual formula Notional*Fk*dc/360, where dc is the number of calendar days in that period.
The rate Fk is calculated as the compounded weighted average of the overnight SOFR rates observed at each business day of that period.
The precise formula for Fk is shown below. The image has been copied from this Federal Reserve page.
Formulas for Compounded and Arithmetic Average SOFR Index
One of Deriscope's salient features is its Excel-integrated wizard that can generate all spreadsheet formulas that carry out a custom, user-specified task.
Right now, my task is to create a yield curve stripped out of given market SOFR OIS rates.
I communicate this task to the wizard by selecting the element labelled Yield Curve in its Type Selector entry bar, as shown below:
As soon as the element Yield Curve is selected, the area underneath is automatically filled with multiple choice properties that control what types of market instruments should be used in the curve generation.
Since I want to generate a curve out of OIS, I will check the -Use OIS box.
Then I simply click on the Go button and the wizard produces and inserts in the spreadsheet all required formulas, seen below with their dependency arrows visible:
As you see above, cell A1 contains the formula =ds( A2:B7 ).
Almost all Deriscope formulas have the form =ds( RANGE1, RANGE2, … )
In the case here, only one range – the A2:B7 – is passed as input to the ds formula.
The ds formula expects that the passed ranges contain data in the form of key-value pairs.
Details on the syntax forms the Deriscope ds formula supports, are supplied in this introductory article.
The keys are text labels ending with =, whereas the values can be anything.
The two keys named Type= and Function= must be – almost – always present. They define the task being undertaken by the formula.
The formula in cell A1 has Type= Yield Curve and Function= Create, which means the formula's task is to create an object of type Yield Curve.
That task has been completed successfully, as one may tell by observing the formula's output in cell A1.
It is the text &USDCrv_A1:4.1, which is the handle name of an object created by Deriscope. That object is essentially a binary blob that lives in Excel's memory and represents the requested SOFR yield curve in the following sense.
Every possible information one usually associates with a yield curve – for example, the curve of zero rates - can be retrieved from this object and displayed on the spreadsheet by means of its handle name and an appropriate function.
By default, the wizard formats the pasted ranges using the following color convention:
Blue for data representing user input
Red for output of Deriscope formulas
Green for formulas representing simple links among ranges
Black for keys
The key Market Data= is paired with the value &YldCrvOis_A9:4.1.
That value has been formatted green by the wizard because it is the output of a simple link to cell A9, where the handle name &YldCrvOis_A9:4.1 is produced by the formula =ds( A10:B12 , A14 , A15:B17 ).
The latter handle name points to an object of type Yield Curve Ois, as seen in cell B10. Its purpose is to collect the conventions and rates of the OIS instruments.
The wizard has set the #Tenor and #Rate columns to default values that are not linked to any market data source. Their only utility is to be reasonable enough for allowing the Deriscope formulas to complete their task of creating a corresponding Yield Curve object.
It will be my next task to replace these default values with data I receive from a trusted data source, such as Bloomberg.
Plugging in the Bloomberg Swap Rates
I will use SOFR swap rates imported from Bloomberg on November 10, 2020.
Below is the Bloomberg page that shows the rates for maturities up to 3 years.
The screenshots with all rates are included in the downloadable spreadsheet at the end of this article.
In fact, I will take the average of the shown Bid/Ask quotes and ignore the spot overnight Cash Rate displayed as 0.1% because this is the SOFR rate published by the Federal Reserve on November 10, but its value date – i.e. the start date of the overnight accrual period – is the previous day on November 09 and therefore not at sync with the swap quotes.
Below are my earlier two formulas after I have added the data from Bloomberg in the two columns under the titles #Tenor and #Rate(%).
Note the added suffix (%) in the rate title that instructs the formula that all numbers in the respective column are in percentage scale.
I have also set Modelled Qty= Discount and Interp Method= Log Cubic in order to get a smoother curve that is more aligned with market practice.
For referencing convenience, I have also inserted the optional key/value pairs Handle= SwapRates and Handle= SwapCurve that set the handle names of the respective created objects.
The first sanity test that should be carried out to check the minimum validity of the created curve is finding out whether it implies zero prices for the par OIS instruments that were used as input.
I will skip this important test here, since I will do it in the more general case below where both futures and swaps are used in the curve construction.
Using the created Swaps-only Yield Curve object
The formula in cell D1 has produced an object of Deriscope type Yield Curve - as specified in cell E2 - and returned the handle name &SwapCurve.1, which serves as the object's "ambassador" in the world of the spreadsheet interface.
If I select that handle name, the wizard reacts automatically by displaying the object's contents as read at that time from memory:
The left column in the wizard area contains the keys.
The values corresponding to these keys are shown on the second column.
A few of these values are handle names of objects that can be inspected by clicking on the little lens sign on their left.
For example, clicking on the lens sign of the object named &SwapRates.1 associated with the Market Data= key, the wizard displays the following:
This screen shows the contents kept in memory of the object &SwapRates.1 originally created by the spreadsheet formula in cell A1.
Interestingly, the object appears to contain data that were not passed as input in that formula. These additional data appear with keys, of which the somewhat dimmed font serves as a visual indication that their usage in the spreadsheet formula is optional, in which case they assume the specific default values shown above.
In the currently examined case, the shown default values can be very informative about the conventions that apply in the supplied swap rates.
Starting from the top, the key/value pairs are:
ON Index= %Sofr
This defines the overnight index underlying the OIS contracts as an object with the handle name %Sofr, which is a so-called trivial handle name.
The details of this Sofr index are easily shown by clicking on its lens sign. Below is the respective screenshot that assures us that this overnight rate has the correct conventions, i.e. spot settlement, US Government Bond calendar and ACT/360 daycount.
Rate Formula= Compound
This informs us that the effective index on the OIS floating legs is calculated as a compounded average of the daily overnight rates.
A detailed description about the value Compound can be displayed by selecting the containing cell, as shown below:
Avg Method= Telescopic
This is a technical feature that optimizes the floating accrual calculation.
Settle Days= 2
This means the OIS have a settlement of 2 business days, i.e. their legs start accruing 2 business days after the swap's trading date.
Pmt Lag= %2D{USGOVBOND|F}
This defines the payment lag of each cash flow from the end of its respective accrual period as 2 business days in accordance with the US Government Bond calendar and the Following date bump convention. For avoidance of any misunderstanding, note the end of each accrual period coincides with the date the last SOFR index applicable for the average calculation of that period's cash flow is published by the Fed.
Pmt Freq= Annual
This defines the payment frequency on both legs.
Disc Curve= <blank>
This tells us that no exogeneous discounting curve is considered during bootstrapping.
Set= $Set#2
This is the most important entry, since it contains the market swap rates as part of the object shown under the handle name $Set#2.
If I click on the lens sign, all swap rates originally entered as input to the spreadsheet formula are displayed, albeit in standard (non-percentage) scale:
Apart from displaying the contents of the &SwapCurve.1 object, I may also run various Deriscope-supported functions on this object that return useful information.
For example, I can run the special DF function that returns the curve's implied discount factors for any given maturities.
I don't need to remember the name and syntax of this function, since the wizard can paste the correct spreadsheet formula for me.
I start by selecting the cell containing the handle name &SwapCurve.1.
As we have seen, the wizard reacts by displaying the object's contents.
I may then click on the wizard's Function Selector, which is located at the top of the wizard's area and choose the function named DF out of the available list.
Below is, how the wizard appears after my selection:
It is convenient to let the wizard know that I am interested in the array form of the about to be generated formula, by adding one more maturity through the + button:
Finally, I click on the Go button to insert the corresponding wizard-generated formula in my spreadsheet.
As shown above, the wizard inserted the formula =ds(G3:H4,G6:G8) as a dynamic array formula in cell G1.
It also filled the two input ranges G3:H4 and G6:G8 with the appropriate data.
It was also smart enough to realize that the object &SwapCurve.1 has been previously created in cell D1 and made use of this fact by inserting the link =D1 as formula in cell H3, which therefore appears with green color. (See my above comments on Deriscope's color conventions).
From this point on, it is up to me to add additional maturities in the column below the key Maturities=.
For example, below I have added the 10 consecutive calendar dates that follow the today's date of November 10 and shifted the ranges appropriately so that the output discount factors appear next to the maturities:
It is a trivial exercise to extend the maturities all the way to a desired horizon of X years and have the ds formula returning the corresponding discount factors, which could then be used as input to the standard pricing and risk management systems.
As a second example of how the &SwapCurve.1 handle name may be used, I will now create the chart of implied continuously compounded zero rates.
I start as usual by selecting the cell D1 in order to shift the wizard's attention to the Yield Curve object located in that cell.
Then I click – as I did before – on the wizard's Function Selector, but this time I choose the function named Implied Values, as shown below at the instant prior to finalizing my selection:
After my selection has been made, the wizard displays the input parameters expected by the Implied Values function, as shown below:
I can see each item's description by selecting the corresponding key.
For example, this is what I see if I select the Use Dates= key:
I would like to setup a maturity grid with monthly frequency down to a 50-year horizon without worrying about the exact dates, since I am only interested in the shape of the implied zero rate chart.
So, I uncheck the Use Dates= box, which results in a new set of displayed key/value pairs.
This is my final set of input parameters:
Below is the formula pasted in my preselected cell J1 by the wizard after pressing the Go button:
The result is the object named &Set_J1:3.1, which happens to be a Deriscope object of type Set. Its contents are visible in the wizard because the cell J1 is currently selected.
There is a standard way of transferring any object's contents to the spreadsheet by means of the special Deriscope Show function. The transfer is done most conveniently with the wizard, as explained here.
Below is my whole Excel window with the top section of the dynamic array formula output. The vertical scroll bar on the right indicates there are several rows of data, as expected due to the selected Monthly frequency and the 50-year horizon.
I am only interested in the zero rates, which are shown at the third column under the title #ZeroRate.
The #Maturity column contains the corresponding maturities expressed in annual units.
The #DF column contains the corresponding discount factors.
The #FwdDF(0.5) contains the corresponding forward discount factors that have a forward valuation date at the indicated maturity under #Maturity and a final maturity that lies 0.5 years beyond their valuation date.
The #FwdRate(0.5) contains the corresponding 6-month forward rates that have a forward valuation date at the indicated maturity under #Maturity and a final maturity that lies 0.5 years beyond their valuation date.
It is easy now to setup a chart with the implied zero rates for the next 50 years with monthly granularity.
Here is the result:
You may observe some funny, microscopic zig zag behavior during the first two years when a kink with an upwards slope establishes.
In order to investigate the short end behavior in more detail, I have decided to calculate the implied zero rates during the first two years with daily granularity. Here is the result:
Apparently, the market OIS rates for maturities less than a year are not conducive to a smooth curve!
My first thought goes to liquidity. The chart is based on the mid points of the bid-ask quotes. Can I trust these quotes?
The difference ask – bid can give me a clue regarding the market liquidity at the time when these quotes were captured. If that difference is too high for a particular contract, it can be possible that its published quotes are stale, i.e. represent transactions that took place at a much earlier time and therefore they are not at synch with the quotes of the remaining contracts. Such mismatched quotes can easily lead to curve oscillations like those observed above.
Here are the bid-ask spreads in basis points for the SOFR OIS that have maturities up to 5 years:
Indeed, the spreads for the 1-, 2- and 3- week OIS contracts seem to be high with almost 3 basis points.
Also high are the spreads for the contracts with maturity from 7 to 18 months.
But the effect on the yield curve smoothness of any stale quotes is much more severe when the respective maturities are close to each other because then the curve building algorithm has much fewer dates between these maturities at its disposal. The latter "free" dates are of great importance because their corresponding discount factors are adjusted until the given market prices are matched. The fewer these "free" dates are, the greater these adjustments need to be in order for their cumulative effect to be strong enough to achieve the market price match. Greater adjustments on the discount factors between successive market instrument maturities means greater oscillations!
This observation is perhaps a good motivation for incorporating SOFR futures in our curve building, at least when one is interested in the fine details of the curve's front end.
SOFR Futures
In certain applications one may prefer to fine tune the short end of the curve to match the liquid market SOFR futures prices, while keeping the medium and long end tied to the swaps market.
Specifically, there exist 1-month and 3-month SOFR futures traded in exchanges, such as the CME, that can be used as input in the Deriscope ds formula alongside the swaps.
A SOFR futures contract is defined by the time interval bounded by two dates, T1 and T2 with T1 < T2, over which interval the overnight rates are observed and put together into calculating their arithmetic (1-month contracts) or compounded (3-month contracts) weighted average.
Concretely T1 and T2 are defined as follows:
- T1 is the value date for the first SOFR value referenced by the final average, which is reported by the Federal Reserve Bank of New York one business day after T1.
- T2 is the final settlement date of the futures contract, i.e. the settlement based on the quoted futures price (IMM price index) after trading has ceased. On T2 all SOFR values spanning the interval from T1 to T2 are available for the calculation of their average.
- T2 – 1B (1B means 1 business day) is the value date for the last SOFR value referenced by the final average. This last SOFR value is reported by the Federal Reserve Bank of New York on T2.
- T2 – 1B is also the last trading day for the futures contract, which makes sense because the ultimate settlement price of the contract is still unknown on that date and there is therefore motivation for taking a long or short position on the contract. The settlement price is unknown because the SOFR index associated with the last trading date – which index also happens to be the last numerical input to the final settlement formula – is not known as of T2 – 1B, since it is published by the Fed on T2.
In principle, futures contracts could exist with any arbitrary dates T1 and T2, but for liquidity reasons exchanges restrict these dates as follows:
1-month futures:
T1 and T2 – 1B are the first business day and last business day of the contract's delivery month respectively.
Note that 1-month futures contracts are named after their delivery month.
For example, the November 2020 1-month futures contract has T1 = November 2, 2020 and T2 – 1B = November 30, 2020.
3-month futures:
T1 and T2 are two consecutive International Money Market (IMM) dates. The IMM dates in a particular year are the 3rd Wednesday of March, June, September, December of that year.
Note that 3-month futures contracts are named after the month of T1.
For example, the September 2020 3-month futures contract has T1 = September 16, 2020 and T2 = December 16, 2020.
Note that Deriscope allows the user to set T1 or T2 to non-business days. Deriscope would then convert them internally to the following business day.
Allow me a small digression here on the meaning of the term futures price:
The Net Present Value (NPV) of any futures contract at any time – during or after the initial transaction – is exactly 0.
One would normally be tempted to say that the price of a futures contract is always 0, but the established convention in futures market jargon assigns a meaning to the word price that differs from the usual NPV meaning that prevails in other markets.
Formally, the futures price at the settlement date T2 is defined as that number F such that the quantity (100 – F) % equals the weighted arithmetic (1-month futures) or compounded (3-month futures) average of the overnight SOFR rates prevailing over the period ( T1 , T2 ).
More precisely, the number F is rounded to 1/10 of a basis point for 1-month futures and 1/100 of a basis point for 3-month futures, while rounded up in case of a tie.
This definition means the number F can be properly calculated and published by the exchange at the settlement date T2. It is this number that is reported as the futures price – actually, the futures settlement price – at T2.
For example, if the exchange calculates the composite weighted average of all Fed-published SOFR indices with a corresponding value date ranging from the Wednesday September 16, 2020 (included) to the Wednesday December 16, 2020 (excluded) as 0.1%, then the above definition of F states (100 – F) % = 0.1%, from which results that F = 99.9.
At any other time t prior to settlement, the futures price Ft constantly varies and is determined by supply and demand based on the contract specification, whereby the change Ft-Δt - Ft over a time interval Δt results (roughly) to the margin account of the long holder being credited by an amount directly proportional to Ft-Δt - Ft., with the proportionality factor set by the exchange as part of the futures contract's spec.
Precisely, the amount per contract associated with a change of one basis point is $41.67 for 1-month futures and $25 for 3-month futures.
Ending this digression on the meaning of futures price, one should note that the SOFR futures settlement price F is determined in a backward-looking sense because it depends on historical rates of which the lifespan has ended before T2.
This is in stark contrast to the definition of the settlement price of a 3-month Eurodollar futures contract, of which the underlying Libor rate references the 3-month period ( T1 , T2 ). Such a contract both expires and settles on the Monday on T1 – 2B, because on that Monday the underlying spot Libor rate spanning the period ( T1 , T2 ) becomes known due to the embedded 2B spot settlement in all Eurodollar deposits. The Eurodollar futures contract is then forward-looking because its settlement price depends on a Libor rate that accrues over a time interval that lies after the contract's settlement date.
Nevertheless, from a valuation perspective a 3-month SOFR futures contract differs from a 3-month Eurodollar futures contract ONLY with regard to their credit/liquidity risk profiles, provided both contracts have the same T1 , T2 and T1 > today. The mathematical proof for this relies on the compounding character of the 3-month SOFR futures contracts and breaks down for the 1-month SOFR futures contracts that use an arithmetic average settlement formula.
This relation is also empirically corroborated by a study undertaken by CME that resulted in the following chart depicting a very strong correlation between the compounded 3-month SOFR and the 3-month USD Libor.
Using the Wizard to setup the Formulas that create a Yield Curve out of Futures and Swaps
The Deriscope wizard allows for the selection of several instrument types during the setup of the Yield Curve creation formula.
The screenshot below shows that I have selected the type Yield Curve and checked the two boxes next to -Use Futures= and -Use OIS=.
Before pasting the formula in the spreadsheet, I need to make a few changes in the first object that appears as $YldCrv#1 to the right of the Market Data= key.
Clicking on its lens sign, I see its contents as:
I notice this involves the 3-month USD Libor, which is not what I want!
There must be some option that allows me to set it up as a futures contract on SOFR.
Such an option exists, but it is not visible because only the mandatory parameters are currently shown.
In order to display all parameters, including the optional, I must first click on the little button to the right of the Go button. Then the screen changes to:
Now I can click on the shown Ibor element at the second row and select the element Overnight from the listed values. Then the screen changes to:
This screen obviously defines only futures with IMM dates and Compound averaging formula, as one may tell from the two key/value pairs Standard Dates= IMM and Rate Formula= Compound.
This would be ok if I were interested in 3-month SOFR futures that indeed expire on IMM dates and their settlement is based on the compounded rate formula.
But I intend to mix both 1-month and 3-month instruments, which means I must edit these two settings, as shown below, where I have also clicked on the little button in order to suppress again the optional inputs:
Now I am ready to click on the Go button in order to paste the generated formulas in my currently selected spreadsheet cell in the same sheet where I had pasted my very first wizard-generated formulas at the beginning of this post.
Here is the result, after some range shuffling for better visual exposition:
The main new formula is in the currently selected cell J1 that returns the handle name &USDCrv_J1:4.1 that represents an object of type Yield Curve created out of both futures prices and swap rates.
The table with the futures prices can be seen in columns D to H, whereas the previously existed swap rates in columns A to B are being reused.
The interesting new element here is the long list of historical overnight rates created by the wizard in columns M to N. The wizard was smart enough to notice that the start date of September 16 of the first futures contract is in the past and decided accordingly to supply the required historical SOFR fixings through the object &HistVals_M1:4.1 in cell M1.
As seen before, the wizard sets up all formulas with dummy but reasonable input data. It is now my task to edit these data so that they match my actual quotes and conventions.
Below is how the final spreadsheet looks like after I have added the missing market rates from Bloomberg:
Plugging in the Bloomberg Futures and Swap Rates
I will use SOFR futures prices and swap rates imported from Bloomberg on November 10, 2020.
I plan to use a mixture of 1-month and 3-month SOFR futures with expiries until the end of the first year, followed by SOFR swaps.
Below is the Bloomberg page that shows the quoted prices and the Bloomberg calculated convexity adjustments for the short term subset of the available futures contracts.
Note the second contract Sep 20+3 is a 3-month compounded contract settled on the 3rd Wednesday of December 2020, whereas the next contract Dec 20+1 is a 1-month arithmetic average contract expiring on December 31, 2020 and settles on January 4, 2021.
All in all, I will use the top 16 contracts with increasing expiries reaching up to November 1, 2021.
The screenshots with all rates are included in the downloadable spreadsheet at the end of this article.
Below is the final formula that creates the Yield Curve object &FutSwapCurve.1 in cell M1:
The shown historical SOFR fixings are taken from this Fed site.
Sanity Check: Reproducing the Market Prices
As I promised above, I will now use this Yield Curve object to calculate the prices of a few of the market instruments that I used as input in its creation.
In order to have some minimum trust in this object, the produced prices ought to be very close to 0.
The only challenging part in this task is the setup of the about-to-test instruments as Deriscope objects of type Overnight Index Swap, with their defining parameters properly set so that they precisely represent the chosen market instruments.
Perhaps you would like to consult here my earlier post on building and pricing Overnight Index Swaps.
I will start by picking up, quite arbitrarily, the 2-year OIS that I used in my curve building with the mid-market rate of 0.074%.
Below are the 3 formulas needed for the test. The price is calculated by the formula at the top, which returns -2.49624E-13, which is a very small number that can safely be regarded as very close to 0.
Note that I have built my Overnight Index Swap instrument in cell A7 and named it SpotSwap for easy reference.
Its rate has been set to 0.074% so that it matches the corresponding market rate.
Its payment lag has been set to 2 days following the US Government Bond calendar, as betrayed by the setting Pmt Lag= %2D{US_GOVBOND|F}
Its accrual schedule is defined by the separate object further below, which sets its Start Date= as November 13, 2020 because the market OIS instruments have a settlement of 2 business days.
My next test instrument will be the 3-month futures contract that starts in the past date of September 16, 2020 and expires on December 16, 2020. Due to its past start date, this choice would also help to test the proper handling of the SOFR historical fixings.
Below is my spreadsheet after adding the pricing of this instrument, which once more indicates success:
Note that I have decided to represent the 3-month futures contract with a Deriscope object of type Overnight Index Swap.
This is permissible because the library prices the futures contracts as if they were forward contracts. It is also true that the cash flows of a forward contract on the compounded average SOFR index are the same as that of an appropriately defined OIS contract consisting of one single accrual period.
I have set the rate to equal 100 minus the quoted price of the corresponding futures contract.
I have set the payment lag to 0 days since the futures settlement occurs on December 16 without further delay.
I have also defined the Schedule object by specifying the End Date= rather than the Tenor= key.
Proceeding similarly, I setup three more instruments that represent a 3-month forward-start, 1-month past-start and 1-month forward-start futures contract respectively.
Below are the pricing results of these three additional instruments:
All tests have proved successful.
Note the two 1-month instruments have Rate Formula= Average.
This is important because the 1-month futures settle based on the arithmetic rather than compounded average.
They also have Rate Cutoff= 0.
This is also needed because the default Rate Cutoff setting for an arithmetic average OIS is 2 business days. So, it should be set here to 0, since the 1-month futures contracts have no rate cutoff.
At this point, it could be instructive to show you how to easily verify that these Deriscope objects indeed correspond to the financial instruments they purport to represent.
For example, assume I want to check if the instrument &FwdSwap1Mb.1 created in cell M7 represents indeed a 1-month futures contract with the stated start date December 1, 2020 and expiry (actually, settlement date before being bumped to its respective good business day) January 1, 2021.
I could gain important information on this object by inspecting its contents inside the wizard, but this would fail to show me data dependent on volatile context variables such as applicable floating rates and valuation dates.
More comprehensive information can be gathered by selecting the cell that returns the pricing output of the object under consideration.
The reason this is better is because the pricing output must necessarily have access to all volatile data, something that is not possible by the object created in cell M7.
If I select the cell M7 while the wizard is open, I see the following:
Next, I click on the first here, and I see:
Next, I click on the lens sign to the right of CashFlows=:
Next, I click on the lens sign to the left of $Variant#3:
The important information here lies at the bottom rows, where the value dates of the daily SOFR indices involved in the arithmetic average calculation are shown.
The first value date appears as December 1, 2020, which indeed is as expected.
Then the dates keep increasing from left to right, with occasional jumps due to encountered non-business days. The row below displays the corresponding time intervals, where increases are observed whenever a non-business day gives rise to a longer accrual interval.
If I scroll to the very right, I see:
I notice that the last two value dates are December 31, 2020 and January 4, 2021, but there is no accrual period underneath January 4, 2021.
This means that the last SOFR index has been calculated as of December 31 for an interest accrual period of 4 days extending to January 4.
All this is correct and exactly reflects the method the exchange will apply on January 4 to calculate the arithmetic average over the past one month.
Using the created Futures + Swaps Yield Curve object
Here I apply the same formulas I used with the previous curve that was created with only the OIS instruments.
I am only interested in the first 2 years, since the curve beyond 2 years should be almost the same with the corresponding section of the previous curve, due to the fact that both curves rely on the same OIS instruments for these maturities.
Below is the implied zero rate chart:
This chart has surprised me!
I would have expected that the first 2-year portion would be much smoother in the current case when the curve is built out of SOFR futures.
Can it be that the two different types of SOFR futures contracts – the 1-month and 3-month – do not mix well together, perhaps due to being traded by different players for diverse purposes?
For now, I would be curious to find out if the situation improves when I construct the curve using either only 1-month futures or only 3-month futures.
Indeed, this seems to be the case, as the following two charts testify:
My next post explores the possibility that the oscillatory shape of the above curves may have something to do with the well-known phenomenon of end-of-the-month overnight rate jumps.
Click on YieldCurveSofr.xlsx to download the spreadsheet produced with the above steps.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com
