> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Documentation for financial functions # Financial functions The documentation below is generated from the `system.functions` system table {/*AUTOGENERATED_START*/}

financialInternalRateOfReturn

Introduced in: v25.7.0 Calculates the Internal Rate of Return (IRR) for a series of cash flows occurring at regular intervals. IRR is the discount rate at which the Net Present Value (NPV) equals zero. IRR attempts to solve the following equation: $$ \sum_{i=0}^n \frac{cashflow_i}{(1 + irr)^i} = 0 $$ **Syntax** ```sql theme={null} financialInternalRateOfReturn(cashflows[, guess]) ``` **Arguments** * `cashflows` — Array of cash flows. Each value represents a payment (negative value) or income (positive value). [`Array(Int8/16/32/64)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array) * `[, guess]` — Optional initial guess (constant value) for the internal rate of return (default 0.1). [`Float*`](/reference/data-types/float) **Returned value** Returns the internal rate of return or `NaN` if the calculation cannot converge, input array is empty or has only one element, all cash flows are zero, or other calculation errors occur. [`Float64`](/reference/data-types/float) **Examples** **simple\_example** ```sql title=Query theme={null} SELECT financialInternalRateOfReturn([-100, 39, 59, 55, 20]) ``` ```response title=Response theme={null} 0.2809484211599611 ``` **simple\_example\_with\_guess** ```sql title=Query theme={null} SELECT financialInternalRateOfReturn([-100, 39, 59, 55, 20], 0.1) ``` ```response title=Response theme={null} 0.2809484211599611 ```

financialInternalRateOfReturnExtended

Introduced in: v25.7.0 Calculates the Extended Internal Rate of Return (XIRR) for a series of cash flows occurring at irregular intervals. XIRR is the discount rate at which the net present value (NPV) of all cash flows equals zero. XIRR attempts to solve the following equation (example for `ACT_365F`): $$ \sum_{i=0}^n \frac{cashflow_i}{(1 + rate)^{(date_i - date_0)/365}} = 0 $$ Arrays should be sorted by date in ascending order. Dates need to be unique. **Syntax** ```sql theme={null} financialInternalRateOfReturnExtended(cashflow, date [, guess, daycount]) ``` **Arguments** * `cashflow` — An array of cash flows corresponding to the dates in second param. [`Array(Int8/16/32/64)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array) * `date` — A sorted array of unique dates corresponding to the cash flows. [`Array(Date)`](/reference/data-types/array) or [`Array(Date32)`](/reference/data-types/array) * `[, guess]` — Optional. Initial guess (constant value) for the XIRR calculation. [`Float*`](/reference/data-types/float) * `[, daycount]` — Optional day count convention (default 'ACT\_365F'). Supported values: * 'ACT\_365F' - Actual/365 Fixed: Uses actual number of days between dates divided by 365 * 'ACT\_365\_25' - Actual/365.25: Uses actual number of days between dates divided by 365.25 [`String`](/reference/data-types/string) **Returned value** Returns the XIRR value. If the calculation cannot be performed, it returns NaN. [`Float64`](/reference/data-types/float) **Examples** **simple\_example** ```sql title=Query theme={null} SELECT financialInternalRateOfReturnExtended([-10000, 5750, 4250, 3250], [toDate('2020-01-01'), toDate('2020-03-01'), toDate('2020-10-30'), toDate('2021-02-15')]) ``` ```response title=Response theme={null} 0.6342972615260243 ``` **simple\_example\_with\_guess** ```sql title=Query theme={null} SELECT financialInternalRateOfReturnExtended([-10000, 5750, 4250, 3250], [toDate('2020-01-01'), toDate('2020-03-01'), toDate('2020-10-30'), toDate('2021-02-15')], 0.5) ``` ```response title=Response theme={null} 0.6342972615260243 ``` **simple\_example\_daycount** ```sql title=Query theme={null} SELECT round(financialInternalRateOfReturnExtended([100000, -110000], [toDate('2020-01-01'), toDate('2021-01-01')], 0.1, 'ACT_365_25'), 6) AS xirr_365_25 ``` ```response title=Response theme={null} 0.099785 ```

financialNetPresentValue

Introduced in: v25.7.0 Calculates the Net Present Value (NPV) of a series of cash flows assuming equal time intervals between each cash flow. Default variant (`start_from_zero` = true): $$ \sum_{i=0}^{N-1} \frac{values_i}{(1 + rate)^i} $$ Excel-compatible variant (`start_from_zero` = false): $$ \sum_{i=1}^{N} \frac{values_i}{(1 + rate)^i} $$ **Syntax** ```sql theme={null} financialNetPresentValue(rate, cashflows[, start_from_zero]) ``` **Arguments** * `rate` — The discount rate to apply. [`Float*`](/reference/data-types/float) * `cashflows` — Array of cash flows. Each value represents a payment (negative value) or income (positive value). [`Array(Int8/16/32/64)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array) * `[, start_from_zero]` — Optional boolean parameter indicating whether to start the NPV calculation from period `0` (true) or period `1` (false, Excel-compatible). Default: true. [`Bool`](/reference/data-types/boolean) **Returned value** Returns the net present value as a Float64 value. [`Float64`](/reference/data-types/float) **Examples** **default\_calculation** ```sql title=Query theme={null} SELECT financialNetPresentValue(0.08, [-40000., 5000., 8000., 12000., 30000.]) ``` ```response title=Response theme={null} 3065.2226681795255 ``` **excel\_compatible\_calculation** ```sql title=Query theme={null} SELECT financialNetPresentValue(0.08, [-40000., 5000., 8000., 12000., 30000.], false) ``` ```response title=Response theme={null} 2838.1691372032656 ```

financialNetPresentValueExtended

Introduced in: v25.7.0 Calculates the Extended Net Present Value (XNPV) for a series of cash flows occurring at irregular intervals. XNPV considers the specific timing of each cash flow when calculating present value. XNPV equation for `ACT_365F`: $$ XNPV=\sum_{i=1}^n \frac{cashflow_i}{(1 + rate)^{(date_i - date_0)/365}} $$ Arrays should be sorted by date in ascending order. Dates need to be unique. **Syntax** ```sql theme={null} financialNetPresentValueExtended(rate, cashflows, dates[, daycount]) ``` **Arguments** * `rate` — The discount rate to apply. [`Float*`](/reference/data-types/float) * `cashflows` — Array of cash flows. Each value represents a payment (negative value) or income (positive value). Must contain at least one positive and one negative value. [`Array(Int8/16/32/64)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array) * `dates` — Array of dates corresponding to each cash flow. Must have the same size as cashflows array. [`Array(Date)`](/reference/data-types/array) or [`Array(Date32)`](/reference/data-types/array) * `[, daycount]` — Optional day count convention. Supported values: `'ACT_365F'` (default) — Actual/365 Fixed, `'ACT_365_25'` — Actual/365.25. [`String`](/reference/data-types/string) **Returned value** Returns the net present value as a Float64 value. [`Float64`](/reference/data-types/float) **Examples** **Basic usage** ```sql title=Query theme={null} SELECT financialNetPresentValueExtended(0.1, [-10000., 5750., 4250., 3250.], [toDate('2020-01-01'), toDate('2020-03-01'), toDate('2020-10-30'), toDate('2021-02-15')]) ``` ```response title=Response theme={null} 2506.579458169746 ``` **Using different day count convention** ```sql title=Query theme={null} SELECT financialNetPresentValueExtended(0.1, [-10000., 5750., 4250., 3250.], [toDate('2020-01-01'), toDate('2020-03-01'), toDate('2020-10-30'), toDate('2021-02-15')], 'ACT_365_25') ``` ```response title=Response theme={null} 2507.067268742502 ``` * [Financial functions in ClickHouse video](https://www.youtube.com/watch?v=BePLPVa0w_o)