Financial functions

Documentation for financial functions

The documentation below is generated from the system.functions system table

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

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) or Array(Float*)
• [, guess] — Optional initial guess (constant value) for the internal rate of return (default 0.1). 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

Examples

simple_example

Query

SELECT financialInternalRateOfReturn([-100, 39, 59, 55, 20])

Response

0.2809484211599611

simple_example_with_guess

Query

SELECT financialInternalRateOfReturn([-100, 39, 59, 55, 20], 0.1)

Response

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

financialInternalRateOfReturnExtended(cashflow, date [, guess, daycount])

Arguments

• cashflow — An array of cash flows corresponding to the dates in second param. Array(Int8/16/32/64) or Array(Float*)
• date — A sorted array of unique dates corresponding to the cash flows. Array(Date) or Array(Date32)
• [, guess] — Optional. Initial guess (constant value) for the XIRR calculation. 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

Returned value

Returns the XIRR value. If the calculation cannot be performed, it returns NaN. Float64

Examples

simple_example

Query

SELECT financialInternalRateOfReturnExtended([-10000, 5750, 4250, 3250], [toDate('2020-01-01'), toDate('2020-03-01'), toDate('2020-10-30'), toDate('2021-02-15')])

Response

0.6342972615260243

simple_example_with_guess

Query

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

0.6342972615260243

simple_example_daycount

Query

SELECT round(financialInternalRateOfReturnExtended([100000, -110000], [toDate('2020-01-01'), toDate('2021-01-01')], 0.1, 'ACT_365_25'), 6) AS xirr_365_25

Response

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

financialNetPresentValue(rate, cashflows[, start_from_zero])

Arguments

• rate — The discount rate to apply. Float*
• cashflows — Array of cash flows. Each value represents a payment (negative value) or income (positive value). Array(Int8/16/32/64) or Array(Float*)
• [, 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

Returned value

Returns the net present value as a Float64 value. Float64

Examples

default_calculation

Query

SELECT financialNetPresentValue(0.08, [-40000., 5000., 8000., 12000., 30000.])

Response

3065.2226681795255

excel_compatible_calculation

Query

SELECT financialNetPresentValue(0.08, [-40000., 5000., 8000., 12000., 30000.], false)

Response

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

financialNetPresentValueExtended(rate, cashflows, dates[, daycount])

Arguments

• rate — The discount rate to apply. 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) or Array(Float*)
• dates — Array of dates corresponding to each cash flow. Must have the same size as cashflows array. Array(Date) or Array(Date32)
• [, daycount] — Optional day count convention. Supported values: 'ACT_365F' (default) — Actual/365 Fixed, 'ACT_365_25' — Actual/365.25. String

Returned value

Returns the net present value as a Float64 value. Float64

Examples

Basic usage

Query

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

2506.579458169746

Using different day count convention

Query

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

2507.067268742502

Related resources

• Financial functions in ClickHouse video