TSQL window function to calculate interest

Question

I am trying to calculate interest using window functions on SQL Server 2022

with t as 
(
    select * 
    from 
        (values
            ('2000-1-1',100,0.1),
            ('2000-1-2',100,0.1),
            ('2000-1-3',100,0.1),
            ('2000-1-4',100,0.1),
            ('2000-1-5',100,0.1),
            ('2000-1-6',100,0.1)
        ) as t(date, amount, perc)
)
select 
    *, 
    perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365 interest,
    sum(amount) over (order by date rows between unbounded preceding and current row) +
        perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365
from 
    t

Can someone point me to a problem why interest calculated isn't being cummulated f.e. I expect balance to be 300.08 on 3rd while it is 300.05 (interest from 2nd isn't taken into account)

you can't do this kind of stuff with just window functions, because they don't allow you to access previously *calculated* value — siggemannen, Jul 10 '23 at 12:16
To elaborate, the value of `balance` is being calculated as `(100 + 100 + 100) + ((0.1 * (100+100)) / 365) = 300 + ((0.1 * 200) / 365) = 300 + (20 / 365) = 300 + 0.05479 = 3.05479` — Thom A, Jul 10 '23 at 12:17
What *you* want is `100 + (100 + ((0.1*100)/365)) + (100 + ((0.1*200.027397)/365))` where `200.027397` is the value of `100 + (100 + ((0.1*100)/365))` — Thom A, Jul 10 '23 at 12:21
Aside... the date values you're specifying there are just strings. If you want to do some calculations with them when using a values constructor like that you may wish to use ODBC date literals to avoid casting, e.g. `{d'2000-01-06'}`. — AlwaysLearning, Jul 10 '23 at 12:46

score 1 · Accepted Answer · answered Jul 10 '23 at 12:41

1

As mentioned in the comments, this isn't cumulative aggregation it's recursive aggregation. Effectively your expression wants to read something like:
Amount + PriorBalance + (Interest + (Percentage * PriorBalance) / 365)

To achieve this, you're doing to need to use a recursive CTE, as a cumulative SUM (or any of the windowed aggregated) cannot access the prior rows calculated value.

Therefore this appears to be the solution you need:


WITH t AS (
    SELECT date,
           amount,
           perc,
           ROW_NUMBER() OVER (ORDER BY date ASC) AS I
    FROM (VALUES(CONVERT(date,'20000101'),100,0.1),
                (CONVERT(date,'20000102'),100,0.1),
                (CONVERT(date,'20000103'),100,0.1),
                (CONVERT(date,'20000104'),100,0.1),
                (CONVERT(date,'20000105'),100,0.1),
                (CONVERT(date,'20000106'),100,0.1)) as t(date,amount,perc)),
rCTE AS (
    SELECT date,
           amount,
           perc,
           I,
           CONVERT(numeric(7,6),0.000000) AS interest,
           CONVERT(numeric(12,6),0.000000) AS balance
    FROM t
    WHERE I = 1
    UNION ALL
    SELECT t.date,
           t.amount,
           t.perc,
           t.I,
           CONVERT(numeric(7,6),r.interest + ((t.perc * r.amount)/365)) AS interest,
           CONVERT(numeric(12,6),t.amount + r.balance + (r.interest + ((t.perc * r.amount)/365))) AS balance
    FROM t t
         JOIN rCTE r ON r.I = t.I -1
)
SELECT date,
       amount,
       perc,
       interest,
       balance
FROM rCTE;
GO

answered Jul 10 '23 at 12:41

Thom A

88,727
11
45
75

I believe you can do recursive multiplication using `EXP(SUM(LOG(someValue)) OVER (ORDER BY...))` but can't get my head round how to do it with a yearly interest split by day. Something like https://dbfiddle.uk/c8GIuHA1 – Charlieface Jul 10 '23 at 13:20
Not a method I'm familiar with, @Charlieface , but if I recall I'll have a go tomorrow. – Thom A Jul 10 '23 at 13:26
see also https://stackoverflow.com/a/5416205/14868997 – Charlieface Jul 10 '23 at 13:27
I would fix it slightly: first day's balance is equal to its amount and day's interest as "previous day balance * rate" – Kodak Jul 10 '23 at 16:48
Recursive version does not work for longer periods (The statement terminated. The maximum recursion 100 has been exhausted before statement completion.) – Kodak Jul 10 '23 at 17:13
With EXP(SUM(LOG()) OVER()) - how to calculate value of interest (as rate * previous day balance)? I was trying EXP(SUM(LOG(0 + perc / 365)) OVER (ORDER BY date ROWS between UNBOUNDED PRECEDING and 1 PRECEDING)) * SUM(amount) OVER (ORDER BY date ROWS between UNBOUNDED preceding and 1 PRECEDING) but it gives me strange results – Kodak Jul 10 '23 at 17:47
You'll need to increase the max recursion in your `OPTION` clause, @Kodak . As your sample data only contained 6 rows, I didn't add it. – Thom A Jul 10 '23 at 18:11
recursion looks like an overkill to me as I need to run it on two years period; EXP SUM LOG would be fine if I could calculate value of interest... – Kodak Jul 11 '23 at 08:09

TSQL window function to calculate interest

1 Answers1