1

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?

djrobstep
  • 23
  • 4

2 Answers2

3

Because of the need to multiply the prior year balance by the current interest, this is most easily achieved using a recursive CTE:

WITH RECURSIVE CTE AS (
  SELECT t.year, t.changes, t.interest, t.changes * (1.0 + t.interest / 100.0) AS balance
  FROM transactions t
  WHERE year = (SELECT MIN(year) FROM transactions)
  UNION ALL
  SELECT t.year, t.changes, t.interest, (t.changes + CTE.balance) * (1.0 + t.interest / 100.0)
  FROM transactions t
  JOIN CTE ON t.year = CTE.year + 1
)
SELECT year, changes, interest, ROUND(CAST(balance AS numeric), 2) AS balance
FROM CTE

Output:

year    changes     interest    balance
2020    10000       2.5         10250.00
2021    0           2           10455.00
2022    5000        2           15764.10
2023    0           1.5         16000.56

Demo on dbfiddle

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Nick
  • 138,499
  • 22
  • 57
  • 95
  • This definitely does the job but I'm curious if a solution is possible using window functions? Running totals are the classic use case for window functions I had thought, yet I had trouble coming up with a solution. – djrobstep May 04 '20 at 05:05
  • The problem is the need to multiply the previous balance by the current interest rate which requires effectively a `MUL` aggregation function. You could simulate one (see [this question](https://stackoverflow.com/questions/5416169/multiplication-aggregate-operator-in-sql)) but it is simpler to use a recursive CTE. – Nick May 04 '20 at 05:09
  • @Nick . . . Actually, the window functions are not at all complicated (I could even reduce it down to two such functions rather than three). The recursive CTE is likely to be more accurate, using explicit multiplication rather than logs and power functions. – Gordon Linoff May 04 '20 at 13:56
  • @Nick . . . My apologies. Somehow I accidentally downvoted you answer. That was definitely not intended! – Gordon Linoff May 04 '20 at 14:05
  • @GordonLinoff no problem at all. I was sleeping through all the drama! :-) – Nick May 04 '20 at 23:18
2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is great. I spent a bit of time trying to solve this using the `exp/sum/ln` trick but missed the vital point of figuring out the interest going *backwards* in time. Thanks for sharing. – Nick May 04 '20 at 23:18
  • Accepted this answer because it matched most closely what I had in mind, but in real usage I would probably use Nicks answer as it's a little clearer (to me at least) and less hacky. – djrobstep May 08 '20 at 04:56