Calculating compound interest with deposits/withdraws

Question

I'm trying to calculate the total on an interest-bearing account accounting for deposits/withdraws with BigQuery.

Example scenario:

Daily interest rate = 10%
Value added/removed on every day: [100, 0, 29, 0, -100] (negative means amount removed)

The totals for each day are:

Day 1: 0*1.1 + 100 = 100
Day 2: 100*1.1 + 0 = 110
Day 3: 110*1.1 + 29 = 150
Day 4: 150*1.1 + 0 = 165
Day 5: 165*1.1 - 100 = 81.5

This would be trivial to implement in a language like Python

daily_changes = [100, 0, 29, 0, -100]
interest_rate = 0.1

result = []
for day, change in enumerate(daily_changes):
    if day == 0:
        result.append(change)
    else:
        result.append(result[day-1]*(1+interest_rate) + change)

print(result)
# Result: [100, 110.00000000000001, 150.00000000000003, 165.00000000000006, 81.50000000000009]

My difficulty lies in calculating values for row N when they depend on row N-1 (the usual SUM(...) OVER (ORDER BY...) solution does not suffice here).

Here's a CTE to test with the mock data in this example.

with raw_data as (
  select 1 as day, numeric '100' as change union all
  select 2 as day, numeric '0' as change union all
  select 3 as day, numeric '29' as change union all
  select 4 as day, numeric '0' as change union all
  select 5 as day, numeric '-100' as change
)

select * from raw_data

score 3 · Accepted Answer · answered Sep 26 '22 at 21:17

You may try below:

SELECT day,
       ROUND((SELECT SUM(c * POW(1.1, day - o - 1))
                FROM t.changes c WITH OFFSET o), 2) AS totals 
  FROM (
    SELECT *, ARRAY_AGG(change) OVER (ORDER BY day) changes
      FROM raw_data
  ) t;
+-----+--------+
| day | totals |
+-----+--------+
|   1 |  100.0 |
|   2 |  110.0 |
|   3 |  150.0 |
|   4 |  165.0 |
|   5 |   81.5 |
+-----+--------+

score 3 · Answer 2 · answered Sep 26 '22 at 22:06

Another option with use of recursive CTE

with recursive raw_data as (
  select 1 as day, numeric '100' as change union all
  select 2 as day, numeric '0' as change union all
  select 3 as day, numeric '29' as change union all
  select 4 as day, numeric '0' as change union all
  select 5 as day, numeric '-100' as change
), iterations as (
  select *, change as total
  from raw_data where day = 1
  union all
  select r.day, r.change, 1.1 * i.total + r.change
  from iterations i join raw_data r
  on r.day = i.day + 1 
) 
select *
from iterations

with output

Accepted Jaytiger's answer but this is great too - thanks! – fnery Sep 27 '22 at 08:22 — fnery, Sep 27 '22 at 08:22

Calculating compound interest with deposits/withdraws

2 Answers2