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?
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