Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server constant daily effective yield amortization


BONDAMORT

Updated: 13 May 2012


Use the table-valued function BONDAMORT to generate a bond amortization schedule, given the settlement and maturity dates of the bond, the clean price, the face amount, the redemption amount, and the coupon rate. Additionally, if the bond has an odd first or an odd last period, that information can be included in the amortization schedule. The amortization schedule includes the date, the number of days to maturity, the beginning book value, the daily coupon amount, the daily amortization amount, the daily effective amount (which is the daily coupon + the daily amortization), and the ending book balance. The amortization follows the coupon accruals, so there might be some days with no coupon accruals and no amortization, as well as some days with multiple days accrued and therefore multiple amortization.
Syntax
SELECT * FROM [wctFinancial].[wct].[BONDAMORT](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@FaceAmount, float,>
 ,<@CleanPrice, float,>
 ,<@Redemption, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@IssueDate, datetime,>
 ,<@FirstInterestDate, datetime,>
 ,<@LastInterestDate, datetime,>
 ,<@Holidays, nvarchar(max),>)
Arguments
@Settlement
the settlement date of the transaction. @Settlement is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Maturity
the maturity date for the financial instrument. @Maturity is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Rate
the coupon rate, as a decimal, for the financial instrument. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@FaceAmount
the face (or notional) amount of the financial instrument. @FaceAmount is not necessarily the same as par value. For example, if you bought $1 million on US Treasury Bonds, the @FaceAmount would be $1 million.  @FaceAmount is an expression of type float or of a type that can be implicitly converted to float.
@CleanPrice
the initial value of the financial instrument, exclusive of any accrued interest. @CleanPrice should be expressed in relation to @FaceAmount. @CleanPrice is an expression of type float or of a type that can be implicitly converted to float.
@Redemption
the redemption value of the financial instrument expressed in relation to the @FaceAmount. @Redemption is an expression of type float or of a type that can be implicitly converted to float.
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for monthly, @Frequency = 12. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
 
@Basis
the day-count convention used in the calculation of the accrued coupon interest. @Basis is an expression of the character string data type category.
 

@Basis
Day count basis
 
0 or omitted
US (NASD) 30/360
1
Actual/Actual
2
Actual/360
3
Actual/365
4
European 30/360
5
30/360 ISDA
6
NL/ACT
7
NL/365
8
NL/360
9
A/364
10
US (NASD) 30/360 non-end-of-month
11
Actual/Actual non-end-of-month
12
Actual/360 non-end-of-month
13
Actual/365 non-end-of-month
14
European 30/360 non-end-of-month
15
30/360 ISDA non-end-of-month
16
NL/ACT non-end-of-month
17
NL/365 non-end-of-month
18
NL/360 non-end-of-month
19
A/364 non-end-of-month
20
BUS/252
21
Actual/ISDA
22
Actual/ISMA
23
Actual/365L
24
Actual/AFB
25
30E+360
30
BUS/252 non-end-of-month

@IssueDate
the issue date of the security; the date from which the security starts accruing interest. @IssueDate is an expression of type datetime or of a type that can be implicitly converted to datetime
@FirstInterestDate
the first coupon date of the security. The period from the issue date until the first coupon date defines the odd first interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency in relation to the @LastInterestDate (if entered) or @Maturity. @FirstInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@LastInterestDate
the last coupon date of the security prior to maturity date, if the last coupon period is an odd period. The period from the last interest date date until the maturity date defines the odd last interest period. All previous coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @LastInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Holidays
a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NDB to create an appropriately formatted string.
Return Types
RETURNS TABLE (
      [amort_date] [datetime] NULL,
      [dtm] [int] NULL,
      [begin_book_val] [float] NULL,
      [dly_coup] [float] NULL,
      [dly_eff_rate] [float] NULL,
      [dly_amort] [float] NULL,
      [end_book_val] [float] NULL
)

Column
Column Description
amort_date
The date of the amortization. There will be an amort_date row returned for each day from the settlement date through to the maturity date.
dtm
The number of days-to-maturity using the specified day-count convention.
begin_book_val
The beginning book value on the amort_date. begin_book_val equals end_book_val from the previous amort_date
dly_coup
The daily coupon accrual based on the day-count convention, face amount, and coupon rate.
dly_eff_rate
The daily income recognized based upon the constant daily effective rate (calculated by the tvf) and the begin_book_val. You can use the AMORTRATE to calculate the constant daily effective rate, or you simply divided the dly_eff_rate by the begin_book_val.
dly_amort
The daily amortization amount. The difference between the dly_eff_rate and the dly_coup.
end_book_val
The begin_bool_val plus dly_amort.

Remarks
·         @Settlement cannot be NULL
·         @Maturity cannot be NULL
·         @Settlement must be less than @Maturity
·         @FaceAmount, @CleanPrice, and @Redemption must all have the same sign.
·         If @Redemption is NULL, then @Redemption = @FaceAmount
·         If @Frequency is NULL, then @Frequency = 2
·         If @Basis is NULL, then @Basis = 0
·         If @FirstInterestDate is NOT NULL, then @IssueDate cannot be NULL
·         If @FirstInterestDate is NOT NULL, then @FirstInterestDate must be greater than @IssueDate
·         If @LastInterestDate is NOT NULL, The @LastInterestDate must be less than @Maturity
·         If @LastInterestDate is NOT NULL and @FirstInterestDate is NOT NULL, then @FirstInterestDate must be less than @LastInterestDate.
Examples
All the examples have been reformatted to make them easier to read.
We buy 1,000,000 in face value of a bond on 2012-05-03 at a price of 999000. The bond matures on 2012-06-30 and accrues interest using the Actual/365 day-count method. The interest rate is 5%
SELECT *
FROM wct.BONDAMORT(
 '2012-05-03'           --Settlement
,'2012-06-30'           --Maturity
,0.05                   --Rate
,1000000.00             --FaceAmount
,999000.00              --CleanPrice
,NULL                   --Redemption
,2                      --Frequency
,'3'                    --Basis
,NULL                   --IssueDate
,NULL                   --FirstInterestDate
,NULL                   --LastInterestDate
,NULL                   --Holidays 
)
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
2012-05-03
58
0.0000
0.0000
0.0000
0.0000
999000.0000
2012-05-04
57
999000.0000
136.9863
154.1520
17.1657
999017.1657
2012-05-05
56
999017.1657
136.9863
154.1546
17.1683
999034.3340
2012-05-06
55
999034.3340
136.9863
154.1573
17.1710
999051.5050
2012-05-07
54
999051.5050
136.9863
154.1599
17.1736
999068.6786
2012-05-08
53
999068.6786
136.9863
154.1626
17.1763
999085.8548
2012-05-09
52
999085.8548
136.9863
154.1652
17.1789
999103.0338
2012-05-10
51
999103.0338
136.9863
154.1679
17.1816
999120.2153
2012-05-11
50
999120.2153
136.9863
154.1705
17.1842
999137.3996
2012-05-12
49
999137.3996
136.9863
154.1732
17.1869
999154.5864
2012-05-13
48
999154.5864
136.9863
154.1758
17.1895
999171.7760
2012-05-14
47
999171.7760
136.9863
154.1785
17.1922
999188.9681
2012-05-15
46
999188.9681
136.9863
154.1811
17.1948
999206.1630
2012-05-16
45
999206.1630
136.9863
154.1838
17.1975
999223.3604
2012-05-17
44
999223.3604
136.9863
154.1864
17.2001
999240.5606
2012-05-18
43
999240.5606
136.9863
154.1891
17.2028
999257.7634
2012-05-19
42
999257.7634
136.9863
154.1917
17.2054
999274.9688
2012-05-20
41
999274.9688
136.9863
154.1944
17.2081
999292.1769
2012-05-21
40
999292.1769
136.9863
154.1971
17.2108
999309.3877
2012-05-22
39
999309.3877
136.9863
154.1997
17.2134
999326.6011
2012-05-23
38
999326.6011
136.9863
154.2024
17.2161
999343.8172
2012-05-24
37
999343.8172
136.9863
154.2050
17.2187
999361.0359
2012-05-25
36
999361.0359
136.9863
154.2077
17.2214
999378.2573
2012-05-26
35
999378.2573
136.9863
154.2103
17.2240
999395.4813
2012-05-27
34
999395.4813
136.9863
154.2130
17.2267
999412.7080
2012-05-28
33
999412.7080
136.9863
154.2157
17.2294
999429.9374
2012-05-29
32
999429.9374
136.9863
154.2183
17.2320
999447.1694
2012-05-30
31
999447.1694
136.9863
154.2210
17.2347
999464.4040
2012-05-31
30
999464.4040
136.9863
154.2236
17.2373
999481.6414
2012-06-01
29
999481.6414
136.9863
154.2263
17.2400
999498.8814
2012-06-02
28
999498.8814
136.9863
154.2290
17.2427
999516.1240
2012-06-03
27
999516.1240
136.9863
154.2316
17.2453
999533.3693
2012-06-04
26
999533.3693
136.9863
154.2343
17.2480
999550.6173
2012-06-05
25
999550.6173
136.9863
154.2369
17.2506
999567.8679
2012-06-06
24
999567.8679
136.9863
154.2396
17.2533
999585.1212
2012-06-07
23
999585.1212
136.9863
154.2423
17.2560
999602.3772
2012-06-08
22
999602.3772
136.9863
154.2449
17.2586
999619.6358
2012-06-09
21
999619.6358
136.9863
154.2476
17.2613
999636.8971
2012-06-10
20
999636.8971
136.9863
154.2503
17.2639
999654.1611
2012-06-11
19
999654.1611
136.9863
154.2529
17.2666
999671.4277
2012-06-12
18
999671.4277
136.9863
154.2556
17.2693
999688.6969
2012-06-13
17
999688.6969
136.9863
154.2582
17.2719
999705.9689
2012-06-14
16
999705.9689
136.9863
154.2609
17.2746
999723.2435
2012-06-15
15
999723.2435
136.9863
154.2636
17.2773
999740.5208
2012-06-16
14
999740.5208
136.9863
154.2662
17.2799
999757.8007
2012-06-17
13
999757.8007
136.9863
154.2689
17.2826
999775.0833
2012-06-18
12
999775.0833
136.9863
154.2716
17.2853
999792.3686
2012-06-19
11
999792.3686
136.9863
154.2742
17.2879
999809.6565
2012-06-20
10
999809.6565
136.9863
154.2769
17.2906
999826.9471
2012-06-21
9
999826.9471
136.9863
154.2796
17.2933
999844.2404
2012-06-22
8
999844.2404
136.9863
154.2822
17.2959
999861.5363
2012-06-23
7
999861.5363
136.9863
154.2849
17.2986
999878.8350
2012-06-24
6
999878.8350
136.9863
154.2876
17.3013
999896.1362
2012-06-25
5
999896.1362
136.9863
154.2903
17.3040
999913.4402
2012-06-26
4
999913.4402
136.9863
154.2929
17.3066
999930.7468
2012-06-27
3
999930.7468
136.9863
154.2956
17.3093
999948.0561
2012-06-28
2
999948.0561
136.9863
154.2983
17.3120
999965.3681
2012-06-29
1
999965.3681
136.9863
154.3009
17.3146
999982.6827
2012-06-30
0
999982.6827
136.9863
154.3036
17.3173
1000000.0000

In this example we will change the basis to 0, to reflect the US 30/360 day-count convention.
SELECT *
FROM wct.BONDAMORT(
       '2012-05-03'           --Settlement
      ,'2012-06-30'           --Maturity
      ,0.05                   --Rate
      ,1000000.00             --FaceAmount
      ,999000.00              --CleanPrice
      ,NULL                   --Redemption
      ,2                      --Frequency
      ,'0'                    --Basis
      ,NULL                   --IssueDate
      ,NULL                   --FirstInterestDate
      ,NULL                   --LastInterestDate
      ,NULL                   --Holidays 
      )

This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
2012-05-03
57
0.0000
0.0000
0.0000
0.0000
999000.0000
2012-05-04
56
999000.0000
138.8889
156.3560
17.4671
999017.4671
2012-05-05
55
999017.4671
138.8889
156.3587
17.4698
999034.9369
2012-05-06
54
999034.9369
138.8889
156.3614
17.4726
999052.4095
2012-05-07
53
999052.4095
138.8889
156.3642
17.4753
999069.8848
2012-05-08
52
999069.8848
138.8889
156.3669
17.4780
999087.3628
2012-05-09
51
999087.3628
138.8889
156.3697
17.4808
999104.8436
2012-05-10
50
999104.8436
138.8889
156.3724
17.4835
999122.3271
2012-05-11
49
999122.3271
138.8889
156.3751
17.4862
999139.8133
2012-05-12
48
999139.8133
138.8889
156.3779
17.4890
999157.3023
2012-05-13
47
999157.3023
138.8889
156.3806
17.4917
999174.7940
2012-05-14
46
999174.7940
138.8889
156.3833
17.4945
999192.2884
2012-05-15
45
999192.2884
138.8889
156.3861
17.4972
999209.7856
2012-05-16
44
999209.7856
138.8889
156.3888
17.4999
999227.2856
2012-05-17
43
999227.2856
138.8889
156.3916
17.5027
999244.7882
2012-05-18
42
999244.7882
138.8889
156.3943
17.5054
999262.2936
2012-05-19
41
999262.2936
138.8889
156.3970
17.5081
999279.8018
2012-05-20
40
999279.8018
138.8889
156.3998
17.5109
999297.3127
2012-05-21
39
999297.3127
138.8889
156.4025
17.5136
999314.8263
2012-05-22
38
999314.8263
138.8889
156.4053
17.5164
999332.3427
2012-05-23
37
999332.3427
138.8889
156.4080
17.5191
999349.8618
2012-05-24
36
999349.8618
138.8889
156.4107
17.5219
999367.3836
2012-05-25
35
999367.3836
138.8889
156.4135
17.5246
999384.9082
2012-05-26
34
999384.9082
138.8889
156.4162
17.5273
999402.4355
2012-05-27
33
999402.4355
138.8889
156.4190
17.5301
999419.9656
2012-05-28
32
999419.9656
138.8889
156.4217
17.5328
999437.4984
2012-05-29
31
999437.4984
138.8889
156.4245
17.5356
999455.0340
2012-05-30
30
999455.0340
138.8889
156.4272
17.5383
999472.5723
2012-05-31
30
999472.5723
0.0000
0.0000
0.0000
999472.5723
2012-06-01
29
999472.5723
138.8889
156.4299
17.5411
999490.1134
2012-06-02
28
999490.1134
138.8889
156.4327
17.5438
999507.6572
2012-06-03
27
999507.6572
138.8889
156.4354
17.5465
999525.2037
2012-06-04
26
999525.2037
138.8889
156.4382
17.5493
999542.7530
2012-06-05
25
999542.7530
138.8889
156.4409
17.5520
999560.3051
2012-06-06
24
999560.3051
138.8889
156.4437
17.5548
999577.8599
2012-06-07
23
999577.8599
138.8889
156.4464
17.5575
999595.4174
2012-06-08
22
999595.4174
138.8889
156.4492
17.5603
999612.9777
2012-06-09
21
999612.9777
138.8889
156.4519
17.5630
999630.5407
2012-06-10
20
999630.5407
138.8889
156.4547
17.5658
999648.1065
2012-06-11
19
999648.1065
138.8889
156.4574
17.5685
999665.6750
2012-06-12
18
999665.6750
138.8889
156.4602
17.5713
999683.2463
2012-06-13
17
999683.2463
138.8889
156.4629
17.5740
999700.8203
2012-06-14
16
999700.8203
138.8889
156.4657
17.5768
999718.3971
2012-06-15
15
999718.3971
138.8889
156.4684
17.5795
999735.9766
2012-06-16
14
999735.9766
138.8889
156.4712
17.5823
999753.5589
2012-06-17
13
999753.5589
138.8889
156.4739
17.5850
999771.1439
2012-06-18
12
999771.1439
138.8889
156.4767
17.5878
999788.7317
2012-06-19
11
999788.7317
138.8889
156.4794
17.5905
999806.3223
2012-06-20
10
999806.3223
138.8889
156.4822
17.5933
999823.9156
2012-06-21
9
999823.9156
138.8889
156.4849
17.5960
999841.5116
2012-06-22
8
999841.5116
138.8889
156.4877
17.5988
999859.1104
2012-06-23
7
999859.1104
138.8889
156.4904
17.6016
999876.7120
2012-06-24
6
999876.7120
138.8889
156.4932
17.6043
999894.3163
2012-06-25
5
999894.3163
138.8889
156.4960
17.6071
999911.9233
2012-06-26
4
999911.9233
138.8889
156.4987
17.6098
999929.5332
2012-06-27
3
999929.5332
138.8889
156.5015
17.6126
999947.1457
2012-06-28
2
999947.1457
138.8889
156.5042
17.6153
999964.7611
2012-06-29
1
999964.7611
138.8889
156.5070
17.6181
999982.3792
2012-06-30
0
999982.3792
138.8889
156.5097
17.6208
1000000.0000

Notice the row for the 31-May-12. There is no daily coupon accrual, reflecting the market convention for this day-count convention. Since there is no daily coupon accrual, there is no need to adjust the interest for this day and there is no daily amortization. Let’s look at what happens with a US 30/360 bond over a February month end.
SELECT *
FROM wct.BONDAMORT(
       '2012-02-15'           --Settlement
      ,'2012-03-15'           --Maturity
      ,0.05                   --Rate
      ,1000000.00             --FaceAmount
      ,999000.00              --CleanPrice
      ,NULL                   --Redemption
      ,2                      --Frequency
      ,'0'                    --Basis
      ,NULL                   --IssueDate
      ,NULL                   --FirstInterestDate
      ,NULL                   --LastInterestDate
      ,NULL                   --Holidays 
      )
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
2012-02-15
30
0.0000
0.0000
0.0000
0.0000
999000.0000
2012-02-16
29
999000.0000
138.8889
172.1390
33.2501
999033.2501
2012-02-17
28
999033.2501
138.8889
172.1447
33.2559
999066.5060
2012-02-18
27
999066.5060
138.8889
172.1505
33.2616
999099.7676
2012-02-19
26
999099.7676
138.8889
172.1562
33.2673
999133.0349
2012-02-20
25
999133.0349
138.8889
172.1619
33.2730
999166.3079
2012-02-21
24
999166.3079
138.8889
172.1677
33.2788
999199.5867
2012-02-22
23
999199.5867
138.8889
172.1734
33.2845
999232.8712
2012-02-23
22
999232.8712
138.8889
172.1791
33.2903
999266.1615
2012-02-24
21
999266.1615
138.8889
172.1849
33.2960
999299.4575
2012-02-25
20
999299.4575
138.8889
172.1906
33.3017
999332.7592
2012-02-26
19
999332.7592
138.8889
172.1964
33.3075
999366.0666
2012-02-27
18
999366.0666
138.8889
172.2021
33.3132
999399.3798
2012-02-28
17
999399.3798
138.8889
172.2078
33.3189
999432.6988
2012-02-29
15
999432.6988
277.7778
344.4329
66.6551
999499.3539
2012-03-01
14
999499.3539
138.8889
172.2251
33.3362
999532.6901
2012-03-02
13
999532.6901
138.8889
172.2308
33.3419
999566.0320
2012-03-03
12
999566.0320
138.8889
172.2365
33.3477
999599.3796
2012-03-04
11
999599.3796
138.8889
172.2423
33.3534
999632.7330
2012-03-05
10
999632.7330
138.8889
172.2480
33.3592
999666.0922
2012-03-06
9
999666.0922
138.8889
172.2538
33.3649
999699.4571
2012-03-07
8
999699.4571
138.8889
172.2595
33.3706
999732.8277
2012-03-08
7
999732.8277
138.8889
172.2653
33.3764
999766.2041
2012-03-09
6
999766.2041
138.8889
172.2710
33.3821
999799.5863
2012-03-10
5
999799.5863
138.8889
172.2768
33.3879
999832.9742
2012-03-11
4
999832.9742
138.8889
172.2825
33.3937
999866.3678
2012-03-12
3
999866.3678
138.8889
172.2883
33.3994
999899.7672
2012-03-13
2
999899.7672
138.8889
172.2941
33.4052
999933.1724
2012-03-14
1
999933.1724
138.8889
172.2998
33.4109
999966.5833
2012-03-15
0
999966.5833
138.8889
172.3056
33.4167
1000000.0000

 
Notice that for 2012-02-29 the daily coupon is actually two days of coupon interest (again, reflecting market practice). Thus, the daily amortization also needs to reflect two days of amortization.
Here’s an example for NL/365 in which Feb-29 is not included in the date calculations (NL meaning No Leap year).
SELECT *
FROM wct.BONDAMORT(
       '2012-02-15'           --Settlement
      ,'2012-03-15'           --Maturity
      ,0.05                   --Rate
      ,1000000.00             --FaceAmount
      ,999000.00              --CleanPrice
      ,NULL                   --Redemption
      ,2                      --Frequency
      ,'7'                    --Basis
      ,NULL                   --IssueDate
      ,NULL                   --FirstInterestDate
      ,NULL                   --LastInterestDate
      ,NULL                   --Holidays 
      )
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
2012-02-15
28
0.0000
0.0000
0.0000
0.0000
999000.0000
2012-02-16
27
999000.0000
136.9863
172.6173
35.6310
999035.6310
2012-02-17
26
999035.6310
136.9863
172.6235
35.6372
999071.2682
2012-02-18
25
999071.2682
136.9863
172.6297
35.6434
999106.9116
2012-02-19
24
999106.9116
136.9863
172.6358
35.6495
999142.5611
2012-02-20
23
999142.5611
136.9863
172.6420
35.6557
999178.2168
2012-02-21
22
999178.2168
136.9863
172.6481
35.6618
999213.8786
2012-02-22
21
999213.8786
136.9863
172.6543
35.6680
999249.5466
2012-02-23
20
999249.5466
136.9863
172.6605
35.6742
999285.2208
2012-02-24
19
999285.2208
136.9863
172.6666
35.6803
999320.9011
2012-02-25
18
999320.9011
136.9863
172.6728
35.6865
999356.5876
2012-02-26
17
999356.5876
136.9863
172.6790
35.6927
999392.2803
2012-02-27
16
999392.2803
136.9863
172.6851
35.6988
999427.9791
2012-02-28
15
999427.9791
136.9863
172.6913
35.7050
999463.6841
2012-02-29
14
999463.6841
0.0000
0.0000
0.0000
999463.6841
2012-03-01
14
999463.6841
136.9863
172.6975
35.7112
999499.3953
2012-03-02
13
999499.3953
136.9863
172.7036
35.7173
999535.1126
2012-03-03
12
999535.1126
136.9863
172.7098
35.7235
999570.8361
2012-03-04
11
999570.8361
136.9863
172.7160
35.7297
999606.5658
2012-03-05
10
999606.5658
136.9863
172.7222
35.7359
999642.3017
2012-03-06
9
999642.3017
136.9863
172.7283
35.7420
999678.0437
2012-03-07
8
999678.0437
136.9863
172.7345
35.7482
999713.7919
2012-03-08
7
999713.7919
136.9863
172.7407
35.7544
999749.5463
2012-03-09
6
999749.5463
136.9863
172.7469
35.7606
999785.3068
2012-03-10
5
999785.3068
136.9863
172.7530
35.7667
999821.0736
2012-03-11
4
999821.0736
136.9863
172.7592
35.7729
999856.8465
2012-03-12
3
999856.8465
136.9863
172.7654
35.7791
999892.6256
2012-03-13
2
999892.6256
136.9863
172.7716
35.7853
999928.4109
2012-03-14
1
999928.4109
136.9863
172.7778
35.7915
999964.2023
2012-03-15
0
999964.2023
136.9863
172.7840
35.7977
1000000.0000

On 29-Feb-12 there is no coupon accrual and therefore these is no amortization.
Let’s look at a bond which accrues on an actual/actual basis.
SELECT *
FROM wct.BONDAMORT(
       '2012-03-10'           --Settlement
      ,'2022-03-15'           --Maturity
      ,0.05                   --Rate
      ,1000000.00             --FaceAmount
      ,990000.00              --CleanPrice
      ,NULL                   --Redemption
      ,2                      --Frequency
      ,'1'                    --Basis
      ,NULL                   --IssueDate
      ,NULL                   --FirstInterestDate
      ,NULL                   --LastInterestDate
      ,NULL                   --Holidays 
      )
WHERE amort_date < '2012-03-20'
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
amort_rate
2012-03-10
3657
0.0000
0.0000
0.0000
0.0000
990000.0000
0.000000000
2012-03-11
3656
990000.0000
137.3626
138.9934
1.6308
990001.6308
0.000140397
2012-03-12
3655
990001.6308
137.3626
138.9936
1.6310
990003.2618
0.000140397
2012-03-13
3654
990003.2618
137.3626
138.9939
1.6312
990004.8930
0.000140397
2012-03-14
3653
990004.8930
137.3626
138.9941
1.6315
990006.5244
0.000140397
2012-03-15
3652
990006.5244
137.3626
138.9943
1.6317
990008.1561
0.000140397
2012-03-16
3651
990008.1561
135.8696
138.9945
3.1250
990011.2811
0.000140397
2012-03-17
3650
990011.2811
135.8696
138.9950
3.1254
990014.4065
0.000140397
2012-03-18
3649
990014.4065
135.8696
138.9954
3.1259
990017.5324
0.000140397
2012-03-19
3648
990017.5324
135.8696
138.9959
3.1263
990020.6587
0.000140397

Notice the on 2012-03-16 the dly_coup changed from 137.3626 to 135.8696 and the dly_amort changed from 1.6317 to 3.1250. The dly_eff_rate, however, only changed slightly, from 138.9943 to 138.9945 and the (calculated) amort_rate did not change at all. In fact, if we were to look at the last 10 days of the amortization schedule we would see that the (calculated) amortization rate is still the same.
SELECT *
,CASE
      WHEN begin_book_val = 0 THEN 0
      ELSE dly_eff_rate / begin_book_val
 END as amort_rate
FROM wct.BONDAMORT(
       '2012-03-10'           --Settlement
      ,'2022-03-15'           --Maturity
      ,0.05                   --Rate
      ,1000000.00             --FaceAmount
      ,990000.00              --CleanPrice
      ,NULL                   --Redemption
      ,2                      --Frequency
      ,'1'                    --Basis
      ,NULL                   --IssueDate
      ,NULL                   --FirstInterestDate
      ,NULL                   --LastInterestDate
      ,NULL                   --Holidays 
      )
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
amort_rate
2022-03-06
9
999977.2593
138.1215
140.3942
2.2726
999979.5319
0.000140397
2022-03-07
8
999979.5319
138.1215
140.3945
2.2730
999981.8049
0.000140397
2022-03-08
7
999981.8049
138.1215
140.3948
2.2733
999984.0781
0.000140397
2022-03-09
6
999984.0781
138.1215
140.3951
2.2736
999986.3517
0.000140397
2022-03-10
5
999986.3517
138.1215
140.3955
2.2739
999988.6256
0.000140397
2022-03-11
4
999988.6256
138.1215
140.3958
2.2742
999990.8999
0.000140397
2022-03-12
3
999990.8999
138.1215
140.3961
2.2746
999993.1744
0.000140397
2022-03-13
2
999993.1744
138.1215
140.3964
2.2749
999995.4493
0.000140397
2022-03-14
1
999995.4493
138.1215
140.3967
2.2752
999997.7245
0.000140397
2022-03-15
0
999997.7245
138.1215
140.3971
2.2755
1000000.0000
0.000140397

Finally, here’s an example of a Brazilian NTN-F. We have stored the Brazilian holidays in a table called Holidays, which is keyed by country code, and used the NBD aggregate function to create the input to Holidays parameter of the BONDAMORT function.
DECLARE @hol as varchar(max)
SET @hol =(SELECT wct.NBD(holiday)
                  FROM HOLIDAYS
                  WHERE COUNTRY = 'BR')
 
SELECT *
,CASE
      WHEN begin_book_val = 0 THEN 0
      ELSE dly_eff_rate / begin_book_val
 END as amort_rate
FROM wct.BONDAMORT(
       '2011-12-15'           --Settlement
      ,'2013-01-03'           --Maturity
      ,0.10                   --Rate
      ,1000000.00             --FaceAmount
      ,1038692.42             --CleanPrice
      ,NULL                   --Redemption
      ,2                      --Frequency
      ,'20'                   --Basis
      ,NULL                   --IssueDate
      ,NULL                   --FirstInterestDate
      ,NULL                   --LastInterestDate
      ,@hol                   --Holidays 
      )
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
amort_rate
2011-12-15
264
0.0000
0.0000
0.0000
0.0000
1038692.4200
0.00000000
2011-12-16
263
1038692.4200
384.3216
246.6708
-137.6508
1038554.7692
0.00023748
2011-12-17
263
1038554.7692
0.0000
0.0000
0.0000
1038554.7692
0.00000000
2011-12-18
263
1038554.7692
0.0000
0.0000
0.0000
1038554.7692
0.00000000
2011-12-19
262
1038554.7692
384.3216
246.6381
-137.6835
1038417.0857
0.00023748
2011-12-20
261
1038417.0857
384.3216
246.6054
-137.7162
1038279.3695
0.00023748
2011-12-21
260
1038279.3695
384.3216
246.5727
-137.7489
1038141.6206
0.00023748
2011-12-22
259
1038141.6206
384.3216
246.5400
-137.7816
1038003.8389
0.00023748
2011-12-23
258
1038003.8389
384.3216
246.5073
-137.8143
1037866.0246
0.00023748
2011-12-24
258
1037866.0246
0.0000
0.0000
0.0000
1037866.0246
0.00000000
2011-12-25
258
1037866.0246
0.0000
0.0000
0.0000
1037866.0246
0.00000000
2011-12-26
257
1037866.0246
384.3216
246.4746
-137.8471
1037728.1775
0.00023748
2011-12-27
256
1037728.1775
384.3216
246.4418
-137.8798
1037590.2977
0.00023748
2011-12-28
255
1037590.2977
384.3216
246.4091
-137.9126
1037452.3852
0.00023748
2011-12-29
254
1037452.3852
384.3216
246.3763
-137.9453
1037314.4399
0.00023748
2011-12-30
253
1037314.4399
384.3216
246.3436
-137.9781
1037176.4618
0.00023748
2011-12-31
253
1037176.4618
0.0000
0.0000
0.0000
1037176.4618
0.00000000
2012-01-01
253
1037176.4618
0.0000
0.0000
0.0000
1037176.4618
0.00000000
2012-01-02
252
1037176.4618
384.3216
246.3108
-138.0108
1037038.4510
0.00023748
2012-01-03
251
1037038.4510
384.3216
246.2780
-138.0436
1036900.4074
0.00023748
2012-01-04
250
1036900.4074
390.4708
246.2453
-144.2255
1036756.1818
0.00023748
2012-01-05
249
1036756.1818
390.4708
246.2110
-144.2598
1036611.9220
0.00023748
2012-01-06
248
1036611.9220
390.4708
246.1767
-144.2940
1036467.6280
0.00023748
2012-01-07
248
1036467.6280
0.0000
0.0000
0.0000
1036467.6280
0.00000000
2012-01-08
248
1036467.6280
0.0000
0.0000
0.0000
1036467.6280
0.00000000
2012-01-09
247
1036467.6280
390.4708
246.1425
-144.3283
1036323.2997
0.00023748
2012-01-10
246
1036323.2997
390.4708
246.1082
-144.3626
1036178.9371
0.00023748
2012-01-11
245
1036178.9371
390.4708
246.0739
-144.3969
1036034.5402
0.00023748
2012-01-12
244
1036034.5402
390.4708
246.0396
-144.4312
1035890.1091
0.00023748
2012-01-13
243
1035890.1091
390.4708
246.0053
-144.4655
1035745.6436
0.00023748
2012-01-14
243
1035745.6436
0.0000
0.0000
0.0000
1035745.6436
0.00000000
2012-01-15
243
1035745.6436
0.0000
0.0000
0.0000
1035745.6436
0.00000000
2012-01-16
242
1035745.6436
390.4708
245.9710
-144.4998
1035601.1438
0.00023748
2012-01-17
241
1035601.1438
390.4708
245.9367
-144.5341
1035456.6098
0.00023748
2012-01-18
240
1035456.6098
390.4708
245.9024
-144.5684
1035312.0413
0.00023748
2012-01-19
239
1035312.0413
390.4708
245.8680
-144.6027
1035167.4386
0.00023748
2012-01-20
238
1035167.4386
390.4708
245.8337
-144.6371
1035022.8015
0.00023748
2012-01-21
238
1035022.8015
0.0000
0.0000
0.0000
1035022.8015
0.00000000
2012-01-22
238
1035022.8015
0.0000
0.0000
0.0000
1035022.8015
0.00000000
2012-01-23
237
1035022.8015
390.4708
245.7994
-144.6714
1034878.1301
0.00023748
2012-01-24
236
1034878.1301
390.4708
245.7650
-144.7058
1034733.4243
0.00023748
2012-01-25
235
1034733.4243
390.4708
245.7306
-144.7402
1034588.6841
0.00023748
2012-01-26
234
1034588.6841
390.4708
245.6963
-144.7745
1034443.9096
0.00023748
2012-01-27
233
1034443.9096
390.4708
245.6619
-144.8089
1034299.1007
0.00023748
2012-01-28
233
1034299.1007
0.0000
0.0000
0.0000
1034299.1007
0.00000000
2012-01-29
233
1034299.1007
0.0000
0.0000
0.0000
1034299.1007
0.00000000
2012-01-30
232
1034299.1007
390.4708
245.6275
-144.8433
1034154.2574
0.00023748
2012-01-31
231
1034154.2574
390.4708
245.5931
-144.8777
1034009.3797
0.00023748
2012-02-01
230
1034009.3797
390.4708
245.5587
-144.9121
1033864.4676
0.00023748
2012-02-02
229
1033864.4676
390.4708
245.5243
-144.9465
1033719.5211
0.00023748
2012-02-03
228
1033719.5211
390.4708
245.4898
-144.9809
1033574.5402
0.00023748
2012-02-04
228
1033574.5402
0.0000
0.0000
0.0000
1033574.5402
0.00000000
2012-02-05
228
1033574.5402
0.0000
0.0000
0.0000
1033574.5402
0.00000000
2012-02-06
227
1033574.5402
390.4708
245.4554
-145.0154
1033429.5248
0.00023748
2012-02-07
226
1033429.5248
390.4708
245.4210
-145.0498
1033284.4750
0.00023748
2012-02-08
225
1033284.4750
390.4708
245.3865
-145.0843
1033139.3907
0.00023748
2012-02-09
224
1033139.3907
390.4708
245.3521
-145.1187
1032994.2720
0.00023748
2012-02-10
223
1032994.2720
390.4708
245.3176
-145.1532
1032849.1188
0.00023748
2012-02-11
223
1032849.1188
0.0000
0.0000
0.0000
1032849.1188
0.00000000
2012-02-12
223
1032849.1188
0.0000
0.0000
0.0000
1032849.1188
0.00000000
2012-02-13
222
1032849.1188
390.4708
245.2831
-145.1876
1032703.9312
0.00023748
2012-02-14
221
1032703.9312
390.4708
245.2487
-145.2221
1032558.7091
0.00023748
2012-02-15
220
1032558.7091
390.4708
245.2142
-145.2566
1032413.4525
0.00023748
2012-02-16
219
1032413.4525
390.4708
245.1797
-145.2911
1032268.1614
0.00023748
2012-02-17
218
1032268.1614
390.4708
245.1452
-145.3256
1032122.8358
0.00023748
2012-02-18
218
1032122.8358
0.0000
0.0000
0.0000
1032122.8358
0.00000000
2012-02-19
218
1032122.8358
0.0000
0.0000
0.0000
1032122.8358
0.00000000
2012-02-20
218
1032122.8358
0.0000
0.0000
0.0000
1032122.8358
0.00000000
2012-02-21
218
1032122.8358
0.0000
0.0000
0.0000
1032122.8358
0.00000000
2012-02-22
217
1032122.8358
390.4708
245.1107
-145.3601
1031977.4756
0.00023748
2012-02-23
216
1031977.4756
390.4708
245.0761
-145.3946
1031832.0810
0.00023748
2012-02-24
215
1031832.0810
390.4708
245.0416
-145.4292
1031686.6518
0.00023748
2012-02-25
215
1031686.6518
0.0000
0.0000
0.0000
1031686.6518
0.00000000
2012-02-26
215
1031686.6518
0.0000
0.0000
0.0000
1031686.6518
0.00000000
2012-02-27
214
1031686.6518
390.4708
245.0071
-145.4637
1031541.1881
0.00023748
2012-02-28
213
1031541.1881
390.4708
244.9725
-145.4983
1031395.6899
0.00023748
2012-02-29
212
1031395.6899
390.4708
244.9380
-145.5328
1031250.1571
0.00023748

Notice that there is no amortization or interest accrual on Saturdays or Sundays or on non-business days like 2012-02-20 and 2012-02-21.


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service