PostgreSQL Financial Extension

PostgreSQL extension for financial calculations.

Current version: 1.2.0 Tested with: PostgreSQL 10-17

Functions

XIRR - Irregular Internal Rate of Return

xirr(amount float8, date timestamptz [, guess float8]) → float8

Aggregate function similar to Excel/LibreOffice XIRR. Uses Newton's method to find the rate where NPV equals zero.

TWR - Time-Weighted Return

twr(amount float8, portfolio_value float8, date timestamptz) → float8

Time-weighted return calculation. Requires cashflow amount, portfolio value, and timestamp for each period.

NPV - Net Present Value

npv(rate float8, amount float8, date timestamptz) → float8

Discounts future cashflows to present value: NPV = Σ(amount / (1 + rate)^years)

Installation

make
sudo make install

CREATE EXTENSION financial;

Usage

XIRR Examples

Basic usage:

SELECT xirr(amount, transaction_date ORDER BY transaction_date)
FROM transactions;

Multiple portfolios:

SELECT portfolio_id, xirr(amount, date ORDER BY date)
FROM transactions
GROUP BY portfolio_id;

With initial guess:

SELECT xirr(amount, date, 0.1 ORDER BY date)
FROM transactions;

TWR Examples

Basic usage:

SELECT twr(cashflow, portfolio_value, date ORDER BY date)
FROM portfolio_history;

Window function:

SELECT date,
       twr(cashflow, portfolio_value, date) OVER (ORDER BY date) as cumulative_twr
FROM portfolio_history;

NPV Examples

Basic usage:

SELECT npv(0.05::double precision, cashflow, date ORDER BY date)
FROM project_cashflows;

Multiple projects:

SELECT project_id, npv(0.08::double precision, amount, date ORDER BY date)
FROM cashflows
GROUP BY project_id;

Performance

Optimizations (v1.0.3+)

Sorted data optimization:

• XIRR: 20-40% faster when data is sorted by timestamp
• NPV: 30-40% faster when data is sorted by timestamp
• Automatically detected and applied

Adaptive memory allocation:

• Dynamic growth factor based on dataset size
• Reduces memory waste by up to 97% for large datasets

Parallel Execution (v1.1.0+)

All aggregates support parallel query execution:

SET max_parallel_workers_per_gather = 4;

SELECT portfolio_id, xirr(amount, date ORDER BY date)
FROM transactions
GROUP BY portfolio_id;
-- Automatically parallelizes across portfolios

Performance impact:

• 2-4x throughput improvement for queries with many groups
• Best with 500+ portfolios and 500+ rows per portfolio

Combined Performance

For 1000 portfolios with 100 transactions each:

• v1.0.3 baseline: 58ms
• v1.2.0 parallel + sorted: 18-25ms
• 3-5x overall improvement

Data Considerations

XIRR and NPV

• Data is sorted by timestamp: Automatically uses optimized calculation path
• Data is unsorted: Falls back to standard calculation (no performance penalty)
• Memory usage: 16 bytes per row for XIRR, 24 bytes per row for TWR

TWR

• Requires portfolio value at each cashflow date
• Cashflow can be zero for periods without transactions
• For partial periods: Include initial row with cashflow=0 and starting portfolio value

Limitations

XIRR convergence:

• Newton's method may not converge for some inputs
• Returns NULL if no solution found after 50 iterations
• Try different initial guess if needed

Memory constraints:

• All aggregate data held in memory during calculation
• For very large datasets (10M+ rows), monitor memory usage

Advanced Usage

Window Functions

-- Rolling XIRR
SELECT date,
       xirr(amount, date) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM transactions;

Portfolio-Level XIRR

WITH portfolio_flows AS (
    SELECT transaction_date,
           CASE
               WHEN type IN ('buy', 'contribution') THEN -amount
               WHEN type IN ('sell', 'withdrawal') THEN amount
           END as cashflow
    FROM transactions
    UNION ALL
    SELECT CURRENT_TIMESTAMP, current_portfolio_value
    FROM portfolio_value
)
SELECT xirr(cashflow, transaction_date ORDER BY transaction_date)
FROM portfolio_flows;

Configuration

For Best Performance

1. Ensure data is sorted:

SELECT xirr(amount, date ORDER BY date) ...  -- Explicit ORDER BY

2. Use appropriate indexes:

CREATE INDEX ON transactions (portfolio_id, date);

3. Enable parallel execution:

SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';  -- For large aggregations

4. Monitor optimization path (debug mode):

SET client_min_messages = 'debug1';
SELECT xirr(amount, date ORDER BY date) FROM transactions;
-- Check logs for "sorted=1" and "optimized path"

Upgrading

ALTER EXTENSION financial UPDATE TO '1.2.0';

All upgrades are backward compatible. Existing queries work without modification.

Version History

• 1.2.0 (2026-01-23): Incremental sortedness tracking, NPV sorted optimization
• 1.1.0 (2026-01-23): Parallel query support for all aggregates
• 1.0.3 (2026-01-23): Sorted data optimization, adaptive memory allocation
• 1.0.2: TWR and XIRR aggregates

See CHANGELOG.md for detailed release notes.

Testing

make installcheck

Runs regression tests for all functions.

License

See LICENSE for details.