I am trying to understand how an application has amortized the discount (PAR value - discounted value) of a promissory note purchased on 9 Feb 2023. The note has a PAR value of 1,826,971,500.00, a discounted value of 1,315,147,186.17 and the difference (discount) is 511,824,313.83. The effective interest rate is 13.5028% and the discount rate is 9.72%.
The system has amortized the discount monthly as follows:
AMORTIZATION DATE DISCOUNT AMORTIZATION MATURITY DATE
02/09/2023 511824313.83 12/28/2025
03/07/2023 -9001548.39 12/28/2025
03/31/2023 -14080392.04 12/28/2025
04/28/2023 -13775775.36 12/28/2025
05/31/2023 -14391250.04 12/28/2025
06/30/2023 -14079908.22 12/28/2025
07/31/2023 -14708970.96 12/28/2025
08/31/2023 -14873114.19 12/28/2025
09/29/2023 -14551347.67 12/28/2025
10/31/2023 -15201473.40 12/28/2025
11/30/2023 -14872603.13 12/28/2025
12/31/2023 -15537081.92 12/28/2025
01/31/2024 -15710466.37 12/28/2025
02/29/2024 -14855567.70 12/28/2025
03/29/2024 -16051564.84 12/28/2025
04/30/2024 -15704303.60 12/28/2025
05/31/2024 -16405941.14 12/28/2025
06/28/2024 -16051013.29 12/28/2025
07/31/2024 -16768141.14 12/28/2025
08/30/2024 -16955263.46 12/28/2025
09/30/2024 -16588451.51 12/28/2025
10/31/2024 -17329591.05 12/28/2025
11/29/2024 -16954680.86 12/28/2025
12/31/2024 -17712182.81 12/28/2025
01/31/2025 -17909840.06 12/28/2025
02/28/2025 -16348354.59 12/28/2025
Does anyone have any clue how the system has done the amortization and how I can replicate this in sql?
2nd Answer:
It looks like there are two issues with the calculations:
WITH
params AS
( Select 1826971500.00 "PAR_VALUE", 0.0972 "DISCOUNT_RATE"
From Dual
),
cal AS
( Select 1 "ID", DATE '2023-01-01' "FIRST_DAY_OF_YEAR", DATE '2023-12-31' "LAST_DAY_OF_YEAR" From Dual Union All
Select 2, DATE '2024-01-01', DATE '2024-12-31' From Dual Union All
Select 3, DATE '2025-01-01', DATE '2025-12-31' From Dual
),
grid AS
( Select Greatest(c.FIRST_DAY_OF_YEAR, DATE '2023-02-09') "START_DATE",
Least(DATE '2025-12-28', c.LAST_DAY_OF_YEAR) "END_DATE",
--
c.LAST_DAY_OF_YEAR - c.FIRST_DAY_OF_YEAR + 1 "TOTAL_DAYS_IN_YEAR",
p.PAR_VALUE * DISCOUNT_RATE / (c.LAST_DAY_OF_YEAR - c.FIRST_DAY_OF_YEAR + 1) "DISCOUNT_PER_DAY",
--
Least(DATE '2025-12-28', c.LAST_DAY_OF_YEAR) - Greatest(c.FIRST_DAY_OF_YEAR, DATE '2023-02-09') + 1 "DAYS_OF_AMORTIZATION_1",
Least(DATE '2025-12-28', c.LAST_DAY_OF_YEAR) - Greatest(c.FIRST_DAY_OF_YEAR, DATE '2023-02-09')
+ CASE WHEN c.FIRST_DAY_OF_YEAR < DATE '2023-02-09'
THEN 0
ELSE 1
END "DAYS_OF_AMORTIZATION_2",
p.PAR_VALUE, p.DISCOUNT_RATE
From cal c
Inner Join params p ON( 1 = 1 )
Order By ID
)
Select START_DATE, END_DATE,
Round(( PAR_VALUE * DISCOUNT_RATE / TOTAL_DAYS_IN_YEAR ) * DAYS_OF_AMORTIZATION_1, 2) "DISCOUNT_1",
Round(Sum( ( PAR_VALUE * DISCOUNT_RATE / TOTAL_DAYS_IN_YEAR ) * DAYS_OF_AMORTIZATION_1 ) Over(), 2) "TOTAL_1",
Round(( PAR_VALUE * DISCOUNT_RATE / TOTAL_DAYS_IN_YEAR ) * DAYS_OF_AMORTIZATION_2, 2) "DISCOUNT_2",
Round(Sum( ( PAR_VALUE * DISCOUNT_RATE / TOTAL_DAYS_IN_YEAR ) * DAYS_OF_AMORTIZATION_2 ) Over(), 2) "TOTAL_2"
From grid g
START_DATE | END_DATE | DISCOUNT_1 | TOTAL_1 | DISCOUNT_2 | TOTAL_2 |
---|---|---|---|---|---|
09-FEB-23 | 31-DEC-23 | 158607154.29 | 512310838.85 | 158120629.27 | 511824313.83 |
01-JAN-24 | 31-DEC-24 | 177581629.8 | 512310838.85 | 177581629.8 | 511824313.83 |
01-JAN-25 | 28-DEC-25 | 176122054.76 | 512310838.85 | 176122054.76 | 511824313.83 |
WITH
params AS
( Select 1826971500.00 "PAR_VALUE", 0.0972 "DISCOUNT_RATE", 9001548.39 "SYSTEM_DISCOUNT"
From Dual
),
cal AS
( Select 1 "ID", DATE '2023-02-01' "FIRST_DAY_OF_MONTH", DATE '2023-02-28' "LAST_DAY_OF_MONTH" From Dual
),
grid AS
( Select Greatest(c.FIRST_DAY_OF_MONTH, DATE '2023-02-09') "START_DATE",
Least(DATE '2025-12-28', c.LAST_DAY_OF_MONTH) "END_DATE",
--
365 "TOTAL_DAYS_IN_YEAR",
p.PAR_VALUE * DISCOUNT_RATE / 365 "DISCOUNT_PER_DAY",
--
Least(DATE '2025-12-28', c.LAST_DAY_OF_MONTH) - Greatest(c.FIRST_DAY_OF_MONTH, DATE '2023-02-09') "DAYS_OF_AMORTIZATION",
p.PAR_VALUE, p.DISCOUNT_RATE, p.SYSTEM_DISCOUNT
From cal c
Inner Join params p ON( 1 = 1 )
Order By ID
)
Select START_DATE, END_DATE, DISCOUNT_PER_DAY, DAYS_OF_AMORTIZATION,
Round(( PAR_VALUE * DISCOUNT_RATE / TOTAL_DAYS_IN_YEAR ) * DAYS_OF_AMORTIZATION, 2) "DISCOUNT",
SYSTEM_DISCOUNT / DISCOUNT_PER_DAY "DAYS_REALY",
END_DATE - (SYSTEM_DISCOUNT / DISCOUNT_PER_DAY) "START_DATE_AND_TIME",
To_Char(END_DATE - (SYSTEM_DISCOUNT / DISCOUNT_PER_DAY), 'yyyy-mm-dd hh24:mi:ss') "START_DATE_AND_TIME_STR"
From grid g
START_DATE | END_DATE | DISCOUNT_PER_DAY | DAYS_OF_AMORTIZATION | DISCOUNT | DAYS_REALY | START_DATE_AND_TIME | START_DATE_AND_TIME_STR |
---|---|---|---|---|---|---|---|
09-FEB-23 | 28-FEB-23 | 486525.0131506849315068493150684931506849 | 19 | 9243975.25 | 18.50171758222032040388447882124347976899 | 09-FEB-23 | 2023-02-09 11:57:32 |