Skip to main content
This guide covers our optimized XIRR (Extended Internal Rate of Return) implementations for PostgreSQL.

​
Performance Overview

We’ve optimized XIRR calculations from ~385ms to ~3-5ms per calculation (100-150x improvement). In real-world queries with multiple entities, we’ve seen improvements up to 700x faster (23 seconds → 32ms).

​
Available Implementations

ImplementationException HandlingParallel-SafeSpeedUse Case
xirr_aggFullNo~385msDebugging, edge cases
xirr_safe_fast_aggReturns NULLNo~3-5msProduction (recommended)
xirr_fast_aggRaises errorsNo~3-5msDevelopment/debugging

​
Quick Start

For production use: 
-- Use xirr_safe_fast_agg for NULL on error (Excel-compatible)
SELECT portfolio_id, xirr_safe_fast_agg(amount, date) 
FROM cash_flows 
GROUP BY portfolio_id;

-- Use xirr_fast_agg for debugging (raises exceptions)
SELECT portfolio_id, xirr_fast_agg(amount, date) 
FROM cash_flows 
GROUP BY portfolio_id;

-- With custom initial guess
SELECT xirr_safe_fast_agg(amount, date, 0.5) FROM cash_flows;

​
Key Optimizations

​
1. Data Type Change: NUMERIC → DOUBLE PRECISION

Hardware acceleration for floating-point math provides orders of magnitude faster mathematical operations.

​
2. Algorithm Improvements

  • Pre-compute year offsets once instead of repeatedly
  • Vectorized NPV calculation using SQL aggregates
  • Smart initial guess based on total positive/negative flows
  • Early exit for impossible cases

​
3. Exception Handling Trade-offs

  • Original: EXCEPTION blocks prevent parallel execution
  • Production: Balanced approach with error details

​
File Reference

Files are located in backend/server/apps/calculations/sql/:
FileDescription
xirr_original.sqlBaseline implementation using NUMERIC type
xirr_production.sqlRecommended production implementation
benchmark_xirr.sqlPerformance testing suite
recreate_xirr.shDrop and recreate all implementations

​
Parallel Processing

We tested parallel XIRR aggregation but removed it after benchmarking showed only modest improvements:
Dataset SizePerformance Change
Small (1K rows)12% slower (overhead)
Medium (20K rows)13% faster
Large (300K rows)28% faster
Given these marginal gains, we prioritize exception handling and debugging capabilities over parallelism. The ability to understand why calculations fail (matching Excel’s #NUM! errors) is more valuable than a 13-28% speedup.

​
Re-enabling Parallel Processing

If needed for very large datasets:
  1. Add COMBINEFUNC to enable true parallelism:
CREATE OR REPLACE FUNCTION xirr_combine(
    state1 xirr_state,
    state2 xirr_state
)
RETURNS xirr_state
LANGUAGE SQL
IMMUTABLE
PARALLEL SAFE
AS $$
    SELECT CASE
        WHEN state1 IS NULL THEN state2
        WHEN state2 IS NULL THEN state1
        ELSE (
            state1.amounts || state2.amounts,
            state1.dates || state2.dates
        )::xirr_state
    END;
$$;
  1. Remove EXCEPTION blocks from the main function
  2. Update aggregate definition with COMBINEFUNC and PARALLEL = SAFE
  3. Configure PostgreSQL:
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 10;
SET parallel_tuple_cost = 0.01;
Only use parallel execution if:
  • You have very large datasets (>100K rows)
  • Performance is critical
  • You can accept NULL returns without error details
  • You have multiple CPU cores available

​
Known Limitations

  1. Extreme Date Ranges: Production version validates date ranges to be within 100 years
  2. Precision Differences: DOUBLE PRECISION may show slight differences from NUMERIC in edge cases
  3. Convergence: Some extreme returns may not converge (matching Excel #NUM! behavior)

​
Running Benchmarks

cd server/apps/calculations/sql

# Run benchmarks
psql -d your_database -f benchmark_xirr.sql

# Recreate all implementations
./recreate_xirr.sh

​
Development Guidelines

​
Creating New Implementations

  1. Create as a standalone file (e.g., xirr_gpu_accelerated.sql)
  2. Include all components: functions, types, aggregates in one file
  3. Test thoroughly using benchmark_xirr.sql as a template
  4. Document what makes this implementation unique

​
Modifying Existing Implementations

  1. Create versioned copy: Copy original to _v2 (e.g., xirr_production_v2.sql)
  2. Make changes in the v2 file: Keep original intact for comparison
  3. Test the v2 implementation:
psql -d your_database -f xirr_production_v2.sql
  1. Benchmark against original: Ensure improvements and no regressions
  2. Once verified, merge back:
    • Replace content in original file
    • Delete the v2 file
    • Update recreate_xirr.sh if function signatures changed
This approach ensures you always have a working version while developing improvements.