SQL Server INTERPDFACT Function

Updated 2024-02-23 17:08:39.987000

Description

Use the table-valued-function INTERPDFACT to calculate interpolated discount factors for a range of dates. INTERPDFACT uses the following formula in its calculation.

df=df_1^{(1-\alpha)*d\slash{d_1}}*df_2^{\alpha*d\slash{d_2}

\alpha=\frac{d-d_1}{d_2-d_1}

Where

d = number of days from @StartDate to the interpolated date

d₁ = number of days from @StartDate to the greatest discount factor date less than or equal to the interpolated date

d₂ = number of days from @StartDate to the lowest discount factor date greater than the interpolated date

df₁ discount factor for d₁

df₂ discount factor for d2

Syntax

SELECT * FROM [wctFinancial].[wct].[INTERPDFACT](
  <@InputData_RangeQuery, nvarchar(max),>
 ,<@iStartDate, datetime,>
 ,<@iEndDate, datetime,>
 ,<@Startdate, datetime,>)

Arguments

@InputData_RangeQuery

a T-SQL statement, as a string, that specifies the discount factors and their associated dates.

@iStartDate

the start date of the interpolation date range. @iStartDate must be of the type datetime or of a type that implicitly converts to datetime.

@iEndDate

the end date of the interpolation date range. @iEndDate must be of the type datetime or of a type that implicitly converts to datetime.

@Startdate

the starting date used in the calculation of the discount factors. @StartDate must be of the type datetime or of a type that implicitly converts to datetime.

Return Type

table

Remarks

The function is insensitive to order; it does not matter what order the dates and rates are passed in.

If @StartDate is NULL, it defaults to GETDATE().

If @iStartDate and @iEndDate are NULL, then the function will return the interpolated discount factor for every date from the start of the yield curve to the end.

Examples

SELECT cast(dfdate as datetime) as dfdate,
       df
INTO   #x
  FROM (   SELECT '2013-01-16',
                  0.999995555575309
           UNION ALL
           SELECT '2013-01-17',
                  0.99999111117037
           UNION ALL
           SELECT '2013-01-24',
                  0.999956112706425
           UNION ALL
           SELECT '2013-01-31',
                  0.999916450742048
           UNION ALL
           SELECT '2013-02-18',
                  0.999804481000583
           UNION ALL
           SELECT '2013-03-18',
                  0.999574621744643
           UNION ALL
           SELECT '2013-04-17',
                  0.999241679910437
           UNION ALL
           SELECT '2013-06-19',
                  0.998800609148515
           UNION ALL
           SELECT '2013-09-18',
                  0.998022836090921
           UNION ALL
           SELECT '2013-12-18',
                  0.997197057207847
           UNION ALL
           SELECT '2014-03-19',
                  0.996311568695976
           UNION ALL
           SELECT '2014-06-18',
                  0.995354720378904
           UNION ALL
           SELECT '2014-09-17',
                  0.994289565586446
           UNION ALL
           SELECT '2014-12-17',
                  0.993104681356623
           UNION ALL
           SELECT '2015-01-19',
                  0.992402694592988
           UNION ALL
           SELECT '2016-01-18',
                  0.98508478388398
           UNION ALL
           SELECT '2017-01-17',
                  0.973098042807202
           UNION ALL
           SELECT '2018-01-17',
                  0.955265832115111
           UNION ALL
           SELECT '2020-01-17',
                  0.906604451702898
           UNION ALL
           SELECT '2023-01-17',
                  0.820620615064395
           UNION ALL
           SELECT '2043-01-19',
                  0.385646181323946) n(dfdate, df);

SELECT *
  FROM wct.INTERPDFACT('SELECT dfdate, df FROM #x', --@InputData_RangeQuery
                       '2013-01-16', --@iStartDate
                       '2013-02-13', --@iEndDate
                       '2013-01-15' --@StartDate
    );

This produces the following result.