Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server number of periods between 2 dates


T360

Updated: 13 March 2015


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 [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.
@CFDate
the date of interest for the function. @CFDate 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 used 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.
·         Available in XLeratorDB / financial 2008 only

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

This produces the following result.

 

See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service