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.

Nov 26, 2022 in Others 72 views

## 1 answer to this question.

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)```
• 51,240 points

## How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

## How to convert an XML file to an Excel file?

Create a CSV file that is in ...READ MORE

## How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

## Export SQL query data to Excel

My query returns a huge amount of ...READ MORE

## Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

## How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

## How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE