September 3, 2021 at 7:46 am
Hello Friends of SQLServerCentral!
I am trying to create an amortization table in a view (this is necessary in order to make sure that it refreshes with the live data).
I am struggling with the code in the sense that I am not too sure how to make the opening balance of a row the same as the closing balance of a previous row. Also, I need the closing balance of a row to be equal to the opening balance in that row + the payment in that row. The initial opening balance is identified with an Entry Type ="@" and the payments (InterestAmount + CapitalAmount) that reduce the opening balance and closing balances are identified with an Entry Type="I".
This is the code that I have been using:
SELECT DISTINCT
t1.ID, t1.DealID, dbo.UniverseDate(t1.ValueDate) AS EffectiveDate, CASE WHEN A.TotalAmount IS NOT NULL THEN A.TotalAmount ELSE A.TotalAmount + SUM((CASE WHEN B.Payment IS NULL THEN 0 ELSE B.Payment END))
OVER (PARTITION BY t1.DealID
ORDER BY t1.ID) END AS Capital, LAG(A.TotalAmount + (CASE WHEN B.Payment IS NULL THEN 0 ELSE B.Payment END), 1) OVER (PARTITION BY t1.DealID
ORDER BY t1.ID) AS OB, CASE WHEN B.InterestAmount IS NULL THEN 0 ELSE B.InterestAmount END AS InterestAmount, CASE WHEN B.Payment IS NULL THEN 0 ELSE B.Payment END AS Payment, CASE WHEN A.TotalAmount IS NULL
THEN LAG(A.TotalAmount + (CASE WHEN B.Payment IS NULL THEN 0 ELSE B.Payment END), 1) OVER (PARTITION BY t1.DealID
ORDER BY t1.ID) ELSE A.TotalAmount + (CASE WHEN B.Payment IS NULL THEN 0 ELSE B.Payment END) END AS ClosingBalance
FROM dbo.Instalment AS t1 LEFT OUTER JOIN
(SELECT ID, dbo.UniverseDate(ValueDate) AS EffDate, CAST(TotalAmount AS int) AS TotalAmount
FROM dbo.Instalment AS t2
WHERE (EntryType = '@')) AS A ON A.ID = t1.ID LEFT OUTER JOIN
(SELECT ID, dbo.UniverseDate(ValueDate) AS EffDate, CAST(ABS(InterestAmount) AS int) InterestAmount, CAST(InterestAmount + CapitalAmount AS int) AS Payment
FROM dbo.Instalment AS t3
WHERE (EntryType = 'I')) AS B ON B.ID = t1.ID
WHERE t1.EntryType<>'C'
However I am not getting the desired result. The result that I am getting is attached in the excel file as the"CURRENT OUTPUT" sheet and the result that I need to produce is in the "DESIRED OUTPUT" sheet.
I am assuming that this will work using a while loop but I am unsure of where to start?
Any help will be much appreciated. Thanks so much friends!
September 3, 2021 at 1:52 pm
I've extensively worked on financial calculations, and based on my experience I would not suggest using the WHILE loop for the amortization calculation if the number of transactions to be amortized is huge.
I see you've used the LAG() function. You can indeed get the previous closing using the LAG() function. You can get the Initial Closing using the FIRST_VALUE() function.
September 3, 2021 at 3:20 pm
It appears you're attempting to create "running totals" using the LAG function (which doesn't work). You could maybe try SUM(...) OVER instead
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply