I have a table with a loanee's loan payments. This table has the date the loan was granted, the Amount Granted, Interest Rate on that loan (monthly) and the Dates and Amount paid in each installment.
Loanees do not always pay the same amount, nor do they pay the same day of the month. So the way my company manages interests due, is by calculating the difference in days between payment dates (with the exclusion of the first installment, where the difference in days comes from the date granted and the payment's date). Below, an excerpt from a made up loanee:
| Payment_Number | Date_granted | Amount_Granted | Interest_Rate | Payment_Date | Payment_Amount |
|---|---|---|---|---|---|
| 1 | 2021-01-01 | 100000 | 0.03 | 2021-01-11 | 1,100.00 |
| 2 | 2021-01-01 | 100000 | 0.03 | 2021-01-31 | 2,000.00 |
| 3 | 2021-01-01 | 100000 | 0.03 | 2021-03-02 | 800.00 |
My problem is when I calculate the loan account statement. For the first row I know that Accrued Interests is:
Amount_Granted * (Interest Rate / 30) * (Payment_Date - Date_Granted)
However, I can't do the same for all other installments because, if part of the first one goes to Principal, the new Accrued Interests must be calculated with this new Principal instead of the Amount_Granted like this:
| Payment_Number | Date_granted | Amount_Granted | Interest_Rate | Payment_Date | Payment_Amount | Accrued_interests | Interests_Paid | Principal_Paid | New_Principal |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 2021-01-01 | 100000 | 0.03 | 2021-01-11 | 1,100.00 | 1,000.00 | 1,000.00 | 100.00 | 99,900.00 |
| 2 | 2021-01-01 | 100000 | 0.03 | 2021-01-31 | 2,000.00 | 1,998.00 | 1,998.00 | 2.00 | 99,898.00 |
| 3 | 2021-01-01 | 100000 | 0.03 | 2021-03-02 | 800.00 | 2,996.94 | 800.00 | 0.00 | 99,898.00 |
In Excel, this is a no-brainer. But I'm struggling to do this in SQL. So far I have tried doing a CASE WHEN, as an example:
SELECT
*,
CASE
WHEN Payment_Number = 1
THEN (Amount_Granted * (Interest_Rate / 30) * (Payment_Date - Date_Granted))
ELSE (New_Principal * (Interest_Rate / 30) * (LAG(Payment_Date,1) - Payment_Date))
END AS Accrued_Interests,
CASE
WHEN Accrued_Interests < Payment_Amount
THEN Accrued_Interests
ELSE Payment_Amount
END AS Interests_Paid,
CASE
WHEN Accrued_Interests < Payment_Amount
THEN 0
ELSE Payment_Amount - Interests_Paid
END AS Principal_Paid,
CASE
WHEN Payment_Number = 1
THEN Amount_Granted - Principal_Paid
ELSE New_Principal - LAG(New_Principal,1) - Principal_Paid
END AS New_Principal
FROM
MY_TABLE
But this yields an error because New_Principal has not yet been computed when I'm trying to use it in Accrued Interests. In SQLFiddle I get "Unknown column 'New_Principal' in 'field list'". I also tried with JOINS but no success, mostly because I have little experience with SQL to know how to do this.
Please, I kindly ask for your orientation. Below, the SQLFiddle Link for a reproduceable example:
And the SQLFiddle Link for the error:
PS: I know the LAG function is not complete, I just wanted to simplify things. Also: accrued interests that are not paid in complete go to an interests balance, but for the sake of simplification I am ignoring them.
The numbers in your example are a bit off and don't really follow the formula you specified. I tweaked the numbers to demonstrate how SQL Server can perform the calculations according to your algorithm.
You can use a Recursive CTE to compute each payment according to the dates and updated principal from the previous payment, as in:
with
p as (
select *,
payment_amount - interest_paid as principal_paid,
amount_granted - (payment_amount - interest_paid) as new_principal, cast(null as float) as prev_principal
from (
select *,
case when payment_amount < accrued_interest then payment_amount else accrued_interest end as interest_paid
from (
select
Payment_Number, Date_granted, Amount_Granted,
Interest_Rate, Payment_Date, Payment_Amount,
Amount_Granted * (Interest_Rate / 30) * datediff(day, date_granted, payment_date) as accrued_interest
from account where payment_number = 1
) x
) y
union all
select
Payment_Number, Date_granted, Amount_Granted,
Interest_Rate, Payment_Date, Payment_Amount,
accrued_interest, interest_paid,
payment_amount - interest_paid,
prev_principal - (payment_amount - interest_paid),
prev_principal
from (
select *,
case when payment_amount < accrued_interest then payment_amount else accrued_interest end as interest_paid
from (
select a.Payment_Number, a.Date_granted, a.Amount_Granted,
a.Interest_Rate, a.Payment_Date, a.Payment_Amount,
p.new_principal * (a.Interest_Rate / 30) * datediff(day, a.date_granted, a.payment_date) as accrued_interest,
p.new_principal as prev_principal
from p
join account a on a.payment_number = p.payment_number + 1
) z
) w
)
select * from p;
Result:
Payment_Number Date_granted Amount_Granted Interest_Rate Payment_Date Payment_Amount accrued_interest interest_paid principal_paid new_principal prev_principal
-------------- ------------ -------------- ------------- ------------ -------------- ---------------- ------------- -------------- ------------- --------------
1 2021-01-01 100,000 0.03 2021-01-11 1,100 1,000 1,000 100 99,900 <null>
2 2021-01-01 100,000 0.03 2021-01-31 3,000 2,997 2,997 3 99,897 99,900
3 2021-01-01 100,000 0.03 2021-03-02 2,800 5,993.82 2,800 0 99,897 99,897
See running example at db<>fiddle.