Financial

The financial extension in PostgreSQL provides specialized functions for financial calculations, particularly for computing the Irregular Internal Rate of Return (XIRR). It enables handling cash flows with irregular timing, similar to spreadsheet functions like XIRR in Excel or LibreOffice. Your Nile database arrives with financial extension already enabled, so there’s no need to run create extension.

​

Creating and Populating transactions Table

Let’s create a sample table to store financial transactions with dates and cash flows:

CREATE TABLE transactions (
    tenant_id UUID NOT NULL,
    account TEXT,
    amount NUMERIC,
    transaction_date DATE
);

-- Create a tenant first, if it doesn't exist yet
INSERT INTO tenants (id, name) VALUES 
    ('11111111-1111-1111-1111-111111111111', 'Example Corp');

INSERT INTO transactions (tenant_id, account, amount, transaction_date) VALUES
    ('11111111-1111-1111-1111-111111111111', 'Alice', -1000.00, '2024-01-01'),
    ('11111111-1111-1111-1111-111111111111', 'Alice', 200.00, '2024-02-01'),
    ('11111111-1111-1111-1111-111111111111', 'Alice', 300.00, '2024-03-01'),
    ('11111111-1111-1111-1111-111111111111', 'Alice', 500.00, '2024-06-01'),
    ('11111111-1111-1111-1111-111111111111', 'Alice', 700.00, '2024-12-01');

​

Calculating the Irregular Internal Rate of Return (XIRR)

To compute the XIRR for an account using pg_financial:

SELECT xirr(amount, transaction_date) AS irr 
FROM transactions;

​

Providing an Initial Guess for XIRR

The guess argument is an optional initial guess. When omitted, the function will use annualized return as the guess, which is usually reliable. This attempts to compute the XIRR starting with an initial guess of 10% (0.1).

SELECT xirr(amount, transaction_date, 0.1) AS irr 
FROM transactions;

​

Conclusion

The pg_financial extension in PostgreSQL provides essential financial calculation capabilities, particularly for evaluating investment returns with irregular cash flows. It is useful for financial modeling and investment analytics.

For more details, refer to the pg_financial GitHub repository.