Calculating Extended Internal Rate of Return in sqlite

xirr.sql

-- The following snippet calculates the extended internal rate of return
-- for an investment, provided the list of transactions made during the time.
-- 
-- It expects a table `transactions` to be present (of-course feel free to rename)
-- with two columns, date and amount, where date is the date of the transaction
-- and amount being the amount of the transaction. Each outflow (like, money put into the investment)
-- must be a negative value and each inflow (like, money take out from investment) must be positive.
--
-- P.S. This snippet depends on POWER(...) function from [https://github.com/nalgeon/sqlean].
--      Either import that or provide your own.
-- 
-- Copyright (c) 2021 Riyaz Ali (MIT License)

WITH RECURSIVE
    rates(value) AS (
        -- generate_series() equivalent that can work with decimal increments
        -- adapted from: https://www.sqlite.org/series.html
        SELECT 0
            UNION ALL
        SELECT PRINTF('%.2f', value + 0.01)
            FROM rates
        WHERE value + 0.01 <= 100
    ),
    txns AS (
        SELECT date, amount,
            (JULIANDAY(date) - (FIRST_VALUE(JULIANDAY(date)) OVER ())) AS n
        FROM transactions
    ),
    npv(rate, net_present_value) AS (
        -- for each rate, we calculate the Net Present Value
        -- and in next step, pick the one closest to zero (essentially bruteforcing our way through)
        SELECT CAST(value AS real),
               -- see: https://www.mathsisfun.com/money/internal-rate-return.html
               -- see: https://www.quora.com/What-is-the-mathematical-formula-to-calculate-XIRR
               PRINTF('%.4f', SUM(amount / POWER((1 + value / 100), n / 365)))
        FROM txns
                 CROSS JOIN rates
        GROUP BY CAST(value AS real)
    )
-- pick the one _closest_ to zero (minimum absolute distance from zero)
SELECT rate, MIN(ABS(net_present_value)) AS net_present_value FROM npv;