I have a table like below: enter image description here

  1. Need to calculate interest for next month 100*(4.29/100).
  2. Row+1:Capital_Balance = Row:Capital_Balance_Calculated - Calculated interest from above step.

Resultant table is like below: [![enter image description here][2]][2]

For sample data:

     Account_Number int
    ,Interest_Rate float
    ,LastDateOfMonth Date
    ,Capital_Balance_Calculated Float
    ,Interest float
    ,Payment float


INSERT INTO #Test VALUES (12345,4.2,'2019-03-31',100,5,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-04-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-05-31',0,NULL,5);
INSERT INTO #Test VALUES (12345,4.2,'2019-06-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-07-31',0,NULL,7);
INSERT INTO #Test VALUES (54321,5.1,'2019-03-31',200,6,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-04-30',0,NULL,6);
INSERT INTO #Test VALUES (54321,5.1,'2019-05-31',0,NULL,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-06-30',0,NULL,10);
INSERT INTO #Test VALUES (54321,5.1,'2019-07-31',0,NULL,8);

Please take a look at the calculation: enter image description here

  • I tried using While loop and joined on the same table based on row_number but it got way too complicated. Apr 15, 2019 at 14:20
  • Please consider reading this advice
    – mustaccio
    Apr 15, 2019 at 15:29
  • Are Capital_Balance_Calculated and Interest amounts in the expected results correct ? One set has decreasing capital balance while the other has increasing capital balance.
    – armitage
    Apr 15, 2019 at 15:56
  • Hi Armitage, Yes the numbers are correct. Thats is because of the payments column. Th ecustomer is being charged the interest month on month. The customer is also paying some amount month on month(say direct debit ) Apr 15, 2019 at 16:08
  • it doesn't make sense that both customers are making payments, and one customer balance is decreasing while the other's is increasing. Can you provide the full formula that you use?
    – SQLRaptor
    Apr 15, 2019 at 16:13

1 Answer 1


Your sample data script has an error, it uses 4.2 and 5.1 instead of 4.29 and 1.54. This threw off the numbers so please take care next time to make sure you get it right... Also, after the edit the desired result is missing. It is unclear whether the payment is from the 'current' row or the previous one, and in either case, for the first customer the balance drops and then increases even though the payment is more than the interest... Please check that as well.

Here is my solution, and while there is still some variance from your original desired result which I believe is wrong, this should get you on the right path so you can adjust it to fit your needs. I chose to use ARN.Payment in the balance calculation in the recursive part of the CTE for the 'current row', change that to 'RI.Payment' if you want to use the 'previous row' payment. It is impossible to tell from your original table which one you meant.

The solution adds row numbers per account to allow the following recursive CTE to calculate the interest and payments row by row.

WITH Added_Row_Numbers AS
       ROW_NUMBER() OVER (PARTITION BY Account_Number ORDER BY LastDateOfMonth) AS Rn
FROM   #Test
, Recursive_Interest AS
FROM   Added_Row_Numbers 
WHERE  Rn =1
SELECT ARN.Account_Number,
       RI.Capital_Balance_Calculated + (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) - RI.Payment
       AS Capital_Balance_Calculated,
       (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) AS Interest_Paid,
FROM   Recursive_Interest AS RI 
       Added_Row_Numbers AS ARN
       ON ARN.Rn = RI.Rn + 1 
          RI.Account_Number = ARN.Account_Number
SELECT RI.Account_Number,
FROM   Recursive_Interest AS RI
ORDER BY Account_Number, LastDateOfMonth

Here is the DBFiddle script.


  • Thank you. I will take care of the decimals while asking next time. I did face an error:'Types don't match between the anchor and the recursive part'. So, I had to CAST. The code was perfect. It worked well to calculate the capital balance. Apr 16, 2019 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.