How can I calculate future interest charges and loan balance

Question

I have a table like below:

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:

CREATE TABLE #Test (
     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:

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
(
SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY Account_Number ORDER BY LastDateOfMonth) AS Rn
FROM   #Test
)
, Recursive_Interest AS
(
SELECT * 
FROM   Added_Row_Numbers 
WHERE  Rn =1
UNION ALL
SELECT ARN.Account_Number,
       ARN.Interest_Rate,
       ARN.LastDateOfMonth,
       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,
       ARN.Payment,
       ARN.Rn
FROM   Recursive_Interest AS RI 
       INNER JOIN
       Added_Row_Numbers AS ARN
       ON ARN.Rn = RI.Rn + 1 
          AND 
          RI.Account_Number = ARN.Account_Number
)
SELECT RI.Account_Number,
       RI.Interest_Rate,
       RI.LastDateOfMonth,
       RI.Capital_Balance_Calculated,
       RI.Interest,
       RI.Payment
FROM   Recursive_Interest AS RI
ORDER BY Account_Number, LastDateOfMonth

Here is the DBFiddle script.

HTH