sqlaccountingamortization

Amortization of Promissory Note discount amount


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?


Solution

  • 2nd Answer:
    It looks like there are two issues with the calculations:

    1. The total discount calculated (511824313.83) seems to be short for one day and should be 512310838.85 - most probably the first day wasn't taken into account. Here is the sample where TOTAL_1 should be the result instead of TOTAL_2.
    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
    1. Monthly calculation - after a lot of testings and variations I couldn't get the formula to get the results provided in your question. There is just a possibility that your column AMORTIZATION_DATE containes time component too. This, if it is true, could give the result as provided. Using same discount per day math as in above sample and same one day short calculation for February 2023. the formula should have number of days of discounting not to be 19 but 18.501717582.... instead. That actualy could be true if your system calculates days as clear END_DATE of month (without time component) minus START_DATE that has a time component - such substraction could result with decimal number of days. Sample below:
    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

    fiddle