ODDCOMPINT
Updated: 05 December 2013
Use the scalar function ODDCOMPINT to calculate the accrued interest for a security with an odd first or an odd last coupon period (or both) where interest is compounded periodically and paid at maturity. If the settlement date is less than or equal to the first coupon date, then the accrued interest is the same as the value returned by AIFCATOR_OFC.If the settlement date is greater than the first coupon date and less than the last coupon date or the last coupon date is than the accrued interest is
If the settlement date is greater than the last coupon date, then accrued interest is
Where:
R = the coupon interest rate as a decimal
M = the number of compounding periods per year
N = the number of whole coupons prior to the settlement date
A = the number of accrued days in the coupon period in which the settlement occurs
E = the number of days as specified by the basis code for the coupon period in which the settlement occurs.
Codd = the accrued interest factor as calculated by AIFACTOR_OFC.
Alast = the accrued interest factor as calculated by AIFACTOR_OLC.
Syntax
SELECT [wctFinancial].[wct].[ODDCOMPINT](
<@Basis, nvarchar(4000),>
,<@Rate, float,>
,<@IssueDate, datetime,>
,<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@FirstCouponDate, datetime,>
,<@LastCouponDate, datetime,>
,<@CompFreq, int,>)
Arguments
@Basis
is the type of day count to use. @Basis is an expression of the character string data type category.
|
Basis
|
Day count basis
|
|
0
|
US (NASD) 30/360
|
|
1
|
Actual/Actual
|
|
2
|
Actual/360
|
|
3
|
Actual/365
|
|
4
|
European 30/360
|
@Rate
the coupon rate of the security expressed in decimal terms. @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 first interest accrual date. @IssueDate is an expression of a datetime data type or of a type that can be implicitly converted to datetime.
@Settlement
the settlement date occurring within a coupon period of the security; interest is accrued from @IssueDate through to @Settlement. @Settlement is an expression of a datetime data type or of a type that can be implicitly converted to datetime.
@Maturity
the maturity date of the bond. @Maturity is used to determine the coupon dates. @Maturity is an expression of a datetime data type or of a type that can be implicitly converted to datetime.
@FirstCouponDate
for bonds with an odd first coupon amount, the date of the coupon payment. @FirstCouponDate should only be used when the first interest payment is calculated and compounded on a date other than a regular, periodic coupon date. If @LastCouponDate is NULL, then the coupon dates are calculated backwards from @Maturity, otherwise @LastCouponDate is used to calculate the coupon dates. @FirstCouponDate is an expression of a datetime data type or of a type that can be implicitly converted to datetime.
@LastCouponDate
for bonds with an odd last coupon amount, the last regular coupon date. @LastCouponDate should only be used when the last interest payment is calculated from a start date other than a regular, periodic coupon date calculated backwards from @Maturity. @LastCouponDate is an expression of a datetime data type or of a type that can be implicitly converted to datetime.
@CompFreq
the number of times the coupon are is compounded annually. For annual compounding, @CompFreq = 1; for semi-annual, @CompFreq = 2; for quarterly, @CompFreq = 4, and for monthly, @CompFreq = 12. @CompFreq is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
· If @CompFreq not 1, 2, 4, or 12 an error will be returned.
· @IssueDate <= @Settlement <= @Maturity.
· @IssueDate < @FirstCouponDate < @LastCouponDate < @Maturity
· For bonds with regular coupon payments, use COMPINT.
· ODDCOMPINT returns a factor. To calculate the monetary value of the accrued interest, you should multiply this factor by the face amount of the bond.
Examples
A 3-year bond, compounding monthly, with an odd short first period, settling in the first period.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2013-11-15' --@IssueDate
,'2013-11-29' --@Settlement
,'2016-11-30' --@Maturity
,'2013-11-30' --@FirstCouponDate
,NULL --@LastCouponDate
,12 --@CompFreq
) as ODDCOMPINT
This produces the following result.
ODDCOMPINT
----------------------
0.000680555555555556
The same bond, settling 1 year later.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2013-11-15' --@IssueDate
,'2014-11-29' --@Settlement
,'2016-11-30' --@Maturity
,'2013-11-30' --@FirstCouponDate
,NULL --@LastCouponDate
,12 --@CompFreq
) as ODDCOMPINT
This produces the following result.
ODDCOMPINT
----------------------
0.0183336464432271
By entering the maturity date as the settlement date, we can calculate the total amount of the compound interest at maturity.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2013-11-15' --@IssueDate
,'2016-11-30' --@Settlement
,'2016-11-30' --@Maturity
,'2013-11-30' --@FirstCouponDate
,NULL --@LastCouponDate
,12 --@CompFreq
) as ODDCOMPINT
This produces the following reuslt.
ODDCOMPINT
----------------------
0.0546306985522649
A 5-year bond, compounding semi-annually, with an odd long first period, settling in the first period.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2012-10-23' --@IssueDate
,'2013-03-15' --@Settlement
,'2016-10-31' --@Maturity
,'2013-04-30' --@FirstCouponDate
,NULL --@LastCouponDate
,2 --@CompFreq
) as ODDCOMPINT
This produces the following result.
ODDCOMPINT
----------------------
0.00690667787653135
The same bond, settling one year later.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2012-10-23' --@IssueDate
,'2014-03-15' --@Settlement
,'2016-10-31' --@Maturity
,'2013-04-30' --@FirstCouponDate
,NULL --@LastCouponDate
,2 --@CompFreq
) as ODDCOMPINT
This produces the following result.
ODDCOMPINT
----------------------
0.0246037826349688
A 5 –year bond with an odd short last coupon date settling before the last coupon date.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2012-10-31' --@IssueDate
,'2014-03-15' --@Settlement
,'2017-08-01' --@Maturity
,NULL --@FirstCouponDate
,'2017-04-30' --@LastCouponDate
,2 --@CompFreq
) as ODDCOMPINT
This produces the following result.
ODDCOMPINT
----------------------
0.0242175145135533
The same bond, with settlement in the odd coupon period.
SELECT wct.ODDCOMPINT(
1 --@Basis
,.0175 --@Rate
,'2012-10-31' --@IssueDate
,'2017-07-01' --@Settlement
,'2017-08-01' --@Maturity
,NULL --@FirstCouponDate
,'2017-04-30' --@LastCouponDate
,2 --@CompFreq
) as ODDCOMPINT
This produces the following result.
ODDCOMPINT
----------------------
0.0847521167506424
See Also