INTERPDFACT
Updated 2024-02-23 17:08:39.987000
Syntax
SELECT * FROM [wctFinancial].[wct].[INTERPDFACT](
<@InputData_RangeQuery, nvarchar(max),>
,<@iStartDate, datetime,>
,<@iEndDate, datetime,>
,<@Startdate, datetime,>)
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 dated1 = number of days from @StartDate to the greatest discount factor date less than or equal to the interpolated dated2 = number of days from @StartDate to the lowest discount factor date greater than the interpolated datedf1 discount factor for d1df2 discount factor for d2
Arguments
@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.
@InputData_RangeQuery
a T-SQL statement, as a string, that specifies the discount factors and their associated dates.
@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
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "ab4336ef-cb48-4640-9dd2-d84a7d50bef9", "colName": "vDate", "colDatatype": "datetime", "colDesc": "The interpolated date."}, {"id": "e34db906-0223-4705-9819-aaa6b72b0aa5", "colName": "DF", "colDatatype": "float", "colDesc": "Discount factor."}, {"id": "4f5a7be0-3661-4bb3-bc0d-98a13259644e", "colName": "ZC", "colDatatype": "float", "colDesc": "Zero coupon rate."}, {"id": "ad3b8406-d1bf-4608-8641-595a6744ff53", "colName": "CC", "colDatatype": "float", "colDesc": "Continuously compounded zero coupon rate."}]}
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.
{"columns":[{"field":"vDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ZC","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CC","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"vDate":"2013-01-16","DF":"0.999995555575309","ZC":"0.001622218617151","CC":"0.001622222222071"},{"vDate":"2013-01-17","DF":"0.999991111170370","ZC":"0.001622218617301","CC":"0.001622225827171"},{"vDate":"2013-01-18","DF":"0.999986481628359","ZC":"0.001644746333518","CC":"0.001644757450800"},{"vDate":"2013-01-19","DF":"0.999981728670487","ZC":"0.001667274049738","CC":"0.001667289281630"},{"vDate":"2013-01-20","DF":"0.999976852298516","ZC":"0.001689801765956","CC":"0.001689821323847"},{"vDate":"2013-01-21","DF":"0.999971852514249","ZC":"0.001712329482169","CC":"0.001712353581617"},{"vDate":"2013-01-22","DF":"0.999966729319540","ZC":"0.001734857198388","CC":"0.001734886059131"},{"vDate":"2013-01-23","DF":"0.999961482716284","ZC":"0.001757384914604","CC":"0.001757418760534"},{"vDate":"2013-01-24","DF":"0.999956112706425","ZC":"0.001779912630822","CC":"0.001779951690026"},{"vDate":"2013-01-25","DF":"0.999950742806688","ZC":"0.001797931836784","CC":"0.001797976119136"},{"vDate":"2013-01-26","DF":"0.999945274205812","ZC":"0.001815951042743","CC":"0.001816000734693"},{"vDate":"2013-01-27","DF":"0.999939706905415","ZC":"0.001833970248707","CC":"0.001834025539353"},{"vDate":"2013-01-28","DF":"0.999934040907148","ZC":"0.001851989454667","CC":"0.001852050535799"},{"vDate":"2013-01-29","DF":"0.999928276212688","ZC":"0.001870008660625","CC":"0.001870075726686"},{"vDate":"2013-01-30","DF":"0.999922412823743","ZC":"0.001888027866586","CC":"0.001888101114699"},{"vDate":"2013-01-31","DF":"0.999916450742048","ZC":"0.001906047072547","CC":"0.001906126702502"},{"vDate":"2013-02-01","DF":"0.999910729508426","ZC":"0.001916775523170","CC":"0.001916861085282"},{"vDate":"2013-02-02","DF":"0.999904949527094","ZC":"0.001927503973795","CC":"0.001927595586134"},{"vDate":"2013-02-03","DF":"0.999899110799072","ZC":"0.001938232424417","CC":"0.001938330205998"},{"vDate":"2013-02-04","DF":"0.999893213325388","ZC":"0.001948960875042","CC":"0.001949064945830"},{"vDate":"2013-02-05","DF":"0.999887257107084","ZC":"0.001959689325665","CC":"0.001959799806567"},{"vDate":"2013-02-06","DF":"0.999881242145208","ZC":"0.001970417776288","CC":"0.001970534789163"},{"vDate":"2013-02-07","DF":"0.999875168440823","ZC":"0.001981146226912","CC":"0.001981269894563"},{"vDate":"2013-02-08","DF":"0.999869035994998","ZC":"0.001991874677534","CC":"0.001992005123713"},{"vDate":"2013-02-09","DF":"0.999862844808814","ZC":"0.002002603128159","CC":"0.002002740477565"},{"vDate":"2013-02-10","DF":"0.999856594883364","ZC":"0.002013331578783","CC":"0.002013475957063"},{"vDate":"2013-02-11","DF":"0.999850286219750","ZC":"0.002024060029405","CC":"0.002024211563149"},{"vDate":"2013-02-12","DF":"0.999843918819084","ZC":"0.002034788480030","CC":"0.002034947296780"},{"vDate":"2013-02-13","DF":"0.999837492682488","ZC":"0.002045516930652","CC":"0.002045683158898"}]}