Skip to main content

Financial functions (beta)

info

Financial functions are a beta feature. You should only enable a beta feature after testing in a staging environment.

Financial functions perform financial calculations, such as internal rate of return, on data recorded over a period of time.

Internal rate of return​

Internal rate of return (IRR) functions calculate the internal rate of return for a series of cash flows for an investment during a given time window. Apache Druid® computes the IRR value using the Newton-Raphson method over Net Present Value (NPV).

IRR functions are disabled by default. You enable IRR functions by loading the imply-timeseries extension.

To load the extension, go to the cluster's Setup page and expand the Advanced config section. Click Add custom extension and enter imply-timeseries in the Name field. You do not need to provide the URL or the path to the extension file because it is included with Imply's distribution of Apache Druid. See Imply bundled extensions for more information.

The following table shows the available IRR functions:

FunctionDescription
IRR(time, cashFlow, totalInvestment, window, bucketPeriod [, initEstimate, maxIterations, errorEpsilon])Calculates the internal rate of return for a series of cash flows in an investment. Returns a decimal.
  • time: Timestamp column.
  • cashFlow: Column indicating cash flows for an investment.
  • totalInvestment: Column with the running total value for the investment. Druid uses the values for totalInvestment closest to the beginning and end times of the window as the initial investment and final investment values, respectively.
  • window: ISO 8601 interval that defines the time range to use to calculate the rate of return.
  • bucketPeriod: ISO 8601 period that partitions the time dimension into uniform periods. Druid sums the cash flow values for each time period into a single value.
  • initEstimate: Optional estimate for the IRR result. This can decrease calculation time for IRR when the estimate is close to the actual IRR value. Defaults to 0.1.
  • maxIterations: Optional estimate for the maximum number of iterations in the Newton-Raphson method to converge the IRR value. Defaults to 100.
  • errorEpsilon: Optional estimate indicating an acceptable level of proximity to the actual IRR value for the result to be considered satisfactory. The function returns the computed value as soon as it identifies that the iterative process will not improve by more than errorEpsilon. If the computation doesn’t reach a bound of errorEpsilon after maxIterations, Druid returns a null value. Defaults to 1e-9.
IRR_DEBUG(time, cashFlow, totalInvestment, window, bucketPeriod [, initEstimate, maxIterations, errorEpsilon])Calculates the internal rate of return for a series of cash flows in an investment in a debug mode. The resulting object contains detailed information about the iterative step of computing IRR.
  • time: Timestamp column.
  • cashFlow: Column indicating cash flows for an investment.
  • totalInvestment: Column with the running total value for the investment. Druid uses the values for totalInvestment closest to the beginning and end times of the window as the initial investment and final investment values, respectively.
  • window: ISO 8601 interval that defines the time range to use to calculate the rate of return.
  • bucketPeriod: ISO 8601 period that partitions the time dimension into uniform periods. Druid sums the cash flow values for each time period into a single value.
  • initEstimate: Optional estimate for the IRR result. This can decrease calculation time for IRR when the estimate is close to the actual IRR value. Defaults to 0.1.
  • maxIterations: Optional estimate for the maximum number of iterations in the Newton-Raphson method to converge the IRR value. Defaults to 100.
  • errorEpsilon: Optional estimate indicating an acceptable level of proximity to the actual IRR value for the result to be considered satisfactory. The function returns the computed value as soon as it identifies that the iterative process will not improve by more than errorEpsilon. If the computation doesn’t reach a bound of errorEpsilon after maxIterations, Druid returns a null value. Defaults to 1e-9.

Examples​

IRR​

The following query calculates the internal rate of return for a series of investments over one month with investments bucketed to one day:

SELECT IRR("__time", "cash_flow", "total", '2023-06-01T00:00/2023-06-30T00:00', 'P1D') AS investment_return
FROM "irr_cs_test2"

The function returns an IRR of 0.0002719117776236321 or 0.027% for the monthly period.

IRR_DEBUG​

The following query returns detailed information about the iterative step of computing IRR:

SELECT IRR_DEBUG("__time", "cash_flow", "total", '2023-06-01T00:00/2023-06-30T00:00', 'P1D') AS debug
FROM "irr_cs_test2"
View the results
{
    "debug":{
        "cashFlows":"SimpleTimeSeries{timestamps=[1685577600000, 1685664000000, 1685750400000, 1685836800000, 1685923200000, 1686009600000, 1686096000000, 1686182400000, 1686268800000, 1686355200000, 1686441600000, 1686528000000, 1686614400000, 1686700800000, 1686787200000, 1686873600000, 1686960000000, 1687046400000, 1687132800000, 1687219200000, 1687305600000, 1687392000000, 1687478400000, 1687564800000, 1687651200000, 1687737600000, 1687824000000, 1687910400000, 1687996800000], dataPoints=[1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0], maxEntries=29, start=EdgePoint{timestamp=-1, data=-1.0}, end=EdgePoint{timestamp=1688083211080, data=1000.0}, bucketMillis=86400000, timeProperties={period=P1D, timeZone=UTC, origin=1970-01-01T00:00:00.000Z}}",
        "startValue":"1000.0",
        "endValue":"30004.35041",
        "startEstimate":"0.1",
        "window":"2023-06-01T00:00:00.000Z/2023-06-30T00:00:00.000Z",
        "iterations":[
            {
                "iteration":"1",
                "npv":"1429.679246384796",
                "npvDerivative":"12837.23688887966",
                "estimate":"-0.01136970196625918"
            },
            {
                "iteration":"2",
                "npv":"-188.72520889780208",
                "npvDerivative":"16430.900452793205",
                "estimate":"1.1629111367085845E-4"
            },
            {
                "iteration":"3",
                "npv":"-2.489495316411194",
                "npvDerivative":"16000.067473234833",
                "estimate":"2.7188391479970756E-4"
            },
            {
                "iteration":"4",
                "npv":"-4.45647459855536E-4",
                "npvDerivative":"15994.339566537581",
                "estimate":"2.719117776231772E-4"
            },
            {
                "iteration":"5",
                "npv":"-7.275957614183426E-12",
                "npvDerivative":"15994.338541066174",
                "estimate":"2.719117776236321E-4"
            }
        ]
    }
}