> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Financial

> Provides specialized function for computing the Irregular Internal Rate of Return (XIRR).

The [financial](https://github.com/intgr/pg_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:

```sql theme={null}
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`:

```sql theme={null}
SELECT xirr(amount, transaction_date) AS irr
FROM transactions;
```

<Info>
  Note: `xirr(amount, transaction_date)` computes the **internal rate of
  return** for cash flows occurring at irregular intervals. Negative amounts
  typically represent **investments**, while positive amounts represent
  **returns**.
</Info>

## 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)**.

```sql theme={null}
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](https://github.com/intgr/pg_financial).