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?

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
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) but it is simpler to use a recursive CTE.
@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.
@Nick . . . My apologies. Somehow I accidentally downvoted you answer. That was definitely not intended!
@GordonLinoff no problem at all. I was sleeping through all the drama! :-)
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.

Improve this answer

2 Comments

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.