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

Question

I'm trying to convert an excel formula to SQL to find daily compounded interest. In excel, the formula we use looks like:

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

I need to be able to pull the daily accrual so we can forecast based on rate changes or balance changes so a final total wouldn't work well in this instance.

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. Query will be used in an SSRS report as well, so SSRS solutions work as well.

Answer 1

Here is something that works well. I'm going to walk through the process of discovery to help others that might need to do something similar, but you can jump directly to Try 3 below.

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)

- Try 1 - Simple formula

This would be a common first attempt for people with some sophistication in SQL coding.

UPDATE #tmp
SET Accrual = (
    Balance + ISNULL(
        (
        SELECT SUM(Accrual) 
        FROM #tmp x 
        WHERE x.RecDate < #tmp.RecDate
        ), 0)) * (Rate / 365)

This would work for calculating simple interest, but not for compound interest, and here are the results:

RecDate        Rate    Balance        Accrual
2019-11-19    0.0529    8000.0000    1.159440
2019-11-20    0.0529    8000.0000    1.159440
2019-11-21    0.0529    7000.0000    1.014510

Note that the interest on rows 1 and 2 are the same. This is because all of the rows are updated at the same time, so no individual row's Accrual balance is updated before the next one is calculated. This is how SQL Server is designed to work.

Also interesting would be the fact that this would work IF each row was calculated at the end of the day, since each row would then be working with rows that had their accrual already calculated. We can simulate this form of data by using a cursor, calculating each row one at a time.

- Try 2 - Cursor

This works for batch processing, and emulates day by day processing.

DECLARE cx CURSOR FOR
    SELECT RecDate
    FROM #tmp
    ORDER BY RecDate

DECLARE @Dt Date

OPEN cx
FETCH NEXT FROM cx
    INTO @dt

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #tmp
        SET Accrual = (
            Balance + ISNULL((SELECT SUM(Accrual) FROM #tmp x WHERE x.RecDate < @dt), 0)) 
                * (Rate / 365)
        WHERE CURRENT OF cx

    FETCH NEXT FROM cx
    INTO @dt
END
CLOSE cx
DEALLOCATE cx

The results are:

RecDate       Rate      Balance      Accrual
2019-11-19    0.0529    8000.0000    1.159440
2019-11-20    0.0529    8000.0000    1.159608
2019-11-21    0.0529    7000.0000    1.014846

This accumulates the data correctly, adding in the previous accruals. However, you will notice that this output does not match the sample provided, even though the calculations are correct. Why?

- Try 3 - Cast RATE value to allow sufficient decimal places in calculation

In Excel, the numeric values are high precision floating point, while my sample database is using decimal data types. Why? Decimal data types "prevent" rounding errors in calculations.

However, while decimal data types prevent rounding errors, they also have a fixed length of precision. A formula calculating decimal data types will truncate the results at the number of decimal points equal to about (2 * [total decimal places for all values in the calculation] ). Thus, when the daily rate is calculated as (Rate [4 decimal places] / 365 (0 decimal places)), for the interest rate provided (0.0529) the result is 0.00014493 (2*4 = 8 decimal places).

However, the calculation needs more precision than this to return the correct values in this case. So, here is one more version of the Cursor option:

DECLARE cx CURSOR FOR
    SELECT RecDate
    FROM #tmp
    ORDER BY RecDate

DECLARE @Dt Date

OPEN cx
FETCH NEXT FROM cx
    INTO @dt

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #tmp
        SET Accrual = (
            Balance + ISNULL((SELECT SUM(Accrual) FROM #tmp x WHERE x.RecDate < @dt), 0))
                * (CAST(Rate as decimal(38,35)) / 365)   --<<-- CHANGED HERE
        WHERE CURRENT OF cx

    FETCH NEXT FROM cx
    INTO @dt
END
CLOSE cx
DEALLOCATE cx

With this high-precision version, our results match the user's results:

RecDate       Rate      Balance      Accrual
2019-11-19    0.0529    8000.0000    1.159452
2019-11-20    0.0529    8000.0000    1.159620
2019-11-21    0.0529    7000.0000    1.014857

Hopefully, this covers the issue thoroughly enough.