How to calculate running balance using PostgreSQL window functions?

I want a query to track interest on a mortgage account. For simplicity assume the interest is calculated yearly. There are also one-off deposits/withdrawals (repayments etc).

I want to query this information and calculate a running balance, presumably using window functions. Here is an example of the kind of table I want to query.

year | changes | interest | comment
2020 | 10000   | 2.5      | initial mortgage of 10k
2021 | 0       | 2.0      | next year the rate drops
2022 | 5000    | 2.0      | we borrow an extra 5k
2023 | 0.      | 1.5      | rate drop again

I want a query that calculates the running balance each year, like so:

year | changes | interest | balance
2020 | 10000   | 2.5      | 10250.0 = 10000 * (1 + 2.5 / 100)
2021 | 0       | 2.0      | 10455.0 = 10250 * (1 + 2.0 / 100)
2022 | 5000    | 2.0      | 15764.1 = (10455 + 5000) * (1 + 2.0 / 100)
2023 | 0.      | 1.5      | 16000.56 = 15764.1 * (1 + 1.5 / 100)

How to do this in PostgreSQL?

Answer

The recursive CTE is quite possibly the better approach. But it is possible to do this using window functions.

The three key ideas are:

• Using exp(sum(ln())) as the product() aggregation function.
• Projecting each value to the latest time, accumulating all interest functions.
• Dividing by the “accumulated interest” up to that value to adjust for new values entering in.

The actual code is not that complicated:

select t.*,
        (sum(changes * running_interest) over (order by year) /
         coalesce(prev_running_interest, 1)
        ) as val
from (select t.*, 
             exp(sum(ln(1 + interest / 100)) over (order by year desc)) as running_interest,
             exp(sum(ln(1 + interest / 100)) over (order by year desc rows between unbounded preceding and 1 preceding)) as prev_running_interest
      from t
     ) t
order by year;

You will notice in the db<>fiddle the slight inaccuracies caused by floating point arithmetic. You can always cast to fewer decimal places for more aesthetically appealing numbers.