How to convert an excel formula to SQL to calculate daily compound interest

Question

In order to calculate daily compound interest, I'm attempting to convert an excel formula to SQL. The formula we employ in Excel is as follows:

Date   Rate    Balance  Accrual     (Formula)
11/19  0.0529  8000     1.159452    (=C2*(B2/365))
11/20  0.0529  8000     1.159620    (=(C3+SUM($D$2:$D2))*(B3/365))
11/21  0.0529  7000     1.014857    (=(C4+SUM($D$2:$D3))*(B4/365))

A final total wouldn't be useful in this situation because I need to be able to get the daily accrual so we can forecast depending on rate changes or balance changes.

I've tried a few formulas to get the same result, such as:

1. balance*(apr/365)
2. sum((balance * (apr/365))) over (order by date)
3. (balance+sum(balance*(apr/365)) over (order by date))*(apr/365)

1. works for simple interest, but I need a running sum of interest before the current row
2. nowhere near what I'm looking for
3. Gets me extremely close but not exact

The problem is I need to add ALL previous accrual amounts to the balance and THEN calculate interest off of that. I'm missing something but can't quite put my finger on it. The query will be used in an SSRS report as well, so SSRS solutions work as well.

Answer 1

Here is a solution that is effective. You can skip forward to Try 3 below, but I'll go through the discovery process to aid anyone who might need to take similar action.

First, here is the working table I created:

CREATE TABLE #tmp (
    RecDate Date not null Primary Key,    -- Obviously not appropriate PK for real table
    Rate decimal(7,4),
    Balance Decimal(16,4),
    Accrual Decimal(16,6)
    )

INSERT INTO #tmp VALUES 
    ('20191119',0.0529,8000,0),
    ('20191120',0.0529,8000,0),
    ('20191121',0.0529,7000,0)