1. Home
  2. Article Catalog
  3. Payout Schedule & Cash Flow Analysis for a Bond Portfolio

Payout Schedule & Cash Flow Analysis for a Bond Portfolio

Nick Zincone
Developer Advocate Developer Advocate
Simone Da Costa
Solutions Consultant, Refinitiv Solutions Consultant, Refinitiv

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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)

4. Cash Flow Analysis

Now that we have in place the payment schedules and the corresponding conversion rates, prepare the cash flow metrics required to present our cash flow graph.

    	
            

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

Visualization and analysis of results

The following function provides the analysis of our metrics in a standard graph. The algorithm provides the ability to bucket the analysis on a monthly, quarterly, or annual basis. The algorithm will choose an appropriate representation of the cash flow.

    	
            

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.