Payout Schedule & Cash Flow Analysis for a Bond Portfolio
When holding a portfolio of bonds, it's of particular importance to manage the expected payback (be it Interest Payments or Principal) to be received in the next months, quarters and even years. This allows one to anticipate the expected cash flows from the coupons, receipt of principal payments on bonds that are nearing maturity date, and look at total amounts in the desired currency. Thanks to the Refinitiv Data API and libraries such as Instrument Pricing Analytics (IPA) this is made simple. Follow the steps below to get more insight, faster, into your Bond Portfolio.
Getting Started
To get started we will need to import the Refinitiv Data Platform (RDP) Library as well as the Eikon Data API. The RDP Library provides the analytics to retrieve bond payment schedules as well as FX Forward spot rates based on these schedules. As part of our presentation, we'll include popular Python graphing capabilities.
import refinitiv.dataplatform as rdp
from refinitiv.dataplatform.content.ipa import bond
import refinitiv.dataplatform.eikon as ek
import pandas as pd
import numpy as np
from datetime import datetime
from dateutil.relativedelta import relativedelta
import matplotlib.pyplot as plt
import plotly.graph_objects as go
While the following example will demonstrate data access within the desktop, whether Eikon or Refinitiv Workspace, the example can easily work directly against RDP. The session definition below provides desktop access using a standard CODEBOOK APP Key. If the choice is to run outside of CodeBook or the choice to define a Platform session, you will need to replace the key with a key of your own from the APP KEY GENERATOR.
# Open a session into the desktop
session = rdp.open_desktop_session('DEFAULT_CODE_BOOK_APP_KEY')
Process Workflow
The following workflow outlines concrete steps involved in generating our cash flow analysis. The steps have been intentionally broken out into well-defined segments for the purposes of reusability as well as a basis for understanding the processing details.
Portfolio Definition
The portfolio definition provides the user multiple options to create a list of bonds required for analysis. Whether working directly with your own portfolios defined within the desktop to creating simple collections for what-if analysis, each option will generate a common definition require for processing and analysis.
Schedule Generation
Using the portfolio of bonds, the schedule generation utilizes the power of the IPA bond interfaces to create our payment schedules and what the interest will be for each.
Currency Conversion
The collection of bonds will likely be issued in different currencies. The goal of the analysis is to standardize the cash flow in a single currency. Using the power of the IPA FX Forward analytics engine, we can generate the FX Forward rates based on the bond schedules defined in the previous step.
Analysis
Finally, we can put it all together by bucketing our results into a suitable generation of charts showing the future interest and principals payments over a selected period.
1. Portfolio Definition
To demonstrate bond cash flow, we will offer multiple ways to define a list of bonds representing our portfolio. In all cases, the output will be a collection, or list, of RICs identifying the portfolio of bonds. In a later step, the list is fed into our analytics engine to help generate our cash flow schedule. The following choices are offered:
Define a portfolio of bonds on the desktop
Users that prefer to manage their portfolios using the Portfolio definition app defined within the desktop, via PAL (Portfolio and List), can directly extract the list into their applications using the EDAPI Portfolio() function. For example:
ek.get_data("Portfolio(12345)", "TR.RicCode")where 12345 is the Portfolio Code defined in the PAL application
# If a portfolio has been defined within the desktop, the following function will extract the list...
def get_portfolio(code):
df, err = ek.get_data(f'Portfolio({code})', ['TR.PortfolioShares'])
if err == None:
return df.to_records(index=False).tolist()
print(f"Unable to open portfolio: {code}. {err}")
# Extract the list of RICS based on the specific portfolio code
portfolio = get_portfolio("Cashflow")
portfolio
Generate the bonds for a specific company
In some cases, it may be useful to generate a portfolio of outstanding bonds for a specific company to perform a what-if analysis. This mechanism utilizes the RDP Search call to select the list of bonds based on a specific company identifier. For simplicity, the algorithm provides 2 mechanisms to identify the company of interest - using the company ID (Organization ID / Perm ID), or by specifying the company ticker. If choosing the company ID approach, refer to the Company Selection paragraph defined within the Debt Structure Analysis article for details.
def search_bonds(id_list, ticker=False):
# Ensure the 'id_list' is a list
if not isinstance(id_list, list):
id_list = [id_list]
# Determine the type of filter based on the issuer flag
ids = " ".join(f"'{w}'" for w in id_list)
if ticker:
id_filter = f"IssuerTicker in ({ids})"
else:
id_filter = f"ParentOAPermID in ({ids})"
# Execute the search...
response = rdp.Search.search(
# The 'view' represents a specific domain of content we wish to search across.
view = rdp.SearchViews.FixedIncomeInstruments,
# The 'filter' parameter is a powerful, criteria-based, syntax that allows us to filter for
# specific results. Ensure the bonds are active and have not matured.
filter = f"{id_filter} and \
IsActive eq true and \
AssetStatus ne 'MAT'",
# Define the upper limit of rows within our result set. This is a system imposed maximum value.
top = 10000,
# The 'select' parameter determines the fields of interest in our output.
select = "RIC"
)
rics = []
if response.is_success:
if (response.data.total > 0):
df = response.data.df.dropna()
return df['RIC'].tolist()
return []
print(f"Cannot generate bond list. Error: {response.status}")
# Search for bonds based on a organization ID (PermID) - Example: IBMs PermID is 4295904307
portfolio = search_bonds('4295904307')
print(f"Found a total of {len(portfolio)} bonds: [{portfolio[:5]}]")
# Alternatively, search for bonds based on the Issuer ticker
portfolio = search_bonds('IBM', True)
print(f"Found a total of {len(portfolio)} bonds: [{portfolio[:5]}]")
Note: In most cases, using the company ticker will generate an accurate list of bonds. However, there may be instances where Search will associate a specific ticker belonging to multiple organizations, thus generating an invalid list of bonds. This may be due to Search returning results that closely match the specified ticker, as opposed to finding the exact company ID. To ensure an accurate list, you should utilize the company ID as your company identifier.
Manually create a list of bonds
You have the option to create your own list manually, whether typing in the collection or pulling in from a text file. This option is useful when testing your different collections.
# For simple testing, manually define the portfolio by entering the Refinitiv Instrument Code
# along with the units/PAR holdings for each
portfolio = [('36966TFS6=', 20000), ('36966TBD3=', 50000), ('CH29287789=', 100000), ('RU011428878=', 500000)]
portfolio
2. Schedule Generation
In this step, we'll be utilizing the Instrument Pricing Analytics (IPA) interfaces for bonds to generate our cash flow schedules. Using the portfolio created above, we will define a universe of bonds required by the IPA bond interface and pull out the desired properties, most importantly, the frequency and specific dates each bond pays. In addition to the payment schedule, we also included a number of other fields, for display purposes.
IPA will do all the heavy lifting of determining the specific dates, taking into account country schedules and holidays. This will allow us to accurately report cash flow for a specific month. If you would like more information about IPA, please visit this site for more information.
Notional Amount
The Notional Amount represents the position taken for each bond. By default, the IPA bond interface will assume an amount of 1,000,000 units. In the above steps outlined within the Portfolio Definition section, we provided the ability for users to define their position for each bond. In the case of pulling out bonds for a specific company, we rely on the default set by IPA. In all cases, our payments will be affected by the position defined which will be used to drive the cash flow presented in a later step.
# The universe represents the bonds we'll inject into the IPA bond engine. Depending on the portfolio,
# Each universe entry may contain a specific notional amount. Otherwse, the default will be used.
universe = []
for item in portfolio:
if isinstance(item, str):
universe.append(item)
else:
universe.append(
bond.Definition(
instrument_code = item[0],
notional_amount = item[1]
)
)
Retrieve the payment schedules from IPA using our derived universe of bonds.
The following IPA function uses the universe of bonds defined above to retrieve the payment schedules for each. The following key properties are specified:
- NotionalCcy
The currency issued for the bond - CashFlowDatesArray
The future payment dates for the bond - CashFlowInterestAmountsInDealCcyArray
Based on the interest payments and position, the payment amount at each date - CashFlowCapitalAmountsInDealCcyArray
Based on the principal payments and position, the payment amount at each date
df = rdp.get_bond_analytics(
universe = universe,
fields = ["InstrumentCode",
"NotionalCcy",
"PositionInDealCcy",
"InterestPaymentFrequency",
"CashFlowDatesArray",
"CashFlowInterestAmountsInDealCcyArray",
"CashFlowCapitalAmountsInDealCcyArray"
]
)
bonds = df.dropna()
bonds
3. Currency Conversion
The set of bonds returned may be represented in multiple currencies or in a currency we do not want to present. The goal of our exercise is to standardize the cash flow based on a selected, base currency, for example, the Euro (EUR). The conversion algorithm will utilize the IPA Fx Forward analytics engine to compute the exchange rate on a future date. In the above table, the CashFlowDatesArray represents our future coupon payment dates. Using this array, coupled with the issued currency NotionalCcy, we will compute an array of projected Fx conversion rates which will drive the interest calculations.
Generate conversion arrays
For each bond in our portfolio, generate the conversion factors.
# The IPA endpoint representing the analytics service that provides our FX Forward spot rates
endpoint = rdp.Endpoint(session, "https://api.refinitiv.com/data/quantitative-analytics/v1/financial-contracts")
# get_fx_analytics
# Utilizing IPA's FX Forward engine, generate the future conversion rates.
def get_fx_analytics(bond, base_currency):
cross_code = f'{bond["NotionalCcy"]}{base_currency}'
universe = []
for date in bond["CashFlowDatesArray"]:
universe.append({
"instrumentType" : "FxCross",
"instrumentDefinition" : {
"fxCrossType" : "FxForward",
"fxCrossCode" : cross_code,
"legs" : [{"endDate" : date}]
}
})
# Assume the valuation date is today
pricingParams = {"valuationDate":f"{datetime.now().strftime('%Y-%m-%d')}"}
response = endpoint.send_request(
method = rdp.Endpoint.RequestMethod.POST,
body_parameters = {
"fields": ["InstrumentDescription", "FxOutrightCcy1Ccy2", "StartDate", "EndDate", "ErrorMessage"],
"universe": universe,
"pricingParameters": pricingParams,
"outputs" : ["Data","Headers"]
}
)
if (response.is_success):
headers_name = [h['name'] for h in response.data.raw['headers']]
return pd.DataFrame(data=response.data.raw['data'], columns=headers_name)
# For each bond, request for FX spot rates
def get_rates_array(bond, base_currency):
if bond["NotionalCcy"] != base_currency:
fx = get_fx_analytics(bond, base_currency)
if (fx is None or fx["FxOutrightCcy1Ccy2"][0] is None):
return [0] * len(bond["CashFlowDatesArray"])
return fx["FxOutrightCcy1Ccy2"].tolist()
else:
return [1] * len(bond["CashFlowDatesArray"])
# Build a new column of FX rates for the entire portfolio
def generate_conversion_array(data, base_currency):
conversionRates = []
for i, r in data.iterrows():
conversionRates.append(get_rates_array(r, base_currency))
data['CurrencyRateArray'] = conversionRates
return data
# Based on the base currency, generate our FX conversion rates for each payment date
base_currency = "EUR"
bonds=generate_conversion_array(bonds, base_currency)
# Define the algorithm to build our cash flow table for the entire portfolio
def build_cash_flow(data):
dates=[]
coupons=[]
capital = []
# Process each bond, accumulating the interest and capital for each.
for i, r in data.iterrows():
dates.extend(r['CashFlowDatesArray'])
coupons.extend(list(np.multiply(r['CashFlowInterestAmountsInDealCcyArray'], r['CurrencyRateArray'])))
capital.extend(list(np.multiply(r['CashFlowCapitalAmountsInDealCcyArray'], r['CurrencyRateArray'])))
# Collect the computed values into a dataframe
df = pd.DataFrame()
df['Dates'] = dates
df['Interest'] = coupons
df['Capital'] = capital
return df
# Generate the metrics used in our analysis
cashflow = build_cash_flow(bonds)
def graph_cash_flow(df, bucket, period):
start = datetime.now()
# We'll use the 'Period' column to define our buckets within the chart
df['Period'] = pd.to_datetime(df['Dates'], format="%Y-%m-%dT%H:%M:%SZ")
# Filter out dates that fall outside of our specified period
if bucket=="Y":
df = df[df.Period.between(start, start + relativedelta(years=+period))]
elif bucket=="Q":
df = df[df.Period.between(start, start + relativedelta(months=+3*period))]
elif bucket=="M":
df = df[df.Period.between(start, start + relativedelta(months=+period))]
else:
return f"Bucket {bucket} not supported"
# Merge the dataframes to fill date gaps
rangedf = pd.DataFrame({"Period" : pd.date_range(start=start.strftime("%Y-%m-%d"), periods=period, freq=bucket)})
df = pd.concat([df, rangedf])
# Group and accumulate the values within our defined buckets
per = df.Period.dt.to_period(bucket)
df = df.groupby(per).sum()
# Chart the results...
fig = go.Figure()
name = 'Debt Profile, ' + base_currency
fig.layout.template='plotly_dark'
trace_int = go.Bar(name='Interest', x=df.index.astype(str), y=df['Interest'])
trace_cap = go.Bar(name='Capital', x=df.index.astype(str), y=df['Capital'])
fig.add_trace(trace_cap)
fig.add_trace(trace_int)
fig.update_layout(barmode='stack')
fig.update_layout(
margin=dict(t=5, b=5, l=5, r=5),
plot_bgcolor='#1A1A1D',
paper_bgcolor='#1A1A1D',
yaxis=dict(gridcolor='black', gridwidth=1, zerolinecolor='black', zerolinewidth=1,
side='right', color='#D6D6D5', linecolor='#D6D6D5', ticks='outside'),
xaxis=dict(gridcolor='black', gridwidth=1, zerolinecolor='black', zerolinewidth=1,
color='#D6D6D5', linecolor='#D6D6D5', ticks='outside'),
colorway=['#6978F7', '#A325E9', '#96E05D', '#4A7FB9', '#E75A2D', '#FBE55A', '#8C8C8D', '#5A54F6'],
legend=dict(font=dict(color='#D6D6D5'), orientation='v', yanchor='top', xanchor='center', y=1.05, x=0.1),
title = dict(text=name, xanchor='center', yanchor='top', x=0.45, font=dict(color='#D6D6D5'))
)
return fig
# Analysis over the next 12 months
graph_cash_flow(cashflow, 'M', 12)
# Analyze over the next 8 quarters...
graph_cash_flow(cashflow, 'Q', 8)
# And finally, over the next 5 years...
graph_cash_flow(cashflow, 'Y', 5)
Conclusions
As you can see, leveraging the capabilities of the Financial Contracts API in Instrument Pricing Analytics, we have had to do very little coding to resolve a schedule of coupon and principal payments on a Portfolio/List of bonds. Keep in mind that in this simple, yet illustrative example, we looked at different ways of defining our bond universe, then we analyzed the schedule of coupon payments and principal at maturity, we applied currency conversion so as to be able, to sum up, expected cash flow in potentially different currencies, and finally, we were able to output this to a chart view - be it in Months, Quarters or Years.
Key takeaways are that the heavy lifting of the coupon calculation, payment schedules, and FX Outrights are delivered seamlessly from IPA allowing for us to focus on the preparation of the portfolios and analysis of output. This means that any Bond Portfolio holder, regardless of their programming knowledge, is now able to leverage our APIs and make them do the hard work.
