1. Home
  2. Article Catalog
  3. Computing Risk Free Rates and Excess Returns Part 1: From Sovereign Zero-Coupon-Bonds

Academic Article Series: Economics and Finance 101:

Computing Risk Free Rates and Excess Returns Part 1: From Sovereign Zero-Coupon-Bonds

Jonathan Legrand
Developer Advocate Developer Advocate

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.

 

Use of Government Bonds in calculating risk-free rates

Only certain banks have access to the primary sovereign bond markets where they may purchase Domestic Sovereign/Government Bonds. There are many such types of bonds. Among others, there are:

  • United States (US): US Treasury securities are issued by the US Department of the Treasury and backed by the US government.
    • Fixed principal: A principal is the amount due on a debt. In the case of bonds, it is often referred to as the Face Value. The Face Value of all US Treasury securities is 1000 US Dollars (USD)
      • Treasury‐bills (as known as (a.k.a.): T-bills) have a maturity of less than a year (< 1 yr). These are bonds that do not pay coupons (Zero-Coupon Bonds).
      • Treasury‐notes (a.k.a.: T‐notes) have a maturity between 1 and 10 years (1‐10 yrs).
      • Treasury-bonds (a.k.a.: T‐bonds) have a maturity between 10 and 30 years (10‐30 yrs). It is confusing calling a sub-set of bonds 'T-bonds', but that is their naming conventions. To avoid confusion, I will always refer to them explicitly as Treasury-bonds (or T‐bonds), not just bonds.
    • Inflation‐indexed: TIPS
    • Treasury STRIPS (created by private sector, not the US government)
  • United Kingdom: Since 1998, gilts have been issued by the UK Debt Management Office (DMO), an executive agency of the HMT (Her Majesty's Treasury).
    • Conventional gilts: Short (< 5 yrs), medium (5‐15 yrs), long (> 15 yrs)
    • Inflation‐indexed gilts
  • Japan
    • Medium term (2, 3, 4 yrs), long term (10 yrs), super long term (15, 20 yrs)
  • Eurozone government bonds

There are several ways to compute risk-free rates based on bonds. In this article, we will focus on T-bills, as US Sovereign Bonds are often deemed the safest (which is a reason why the USD is named the world's reserve currency) and T-bills are an example of Zero-Coupon Bonds (as per the method outlined by the Business Research Plus). From there, a risk-free rate of return can be computed as implied by its bond's Yield To Maturity and based the change in the same bond's market price from one day to the next.

    	
            # 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:

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

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 get the data we were looking for. Note that 'TRUS1MT' is in percent, so we divide by 100 to get a ratio.

TRUS1MT_2020_07_13 = ds.get_data(tickers='TRUS1MT', start='2020-07-13',

                                 end='2020-07-17', fields="X", freq='D')/100

TRUS1MT_2020_07_13.loc["2020-07-13"]

Instrument Field
TRUS1MT X 0.00112
Name: 2020-07-13, dtype: float64

    	
            1000 / (1 + (1/12) * 0.00112)
        
        
    

999.9066753769649

    	
            1000 / (1+ (0.00112/12))
        
        
    

999.9066753769649

    	
            1000 / ((1+ (0.00112/24))**2)
        
        
    

999.9066731995933

    	
            

# Now get the data we were looking for. Note that 'TRUS3MT' is in percent, so we divide by 100 to get a ratio.

TRUS3MT_2020_07_13 = ds.get_data(tickers='TRUS3MT', fields="X",

                                 start='2020-07-13', end='2020-07-17', freq='D')/100

TRUS3MT_2020_07_13.loc["2020-07-13"]

Instrument Field
TRUS3MT X 0.00137
Name: 2020-07-13, dtype: float64

    	
            1000 / ((1 + (1/12)*0.00137)**3)
        
        
    

999.6575781892886

    	
            (1000 - 900)/900
        
        
    

0.1111111111111111

    	
            ((1000 - 900)/900)/30
        
        
    

0.0037037037037037034

    	
            ((1 + ((1000-900)/900)))**(1/30) - 1
        
        
    

0.0035181915469957303

    	
            (1.00091**(1/252))-1
        
        
    

3.6094755657689603e-06

    	
            

# The ' from ... import ' structure here allows us to only import the

# module ' python_version ' from the library ' platform ':

from platform import python_version

print("This code runs on Python version " + python_version())

This code runs on Python version 3.8.2

    	
            

# 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:

 

# The username is placed in a text file so that it may be used in this code without showing it itself:

DSWS_username = open("Datastream_username.txt", "r")

# Same for the password:

DSWS_password = open("Datastream_password.txt", "r")

 

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()

pandas will be needed to manipulate data sets

    	
            

import pandas

pandas.set_option('display.max_columns', None) # This line will ensure that all columns of our dataframes are always shown

The below are needed to plot graphs of all kinds

    	
            

import plotly

import plotly.express

from plotly.graph_objs import *

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", "pandas"], [plotly, cufflinks, pandas]):

    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
The pandas library imported in this code is version: 1.2.4

    	
            

def ZCB_YTM_Implied_r_f(ytm, d):

    """ ZCB_YTM_Implied_r_f Version 2.0: This Python function returns the Zero-Coupn Bond (ZCB) Yield To Maturity (YTM) Implied Risk Free Interest Rate, thus its name 'ZCB_YTM_Implied_r_f'

 

    ytm (Datastream pandas dataframe): The Yield To Maturity of the Zero-Coupon Bond in question.

    It requiers the DSWS library from Refinitiv.

    E.g.: ytm = ds.get_data(tickers = 'TRUS1MT', fields = "X", start = '1950-01-01', freq = 'D')/100

 

    d (int): The number of time periods (e.g.: days) until the bond matures

    N.B.: The 1-, 2-, and 3-month rates are equivalent to 30-, 60-, and 90-day dates respectively, as reported on the Board's Commercial Paper Web page.

    E.g.: d = 30

 

    #########

    Update: From Version 1.0 to 2.0:

    Variables were renames with lower case characters, staying consistant with pycodestyle.

    """

 

    # Rename the columns of 'ytm' correctly:

    instrument_name = ytm.columns[0][0]

    arrays = [[instrument_name], ['YTM']]

    tuples = list(zip(*arrays))

    ytm.columns = pandas.MultiIndex.from_tuples(tuples,

                                                names=['Instrument', 'Field'])

 

    # Calculate the r_f

    r_f = ((ytm + 1)**(1/d))-1

 

    # Rename the columns of r_f correctly:

    instrument_name = ytm.columns[0][0]

    arrays = [[instrument_name], ['YTM_Implied_r_f']]

    tuples = list(zip(*arrays))

    r_f.columns = pandas.MultiIndex.from_tuples(tuples,

                                                names=['Instrument', 'Field'])

 

    # return a list including r_f 0th and ytm 1st.

    return(r_f, ytm)

Let's test the Python function with One-Month U.S. T.Bill:

    	
            

test1m = ZCB_YTM_Implied_r_f(

    ytm=ds.get_data(tickers='TRUS1MT', fields="X",

                    start='1950-01-01', end='2020-07-22', freq='D')/100,

    d=30)

    	
            test1m[0].dropna()
        
        
    
Instrument TRUS1MT
Field YTM_Implied_r_f
Dates  
31/07/2001 0.001183
01/08/2001 0.00118
02/08/2001 0.00118
03/08/2001 0.001173
06/08/2001 0.001173
... ...
16/07/2020 0.000038
17/07/2020 0.000037
20/07/2020 0.000037
21/07/2020 0.000032
22/07/2020 0.00003
    	
            test1m[0].dropna().loc["2019-12-31"]*100
        
        
    

Instrument Field
TRUS1MT YTM_Implied_r_f 0.048918
Name: 2019-12-31, dtype: float64

    	
            test1m[1].dropna()
        
        
    
Instrument TRUS1MT
Field YTM
Dates  
31/07/2001 0.0361
01/08/2001 0.036
02/08/2001 0.036
03/08/2001 0.0358
06/08/2001 0.0358
... ...
16/07/2020 0.00114
17/07/2020 0.00112
20/07/2020 0.00112
21/07/2020 0.00096
22/07/2020 0.00091
    	
            

def ZCB_Value_Implied_r_f(ytm, maturity, face_value=1000.0):

    """ ZCB_Value_Implied_r_f Python Function Version 2.0: This Python function returns the Zero-Coupon Bond's Value Implied Risk Free Interest Rate, thus its name 'ZCB_Value_Implied_r_f'

 

    face_value (float): The payment promised by the issuer of the Zero-coupon Bond at the bond's maturity-time.

    Defaulted to: face_value = 1000.0

 

    ytm (Datastream pandas dataframe): The Yield To Maturity of the Zero-Coupon Bond in question.

    It requires the DSWS library from Refinitiv.

    E.g.: ytm = ds.get_data(tickers = 'TRUS1MT', fields = "X", start = '1950-01-01', freq = 'D')/100

 

    maturity (float): The number of years until the bond matures.

    This can be lower than 1, e.g.: One-Month Zero-Coupon T.Bill would have a 'maturity' value of 1/12.

    E.g.: maturity = 1/12

 

    #########

    Update: From Version 1.0 to 2.0:

    Variables were renames with lower case characters, staying consistant with pycodestyle.

    """

 

    # Calculate the ytm

    npv = face_value/(1 + maturity * ytm)

 

    # Rename the columns of 'V' correctly:

    instrument_name = npv.columns[0][0]

    arrays = [[instrument_name], ['Net Present Value']]

    tuples = list(zip(*arrays))

    npv.columns = pandas.MultiIndex.from_tuples(tuples, names=['Instrument',

                                                               'Field'])

 

    # Calculate the r_f

    r_f = (npv - npv.shift(periods=1))/npv.shift(periods=1)

 

    # Rename the columns of r_f correctly:

    instrument_name = npv.columns[0][0]

    arrays = [[instrument_name], ['Value_Implied_r_f']]

    tuples = list(zip(*arrays))

    r_f.columns = pandas.MultiIndex.from_tuples(tuples, names=['Instrument',

                                                               'Field'])

 

    # return a list including r_f 0th and YTM 1st.

    return(r_f, npv)

Let's test the Python function with One-Month U.S. T.Bill:

    	
            

test2 = ZCB_Value_Implied_r_f(

    face_value=1000.0,

    ytm=ds.get_data(tickers='TRUS1MT',

                    fields="X",

                    start='1950-01-01',

                    freq='D')/100,

    maturity=1/12)

    	
            test2[0].dropna()
        
        
    
Instrument TRUS1MT
Field Value_Implied_r_f
Dates  
01/08/2001 0.000008
02/08/2001 0
03/08/2001 0.000017
06/08/2001 0
07/08/2001 0.000008
... ...
09/06/2021 0.000002
10/06/2021 0
11/06/2021 -2E-06
14/06/2021 -2E-06
15/06/2021 -4E-06
    	
            test2[1].dropna()
        
        
    
Instrument TRUS1MT
Field Net Present Value
Dates  
31/07/2001 997.0007
01/08/2001 997.009
02/08/2001 997.009
03/08/2001 997.0255
06/08/2001 997.0255
... ...
09/06/2021 999.9958
10/06/2021 999.9958
11/06/2021 999.9933
14/06/2021 999.9917
15/06/2021 999.9875
    	
            

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

                    fields="X",

                    start='1950-01-01',

                    freq='D')

 

r_f = ZCB_YTM_Implied_r_f(

    ytm=ds.get_data(tickers='TRUS1MT',

                    fields="X",

                    start='1950-01-01',

                    freq='D')/100,

    d=30*3)[0]

 

# r_f and P_SPX must have the same column names to go through some algebra between one-another.

# We thus rename the columns of r_f correctly:

arrays = [["S&PCOMP"], ['X']]

tuples = list(zip(*arrays))

r_f.columns = pandas.MultiIndex.from_tuples(

    tuples,

    names=['Instrument', 'Field'])

 

XSR_SPX = ((P_SPX - P_SPX.shift(periods=1))/P_SPX.shift(periods=1)) - r_f

 

# We now rename the columns of R_SPX correctly:

instrument_name = P_SPX.columns[0][0]

arrays = [[instrument_name], ['Excess Returns']]

tuples = list(zip(*arrays))

XSR_SPX.columns = pandas.MultiIndex.from_tuples(

    tuples,

    names=['Instrument', 'Field'])

    	
            r_f.dropna()
        
        
    
Instrument S&PCOMP
Field X
Dates  
31/07/2001 3.94E-04
01/08/2001 3.93E-04
02/08/2001 3.93E-04
03/08/2001 3.91E-04
06/08/2001 3.91E-04
... ...
09/06/2021 5.56E-07
10/06/2021 5.56E-07
11/06/2021 8.89E-07
14/06/2021 1.11E-06
15/06/2021 1.67E-06
    	
            XSR_SPX.dropna()
        
        
    
Instrument S&PCOMP
Field Excess Returns
Dates  
31/07/2001 5.18E-03
01/08/2001 3.49E-03
02/08/2001 3.57E-03
03/08/2001 -5.63E-03
06/08/2001 -1.18E-02
... ...
09/06/2021 -1.82E-03
10/06/2021 4.65E-03
11/06/2021 1.95E-03
14/06/2021 1.81E-03
15/06/2021 -2.01E-03

We can create a function that goes through this: Excess Returns being abridged to 'XSReturns'

    	
            

def XSReturns(p, r_f):

    """ XSReturns Python Class Version 2.0: This function returns the Excess Return of any one instrument with the price 'p' collected from Datastream and any 

    risk-free return computed with the function 'YTM_Implied_r_f' that collects data from Datastream.

 

    p (pandas data-frame): The price of the Instrument in mind with 2 column name rows: one for the Instrument name and the other for the field name.

    E.g.: p = ds.get_data(tickers = 'S&PCOMP', fields = "X", start = '1950-01-01', freq = 'D')

 

    r_f (pandas data-frame): The risk-free rate of return with 2 column name rows: one for the Instrument

                            (the Zero Coupon Bond chosen to compute r_f from) name and the other for the field name.

    E.g.: r_f = ZCB_YTM_Implied_r_f(YTM = ds.get_data(tickers = 'TRUS3MT', fields = "X", start = '1950-01-01', freq = 'D')/100,

                                    maturity = 3/12,

                                    D = 30 * 3)[0]

    #########

    Update: From Version 1.0 to 2.0:

    Variables were renames with lower case characters, staying consistant with pycodestyle.

    """

 

    # r_f and P must have the same column names to go through some algebra between one-another.

    # We thus rename the columns of r_f correctly:

    instrument_name = p.columns[0][0]

    arrays = [[instrument_name], ['X']]

    tuples = list(zip(*arrays))

    r_f.columns = pandas.MultiIndex.from_tuples(tuples, names=['Instrument',

                                                               'Field'])

 

    r = ((p - p.shift(periods=1))/p.shift(periods=1)) - r_f

 

    # We now rename the columns of R_SPX correctly:

    instrument_name = p.columns[0][0]

    arrays = [[instrument_name], ['Excess Returns']]

    tuples = list(zip(*arrays))

    r.columns = pandas.MultiIndex.from_tuples(tuples, names=['Instrument',

                                                             'Field'])

 

    return(r)

Let's test the Python function with One-Month U.S. T.Bill:

    	
            

TEST = XSReturns(p=ds.get_data(tickers='S&PCOMP', fields="X", start='1950-01-01',

                               freq='D'),

                 r_f=ZCB_YTM_Implied_r_f(ytm=ds.get_data(tickers='TRUS3MT',

                                                         fields="X",

                                                         start='1950-01-01',

                                                         freq='D')/100,

                                         d=30*3)[0])

    	
            TEST.dropna()
        
        
    
Instrument S&PCOMP
Field Excess Returns
Dates  
01/01/1964 -3.83E-04
02/01/1964 5.08E-03
03/01/1964 5.42E-04
06/01/1964 1.87E-03
07/01/1964 -1.21E-04
... ...
09/06/2021 -1.83E-03
10/06/2021 4.65E-03
11/06/2021 1.95E-03
14/06/2021 1.81E-03
15/06/2021 -2.01E-03
    	
            

TEST.dropna().iloc[:, 0].iplot(title="S&P 500's Excess Returns",

                               yaxis_title="$", xaxis_title="Year",

                               colors="#001EFF", theme="solar")