AMORTRATE

Updated 2024-02-13 20:39:43.617000

Syntax

SELECT [westclintech].[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),>)

Description

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**r*a) – CtPt+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.

Arguments

@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.

@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.

@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.

{"columns":[{"field":"@Basis","width":139},{"field":"Day count basis","width":396}],"rows":[{"@Basis":"0 or omitted","Day count basis":"US (NASD) 30/360"},{"@Basis":"1","Day count basis":"Actual/Actual"},{"@Basis":"2","Day count basis":"Actual/360"},{"@Basis":"3","Day count basis":"Actual/365"},{"@Basis":"4","Day count basis":"European 30/360"},{"@Basis":"5","Day count basis":"30/360 ISDA"},{"@Basis":"6","Day count basis":"NL/ACT"},{"@Basis":"7","Day count basis":"NL/365"},{"@Basis":"8","Day count basis":"NL/360"},{"@Basis":"9","Day count basis":"A/364"},{"@Basis":"10","Day count basis":"US (NASD) 30/360 non-end-of-month"},{"@Basis":"11","Day count basis":"Actual/Actual non-end-of-month"},{"@Basis":"12","Day count basis":"Actual/360 non-end-of-month"},{"@Basis":"13","Day count basis":"Actual/365 non-end-of-month"},{"@Basis":"14","Day count basis":"European 30/360 non-end-of-month"},{"@Basis":"15","Day count basis":"30/360 ISDA non-end-of-month"},{"@Basis":"16","Day count basis":"NL/ACT non-end-of-month"},{"@Basis":"17","Day count basis":"NL/365 non-end-of-month"},{"@Basis":"18","Day count basis":"NL/360 non-end-of-month"},{"@Basis":"19","Day count basis":"A/364 non-end-of-month"},{"@Basis":"20","Day count basis":"BUS/252"},{"@Basis":"21","Day count basis":"Actual/ISDA"},{"@Basis":"22","Day count basis":"Actual/ISMA"},{"@Basis":"23","Day count basis":"Actual/365L"},{"@Basis":"24","Day count basis":"Actual/AFB"},{"@Basis":"25","Day count basis":"30E+360"},{"@Basis":"30","Day count basis":"BUS/252 non-end-of-month"}]}

@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.

@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.

@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.

@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.

@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.

@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.

@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.

@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 NBD to create an appropriately formatted string.

Return Type

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];