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

0 votes

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 by Kithuzzz
• 38,020 points
811 views

1 answer to this question.

0 votes

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)
answered Nov 26, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 63,720 points
1,359 views
0 votes
1 answer

How to convert an XML file to an Excel file?

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

answered Jan 3, 2023 in Others by narikkadan
• 63,720 points
1,548 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
976 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
13,908 views
0 votes
0 answers

Export SQL query data to Excel

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

Aug 18, 2022 in Database by Kithuzzz
• 38,020 points
524 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

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

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
748 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
771 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

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

Feb 4, 2022 in Database by Vaani
• 7,070 points
468 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
655 views
0 votes
1 answer

How to calculate percentage of an autosum field in excel

The fact that I was using the ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,720 points
491 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP