# How can I calculate future interest charges and loan balance

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:

• I tried using While loop and joined on the same table based on row_number but it got way too complicated. Commented Apr 15, 2019 at 14:20
• 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. Commented 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 ) Commented 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? Commented Apr 15, 2019 at 16:13

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

• 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. Commented Apr 16, 2019 at 15:41