SQL Server BONDAMORT Function

Updated 2023-10-06 13:15:14.393000

Description

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

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

Return Type

table

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

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.

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

Notice that for 2012-02-29 the daily coupon is actually two days of coupon interest (again, reflecting market practice). Thus, the daily amortization also needs to reflect two days of amortization.

Here's an example for NL/365 in which Feb-29 is not included in the date calculations (NL meaning No Leap year).

SELECT *
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.

On 29-Feb-12 there is no coupon accrual and therefore these is no amortization.

Let's look at a bond which accrues on an actual/actual basis.

SELECT *
FROM wct.BONDAMORT(   '2012-03-10', --Settlement
                      '2022-03-15', --Maturity
                      0.05,         --Rate
                      1000000.00,   --FaceAmount
                      990000.00,    --CleanPrice
                      NULL,         --Redemption
                      2,            --Frequency
                      '1',          --Basis
                      NULL,         --IssueDate
                      NULL,         --FirstInterestDate
                      NULL,         --LastInterestDate
                      NULL          --Holidays 
                  )
WHERE amort_date < '2012-03-20';

This produces the following result.

Notice the on 2012-03-16 the dly_coup changed from 137.3626 to 135.8696 and the dly_amort changed from 1.6317 to 3.1250. The dly_eff_rate, however, only changed slightly, from 138.9943 to 138.9945 and the (calculated) amort_rate did not change at all. In fact, if we were to look at the last 10 days of the amortization schedule we would see that the (calculated) amortization rate is still the same.

SELECT *,
       CASE
           WHEN begin_book_val = 0 THEN
               0
           ELSE
               dly_eff_rate / begin_book_val
       END as amort_rate
FROM wct.BONDAMORT(   '2012-03-10', --Settlement
                      '2022-03-15', --Maturity
                      0.05,         --Rate
                      1000000.00,   --FaceAmount
                      990000.00,    --CleanPrice
                      NULL,         --Redemption
                      2,            --Frequency
                      '1',          --Basis
                      NULL,         --IssueDate
                      NULL,         --FirstInterestDate
                      NULL,         --LastInterestDate
                      NULL          --Holidays 
                  )
WHERE dtm < 10;

This produces the following result.

Finally, here's an example of a Brazilian NTN-F. We have stored the Brazilian holidays in a table called Holidays, which is keyed by country code, and used the NBD aggregate function to create the input to Holidays parameter of the BONDAMORT function.

DECLARE @hol as varchar(max);
SET @hol
    = '20110101,20110307,20110308,20110421,20110422,20110501,20110623,20110907,
              20111012,20111102,20111115,20111225,20120101,20120220,20120221,
              20120406,20120421,20120501,20120607,20120907,20121012,20121102,
              20121115,20121225,20130101,20130211,20130212,20130329,20130421,
              20130501,20130530,20130907,20131012,20131102,20131115,20131225';
SELECT *,
       CASE
           WHEN begin_book_val = 0 THEN
               0
           ELSE
               dly_eff_rate / begin_book_val
       END as amort_rate
FROM wct.BONDAMORT(   '2011-12-15', --Settlement
                      '2013-01-03', --Maturity
                      0.10,         --Rate
                      1000000.00,   --FaceAmount
                      1038692.42,   --CleanPrice
                      NULL,         --Redemption
                      2,            --Frequency
                      '20',         --Basis
                      NULL,         --IssueDate
                      NULL,         --FirstInterestDate
                      NULL,         --LastInterestDate
                      @hol          --Holidays 
                  )
WHERE amort_date < '2012-03-01';

Notice that there is no amortization or interest accrual on Saturdays or Sundays or on non-business days like 2012-02-20 and 2012-02-21.