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
|