How to Calculate IRR and XIRR Using Bold BI ETL and Visualizing the Results in Bold BI

This article explains how to calculate Internal Rate of Return (IRR) and Extended Internal Rate of Return (XIRR) from investment cash-flow data by using a Python script in Bold BI Data Hub ETL. The script reads cash-flow data from an Excel workbook, calculates overall and grouped return metrics, and publishes the output tables to the configured destination data store for use in Bold BI dashboards.

Introduction to IRR and XIRR

What are IRR and XIRR?

Internal Rate of Return (IRR) is a financial metric used to estimate the return generated by an investment based on its cash flows. In this workflow, IRR is calculated using monthly aggregated cash flows and then annualized.

Extended Internal Rate of Return (XIRR) calculates an annualized investment return using the actual dates of cash inflows and outflows. It is useful when cash flows occur at irregular intervals.

What are IRR and XIRR used for?

IRR and XIRR can be used to:

• Evaluate investment performance.
• Analyze investment contributions and distributions.
• Compare returns across funds or contributor groups.
• Calculate an overall return as well as returns for individual groups.

Why use Python in Bold BI ETL?

Bold BI does not provide native support for IRR and XIRR. IRR and XIRR are calculated with a Python script in Bold BI Data Hub ETL. The script reads the cash-flow data, calculates the metrics, and writes the results to tables in the configured destination data store. Bold BI can then use those tables as a data source for dashboard visualizations.

Calculating IRR and XIRR Using a Python Script in Bold BI ETL

The Python script used for this example is available here:

Download or view the IRR_XIRR.py script

Step 1: Configure the script

Update the settings at the beginning of the script to match the workbook path and field names:

EXCEL_FILE = "/path/accessible/to/ETL/mock_irr_xirr.xlsx"
SHEET_NAME = "XIRR_Calculations"

DATE_COLUMN = "date"
CASHFLOW_COLUMN = "cashflow"
GROUP_COLUMN = "Contributor_type"

• EXCEL_FILE: Path to the workbook as seen by the ETL runtime.
• SHEET_NAME: Worksheet containing the cash-flow data.
• DATE_COLUMN: Column containing the date of each cash flow.
• CASHFLOW_COLUMN: Column containing the signed cash-flow amount.
• GROUP_COLUMN: Column used to calculate separate results, in addition to the overall result.

Note: If your workbook uses different header names, change the corresponding settings. The data can contain additional columns; the calculation requires the configured date and cash-flow amount columns, plus the configured grouping column. 

Step 2: Open Bold BI Data Hub and create a pipeline

1. Select the Data Hub icon to open Data Hub in a new browser tab.
   
   

2. In the left panel, select Add Pipeline, enter a project name, and confirm using the check-mark icon.
   

   

3. The YAML editor opens, allowing you to configure the pipeline source and destination connectors.
   

   

Step 3: Add the PythonScript step

1. Select PythonScript from the left panel.
2. Select Add template in the right panel to insert the sample configuration into the YAML editor.

3. Make sure the script creates the required pandas DataFrame objects. To publish a DataFrame to a destination table, add the following line after the DataFrame is created:

   pipeline.run(yourdataframename, table_name="yourtablename")
   

   Replace yourdataframename with the actual DataFrame variable name and yourtablename with the desired destination table name.

Step 4: Upload and configure the Python script

1. Select Upload File in the upper-right corner of the YAML editor.
   

   

2. Select the .py file from your local system and click Upload. Ensure the file has a .py extension.
   

   

3. Copy the path populated in the filepath text box and paste it into the appropriate field in the YAML editor.

Step 5: Save and run the pipeline

1. Select Save and choose the destination database configured in the Data Store settings.

2. Check the Logs tab to review execution details.

3. After successful execution, the output DataFrames are created as tables in the destination database. A corresponding data source is also established in Bold BI Data Sources using the Data Hub project name.

The example publishes two tables:

• xirr_mock_cashflows: Contains the cleaned original mock cash-flow rows.
• xirr_results: Contains the overall result and one result for each value of the configured grouping field.

The xirr_results table contains these columns:

The output tables are retained in the configured destination data store. For example, if PostgreSQL is selected as the destination, the tables are created in that PostgreSQL data store.

Integrating the Output into Bold BI

After the ETL pipeline completes successfully, use the generated data source and tables to create a dashboard.

Step 1: Open the generated data source

1. Open Bold BI.
2. Navigate to Data Sources.
3. Locate the data source created from the Data Hub project.
4. Confirm that the xirr_results table is available.
   
   

Step 2: Create dashboard visualizations

Use the xirr_results table to display the calculated metrics:

Step 3: Validate the dashboard output

Verify that the dashboard values match the records in xirr_results. Confirm that irr_annualized represents annualized IRR based on monthly aggregated cash flows and that xirr represents annualized XIRR based on actual dates.

The PythonScript step in Bold BI ETL can therefore calculate IRR and XIRR from Excel-based investment cash flows and publish the results for dashboard visualization. The workbook path, worksheet, date column, cash-flow column, and grouping column can be configured for compatible datasets.

Reference: Bold BI — Working with Bold Data Hub