how to calculate xirr using aggregation at db level only without using any js library

I want to implement Xirr function using aggregation. I have tried but after i have figured out cashflows array from my database I cannot procced further .

how to calculate xirr using aggregation at db level only without using any js library

I want to implement Xirr function using aggregation. I have tried but after i have figured out cashflows array from my database I cannot procced further .

• mongodb
• aggregate-functions
• xirr

1 Answer 1

An simple example:

1. Retrieve Cash Flows from Database Ensure your database has a structure similar to:

CREATE TABLE cashflows(id SERIAL PRIMARY KEY, amount DECIMAL(18, 6), date DATE);

2. Next let's fetch the required data:

SELECT amount, DATEDIFF(day, MIN(date) OVER (), date) AS days FROM cashflows;

3. Estimate an Initial Guess for r A common approach is to start with a small percentage, e.g., 0.1 (10%).
4. Iteratively Refine the Value of r Using SQL Aggregation SQL does not support loops directly, but you can approximate XIRR using iterative methods through recursive CTEs (Common Table Expressions). Something like the below (not tested):

WITH RECURSIVE XIRR_Calculation AS (
    SELECT 0.1 AS guess, 1 AS iteration
    UNION ALL
    SELECT
        guess - (
            (SELECT SUM(amount / POWER(1 + guess, days / 365.0)) FROM cashflows) /
            (SELECT SUM(-amount * days / 365.0 / POWER(1 + guess, (days / 365.0) + 1)) FROM cashflows)
        ),
        iteration + 1
    FROM XIRR_Calculation
    WHERE iteration < 50 -- Limit to avoid infinite loops
)
SELECT guess AS XIRR FROM XIRR_Calculation ORDER BY iteration DESC LIMIT 1;

PS: Rewrite the queries for MongoDB.