Skip to content

README.md

Latest commit

 

History

History
232 lines (170 loc) · 5.25 KB

README.md

File metadata and controls

232 lines (170 loc) · 5.25 KB

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
  1. Use appropriate indexes:
CREATE INDEX ON transactions (portfolio_id, date);
  1. Enable parallel execution:
SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';  -- For large aggregations
  1. 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.