1. Home
2. Article Catalog
3. Computing Risk Free Rates and Excess Returns from Zero-Coupon-Bonds

Academic Article Series: Economics and Finance 101:

Computing Risk Free Rates and Excess Returns from Zero-Coupon-Bonds

Jonathan Legrand

C:\Users\U6082174.TEN\OneDrive - Refinitiv\Projects\Risk-free rates and Excess returns\Docs\Risk-free rates and Excess returns_008_Published

This article explains what Net Present Values, Face Values, Maturities, Coupons, and risk-free rates are, how to compute them, and how they are used to calculate excess returns using only Zero-Coupon Bonds; other types of bonds are discussed for completeness, but they will only be investigated as such in further articles to come. 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.

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: Since1998, 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.

$$\\$$

1. YTM implied daily risk-free rate

US Treasury Securities: Generalised

A bond is a debt; a debt with the promise to pay a Face Value ($FV$) in $m$ years (m for maturity) in the future as well as Coupons (summing to $C$ every year) at a fixed annual frequency ($f$) (usually every 6 months, such that $f = 0.5$) for an amount today. That latter amount paid for the bond may be fair; a fair value for a bond is calculated as its Net Present Value ($NPV$) such that, at time $t$:

$$NPV_t = \begin{Bmatrix} \frac{FV_t}{\left(1 + YTM_t\right)^m} + \sum^{^\frac{m}{f}}_{\tau=1} {\frac{f \text{ } C_{\tau}}{ (1 + f \text{ } YTM_t)^{\tau} }} & \text{if } m \geq 1 \\ \\ \frac{FV_t}{\left(1 + \text{ } m \text{ } YTM_t\right)} & \text{if } m < 1 \end{Bmatrix}$$

where $YTM$ is the annualised Yield To Maturity of the bond in question. Thus: sub-year interpolation of YTMs are linear/arithmetic; yearly extrapolation of YTMs are geometric. It is easy to see that NPVs and YTMs are therefore (inversely) related; if one changes, the other must change too. We may - therefore - equivalently speak about a change in NPV and a change in YTM since the FV (for each sovereign bond issuer) does not change. The YTM acts as the discount factor here; as a matter of fact, we can see that the YTM is the annual growth rate of our NPV that leads it to the FV in the following:

$$FV_t = \begin{Bmatrix} \left[ NPV_t - \sum^{^\frac{m}{f}}_{\tau=1} {\frac{f \text{ } C_{\tau}}{ \text{ } (1 + f \text{ } YTM_t)^{\tau} \text{ }}} \right] \text{ } {\left( 1 + YTM_t\right)^m} & \text{if } m \geq 1 \\ \\ NPV_t \text{ } {\left(1 + \text{ } m \text{ } YTM_t\right)} & \text{if } m < 1 \end{Bmatrix}$$

NPVs of different bonds are not comparable. That is because they account for bonds maturing at different times. Instead, YTMs of different bonds are comparable because they are annualised, therefore they account for different maturities. It is thus preferable to only speak of changes in sovereign bond NPVs in terms of the change in their YTMs; then we can compare them to each other, e.g.: in a Yield Curve (that can be seen here with Refinitiv credentials):

Nota Bene (N.B.) : It is important to note that the reason we can easily formulate NPV (and FV) in only two cases where $m \geq 1$ and $m < 1$ is because all maturities greater than 1 are a multiple of 1 (i.e.: they are whole numbers) (i.e.: no maturities past 1 year stop mid year, e.g.: 10 years and 6 months).

T-bill Example 1:

Therefore: a T-bill that matures in one month (One-month T-Bill, OMTB) has a Net Present Value:

$$\begin{array}{ll} {NPV}_{\text{OMTB}, t} &= \begin{Bmatrix} \frac{{FV}_{\text{OMTB}, t}}{({1+{YTM}_{\text{OMTB}, t}})^{m_{\text{OMTB}}}} + \sum^{^\frac{m_{\text{OMTB}}}{f_{\text{OMTB}}}}_{\tau=1} {\frac{f_{\text{OMTB}} \text{ } C_{{\text{OMTB}}, \tau}}{ (1 + f_{\text{OMTB}} \text{ } YTM_{{\text{OMTB}},t})^{\tau} }} & \text{if } m_{\text{OMTB}} \geq 1 \\ \\ \frac{{FV}_{\text{OMTB}, t}}{{1+ \text{ } m_{\text{OMTB}} \text{ } {YTM}_{\text{OMTB}, t}}} & \text{if } m_{\text{OMTB}} < 1 \end{Bmatrix} \\ \\ &= \frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} {YTM}_{\text{OMTB}, t}}} \end{array}$$

$\text{since } m_{\text{OMTB}} = \frac{1}{12} < 1$ and ${FV}_{\text{OMTB}, t} =$ 1000 U.S.D.; all US bonds have a $FV$ of 1000 U.S.D.. Lets use the $YTM_{OMTB}$ for the 13$^\text{rd}$ of July 2020 (2020-07-13) quoted on Datastream under TRUS1MT: 0.112 U.S.D.. It is quoted in U.S.D. because it was normalised for every U.S.D..

    	
# 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:
(ds.get_data(tickers = 'TRUS1MT', fields = "X", start = '2020-07-13', freq = 'D')/100).loc["2020-07-13"]





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

This gives a YTM of 0.00112 (i.e.: 0.112%). Then:

$$\begin{array}{ll} {NPV}_{\text{OMTB}, t} &= \frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} {YTM}_{\text{OMTB}, t}}} \\ & = \frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} 0.00112}} \\ & \approx 999.906675 \text{ U.S.D.} \end{array}$$
since
    	
1000 / (1 + (1/12) * 0.00112)




999.9066753769649

T-bill Example 2: Risk-free rate of a One-Month T-Bill

If an investor buys a One-month T-Bill for 900 U.S.D. at the start of a 30 day month, it will mature with a Face Value of 1000 U.S.D., and the investor would have made $1000 - 900 = 100$ U.S.D. in profit. Over that 30 days, that's a straight-line / arithmetic return rate of $\frac{1000 - 900}{900} = 0.\dot{1}$ (note that the dot on top of $1$ in $0.\dot{1}$ is the standard notation of a recurring decimal) , i.e.: approximately 11.11%, since:

    	
(1000 - 900)/900




0.1111111111111111

That - itself - is a straight-line / arithmetic daily return rate of $\frac{0.\dot{1}}{30} = 0.0\dot{0}3\dot{7} \approx 0.37 \%$ since:

    	
((1000 - 900)/900)/30




0.0037037037037037034

(S)He theoretically gets that return every day (theoretically since it doesn't realise until the bond matures, i.e.: until the end of the Bond).

But investors are in the habit of re-investing their returns to benefit from compounding. This way we are not looking at straight-line / arithmetic interests, but geometric interest. The geometric daily interest of our investor is

$$\sqrt[30]{1 + \frac{1000 - 900}{900}} - 1 = \left( 1 + \frac{1000 - 900}{900} \right)^{\frac{1}{30}} - 1 \approx 0.0035181915469957303 \approx 0.35 \%$$

since:

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




0.0035181915469957303

(note that the ${\frac{1}{n}}^{th}$ exponent of a value is its $n^{th}$ root; this is a concept that is imperative to comprehend to understand the coding we go through below) meaning that (s)he gets approximately $900 * 0.0035 = 3.15$ USD the first day, then $(900 + 3.15) * 0.0035 \approx 3.16$ USD the next day, and so on.

$$\\$$

Following the same logic, it is easy to calculate the risk-free rate ($r_f$) on any frequency. The compounding $r_f$ of our 1-Month T-Bill for any number of periods in a year (say, daily, i.e.: $d$) with a yield ${YTM}_{\text{OMTB}, t}$ is such that:

$$\mathbf{r_f = \sqrt[d]{1 + {YTM}_{\text{OMTB}, t}} - 1}$$

It is this simple because YTMs are annualised. E.g.: for a year with 252 trading days where ${YTM}_{\text{OMTB}, t} = 0.00091$ : $$r_f = \sqrt[252]{1 + 0.00091} - 1 \approx 0.0000036094755657689603 \approx 0.0004 \%$$ since:

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




3.6094755657689603e-06

This is the YTM implied daily risk-free rate ($r_f$) of our bond. A similar 'weekly' - 7 day - or 'monthly' - 30 day - rate can be made by letting $d$ be the number of weeks or months for the year in question.

Why would one use 30 days (as per our example)? Because the 1-, 2-, and 3-month rates are equivalent to the 30-, 60-, and 90-day dates respectively, reported on the Board's Commercial Paper Web page. This is as per reference (see more here and here) with that said, one ought to use the exact number of days to maturity.

Note - however - that We only looked at Zero-Coupon Bonds. If $m > 1$, then Coupons usually have to be taken into account.

We may now code a Python function to go through this method:

Getting to the Coding

Development Tools & Resources

The example code demonstrating the use case is based on the following development tools and resources:

• Refinitiv's DataStream Web Services (DSWS): Access to DataStream data. A DataStream or Refinitiv Workspace IDentification (ID) will be needed to run the code below.
• Python Environment: Tested with Python 3.7
• Packages: DatastreamDSWS, Pandas are also required.

Import Libraries

    	
# 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.7.7

$$\\$$

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 $\textit{pip install}$.

    	
# 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
print("The pandas library imported in this code is version: " + pandas.__version__)





The pandas library imported in this code is version: 1.0.5

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"], [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.7.1
The cufflinks library imported in this code is version: 0.17.3

Create a function to compute the risk-free rate of return for any Zero-Coupon Bond's Yield To Maturity gathered from Datastream:

remember, our formula is: $$\mathbf{{r_f}_t = \sqrt[d]{1 + {YTM}_{\text{OMTB}, t}} - 1}$$ where $t \in \mathbb{Z}$ and $1 \le t \ge T$. We define the (time) vector

$$\mathbf{r\_f} = \left[ \begin{matrix} r_{{f},1} \\ r_{{f},2} \\ \vdots\\ r_{{f},T} \end{matrix} \right]$$

This Python function will return the Zero-Coupon Bond's YTM Implied Risk Free Interest Rate, thus its name 'ZCB_YTM_Implied_r_f':

    	
def ZCB_YTM_Implied_r_f(YTM, Maturity, D):
"""
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

Maturity (float): The number of years until the bond matures.
This can be lower than 1, e.g.: One-Month Zero-Coupon Treasury Bill would have a 'Maturity' value of 1/12.
E.g.: Maturity = 1/12

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
"""

# 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', freq = 'D')/100, Maturity = 1/12, D = 30)

test1m[0].dropna()




Instrument TRUS1MT
Field YTM_Implied_r_f
Dates
2001-07-31 0.001183
2001-08-01 0.001180
2001-08-02 0.001180
2001-08-03 0.001173
2001-08-06 0.001173
... ...
2020-07-16 0.000038
2020-07-17 0.000037
2020-07-20 0.000037
2020-07-21 0.000032
2020-07-22 0.000030

4952 rows × 1 columns

    	
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

2. Risk-free rate based the change in the same bond's market value from one time period (e.g.: day) to the next

This method of computing risk-free returns is more academic and less practical. With that said, the notion of a risk-free return itself is theoretical, going further, the notion of a risk-free asset such as a risk-free government bond is also theoretical; it is always possible for the guarantor of the payment of the bond's Face Value at maturity to default and not make do on its promises.

Now we may compute a theoretical risk-free daily return value based on the difference in the price of the risk-free asset from one period (say day) to the next (for every unit currency invested); note that this may result in negative $r_f$ values:

1st: As aforementioned, the 1-, 2-, and 3-month rates are equivalent to the 30-, 60-, and 90-day dates respectively, reported on the Board's Commercial Paper Web page. This is as per reference (see more and here). Figures are annualized using a 360-day year or bank interest. We are using Refinitiv's Datastream YTM in percent data.

2nd: From there, we workout below the unit return from holding a one-month Treasury Bill over the period from t-1 to t by calculating its difference in daily value where:

$$\begin{array}{ll} {NPV}_{\text{OMTB}, t} &= \begin{Bmatrix} \frac{{FV}_{\text{OMTB}, t}}{({1+{YTM}_{\text{OMTB}, t}})^{m_{\text{OMTB}}}} + \sum^{^\frac{m_{\text{OMTB}}}{f_{\text{OMTB}}}}_{\tau=1} {\frac{f_{\text{OMTB}} \text{ } C_{{\text{OMTB}}, \tau}}{ (1 + f_{\text{OMTB}} \text{ } YTM_{{\text{OMTB}},t})^{\tau} }} & \text{if } m_{\text{OMTB}} \geq 1 \\ \\ \frac{{FV}_{\text{OMTB}, t}}{{1+ \text{ } m_{\text{OMTB}} \text{ } {YTM}_{\text{OMTB}, t}}} & \text{if } m_{\text{OMTB}} < 1 \end{Bmatrix} \\ \\ &= \frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} {YTM}_{\text{OMTB}, t}}} \end{array}$$

$\text{since } m_{\text{OMTB}} = \frac{1}{12} < 1$ and ${FV}_{\text{OMTB}, t} =$ 1000 U.S.D.; all US bonds have a $FV$ of 1000 U.S.D.. For YTM data from Datastream we can go for 1-Month yields (TRUS1MT) as above, or 3-Month yields (TRUS3MT).

We may thus define, for a 1-Month T-Bill: $$\begin{array}{ll} r_{{f},t} &= \frac{{NPV}_{\text{OMTB}, t} - {NPV}_{\text{OMTB}, t-1}}{{NPV}_{\text{OMTB}, t-1}} \\ &= \frac{\frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} {YTM}_{\text{OMTB}, t}}} - \frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} {YTM}_{\text{OMTB}, t-1}}}}{\frac{1000 \text{ U.S.D.}}{{1+\frac{1}{12} {YTM}_{\text{OMTB}, t-1}}}} \end{array}$$ where $t \in \mathbb{Z}$ and $1 \le t \ge T$. We define the (time) vector

$$\mathbf{r\_f} = \left[ \begin{matrix} r_{{f},1} \\ r_{{f},2} \\ \vdots\\ r_{{f},T} \end{matrix} \right]$$

Create a function to compute the risk-free rate of return for any Zero-Coupon Bond's Yield To Maturity gathered from Datastream:

This Python function will return the Zero-Coupon Bond's Value Implied Risk Free Interest Rate, thus its name 'ZCB_Value_Implied_r_f':

    	
def ZCB_Value_Implied_r_f(YTM, Maturity, D, Face_Value = 1000.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

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
"""

# 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,
D = 30)

test2[0].dropna()




Instrument TRUS1MT
Field Value_Implied_r_f
Dates
2001-08-01 0.000008
2001-08-02 0.000000
2001-08-03 0.000017
2001-08-06 0.000000
2001-08-07 0.000008
... ...
2020-07-16 0.000008
2020-07-17 0.000002
2020-07-20 0.000000
2020-07-21 0.000013
2020-07-22 0.000004

4951 rows × 1 columns

    	
test2[1].dropna()



Instrument TRUS1MT
Field Net Present Value
Dates
2001-07-31 997.000690
2001-08-01 997.008973
2001-08-02 997.008973
2001-08-03 997.025540
2001-08-06 997.025540
... ...
2020-07-16 999.905009
2020-07-17 999.906675
2020-07-20 999.906675
2020-07-21 999.920006
2020-07-22 999.924172

4952 rows × 1 columns

Excess Returns $R_t$

Now that we have risk-free rates, it is easy to compute the excess return of any instrument.

The excess returns ($XSR_{t}$) at time t are computed from its price ($P_{t}$) and the chosen risk free rate (${r_f}_t$) such that:

$$$$XSR_{t} = \frac{P_{t} - P_{t-1}}{P_{t-1}} - {r_f}_t$$$$

Note here that: Due to the differencing necessary to calculate 'XSR', the first value is empty.

We define the (time) vector

$$\mathbf{XSR} = \left[ \begin{matrix} XSR_{1} \\ XSR_{2} \\ \vdots\\ XSR_{T} \end{matrix} \right]$$

where $t \in \mathbb{Z}$ and $1 \le t \ge T$. $\mathbf{R}$ is thus as defined in the cell below.

Example: The S&P500 index: With the 1-Month U.S. T.Bill:

    	
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,
Maturity = 3/12,
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
1954-01-04 0.000147
1954-01-05 0.000141
1954-01-06 0.000141
1954-01-07 0.000145
1954-01-08 0.000145
... ...
2020-07-16 0.000013
2020-07-17 0.000013
2020-07-20 0.000013
2020-07-21 0.000014
2020-07-22 0.000014

17363 rows × 1 columns

    	
XSR_SPX.dropna()



Instrument S&PCOMP
Field Excess Returns
Dates
1964-01-01 -0.000383
1964-01-02 0.005080
1964-01-03 0.000542
1964-01-06 0.001866
1964-01-07 -0.000121
... ...
2020-07-16 -0.003419
2020-07-17 0.002836
2020-07-20 0.008394
2020-07-21 0.001665
2020-07-22 0.005734

14756 rows × 1 columns

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

    	
def XSReturns(P, r_f):
"""
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]
"""

# 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,
Maturity = 3/12,
D = 30 * 3)[0])

TEST.dropna()




Instrument S&PCOMP
Field Excess Returns
Dates
1964-01-01 -0.000383
1964-01-02 0.005080
1964-01-03 0.000542
1964-01-06 0.001866
1964-01-07 -0.000121
... ...
2020-07-16 -0.003419
2020-07-17 0.002836
2020-07-20 0.008394
2020-07-21 0.001665
2020-07-22 0.005734

14756 rows × 1 columns

    	
TEST.dropna().iloc[:,0].iplot(title = "S&P 500's Excess Returns",
yaxis_title = "\$",
xaxis_title = "Year",
colors = "#001EFF",
theme = "solar")





Conclusion:

One may construct a Python Code to simply find the excess returns for any instrument using Datasream. Next, I will write an article making a function that allows users to include Coupon Paying Bonds.

References

You can find more detail regarding the DSWS API and related technologies for this article from the following resources:

For any question related to this example or Eikon Data API, please use the Developers Community Q&A Forum.