sqldatabasequestdb

Calculate compounding interest using SQL


I am trying to calculate compounding interest. Eventually if I have the year 2000, starting amount as 1, and interest as 0.1, I would like to have these results

timestamp, decimalChange, amount, compounding
2000,0.1,1,1.1
2001,0.1,1.1,1.21
2002,0.1,1.21,1.331
...

I've been trying using window functions but I get stuck. Any pointers how to get this going?


Solution

  • We can use the POWER function to calculate the amount, taking into consideration the difference from the initial year, then we can use the FIRST_VALUE window function to get the value from the row before.

    In the query below, you need to provide the initial year (twice), the initial_principal, the annual interest rate, and the number of years (3 in the example). Note I am using a cross join to minimize the number of places where you need to change values by hand. This way you only need to modify the values in the first two CTEs.

    WITH initial_values AS (
        SELECT 2000 AS start_year, 0.1 AS decimal_change, 1.0 AS initial_amount
    ),
    year_series AS (
        SELECT 2000 + (x - 1) AS timestamp
        FROM long_sequence(3) -- Generating a sequence of 3 years
    ),
    compounded_values AS (
        SELECT 
            ys.timestamp,
            iv.decimal_change,
            iv.initial_amount * POWER(1 + iv.decimal_change, ys.timestamp - iv.start_year + 1) AS compounding
        FROM 
            year_series ys
        CROSS JOIN initial_values iv
    ), compounding_year_before AS (
    SELECT 
        cv.timestamp,
        cv.decimal_change,
        FIRST_VALUE(cv.compounding) OVER (ORDER BY cv.timestamp rows between 1 preceding and 1 preceding) AS amount,
        cv.compounding
    FROM 
        compounded_values cv
    CROSS JOIN initial_values iv
    ORDER BY 
        cv.timestamp)
    select cy.timestamp, cy.decimal_change, coalesce(cy.amount, iv.initial_amount), compounding from compounding_year_before cy
    cross join initial_values iv