OFLFACTORS
Updated: 30 June 2014
Use the tablevalued function OFLFACTORS to return the components used in the calculation of price and yield for a bond with an odd first and odd last coupon. OFLFACTORS supports odd first and odd last coupon bonds with up to 2 quasicoupon periods each.
Syntax
SELECT * FROM [wctFinancial].[wct].[OFLFACTORS](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Issue, datetime,>
,<@FirstCoupon, datetime,>
,<@LastCoupon, datetime,>
,<@Rate, float,>
,<@Price, float,>
,<@Yield, float,>
,<@Redemption, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>)
Arguments
@Settlement
the settlement date of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@Maturity
the maturity date of the bond. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@Issue
the issue date of the bond; the date from which the bond starts accruing interest. @Issue is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@FirstCoupon
the first coupon date of the bond. The period from the issue date until the first coupon date defines the odd interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @FirstCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@LastCoupon
the last coupon date of the bond prior to the maturity. The period from the last interest date until the maturity date defines the odd interest period. All coupon dates from @FirstCoupon to @LastCoupon are assumed to occur at regular periodic intervals as defined by @Frequency. @LastCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@Rate
the bondâ€™s annual coupon rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Price
the price of the bond. @Price is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the bondâ€™s annual yield. @Yld is an expression of type float or of a type that can be implicitly converted to float.
@Redemption
the bondâ€™s redemption value per 100 face value. @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 semiannual, @Frequency = 2; for quarterly, @Frequency = 4; for bimonthly @Frequency = 6; for monthly @Frequency = 12. For bonds with @Basis = 'A/364' or 9, you can enter 364 for payments made every 52 weeks, 182 for payments made every 26 weeks, 91 for payments made every 13 weeks, 28 for payments made every 4 weeks, 14 for payments made every 2 weeks, and 7 for weekly payments. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@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, 'BOND'

US (NASD) 30/360

1, 'ACTUAL'

Actual/Actual

2, 'A360'

Actual/360

3, 'A365'

Actual/365

4, '30E/360 (ISDA)', '30E/360', 'ISDA', '30E/360 ISDA', 'EBOND'

European 30/360

5, '30/360', '30/360 ISDA', 'GERMAN'

30/360 ISDA

6, 'NL/ACT'

No Leap Year/ACT

7, 'NL/365'

No Leap Year /365

8, 'NL/360'

No Leap Year /360

9, 'A/364'

Actual/364

10, 'BOND NONEOM'

US (NASD) 30/360 nonendofmonth

11, 'ACTUAL NONEOM'

Actual/Actual nonendofmonth

12, 'A360 NONEOM'

Actual/360 nonendofmonth

13, 'A365 NONEOM'

Actual/365 nonendofmonth

14, '30E/360 NONEOM', '30E/360 ICMA NONEOM', 'EBOND NONEOM'

European 30/360 nonendofmonth

15, '30/360 NONEOM', '30/360 ISDA NONEOM', 'GERMAN NONEOM'

30/360 ISDA nonendofmonth

16, 'NL/ACT NONEOM'

No Leap Year/ACT nonendofmonth

17, 'NL/365 NONEOM'

No Leap Year/365 nonendofmonth

18, 'NL/360 NONEOM'

No Leap Year/360 nonendofmonth

19, 'A/364 NONEOM'

Actual/364 nonendofmonth

Return Type
RETURNS TABLE (
[A1] [float] NULL,
[A2] [float] NULL,
[DSC] [float] NULL,
[E] [float] NULL,
[N] [int] NULL,
[NCL] [int] NULL,
[NCF] [int] NULL,
[DLC1] [float] NULL,
[DLC2] [float] NULL,
[NLL1] [float] NULL,
[NLL2] [float] NULL,
[DFC1] [float] NULL,
[DFC2] [float] NULL,
[NLF1] [float] NULL,
[NLF2] [float] NULL,
[Nqf] [float] NULL,
[quasistart] [datetime] NULL,
[quasicoupfirst] [datetime] NULL,
[quasicouplast] [datetime] NULL,
[quasimaturity] [datetime] NULL,
[C] [float] NULL,
[LC] [float] NULL,
[FC] [float] NULL,
[P] [float] NULL,
[AI] [float] NULL,
[Y] [float] NULL
)
Column

Description

A1

If NCF = 1, the number of days from @Issue to the @Settlement. If NCF = 2 and @Settlement >= quasicoupfirst then DFC1 else the number of days from quasicoupfirst to @Settlement.

A2

If NCF = 1 then NULL. If NCF = 2 and @Settlement >= quasicoupfirst then the number of days from quasicoupfirst to @Settlement else 0.

DSC

Number of days from the @Settlement to the next quasicoupon date.

E

Number of coupon days in the quasicoupon period in which the settlement date falls.

N

Number of coupons between @FirstCoupon and @LastCoupon.

NCL

Number of quasicoupon periods in the odd last period (1 or 2).

NCF

Number of quasicoupon periods in the odd first period (1 or 2).

DLC1

If NCL = 1, the number of days from @LastCoupon to @Maturity else NLL1

DLC2

If NCL =2 then NULL else the number of days from quasicouplast to @Maturity.

NLL1

The normal length of the first quasicoupon period in the last coupon period. If NCL = 1 the length of the period from @LastCoupon to quasimaturity else the length of the period from @LastCoupon to quasimaturity.

NLL2

If NCL = 1 then NULL else the normal length of the period from quasicouplast to quasimaturity.

DFC1

The normal length of the first quasicoupon period minus the number of days from quasistart to @Issue.

DFC2

If NCF = 2 then NULL else NLF2

NLF1

The normal length of the first quasicoupon period in the odd first period. If NCF = 1 then number of days from quasistart to @FirstCoupon else the number of days from quasistart to quasicoupfirst.

NLF2

The normal length for the second quasicoupon period in the odd first period. If NCF = 1 then NULL else the number of days from quasicoupfirst to @FirstCoupon.

Nqf

Number of whole coupons between @Settlement and @FirstCoupon.

quasistart

Implied previous coupon date with respect to @Issue.

quasicoupfirst

If NCF = 1 then NULL else the implied next coupon date with respect to @Issue.

quasicouplast

If NCL = 1 then NULL else the implied next coupon date with respect to @LastCoupon.

quasimaturity

The implied next coupon date with respect to @Maturity.

C

Coupon amount

LC

Last coupon amount

FC

First coupon amount

P

Price. If @Yield is NOT NULL then P is calculated from the inputs otherwise P is the value entered in @Price.

AI

Accrued interest as of the settlement date.

Y

Yield. If @Yield is NOT NULL then Y is the value entered in @Yield otherwise Y is calculated from the inputs.

Remarks
Â· If @Settlement is NULL then @Settlement = GETDATE().
Â· If @Rate is NULL then @Rate = 0.
Â· If @Redemption is NULL then @Redemption = 100.
Â· If @Frequency is NULL then @Frequency = 2.
Â· If @Basis is NULL then @Basis = 0.
Â· If @Frequency is any number other than 1, 2, 4, 6 or 12, or for @Basis = 'A/364' any number other than 1, 2, 4, 6, or 12 as well as 7, 14, 28, 91, 182, or 364 OFLFACTORS returns an error.
Â· If @Basis is invalid (see above list), OFLFACTORS returns an error.
Â· If @Maturity is NULL then an error is returned.
Â· If @Issue is NULL then an error is returned.
Â· If @FirstCoupon is NULL then an error is returned.
Â· If @LastCoupon is NULL then an error is returned.
Â· If @Settlement >= @FirstCoupon then nothing is returned.
Â· The first quasicoupon period in the odd first period is always the quasicoupon period in which @Issue occurs.
Â· The first quasicoupon in the odd last period is always the quasicoupon period in which @LastCoupon occurs.
Â· The previous coupon date for the first quasicoupon period is calculated using @Frequency, @Basis, and @LastCoupon. This is the value returned in quasistart.
Â· If there is only one quasicoupon in the odd first period then quasicoupfirst is NULL. Otherwise the previous coupon date for the second quasicoupon period is calculated using @Frequency, @Basis, and @LastCoupon.
Â· If there is only on quasicoupon period in the odd last period the quasicouplast is NULL.
Examples
This is a bond with an odd short first coupon and an odd short last coupon.
SELECT
*
FROM
wct.OFLFACTORS(
'20130304' @Settlement
,'20220428' @Maturity
,'20121207' @Issue
,'20130315' @FirstCoupon
,'20220315' @LastCoupon
,.03125 @Rate
,NULL @Price
,.02875 @Yield
,100 @Redemption
,2 @Frequency
,1 @Basis
)
This produces the following result (which has been reformatted for ease of viewing).
A1

A2

DSC

E

N

NCL

NCF

DLC1

DLC2

NLL1

NLL2

87

NULL

11

181

18

1

1

44

NULL

184

NULL

DFC1

DFC2

NLF1

NLF2

Nqf

quasistart

quasicoupfirst

quasicouplast

quasimaturity

98

NULL

NULL

NULL

0

9/15/2012

NULL

NULL

9/15/2022

C

LC

FC

P

AI

Y

1.5625

0.373641304

0.845994475

102.0003622

0.751035912

0.02875

This is a bond with an odd long first coupon and an odd long last coupon.
SELECT
*
FROM
wct.OFLFACTORS(
'20130304' @Settlement
,'20221128' @Maturity
,'20120607' @Issue
,'20130315' @FirstCoupon
,'20220315' @LastCoupon
,.03125 @Rate
,NULL @Price
,.02875 @Yield
,100 @Redemption
,2 @Frequency
,1 @Basis
)
This produces the following result (which has been reformatted for ease of viewing).
A1

A2

DSC

E

N

NCL

NCF

DLC1

DLC2

NLL1

NLL2

100

170

11

181

18

2

2

184

74

184

181

DFC1

DFC2

NLF1

NLF2

Nqf

quasistart

quasicoupfirst

quasicouplast

quasimaturity

100

181

184

181

0

3/15/2012

9/15/2012

9/15/2022

3/15/2023

C

LC

FC

P

AI

Y

1.5625

2.201312155

2.411684783

102.1034334

2.316726219

0.02875

This is a bond with an odd long first coupon and an odd short last coupon.
SELECT
*
FROM
wct.OFLFACTORS(
'20130304' @Settlement
,'20220428' @Maturity
,'20120607' @Issue
,'20130315' @FirstCoupon
,'20220315' @LastCoupon
,.03125 @Rate
,101.999004756314 @Price
,NULL @Yield
,100 @Redemption
,2 @Frequency
,1 @Basis
)
This produces the following result.
A1

A2

DSC

E

N

NCL

NCF

DLC1

DLC2

NLL1

NLL2

100

170

11

181

18

1

2

44

NULL

184

NULL

DFC1

DFC2

NLF1

NLF2

Nqf

quasistart

quasicoupfirst

quasicouplast

quasimaturity

100

181

184

181

0

3/15/2012

9/15/2012

NULL

9/15/2022

C

LC

FC

P

AI

Y

1.5625

0.373641304

2.411684783

101.9990048

2.316726219

0.02875

This is a bond with an odd short first coupon and an odd long last coupon.
SELECT
*
FROM
wct.OFLFACTORS(
'20130304' @Settlement
,'20221128' @Maturity
,'20121207' @Issue
,'20130315' @FirstCoupon
,'20220315' @LastCoupon
,.03125 @Rate
,102.104790915433 @Price
,NULL @Yield
,100 @Redemption
,2 @Frequency
,1 @Basis
)
This produces the following result.
A1

A2

DSC

E

N

NCL

NCF

DLC1

DLC2

NLL1

NLL2

87

NULL

11

181

18

2

1

184

74

184

181

DFC1

DFC2

NLF1

NLF2

Nqf

quasistart

quasicoupfirst

quasicouplast

quasimaturity

98

NULL

NULL

NULL

0

9/15/2012

NULL

9/15/2022

3/15/2023

C

LC

FC

P

AI

Y

1.5625

2.201312155

0.845994475

102.1047909

0.751035912

0.02875

This is an example of a bond paying interest every 26 weeks.
SELECT
*
FROM
wct.OFLFACTORS(
'20141004' @Settlement
,'20291212' @Maturity
,'20140730' @Issue
,'20150318' @First_coupon
,'20290228' @Last_coupon
,.1250 @Rate
,NULL @Price
,.1100 @Yld
,100 @Redemption
,182 @Frequency
,9 @Basis
)
This produces the following result.
A1

A2

DSC

E

N

NCL

NCF

DLC1

DLC2

NLL1

NLL2

49

17

165

182

28

2

2

182

105

182

182

DFC1

DFC2

NLF1

NLF2

Nqf

quasistart

quasicoupfirst

quasicouplast

quasimaturity

49

182

182

182

0

20140319

20140917

20290829

20300227

C

LC

FC

P

AI

Y

6.25

9.855769231

7.932692308

110.8460988

2.266483516

0.11

See Also