# 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