1. Home
  2. Article Catalog
  3. Computing Risk Free Rates and Excess Returns Part 2: From Sovereign Coupon-Paying-Bonds (and Bootstrapping / Spot Rates / Discount-Factors)

Academic Article Series: Economics and Finance 101:

Computing Risk Free Rates and Excess Returns Part 2: From Sovereign Coupon-Paying-Bonds (and Bootstrapping / Spot Rates / Discount-Factors)

Author:
Jonathan Legrand
Developer Advocate Developer Advocate

Our previous article explained what Net Present ValuesFace ValuesMaturitiesCoupons, and risk-free rates are, how to compute them, and how they are used to calculate excess returns using only Zero-Coupon Bonds; in this article, we look at Coupon Paying Bonds, particularly Cash Flow incurred by Coupons, Bootstrapping and particularities about Sovereign Bond data. Very little is changed in this article until the 'Coupon Paying Rate' section. It is aimed at academics from undergraduate level up, and thus will explain all mathematical notations to ensure that there is no confusion and so that anyone - no matter their expertise on the subject - can follow.

 

One may find many uses for the methods outlined below. For example: To calculate metrics such as the Sharpe-Ratio, one needs first to calculate excess returns, that then necessitates the calculation of risk-free rates of return.

 

You may find recordings of my Live Coding Sessions on my YouTube Playlist and Chanel where I explain the code and concepts below.

 

Webinar Video

This Webinar was broadcasted live on Twitch where I code live every Tuesday at 4pm GMT.

Using this Site

You may use the links below to reach out to contents, and scale up or down the math figures at your convenience as per the below.

Let's have a look at data from "2020-07-31":

    	
            

# We need to gather our data. Since Refinitiv's DataStream Web Services (DSWS) allows for access to the most accurate and wholesome end-of-day (E.O.D.) economic database (DB), naturally it is more than appropriate. We can access DSWS via the Python library "DatastreamDSWS" that can be installed simply by using  pip install .

import DatastreamDSWS as DSWS

 

# We can use our Refinitiv's Datastream Web Socket (DSWS) API keys that allows us to be identified by Refinitiv's back-end services and enables us to request (and fetch) data:

DSWS_username = open("Datastream_username.txt", "r")  # The username is placed in a text file so that it may be used in this code without showing it itself

DSWS_password = open("Datastream_password.txt", "r")  # Same for the password

ds = DSWS.Datastream(username=str(DSWS_username.read()),

                     password=str(DSWS_password.read()))

 

# It is best to close the files we opened in order to make sure that we don't stop any other services/programs from accessing them if they need to:

DSWS_username.close()

DSWS_password.close()

    	
            

# Now let's get US Treasury Note (Maturing) 31/07/25:

fytn_Coupon_2020_07_31_p = ds.get_data( #  the ' _p ' is for 'price'

    tickers='613XGU', # found this ticker on https://product.datastream.com/browse/search.aspx?dsid=ZRQW955&AppGroup=DSAddin&q=US+Treasury+Note+31%2F07%2F25&prev=99_US+Treasury+Note+(Maturing)+31%2F07%2F25&nav_category=13

    start = "2020-07-29", fields="X", freq='D')

    	
            fytn_Coupon_2020_07_31_p.dropna().T
        
        
    
  Dates 29/07/2020 30/07/2020 31/07/2020 03/08/2020 04/08/2020 05/08/2020 06/08/2020 07/08/2020 10/08/2020 11/08/2020 ... 05/08/2021 06/08/2021 09/08/2021 10/08/2021 11/08/2021 12/08/2021 13/08/2021 16/08/2021 17/08/2021 18/08/2021
Instrument Field                                          
613XGU X 99.9765 100.1016 100.2031 100.1484 100.2891 100.1328 100.1719 100.1016 100.0625 99.8672 ... 98.7109 98.5469 98.4688 98.3515 98.4375 98.3672 98.5469 98.5859 98.5547 98.5625
    	
            fytn_Coupon_2020_07_31_p.loc["2020-07-30"]
        
        
    

Instrument Field
613XGU X 100.1016
Name: 2020-07-30, dtype: float64

    	
            

# Now let's get US Treasury Note (Maturing) 31/07/25:

fytn_Coupon_2020_07_31_f = ds.get_data(  # the ' _f ' is for 'fixed', as in fixed data.

    tickers='613XGU',  # found this ticker on https://product.datastream.com/browse/search.aspx?dsid=ZRQW955&AppGroup=DSAddin&q=US+Treasury+Note+31%2F07%2F25&prev=99_US+Treasury+Note+(Maturing)+31%2F07%2F25&nav_category=13

    kind=0,  # ' kind=0 ' is needed here as we are looking for static data that doesn't change with time.

    fields=["NAME", "ID", "TERM", "RV", "C", "CTYP", "RDL", "EXCHB", "DEF",

            "MPD.U", "BTYP", "AIS", "BAB"])

fytn_Coupon_2020_07_31_f.T

  0 1 2 3 4 5 6 7 8 9 10 11 12
Instrument 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU 613XGU
Datatype NAME ID TERM RV C CTYP RDL EXCHB DEF MPD.U BTYP AIS BAB
Value US TREASURY NOTE 2020 1/4% 31/07/25 AB-2025 31/07/2020 5 100 0.25 FIX 31/07/2025 FF MU DD MPD % STR 54568290 5
Dates 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021 18/08/2021
    	
            0.5 * (0.25/100) * 100
        
        
    

0.125

    	
            ytm_op = YTM_Solver(fv=100, c=0.25, m=5, p=100.1016, f_acf=0.5, details=True)['Opt. YTM']
print(f"Our optimal YTM is {ytm_op}")

remainder: 0.0, m: 5.0, m_rounded: 5.0
It is most likely that if ' remainder ' is 0, we're looking at a newly issued Bond, in which case we're not going to get a coupon payment at time '0'. If you do expect a coupon payment straight away, then please specify it in arguments ' cash_flow_no_fv ' or ' cash_flow_with_fv '.
Years to each Coupon Payments (len: 10): [0.5, 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0]
0.5 year periods to each Coupon Payments (len: 10): [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0]
Cashflow (len: 10): [0.125, 0.125, 0.125, 0.125, 0.125, 0.125, 0.125, 0.125, 0.125, 100.125]
Our optimal YTM is 0.002295515059055018

    	
            

(100/((1+(0.5*ytm_op))**(10))) - 100.1016 + (sum(

    [0.125/((1 + (0.5 * ytm_op))**(i)) for i in range(1, 11)]))

1.2434497875801753e-14

    	
            # There are some libraries we need to import:
import pandas # pandas will be needed to manipulate data sets
from datetime import datetime
import calendar
print("The pandas library imported in this code is version: " + pandas.__version__)

The pandas library imported in this code is version: 1.2.4

    	
            

# Collect a list of all the relevent Datastream Instrument Codes:

list_of_instrument_dfs, list_of_instruments = [], []

for j in range(2022, 2027):  # range from year to year

    for i in range(1, 13):  # range from month to month

        monthrange = calendar.monthrange(j, i)  # This gives two numbers in a tuple, the start day of any month (always 1) and the last.

        search_term = 'US Treasury Note ' + str(j-5) + ' ' + str(  # Our search term is what we are going to look for; imagine we are using the Datastream Navigator and searching for that term. The ' -5 ' is here because we're looking at Bonds with 5 year terms.

            monthrange[1]) + '/' + '{:>02}'.format(i) + '/' + str(j)[2:]  # ' monthrange[1] ' is the last date of that month. Also, ' '{:>02}'.format(i) ' adds a leading '0' in front of single digits, which is needed in our search term.

        instrument = ds.get_data(  # This gets the lookup data from Datastream's Navigator's API via the ' DS.SYMBOLLOOKUP ' field.

            tickers=search_term, kind=0,

            fields=['DS.SYMBOLLOOKUP(Category=Bonds & Convertibles)'])

        list_of_instrument_dfs.append(instrument)

        if instrument["Value"][0] != "No matches":

            list_of_instruments.append(instrument["Instrument"].values[0])

Now we can use them all together:

    	
            # Collect original fixed data from DSWS:
ytm_df = DSWS_fixed_and_price_data_collection_and_ytm(instruments=list_of_instruments)
ytm_df
  DSCD NAME ID TERM RV C CTYP RDL Price at issue CD CF YTM
0 613XGU US TREASURY NOTE 2020 1/4% 31/07/25 AB-2025 31/07/2020 5 100 0.25 FIX 31/07/2025 100.2031 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.002091
0 616J44 US TREASURY NOTE 2020 1/4% 31/08/25 AC-2025 31/08/2020 5 100 0.25 FIX 31/08/2025 99.9063 [2021-03-01 00:00:00, 2021-08-31 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.002689
0 619AX8 US TREASURY NOTE 2020 1/4% 30/09/25 AD-2025 30/09/2020 5 100 0.25 FIX 30/09/2025 99.8594 [2021-03-31 00:00:00, 2021-09-30 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.002783
0 622HAG US TREASURY NOTE 2020 1/4% 31/10/25 AF-2025 31/10/2020 5 100 0.25 FIX 31/10/2025 99.3359 [2021-04-30 00:00:00, 2021-11-01 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.003842
0 624Z3N US TREASURY NOTE 2020 3/8% 30/11/25 AG-2025 30/11/2020 5 100 0.375 FIX 30/11/2025 100.0547 [2021-06-01 00:00:00, 2021-11-30 00:00:00, 202... [0.375, 0.375, 0.375, 0.375, 0.375, 0.375, 0.3... 0.00364
0 628GF5 US TREASURY NOTE 2020 3/8% 31/12/25 AH-2025 31/12/2020 5 100 0.375 FIX 31/12/2025 100.0703 [2021-06-30 00:00:00, 2021-12-31 00:00:00, 202... [0.375, 0.375, 0.375, 0.375, 0.375, 0.375, 0.3... 0.003608
0 6309UZ US TREASURY NOTE 2021 3/8% 31/01/26 U-2026 31/01/2021 5 100 0.375 FIX 31/01/2026 99.75 [2021-08-02 00:00:00, 2022-01-31 00:00:00, 202... [0.375, 0.375, 0.375, 0.375, 0.375, 0.375, 0.3... 0.004256
0 634DPQ US TREASURY NOTE 2021 1/2% 28/02/26 V-2026 28/02/2021 5 100 0.5 FIX 28/02/2026 98.875 [2021-08-31 00:00:00, 2022-02-28 00:00:00, 202... [0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5, ... 0.007295
0 637QZJ US TREASURY NOTE 2021 3/4% 31/03/26 W-2026 31/03/2021 5 100 0.75 FIX 31/03/2026 99.0859 [2021-09-30 00:00:00, 2022-03-31 00:00:00, 202... [0.75, 0.75, 0.75, 0.75, 0.75, 0.75, 0.75, 0.7... 0.009376
0 6403CY US TREASURY NOTE 2021 3/4% 30/04/26 Y-2026 30/04/2021 5 100 0.75 FIX 30/04/2026 99.5156 [2021-11-01 00:00:00, 2022-05-02 00:00:00, 202... [0.75, 0.75, 0.75, 0.75, 0.75, 0.75, 0.75, 0.7... 0.008492
0 6439UZ US TREASURY NOTE 2021 3/4% 31/05/26 Z-2026 31/05/2021 5 100 0.75 FIX 31/05/2026 99.8125 [2021-11-30 00:00:00, 2022-05-31 00:00:00, 202... [0.75, 0.75, 0.75, 0.75, 0.75, 0.75, 0.75, 0.7... 0.007883
0 646V3Q US TREASURY NOTE 2021 7/8% 30/06/26 AA-2026 30/06/2021 5 100 0.875 FIX 30/06/2026 99.9219 [2021-12-31 00:00:00, 2022-06-30 00:00:00, 202... [0.875, 0.875, 0.875, 0.875, 0.875, 0.875, 0.8... 0.00891

Here's an example of one of the cash-flows outputted:

    	
            pandas.DataFrame(data=ytm_df['CF'].iloc[0], index=ytm_df['CD'].iloc[0]).T
        
        
    
  01/02/2021 02/08/2021 31/01/2022 01/08/2022 31/01/2023 31/07/2023 31/01/2024 31/07/2024 31/01/2025 31/07/2025
0 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25 0.25
    	
            

# 1st we need to import some new Python libraries:

 

from datetime import date, timedelta

import statistics

 

# The below are needed to plot graphs of all kinds

import plotly

import plotly.express

import plotly.graph_objects as go

from plotly.subplots import make_subplots

from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot

 

init_notebook_mode(connected=True)

 

import cufflinks

cufflinks.go_offline()

# cufflinks.set_config_file(offline = True, world_readable = True)

 

for i, j in zip(["plotly", "cufflinks"], [plotly, cufflinks]):

    print("The " + str(i) + " library imported in this code is version: " + j.__version__)

The plotly library imported in this code is version: 4.14.3
The cufflinks library imported in this code is version: 0.17.3

    	
            

fig = make_subplots(specs=[[{"secondary_y": True}]])  # Create figure with secondary y-axis

 

# Create vertical lines when Coupon rates change

c_changed_date, shapes = [], []

for i in range(len(ytm_df)-1):

    if ytm_df.iloc[i]["C"] - ytm_df.iloc[i+1]["C"] != 0:

        sdate = datetime.strptime(ytm_df.iloc[i]["ID"], '%Y-%m-%d')

        edate = datetime.strptime(ytm_df.iloc[i+1]["ID"], '%Y-%m-%d')

        delta = edate - sdate

        inter_dates = [sdate + timedelta(days=i) for i in range(delta.days + 1)]

        c_changed_date.append(inter_dates[int(statistics.median(

            range(1, len(inter_dates))))].strftime('%Y-%m-%d'))

for i in c_changed_date:

    shapes.append({'type': 'line', 'xref': 'x', 'yref': 'paper',

                   'x0': i, 'y0': 0, 'x1': i, 'y1': 1})

fig.update_layout(shapes=shapes,)

 

# Add traces

fig.add_trace(go.Scatter(x=ytm_df["ID"], y=ytm_df["YTM"],

                         name="Yield To Maturity", line_color="#1f77b4"),

              secondary_y=False,)

fig.add_trace(go.Scatter(x=ytm_df["ID"], y=ytm_df["Price at issue"],

                         name="Price at issue", line_color="#383838"),

              secondary_y=True,)

 

# Set y-axes titles

fig.update_yaxes(title_text="Yield To Maturity", secondary_y=False)

fig.update_yaxes(title_text="Price in US$", secondary_y=True)

 

fig.update_layout(title_text="US 5 Year T-Note",

                  yaxis=dict(titlefont=dict(color="#1f77b4"),

                             tickfont=dict(color="#1f77b4")),)

fig.update_xaxes(title_text="Date")  # Set x-axis title

 

fig.show()

    	
            

def Spot_Rate_Last(fv, m, c, p, sr, f=0.5):

    """Spot_Rate_Last(f,fv,m,c,p,sr)

    This Python function returns the Zero-Coupon Equivalent rate of a coupon-paying Bond (otherwise known as the Spot-Rate) of one time period (defined as ' f ') after the last one in the list of provided (past) spot-rates (' sp ').

 

    Returns

    -------

 

    float: The last spot rate of a Bond for the period looked at.

    """

    discouted_coupon_payments = sum([(f*c)/((1 + (f*j))**(i+1))  # ' +1 ' because 'enumerate' starts at 0

                                     for i, j in enumerate(sr)])

    last_sr = ((((fv + f*c)/(p - discouted_coupon_payments))**(f/m))-1)/f

    return last_sr

    	
            

yc_df = DSWS_fixed_and_price_data_collection_and_ytm(

    instruments=["613V2R", "613XGW", "613XGU", "613XG1"],

    fields=["DSCD", "NAME", "ID", "TERM",

            "RV", "C", "CTYP", "RDL"])

 

yc_df.index = yc_df['RDL']

 

display(yc_df)

yc_df["YTM"].iplot(title="Yield To Maturity of US Treasury Note issued at end of Jully 2020 with fixed and different Coupon rates")

  DSCD NAME ID TERM RV C CTYP RDL Price at issue CD CF YTM
RDL                        
29/12/2020 613V2R UTD.STS OF AMERICA 2020 ZERO 29/12/20 28/07/2020 0.5 100 0 ZERO 29/12/2020 99.9469 [2021-02-01 00:00:00] [0.0] 0.001063
31/07/2022 613XGW US TREASURY NOTE 2020 1/8% 31/07/22 BD-2022 31/07/2020 2 100 0.125 FIX 31/07/2022 100.0313 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.125, 0.125, 0.125, 0.125] 0.001093
31/07/2025 613XGU US TREASURY NOTE 2020 1/4% 31/07/25 AB-2025 31/07/2020 5 100 0.25 FIX 31/07/2025 100.2031 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.002091
31/07/2027 613XG1 US TREASURY NOTE 2020 3/8% 31/07/27 N-2027 31/07/2020 7 100 0.375 FIX 31/07/2027 99.914 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.375, 0.375, 0.375, 0.375, 0.375, 0.375, 0.3... 0.003875
    	
            

from_date = "2020-07-31"

 

We stick to 360 days a year to keep in line with the FEDERAL RESERVE statistical release, however, we should both (i) let people chose the average number of days per year they deem useful in this calculation and (ii) the rounding they're ready to use

    	
            

off_run_df = DSWS_fixed_and_price_data_collection_and_ytm(

    instruments=["247DK9", "235FFA", "218QH0", "823D0N", "210EV1",

                 "235FFD", "247DLH", "218QJQ", "235FF9", "247DLR"],

    fields=["DSCD", "NAME", "ID", "TERM",

            "RV", "C", "CTYP", "RDL"])

 

# Configure the data-frame's index appropriately

off_run_df.index = off_run_df['RDL']

off_run_df.index.names = ['RD']  # ' RD ' for Redemption Date

 

# Combine off- and on-the-run Bonds' data-frames

off_on_df = yc_df.append(off_run_df)

 

# Collect and add price data as of date of interest

off_on_df["Price on 2020-07-31"] = [

    ds.get_data(start="2020-07-31", end="2020-07-31",

                fields="X", freq='D',

                tickers=i).values[0][2]

    for i in list(off_on_df["DSCD"])]

 

# Add the YTM implied by 2020-07-31 Price

off_on_df["YTM implied by 2020-07-31 Price"] = [

    YTM_Solver(

        fv=off_on_df.iloc[i]["RV"],

        c=off_on_df.iloc[i]["RV"] * off_on_df.iloc[i]["C"] / 100,

        m=(round(2*((datetime.strptime(off_on_df.iloc[i]["RDL"], '%Y-%m-%d') -

                     datetime.strptime(from_date, '%Y-%m-%d')).days)/360)) / 2,

        f_acf=0.5, error=False,

        p=off_on_df.iloc[i]["Price on 2020-07-31"])['Opt. YTM']

    for i in range(len(off_on_df))]

 

# Add the Years Left to Maturity

off_on_df["Years Left to Maturity"] = [

    (round(2*((datetime.strptime(off_on_df.iloc[i]["RDL"], '%Y-%m-%d') -

               datetime.strptime(from_date, '%Y-%m-%d')).days)/360)) / 2

    for i in range(len(off_on_df))]

    	
            

off_on_df.sort_values('RDL', inplace=True)

display(off_on_df)

off_on_df["YTM implied by 2020-07-31 Price"].iplot(

    title="Yield To Maturity of US Treasury Bonds with different issue dates fixed and different Coupon rates")

  DSCD NAME ID TERM RV C CTYP RDL Price at issue CD CF YTM Price on 2020-07-31 YTM implied by 2020-07-31 Price Years Left to Maturity
29/12/2020 613V2R UTD.STS OF AMERICA 2020 ZERO 29/12/20 28/07/2020 0.5 100 0 ZERO 29/12/2020 99.9469 [2021-02-01 00:00:00] [0.0] 0.001063 99.9559 0.000882 0.5
31/07/2021 235FFA US TREASURY NOTE 2019 1 3/4% 31/07/21 BD-2021 31/07/2019 2 100 1.75 FIX 31/07/2021 99.793 [2020-01-31 00:00:00, 2020-07-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75] 0.018559 101.6094 0.001389 1
31/12/2021 247DK9 US TREASURY NOTE 2019 1 5/8% 31/12/21 BL-2021 31/12/2019 2 100 1.625 FIX 31/12/2021 100.0742 [2020-06-30 00:00:00, 2020-12-31 00:00:00, 202... [1.625, 1.625, 1.625, 1.625] 0.015872 102.1016 0.002208 1.5
31/07/2022 613XGW US TREASURY NOTE 2020 1/8% 31/07/22 BD-2022 31/07/2020 2 100 0.125 FIX 31/07/2022 100.0313 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.125, 0.125, 0.125, 0.125] 0.001093 100.0313 0.001093 2
31/12/2022 823D0N US TREASURY NOTE 2015 2 1/8% 31/12/22 T-2022 31/12/2015 7 100 2.125 FIX 31/12/2022 100.2031 [2016-06-30 00:00:00, 2017-01-03 00:00:00, 201... [2.125, 2.125, 2.125, 2.125, 2.125, 2.125, 2.1... 0.020937 104.8281 0.001883 2.5
31/07/2023 210EV1 US TREASURY NOTE 2018 2 3/4% 31/07/23 AB-2023 31/07/2018 5 100 2.75 FIX 31/07/2023 99.5469 [2019-01-31 00:00:00, 2019-07-31 00:00:00, 202... [2.75, 2.75, 2.75, 2.75, 2.75, 2.75, 2.75, 2.7... 0.028479 107.8438 0.001295 3
31/12/2023 218QH0 US TREASURY NOTE 2018 2 5/8% 31/12/23 AG-2023 31/12/2018 5 100 2.625 FIX 31/12/2023 100.5391 [2019-07-01 00:00:00, 2019-12-31 00:00:00, 202... [2.625, 2.625, 2.625, 2.625, 2.625, 2.625, 2.6... 0.025096 108.4609 0.00198 3.5
31/07/2024 235FFD US TREASURY NOTE 2019 1 3/4% 31/07/24 AB-2024 31/07/2019 5 100 1.75 FIX 31/07/2024 99.6563 [2020-01-31 00:00:00, 2020-07-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.7... 0.018222 106.3203 0.001641 4
31/12/2024 247DLH US TREASURY NOTE 2019 1 3/4% 31/12/24 AH-2024 31/12/2019 5 100 1.75 FIX 31/12/2024 100.2734 [2020-06-30 00:00:00, 2020-12-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.7... 0.016927 106.8828 0.002124 4.5
31/07/2025 613XGU US TREASURY NOTE 2020 1/4% 31/07/25 AB-2025 31/07/2020 5 100 0.25 FIX 31/07/2025 100.2031 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.002091 100.2031 0.002091 5
31/12/2025 218QJQ US TREASURY NOTE 2018 2 5/8% 31/12/25 T-2025 31/12/2018 7 100 2.625 FIX 31/12/2025 100.25 [2019-07-01 00:00:00, 2019-12-31 00:00:00, 202... [2.625, 2.625, 2.625, 2.625, 2.625, 2.625, 2.6... 0.025857 112.7344 0.002895 5.5
31/07/2026 235FF9 US TREASURY NOTE 2019 1 7/8% 31/07/26 N-2026 31/07/2019 7 100 1.875 FIX 31/07/2026 99.7813 [2020-01-31 00:00:00, 2020-07-31 00:00:00, 202... [1.875, 1.875, 1.875, 1.875, 1.875, 1.875, 1.8... 0.019085 109.2891 0.003111 6
31/12/2026 247DLR US TREASURY NOTE 2019 1 3/4% 31/12/26 T-2026 31/12/2019 7 100 1.75 FIX 31/12/2026 99.4375 [2020-06-30 00:00:00, 2020-12-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.7... 0.01836 108.8984 0.003635 6.5
31/07/2027 613XG1 US TREASURY NOTE 2020 3/8% 31/07/27 N-2027 31/07/2020 7 100 0.375 FIX 31/07/2027 99.914 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.375, 0.375, 0.375, 0.375, 0.375, 0.375, 0.3... 0.003875 99.914 0.003875 7

This vertical zigzagging is an unexpected pattern. The reason for which it is unexpected is that an economic agent ought to be able to exploit arbitrage opportunities brought up by such price discrepancies with other securities (*e.g.*: Swaps).

    	
            off_on_df.iloc[0]["YTM implied by 2020-07-31 Price"]
        
        
    

0.0008823891336079769

    	
            

spot_rates = [0.0008823891336079769]  # This number is from the YTM of the only ZCB we've got as per the above.

for i in range(len(spot_rates), len(off_on_df)):

    spot_rates.append(Spot_Rate_Last(

        fv=off_on_df.iloc[i]["RV"], sr=spot_rates, f=0.5,

        m=off_on_df.iloc[i]["Years Left to Maturity"],

        c=off_on_df.iloc[i]["RV"] * off_on_df.iloc[i]["C"] / 100,

        p=off_on_df.iloc[i]["Price on 2020-07-31"]))

off_on_df["Spot Rates as of 2020-07-31"] = spot_rates

    	
            off_on_df["YTM"][0]
        
        
    

0.0010625642216017055

    	
            off_on_df
        
        
    
  DSCD NAME ID TERM RV C CTYP RDL Price at issue CD CF YTM Price on 2020-07-31 YTM implied by 2020-07-31 Price Years Left to Maturity Spot Rates as of 2020-07-31
29/12/2020 613V2R UTD.STS OF AMERICA 2020 ZERO 29/12/20 28/07/2020 0.5 100 0 ZERO 29/12/2020 99.9469 [2021-02-01 00:00:00] [0.0] 0.001063 99.9559 0.000882 0.5 0.000882
31/07/2021 235FFA US TREASURY NOTE 2019 1 3/4% 31/07/21 BD-2021 31/07/2019 2 100 1.75 FIX 31/07/2021 99.793 [2020-01-31 00:00:00, 2020-07-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75] 0.018559 101.6094 0.001389 1 0.001391
31/12/2021 247DK9 US TREASURY NOTE 2019 1 5/8% 31/12/21 BL-2021 31/12/2019 2 100 1.625 FIX 31/12/2021 100.0742 [2020-06-30 00:00:00, 2020-12-31 00:00:00, 202... [1.625, 1.625, 1.625, 1.625] 0.015872 102.1016 0.002208 1.5 0.002216
31/07/2022 613XGW US TREASURY NOTE 2020 1/8% 31/07/22 BD-2022 31/07/2020 2 100 0.125 FIX 31/07/2022 100.0313 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.125, 0.125, 0.125, 0.125] 0.001093 100.0313 0.001093 2 0.001093
31/12/2022 823D0N US TREASURY NOTE 2015 2 1/8% 31/12/22 T-2022 31/12/2015 7 100 2.125 FIX 31/12/2022 100.2031 [2016-06-30 00:00:00, 2017-01-03 00:00:00, 201... [2.125, 2.125, 2.125, 2.125, 2.125, 2.125, 2.1... 0.020937 104.8281 0.001883 2.5 0.001892
31/07/2023 210EV1 US TREASURY NOTE 2018 2 3/4% 31/07/23 AB-2023 31/07/2018 5 100 2.75 FIX 31/07/2023 99.5469 [2019-01-31 00:00:00, 2019-07-31 00:00:00, 202... [2.75, 2.75, 2.75, 2.75, 2.75, 2.75, 2.75, 2.7... 0.028479 107.8438 0.001295 3 0.001284
31/12/2023 218QH0 US TREASURY NOTE 2018 2 5/8% 31/12/23 AG-2023 31/12/2018 5 100 2.625 FIX 31/12/2023 100.5391 [2019-07-01 00:00:00, 2019-12-31 00:00:00, 202... [2.625, 2.625, 2.625, 2.625, 2.625, 2.625, 2.6... 0.025096 108.4609 0.00198 3.5 0.001998
31/07/2024 235FFD US TREASURY NOTE 2019 1 3/4% 31/07/24 AB-2024 31/07/2019 5 100 1.75 FIX 31/07/2024 99.6563 [2020-01-31 00:00:00, 2020-07-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.7... 0.018222 106.3203 0.001641 4 0.001641
31/12/2024 247DLH US TREASURY NOTE 2019 1 3/4% 31/12/24 AH-2024 31/12/2019 5 100 1.75 FIX 31/12/2024 100.2734 [2020-06-30 00:00:00, 2020-12-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.7... 0.016927 106.8828 0.002124 4.5 0.002141
31/07/2025 613XGU US TREASURY NOTE 2020 1/4% 31/07/25 AB-2025 31/07/2020 5 100 0.25 FIX 31/07/2025 100.2031 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.25, 0.2... 0.002091 100.2031 0.002091 5 0.002093
31/12/2025 218QJQ US TREASURY NOTE 2018 2 5/8% 31/12/25 T-2025 31/12/2018 7 100 2.625 FIX 31/12/2025 100.25 [2019-07-01 00:00:00, 2019-12-31 00:00:00, 202... [2.625, 2.625, 2.625, 2.625, 2.625, 2.625, 2.6... 0.025857 112.7344 0.002895 5.5 0.002966
31/07/2026 235FF9 US TREASURY NOTE 2019 1 7/8% 31/07/26 N-2026 31/07/2019 7 100 1.875 FIX 31/07/2026 99.7813 [2020-01-31 00:00:00, 2020-07-31 00:00:00, 202... [1.875, 1.875, 1.875, 1.875, 1.875, 1.875, 1.8... 0.019085 109.2891 0.003111 6 0.003169
31/12/2026 247DLR US TREASURY NOTE 2019 1 3/4% 31/12/26 T-2026 31/12/2019 7 100 1.75 FIX 31/12/2026 99.4375 [2020-06-30 00:00:00, 2020-12-31 00:00:00, 202... [1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.75, 1.7... 0.01836 108.8984 0.003635 6.5 0.003712
31/07/2027 613XG1 US TREASURY NOTE 2020 3/8% 31/07/27 N-2027 31/07/2020 7 100 0.375 FIX 31/07/2027 99.914 [2021-02-01 00:00:00, 2021-08-02 00:00:00, 202... [0.375, 0.375, 0.375, 0.375, 0.375, 0.375, 0.3... 0.003875 99.914 0.003875 7 0.003893
    	
            

off_on_df[["YTM implied by 2020-07-31 Price", "Spot Rates as of 2020-07-31"]].iplot(

    title="On- and Off-the-run US Treasury Bonds with Zero or Fixed (and different) Coupon Rates")

    	
            

instruments = ["628GA1", "628GAL", "628GD0", "628GF5", "628GDZ", "610JPM", '208PR9', "218QH0", "233HJH", "247DLH", "610JRN", "233HJK", "247DLR", "610JR7"]

fields = ["DSCD", "NAME", "ID", "TERM", "RV", "C", "CTYP", "RDL"]

_df = pandas.DataFrame()

for i in instruments:

    __df = ds.get_data(tickers=i, kind=0, fields=fields)

    _df = _df.append(pandas.DataFrame(

        data=list(__df['Value'].values), index=fields).T)

_df

  DSCD NAME ID TERM RV C CTYP RDL
0 628GA1 UTD.STS OF AMERICA 2020 ZERO 01/07/21 31/12/2020 0.5 100 0 ZERO 01/07/2021
0 628GAL UTD.STS OF AMERICA 2020 ZERO 30/12/21 31/12/2020 1 100 0 ZERO 30/12/2021
0 628GD0 US TREASURY NOTE 2020 1/8% 31/12/22 BL-2022 31/12/2020 2 100 0.125 FIX 31/12/2022
0 628GF5 US TREASURY NOTE 2020 3/8% 31/12/25 AH-2025 31/12/2020 5 100 0.375 FIX 31/12/2025
0 628GDZ US TREASURY NOTE 2020 5/8% 31/12/27 T-2027 31/12/2020 7 100 0.625 FIX 31/12/2027
0 610JPM US TREASURY NOTE 2020 1/8% 30/06/22 BC-2022 30/06/2020 2 100 0.125 FIX 30/06/2022
0 208PR9 US TREASURY NOTE 2018 2 5/8% 30/06/23 AA-2023 30/06/2018 5 100 2.625 FIX 30/06/2023
0 218QH0 US TREASURY NOTE 2018 2 5/8% 31/12/23 AG-2023 31/12/2018 5 100 2.625 FIX 31/12/2023
0 233HJH US TREASURY NOTE 2019 1 3/4% 30/06/24 AA-2024 30/06/2019 5 100 1.75 FIX 30/06/2024
0 247DLH US TREASURY NOTE 2019 1 3/4% 31/12/24 AH-2024 31/12/2019 5 100 1.75 FIX 31/12/2024
0 610JRN US TREASURY NOTE 2020 1/4% 30/06/25 AA-2025 30/06/2020 5 100 0.25 FIX 30/06/2025
0 233HJK US TREASURY NOTE 2019 1 7/8% 30/06/26 M-2026 30/06/2019 7 100 1.875 FIX 30/06/2026
0 247DLR US TREASURY NOTE 2019 1 3/4% 31/12/26 T-2026 31/12/2019 7 100 1.75 FIX 31/12/2026
0 610JR7 US TREASURY NOTE 2020 1/2% 30/06/27 M-2027 30/06/2020 7 100 0.5 FIX 30/06/2027
    	
            

on_off_df_as_of_2020_12_31 = Get_dsws_spot_rates(

    point_in_time="2020-12-31", coupon_paying_freq=0.5,

    instruments=instruments)

on_off_df_as_of_2020_12_31

  DSCD NAME ID TERM RV C CTYP RDL Price on 2020-12-31 YTM implied by 2020-12-31 Price Years from 2020-12-31 to Maturity Spot Rates as of 2020-12-31
RD                        
01/07/2021 628GA1 UTD.STS OF AMERICA 2020 ZERO 01/07/21 31/12/2020 0.5 100 0 ZERO 01/07/2021 99.9555 0.00089 0.5 0.00089
30/12/2021 628GAL UTD.STS OF AMERICA 2020 ZERO 30/12/21 31/12/2020 1 100 0 ZERO 30/12/2021 99.89 0.001101 1 0.001101
30/06/2022 610JPM US TREASURY NOTE 2020 1/8% 30/06/22 BC-2022 30/06/2020 2 100 0.125 FIX 30/06/2022 100.0156 0.001146 1.5 0.001146
31/12/2022 628GD0 US TREASURY NOTE 2020 1/8% 31/12/22 BL-2022 31/12/2020 2 100 0.125 FIX 31/12/2022 100 0.00125 2 0.00125
30/06/2023 208PR9 US TREASURY NOTE 2018 2 5/8% 30/06/23 AA-2023 30/06/2018 5 100 2.625 FIX 30/06/2023 106.1563 0.001567 2.5 0.001578
31/12/2023 218QH0 US TREASURY NOTE 2018 2 5/8% 31/12/23 AG-2023 31/12/2018 5 100 2.625 FIX 31/12/2023 107.3203 0.001773 3 0.001789
30/06/2024 233HJH US TREASURY NOTE 2019 1 3/4% 30/06/24 AA-2024 30/06/2019 5 100 1.75 FIX 30/06/2024 105.3516 0.002144 3.5 0.002163
31/12/2024 247DLH US TREASURY NOTE 2019 1 3/4% 31/12/24 AH-2024 31/12/2019 5 100 1.75 FIX 31/12/2024 105.9141 0.002627 4 0.002658
30/06/2025 610JRN US TREASURY NOTE 2020 1/4% 30/06/25 AA-2025 30/06/2020 5 100 0.25 FIX 30/06/2025 99.7265 0.003113 4.5 0.003119
31/12/2025 628GF5 US TREASURY NOTE 2020 3/8% 31/12/25 AH-2025 31/12/2020 5 100 0.375 FIX 31/12/2025 100.0703 0.003608 5 0.003621
30/06/2026 233HJK US TREASURY NOTE 2019 1 7/8% 30/06/26 M-2026 30/06/2019 7 100 1.875 FIX 30/06/2026 107.875 0.004249 5.5 0.004337
31/12/2026 247DLR US TREASURY NOTE 2019 1 3/4% 31/12/26 T-2026 31/12/2019 7 100 1.75 FIX 31/12/2026 107.4141 0.004944 6 0.005053
30/06/2027 610JR7 US TREASURY NOTE 2020 1/2% 30/06/27 M-2027 30/06/2020 7 100 0.5 FIX 30/06/2027 99.5313 0.005736 6.5 0.005777
31/12/2027 628GDZ US TREASURY NOTE 2020 5/8% 31/12/27 T-2027 31/12/2020 7 100 0.625 FIX 31/12/2027 99.8438 0.006479 7 0.006542
    	
            

on_off_df_as_of_2020_12_31[

    ["YTM implied by 2020-12-31 Price", "Spot Rates as of 2020-12-31"]].iplot(

    title="On- and Off-the-run US Treasury Bonds with Zero or Fixed (and different) Coupon Rates")

    	
            

on_off_df_as_of_2020_12_31 = Get_dsws_spot_rates(

    point_in_time="2020-12-31", coupon_paying_freq=0.5,

    instruments=instruments, dirty=True)

on_off_df_as_of_2020_12_31

    	
            ((1 + on_off_df_as_of_2020_12_31.iloc[-1, -1])**(1/365)) - 1
        
        
    

1.7864081344409755e-05

    	
            

on_off_df_as_of_2020_12_31["rf"] = list(

    ((on_off_df_as_of_2020_12_31["Spot Rates as of 2020-12-31"] + 1)**(1/4))-1)

on_off_df_as_of_2020_12_31

  DSCD NAME ID TERM RV C CTYP RDL Price on 2020-12-31 YTM implied by 2020-12-31 Price Dirty Price on 2020-12-31 Years from 2020-12-31 to Maturity Dirty Years from 2020-12-31 to Maturity Spot Rates as of 2020-12-31 rf
RD                              
01/07/2021 628GA1 UTD.STS OF AMERICA 2020 ZERO 01/07/21 31/12/2020 0.5 100 0 ZERO 01/07/2021 99.9555 0.00089 99.9555 0.5 0.505556 0.00089 0.000223
30/12/2021 628GAL UTD.STS OF AMERICA 2020 ZERO 30/12/21 31/12/2020 1 100 0 ZERO 30/12/2021 99.89 0.001101 99.89 1 1.011111 0.001101 0.000275
30/06/2022 610JPM US TREASURY NOTE 2020 1/8% 30/06/22 BC-2022 30/06/2020 2 100 0.125 FIX 30/06/2022 100.0156 0.001146 100.076017 1.5 1.516667 0.001146 0.000286
31/12/2022 628GD0 US TREASURY NOTE 2020 1/8% 31/12/22 BL-2022 31/12/2020 2 100 0.125 FIX 31/12/2022 100 0.00125 100.121528 2 2.027778 0.00125 0.000312
30/06/2023 208PR9 US TREASURY NOTE 2018 2 5/8% 30/06/23 AA-2023 30/06/2018 5 100 2.625 FIX 30/06/2023 106.1563 0.001567 107.388592 2.5 2.530556 0.001578 0.000394
31/12/2023 218QH0 US TREASURY NOTE 2018 2 5/8% 31/12/23 AG-2023 31/12/2018 5 100 2.625 FIX 31/12/2023 107.3203 0.001773 109.835925 3 3.041667 0.001789 0.000447
30/06/2024 233HJH US TREASURY NOTE 2019 1 3/4% 30/06/24 AA-2024 30/06/2019 5 100 1.75 FIX 30/06/2024 105.3516 0.002144 106.143961 3.5 3.547222 0.002163 0.00054
31/12/2024 247DLH US TREASURY NOTE 2019 1 3/4% 31/12/24 AH-2024 31/12/2019 5 100 1.75 FIX 31/12/2024 105.9141 0.002627 107.562017 4 4.058333 0.002658 0.000664
30/06/2025 610JRN US TREASURY NOTE 2020 1/4% 30/06/25 AA-2025 30/06/2020 5 100 0.25 FIX 30/06/2025 99.7265 0.003113 99.836222 4.5 4.561111 0.003119 0.000779
31/12/2025 628GF5 US TREASURY NOTE 2020 3/8% 31/12/25 AH-2025 31/12/2020 5 100 0.375 FIX 31/12/2025 100.0703 0.003608 100.418217 5 5.072222 0.003621 0.000904
30/06/2026 233HJK US TREASURY NOTE 2019 1 7/8% 30/06/26 M-2026 30/06/2019 7 100 1.875 FIX 30/06/2026 107.875 0.004249 108.671875 5.5 5.575 0.004337 0.001082
31/12/2026 247DLR US TREASURY NOTE 2019 1 3/4% 31/12/26 T-2026 31/12/2019 7 100 1.75 FIX 31/12/2026 107.4141 0.004944 109.013406 6 6.086111 0.005053 0.001261
30/06/2027 610JR7 US TREASURY NOTE 2020 1/2% 30/06/27 M-2027 30/06/2020 7 100 0.5 FIX 30/06/2027 99.5313 0.005736 99.736856 6.5 6.588889 0.005777 0.001441
31/12/2027 628GDZ US TREASURY NOTE 2020 5/8% 31/12/27 T-2027 31/12/2020 7 100 0.625 FIX 31/12/2027 99.8438 0.006479 100.4063 7 7.1 0.006542 0.001631
    	
            

P_SPX = ds.get_data(tickers='S&PCOMP',

                    fields="X",

                    start='2020-12-30',

                    end='2020-12-31',

                    freq='D')

 

r_f = ((on_off_df_as_of_2020_12_31.iloc[-1, -1] + 1)**(1/365))-1  # Remember that ' on_off_df_as_of_2020_12_31.iloc[-1, -1] ' is our Spot Rate

 

XSR_SPX = ((P_SPX.iloc[-1, -1] - P_SPX.iloc[0, -1])/P_SPX.iloc[0, -1]) - r_f

 

XSR_SPX

0.006434371749798833