admin管理员组

文章数量:1388812

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?

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?

  • sql
  • accounting
  • amortization
Share Improve this question edited Mar 15 at 0:01 Emmanuel Salau asked Mar 14 at 23:55 Emmanuel SalauEmmanuel Salau 256 bronze badges
Add a comment  | 

2 Answers 2

Reset to default 1

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

ORACLE - you should adjust it to your rdbms ...
Amortization is calculated annualy. You should create a kind of year calendar with numbers of days:

WITH
  cal AS
    ( Select 1 "ID", DATE '2023-12-31' "YR_END", 365 "DAYS" From Dual Union All 
      Select 2 "ID", DATE '2024-12-31' "YR_END", 366 "DAYS" From Dual Union All 
      Select 3 "ID", DATE '2025-12-31' "YR_END", 365 "DAYS" From Dual 
    ),

... SAMPLE DATA

  tbl AS
    ( Select  DATE '2023-02-09' "AMORTIZATION_DATE",
              1826971500.00 "PAR_VALUE",
              511824313.83 "DISCOUNT", 
              1826971500.00 - 511824313.83 "DISCOUNTED_VALUE",
              0.0972 "DISCOUNT_RATE",
              0.135028 "INTEREST_RATE",
              DATE '2025-12-28' "MATURITY_DATE"
      From Dual
    ),

... prepare the data for calculation - get YR_RATIO (asset's days within year / num of days in year ) to be used as multiplier with DISCOUNT_RATE

  grid AS
    ( Select     c.ID, t.PAR_VALUE, 
                 Case When Extract(Year From t.AMORTIZATION_DATE) = Extract(Year From c.YR_END) 
                      Then t.AMORTIZATION_DATE 
                 Else ADD_MONTHS(c.YR_END, -12) + 1 End  "START_DATE",
                 Case When Extract(Year From t.MATURITY_DATE) = Extract(Year From c.YR_END) 
                      Then t.MATURITY_DATE 
                 Else c.YR_END End  "END_DATE", 
                 --
              ( Case When Extract(Year From t.MATURITY_DATE) = Extract(Year From c.YR_END) 
                      Then t.MATURITY_DATE 
                 Else c.YR_END End 
              -  
                 Case When Extract(Year From t.AMORTIZATION_DATE) = Extract(Year From c.YR_END) 
                      Then t.AMORTIZATION_DATE 
                 Else ADD_MONTHS(c.YR_END, -12) End
              )  / c.DAYS "YR_RATIO", 
              t.DISCOUNT_RATE
      From cal   c
      Inner Join tbl t ON( t.AMORTIZATION_DATE <= c.YR_END )
   )

... now you can do the calculation

--      M a i n    S Q L 
        Select ID, START_DATE, END_DATE, PAR_VALUE, Round(YR_RATIO, 6) "YR_RATIO", DISCOUNT_RATE, 
               Round(PAR_VALUE * YR_RATIO * DISCOUNT_RATE, 2) "DISCOUNT"
        From grid
ID START_DATE END_DATE PAR_VALUE YR_RATIO DISCOUNT_RATE DISCOUNT
1 09-FEB-23 31-DEC-23 1826971500 .890411 .0972 158120629.27
2 01-JAN-24 31-DEC-24 1826971500 1 .0972 177581629.8
3 01-JAN-25 28-DEC-25 1826971500 .991781 .0972 176122054.76

If you summ the DISCOUNT column you'll get 511,824,313.83.
NOTE:
Interest rate is not needed here.

fiddle

本文标签： sqlAmortization of Promissory Note discount amountStack Overflow

版权声明：本文标题：sql - Amortization of Promissory Note discount amount - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1744628110a2616378.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。