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