Financial functions (beta)
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:
Function | Description |
---|---|
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.
|
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 .
|
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"
}
]
}
}