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 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 wct.AMORTRATE (
'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
) as [Amortization Rate]
This produces the following result.
Amortization Rate
----------------------
0.000172311323878642
This creates 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-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
|
begin_book_val
|
dly_coup
|
dly_amort
|
end_book_val
|
amort_rate
|
|
15-Feb-12
|
0.00000
|
0.00000
|
0.00000
|
999000.00000
|
0.000000000000
|
|
16-Feb-12
|
999000.0000
|
138.8889
|
33.2501
|
999033.2501
|
0.000172311324
|
|
17-Feb-12
|
999033.2501
|
138.8889
|
33.2559
|
999066.5060
|
0.000172311324
|
|
18-Feb-12
|
999066.5060
|
138.8889
|
33.2616
|
999099.7676
|
0.000172311324
|
|
19-Feb-12
|
999099.7676
|
138.8889
|
33.2673
|
999133.0349
|
0.000172311324
|
|
20-Feb-12
|
999133.0349
|
138.8889
|
33.2730
|
999166.3079
|
0.000172311324
|
|
21-Feb-12
|
999166.3079
|
138.8889
|
33.2788
|
999199.5867
|
0.000172311324
|
|
22-Feb-12
|
999199.5867
|
138.8889
|
33.2845
|
999232.8712
|
0.000172311324
|
|
23-Feb-12
|
999232.8712
|
138.8889
|
33.2903
|
999266.1615
|
0.000172311324
|
|
24-Feb-12
|
999266.1615
|
138.8889
|
33.2960
|
999299.4575
|
0.000172311324
|
|
25-Feb-12
|
999299.4575
|
138.8889
|
33.3017
|
999332.7592
|
0.000172311324
|
|
26-Feb-12
|
999332.7592
|
138.8889
|
33.3075
|
999366.0666
|
0.000172311324
|
|
27-Feb-12
|
999366.0666
|
138.8889
|
33.3132
|
999399.3798
|
0.000172311324
|
|
28-Feb-12
|
999399.3798
|
138.8889
|
33.3189
|
999432.6988
|
0.000172311324
|
|
29-Feb-12
|
999432.6988
|
277.7778
|
66.6551
|
999499.3539
|
0.000344628393
|
|
1-Mar-12
|
999499.3539
|
138.8889
|
33.3362
|
999532.6901
|
0.000172311324
|
|
2-Mar-12
|
999532.6901
|
138.8889
|
33.3419
|
999566.0320
|
0.000172311324
|
|
3-Mar-12
|
999566.0320
|
138.8889
|
33.3477
|
999599.3796
|
0.000172311324
|
|
4-Mar-12
|
999599.3796
|
138.8889
|
33.3534
|
999632.7330
|
0.000172311324
|
|
5-Mar-12
|
999632.7330
|
138.8889
|
33.3592
|
999666.0922
|
0.000172311324
|
|
6-Mar-12
|
999666.0922
|
138.8889
|
33.3649
|
999699.4571
|
0.000172311324
|
|
7-Mar-12
|
999699.4571
|
138.8889
|
33.3706
|
999732.8277
|
0.000172311324
|
|
8-Mar-12
|
999732.8277
|
138.8889
|
33.3764
|
999766.2041
|
0.000172311324
|
|
9-Mar-12
|
999766.2041
|
138.8889
|
33.3821
|
999799.5863
|
0.000172311324
|
|
10-Mar-12
|
999799.5863
|
138.8889
|
33.3879
|
999832.9742
|
0.000172311324
|
|
11-Mar-12
|
999832.9742
|
138.8889
|
33.3937
|
999866.3678
|
0.000172311324
|
|
12-Mar-12
|
999866.3678
|
138.8889
|
33.3994
|
999899.7672
|
0.000172311324
|
|
13-Mar-12
|
999899.7672
|
138.8889
|
33.4052
|
999933.1724
|
0.000172311324
|
|
14-Mar-12
|
999933.1724
|
138.8889
|
33.4109
|
999966.5833
|
0.000172311324
|
|
15-Mar-12
|
999966.5833
|
138.8889
|
33.4167
|
1000000.0000
|
0.000172311324
|
Notice that for 29-Feb-12 the daily coupon is actaully two days of coupon interest (again, reflecting market practice). Thus, the daily amortization also needs to reflect two days of amortization, which AMORTRATE has factored into the calculation.
Here’s an example for NL/365 in which Feb-29 is not included in the date calculations (NL meaning No Leap year).
SELECT wct.AMORTRATE (
'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
) as [Amortization Rate]
This produces the following result.
Amortization Rate
----------------------
0.000172790137261541
This produces 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-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
|
begin_book_val
|
dly_coup
|
dly_amort
|
end_book_val
|
amort_rate
|
|
15-Feb-12
|
0.00000
|
0.00000
|
0.00000
|
999000.00000
|
0.000000000000
|
|
16-Feb-12
|
999000.0000
|
136.9863
|
35.6310
|
999035.6310
|
0.000172790137
|
|
17-Feb-12
|
999035.6310
|
136.9863
|
35.6372
|
999071.2682
|
0.000172790137
|
|
18-Feb-12
|
999071.2682
|
136.9863
|
35.6434
|
999106.9116
|
0.000172790137
|
|
19-Feb-12
|
999106.9116
|
136.9863
|
35.6495
|
999142.5611
|
0.000172790137
|
|
20-Feb-12
|
999142.5611
|
136.9863
|
35.6557
|
999178.2168
|
0.000172790137
|
|
21-Feb-12
|
999178.2168
|
136.9863
|
35.6618
|
999213.8786
|
0.000172790137
|
|
22-Feb-12
|
999213.8786
|
136.9863
|
35.6680
|
999249.5466
|
0.000172790137
|
|
23-Feb-12
|
999249.5466
|
136.9863
|
35.6742
|
999285.2208
|
0.000172790137
|
|
24-Feb-12
|
999285.2208
|
136.9863
|
35.6803
|
999320.9011
|
0.000172790137
|
|
25-Feb-12
|
999320.9011
|
136.9863
|
35.6865
|
999356.5876
|
0.000172790137
|
|
26-Feb-12
|
999356.5876
|
136.9863
|
35.6927
|
999392.2803
|
0.000172790137
|
|
27-Feb-12
|
999392.2803
|
136.9863
|
35.6988
|
999427.9791
|
0.000172790137
|
|
28-Feb-12
|
999427.9791
|
136.9863
|
35.7050
|
999463.6841
|
0.000172790137
|
|
29-Feb-12
|
999463.6841
|
0.0000
|
0.0000
|
999463.6841
|
0.000000000000
|
|
1-Mar-12
|
999463.6841
|
136.9863
|
35.7112
|
999499.3953
|
0.000172790137
|
|
2-Mar-12
|
999499.3953
|
136.9863
|
35.7173
|
999535.1126
|
0.000172790137
|
|
3-Mar-12
|
999535.1126
|
136.9863
|
35.7235
|
999570.8361
|
0.000172790137
|
|
4-Mar-12
|
999570.8361
|
136.9863
|
35.7297
|
999606.5658
|
0.000172790137
|
|
5-Mar-12
|
999606.5658
|
136.9863
|
35.7359
|
999642.3017
|
0.000172790137
|
|
6-Mar-12
|
999642.3017
|
136.9863
|
35.7420
|
999678.0437
|
0.000172790137
|
|
7-Mar-12
|
999678.0437
|
136.9863
|
35.7482
|
999713.7919
|
0.000172790137
|
|
8-Mar-12
|
999713.7919
|
136.9863
|
35.7544
|
999749.5463
|
0.000172790137
|
|
9-Mar-12
|
999749.5463
|
136.9863
|
35.7606
|
999785.3068
|
0.000172790137
|
|
10-Mar-12
|
999785.3068
|
136.9863
|
35.7667
|
999821.0736
|
0.000172790137
|
|
11-Mar-12
|
999821.0736
|
136.9863
|
35.7729
|
999856.8465
|
0.000172790137
|
|
12-Mar-12
|
999856.8465
|
136.9863
|
35.7791
|
999892.6256
|
0.000172790137
|
|
13-Mar-12
|
999892.6256
|
136.9863
|
35.7853
|
999928.4109
|
0.000172790137
|
|
14-Mar-12
|
999928.4109
|
136.9863
|
35.7915
|
999964.2023
|
0.000172790137
|
|
15-Mar-12
|
999964.2023
|
136.9863
|
35.7977
|
1000000.0000
|
0.000172790137
|
On 29-Feb-12 there is no coupon accrual and therefore these is no amortization.