SQL Server T360 Function

Updated 2023-10-07 00:01:14.367000

Description

Use the scalar function T360 to calculate the number of periods (fractional part included) from a cash flow date to a settlement date. The function supports several 30/360 day-count conventions and annual, semi-annual, quarterly, and monthly compounding.

Syntax

SELECT [westclintech].[wct].[T360](
  <@Maturity, datetime,>
 ,<@Settlement, datetime,>
 ,<@CFdate, datetime,>
 ,<@Freq, int,>
 ,<@Method30360, int,>)

Arguments

@Maturity

the maximum or final cash flow date in a series of cash flow dates. @Maturity is an expression of type datetime or of a type that can be implicitly converted to datetime.

@Settlement

the minimum or first cash flow date in a series of cash flow dates. @Settlement is an expression of type datetime or of a type that can be implicitly converted to datetime.

@Freq

the compounding frequency to be used in the calculation. @Freq must be of type int or of a type that can be implicitly converted to int.

@Method30360

the 30/360 day-count convention to be use d in the calculation. @Method30360 must be of a type int or of a type that can be implicitly converted to int.

Return Type

float

Remarks

Set @Method30360 to 0 for the US convention (also known as 30/360 US).

Set @Method30360 to 1 for the Special German convention (also known as 30E/360, 30/360 ICAM, and Eurobond).

Set @Method30360 to 2 for the German convention (also known as 30E360 ISDA).

If @Method30360 is NULL then @Method30360 is set to 0.

Set @Freq to 1 for annual compounding.

Set @Freq to 2 for semi-annual compounding.

Set @Freq to 4 for quarterly compounding.

Set @Freq to 12 for monthly compounding.

If @Freq is NULL then @Freq = 2.

If @CFDate is NULL then @CFDate equals the current date.

If @CFDate > @Maturity then NULL is returned.

If @CFDate < @Settlement then NULL is returned.

Examples

SELECT wct.T360(   '2022-03-16', --@Maturity
                   '2015-03-12', --@Settlement
                   '2015-03-31', --@CFdate
                   2,            --@Freq
                   0             --@Method30360
               ) as T360;

This produces the following result.

T360
0.105555555555556

In this example, we have a series of cash flows and we want to calculate the time values for each cash flow in the set.

--Put some data into a table
SELECT cf,
       d
INTO #t
FROM
(
    VALUES
        (-1012864.58333333, '2015-03-18'),
        (6875, '2015-07-03'),
        (6875, '2016-01-03'),
        (6875, '2016-07-03'),
        (6875, '2017-01-03'),
        (6875, '2017-07-03'),
        (6875, '2018-01-03'),
        (1006875, '2018-07-03')
) n (cf, d);
--Calculate the T360 value for each cash flow
SELECT cf,
       d,
       wct.T360(   n.maturity,   --@Maturity
                   n.settlement, --@Settlement
                   d,            --@CFdate
                   2,            --@Freq
                   0             --@Method30360
               ) as T360
FROM
(SELECT MAX(d) as Maturity, MIN(d) as settlement FROM #t) n ,
#t;