Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server constant daily effective amortization rate function


AMORTRATE

Updated: 11 May 2012


Use the scalar function AMORTRATE to calculate the constant daily effective rate to be used in the amortization/accretion of bond (or loan) premium or discount.
The AMORTRATE value is used to calculate an adjustment to the daily interest accrual reflecting the appropriate amortization and is calculated in much the same way as the daily interest accrual. One way to think of this adjustment is as follows:
 
At = (Pt*ra) – Ct
Pt+1 = Pt + At
Where:
At            is the amortization amount at time t.
Pt            is the principal amount at time t.
ra             is the amortization rate.
Ct            Is daily coupon amount at time t. Due to various day-count conventions, the daily coupon amount may vary over the life of a financial instrument.
 
Notice that while A and P (and potentially C) vary over the term of the financial instrument, ra is constant.
 
Because ra is an adjustment to the coupon interest, the adjustment should only be applied on days when coupon interest is calculated. For some bonds, this means that there is no interest on the 31st of the month. For others, it might mean that interest is not accrued on Feb-29 or is only accrued on business days. Or, the last day of February might contain 2 or even 3 days of coupon interest. The AMORTRATE function makes the appropriate adjustment based in the day-count convention (also known as interest basis) supplied to the function.
To see a detailed amortization schedule using the AMORTRATE value, you can use the BONDAMORT table-valued function.
Syntax
SELECT [wctFinancial].[wct].[AMORTRATE] (
  <@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 Type
float
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
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 wct.AMORTRATE (
 '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 
) as [Amortization Rate]
This produces the following result.
     Amortization Rate
----------------------
 0.000154306279086793
 
We can create a bond amortization schedule using the BONDAMORT table-valued function and check to see that the daily accrual of the coupon plus the daily accretion of the discount divided by the beginning book value for each day equals our amortization rate and that the rate remains constant through to the maturity date.

amort_date
begin_book_val
dly_coup
dly_amort
end_book_val
amort_rate
3-May-12
0.0000
0.000
0.000
999000.000
0.000000000000
4-May-12
999000.0000
136.9863
17.1657
999017.1657
0.000154306279
5-May-12
999017.1657
136.9863
17.1683
999034.3340
0.000154306279
6-May-12
999034.3340
136.9863
17.1710
999051.5050
0.000154306279
7-May-12
999051.5050
136.9863
17.1736
999068.6786
0.000154306279
8-May-12
999068.6786
136.9863
17.1763
999085.8548
0.000154306279
9-May-12
999085.8548
136.9863
17.1789
999103.0338
0.000154306279
10-May-12
999103.0338
136.9863
17.1816
999120.2153
0.000154306279
11-May-12
999120.2153
136.9863
17.1842
999137.3996
0.000154306279
12-May-12
999137.3996
136.9863
17.1869
999154.5864
0.000154306279
13-May-12
999154.5864
136.9863
17.1895
999171.7760
0.000154306279
14-May-12
999171.7760
136.9863
17.1922
999188.9681
0.000154306279
15-May-12
999188.9681
136.9863
17.1948
999206.1630
0.000154306279
16-May-12
999206.1630
136.9863
17.1975
999223.3604
0.000154306279
17-May-12
999223.3604
136.9863
17.2001
999240.5606
0.000154306279
18-May-12
999240.5606
136.9863
17.2028
999257.7634
0.000154306279
19-May-12
999257.7634
136.9863
17.2054
999274.9688
0.000154306279
20-May-12
999274.9688
136.9863
17.2081
999292.1769
0.000154306279
21-May-12
999292.1769
136.9863
17.2108
999309.3877
0.000154306279
22-May-12
999309.3877
136.9863
17.2134
999326.6011
0.000154306279
23-May-12
999326.6011
136.9863
17.2161
999343.8172
0.000154306279
24-May-12
999343.8172
136.9863
17.2187
999361.0359
0.000154306279
25-May-12
999361.0359
136.9863
17.2214
999378.2573
0.000154306279
26-May-12
999378.2573
136.9863
17.2240
999395.4813
0.000154306279
27-May-12
999395.4813
136.9863
17.2267
999412.7080
0.000154306279
28-May-12
999412.7080
136.9863
17.2294
999429.9374
0.000154306279
29-May-12
999429.9374
136.9863
17.2320
999447.1694
0.000154306279
30-May-12
999447.1694
136.9863
17.2347
999464.4040
0.000154306279
31-May-12
999464.4040
136.9863
17.2373
999481.6414
0.000154306279
1-Jun-12
999481.6414
136.9863
17.2400
999498.8814
0.000154306279
2-Jun-12
999498.8814
136.9863
17.2427
999516.1240
0.000154306279
3-Jun-12
999516.1240
136.9863
17.2453
999533.3693
0.000154306279
4-Jun-12
999533.3693
136.9863
17.2480
999550.6173
0.000154306279
5-Jun-12
999550.6173
136.9863
17.2506
999567.8679
0.000154306279
6-Jun-12
999567.8679
136.9863
17.2533
999585.1212
0.000154306279
7-Jun-12
999585.1212
136.9863
17.2560
999602.3772
0.000154306279
8-Jun-12
999602.3772
136.9863
17.2586
999619.6358
0.000154306279
9-Jun-12
999619.6358
136.9863
17.2613
999636.8971
0.000154306279
10-Jun-12
999636.8971
136.9863
17.2639
999654.1611
0.000154306279
11-Jun-12
999654.1611
136.9863
17.2666
999671.4277
0.000154306279
12-Jun-12
999671.4277
136.9863
17.2693
999688.6969
0.000154306279
13-Jun-12
999688.6969
136.9863
17.2719
999705.9689
0.000154306279
14-Jun-12
999705.9689
136.9863
17.2746
999723.2435
0.000154306279
15-Jun-12
999723.2435
136.9863
17.2773
999740.5208
0.000154306279
16-Jun-12
999740.5208
136.9863
17.2799
999757.8007
0.000154306279
17-Jun-12
999757.8007
136.9863
17.2826
999775.0833
0.000154306279
18-Jun-12
999775.0833
136.9863
17.2853
999792.3686
0.000154306279
19-Jun-12
999792.3686
136.9863
17.2879
999809.6565
0.000154306279
20-Jun-12
999809.6565
136.9863
17.2906
999826.9471
0.000154306279
21-Jun-12
999826.9471
136.9863
17.2933
999844.2404
0.000154306279
22-Jun-12
999844.2404
136.9863
17.2959
999861.5363
0.000154306279
23-Jun-12
999861.5363
136.9863
17.2986
999878.8350
0.000154306279
24-Jun-12
999878.8350
136.9863
17.3013
999896.1362
0.000154306279
25-Jun-12
999896.1362
136.9863
17.3040
999913.4402
0.000154306279
26-Jun-12
999913.4402
136.9863
17.3066
999930.7468
0.000154306279
27-Jun-12
999930.7468
136.9863
17.3093
999948.0561
0.000154306279
28-Jun-12
999948.0561
136.9863
17.3120
999965.3681
0.000154306279
29-Jun-12
999965.3681
136.9863
17.3146
999982.6827
0.000154306279
30-Jun-12
999982.6827
136.9863
17.3173
1000000.0000
0.000154306279

In this example we will change the basis to 0, to reflect the US 30/360 day-count convention.
SELECT wct.AMORTRATE (
 '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 
) as [Amortization Rate]
This produces the following result.
     Amortization Rate
----------------------
 0.000156512494013327
 
Let’s create the amortization table
SELECT amort_date
,begin_book_val
,dly_coup
,dly_amort
,end_book_val
,CASE
      WHEN begin_book_val = 0 THEN 0
      ELSE (dly_coup + dly_amort) / begin_book_val
 END as amort_rate
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
begin_book_val
dly_coup
dly_amort
end_book_val
amort_rate
3-May-12
0.0000
0.0000
0.0000
999000.0000
0.000000000000
4-May-12
999000.0000
138.8889
17.4671
999017.4671
0.000156512494
5-May-12
999017.4671
138.8889
17.4698
999034.9369
0.000156512494
6-May-12
999034.9369
138.8889
17.4726
999052.4095
0.000156512494
7-May-12
999052.4095
138.8889
17.4753
999069.8848
0.000156512494
8-May-12
999069.8848
138.8889
17.4780
999087.3628
0.000156512494
9-May-12
999087.3628
138.8889
17.4808
999104.8436
0.000156512494
10-May-12
999104.8436
138.8889
17.4835
999122.3271
0.000156512494
11-May-12
999122.3271
138.8889
17.4862
999139.8133
0.000156512494
12-May-12
999139.8133
138.8889
17.4890
999157.3023
0.000156512494
13-May-12
999157.3023
138.8889
17.4917
999174.7940
0.000156512494
14-May-12
999174.7940
138.8889
17.4945
999192.2884
0.000156512494
15-May-12
999192.2884
138.8889
17.4972
999209.7856
0.000156512494
16-May-12
999209.7856
138.8889
17.4999
999227.2856
0.000156512494
17-May-12
999227.2856
138.8889
17.5027
999244.7882
0.000156512494
18-May-12
999244.7882
138.8889
17.5054
999262.2936
0.000156512494
19-May-12
999262.2936
138.8889
17.5081
999279.8018
0.000156512494
20-May-12
999279.8018
138.8889
17.5109
999297.3127
0.000156512494
21-May-12
999297.3127
138.8889
17.5136
999314.8263
0.000156512494
22-May-12
999314.8263
138.8889
17.5164
999332.3427
0.000156512494
23-May-12
999332.3427
138.8889
17.5191
999349.8618
0.000156512494
24-May-12
999349.8618
138.8889
17.5219
999367.3836
0.000156512494
25-May-12
999367.3836
138.8889
17.5246
999384.9082
0.000156512494
26-May-12
999384.9082
138.8889
17.5273
999402.4355
0.000156512494
27-May-12
999402.4355
138.8889
17.5301
999419.9656
0.000156512494
28-May-12
999419.9656
138.8889
17.5328
999437.4984
0.000156512494
29-May-12
999437.4984
138.8889
17.5356
999455.0340
0.000156512494
30-May-12
999455.0340
138.8889
17.5383
999472.5723
0.000156512494
31-May-12
999472.5723
0.0000
0.0000
999472.5723
0.000000000000
1-Jun-12
999472.5723
138.8889
17.5411
999490.1134
0.000156512494
2-Jun-12
999490.1134
138.8889
17.5438
999507.6572
0.000156512494
3-Jun-12
999507.6572
138.8889
17.5465
999525.2037
0.000156512494
4-Jun-12
999525.2037
138.8889
17.5493
999542.7530
0.000156512494
5-Jun-12
999542.7530
138.8889
17.5520
999560.3051
0.000156512494
6-Jun-12
999560.3051
138.8889
17.5548
999577.8599
0.000156512494
7-Jun-12
999577.8599
138.8889
17.5575
999595.4174
0.000156512494
8-Jun-12
999595.4174
138.8889
17.5603
999612.9777
0.000156512494
9-Jun-12
999612.9777
138.8889
17.5630
999630.5407
0.000156512494
10-Jun-12
999630.5407
138.8889
17.5658
999648.1065
0.000156512494
11-Jun-12
999648.1065
138.8889
17.5685
999665.6750
0.000156512494
12-Jun-12
999665.6750
138.8889
17.5713
999683.2463
0.000156512494
13-Jun-12
999683.2463
138.8889
17.5740
999700.8203
0.000156512494
14-Jun-12
999700.8203
138.8889
17.5768
999718.3971
0.000156512494
15-Jun-12
999718.3971
138.8889
17.5795
999735.9766
0.000156512494
16-Jun-12
999735.9766
138.8889
17.5823
999753.5589
0.000156512494
17-Jun-12
999753.5589
138.8889
17.5850
999771.1439
0.000156512494
18-Jun-12
999771.1439
138.8889
17.5878
999788.7317
0.000156512494
19-Jun-12
999788.7317
138.8889
17.5905
999806.3223
0.000156512494
20-Jun-12
999806.3223
138.8889
17.5933
999823.9156
0.000156512494
21-Jun-12
999823.9156
138.8889
17.5960
999841.5116
0.000156512494
22-Jun-12
999841.5116
138.8889
17.5988
999859.1104
0.000156512494
23-Jun-12
999859.1104
138.8889
17.6016
999876.7120
0.000156512494
24-Jun-12
999876.7120
138.8889
17.6043
999894.3163
0.000156512494
25-Jun-12
999894.3163
138.8889
17.6071
999911.9233
0.000156512494
26-Jun-12
999911.9233
138.8889
17.6098
999929.5332
0.000156512494
27-Jun-12
999929.5332
138.8889
17.6126
999947.1457
0.000156512494
28-Jun-12
999947.1457
138.8889
17.6153
999964.7611
0.000156512494
29-Jun-12
999964.7611
138.8889
17.6181
999982.3792
0.000156512494
30-Jun-12
999982.3792
138.8889
17.6208
1000000.0000
0.000156512494

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 n