INTERPDFACT
Updated: 29 Mar 2013
Use the table-valued-function INTERPDFACT to calculate interpolated discount factors for a range of dates. INTERPDFACT uses the following formula in its calculation.
Where
d number of days from @StartDate to the interpolated date
d1 number of days from @StartDate to the greatest discount factor date less than or equal to the interpolated date
d2 number of days from @StartDate to the lowest discount factor date greater than the interpolated date
df1 discount factor for d1
df2 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
RETURNS TABLE (
[vDate] [datetime] NULL,
[DF] [float] NULL,
[ZC] [float] NULL,
[CC] [float] NULL
)
|
Column
|
Column Description
|
|
vDate
|
The interpolated date.
|
|
DF
|
Discount factor.
|
|
ZC
|
Zero coupon rate.
|
|
CC
|
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.
vDate DF ZC CC
---------- ----------------- ----------------- -----------------
2013-01-16 0.999995555575309 0.001622218617151 0.001622222222071
2013-01-17 0.999991111170370 0.001622218617301 0.001622225827171
2013-01-18 0.999986481628359 0.001644746333518 0.001644757450800
2013-01-19 0.999981728670487 0.001667274049738 0.001667289281630
2013-01-20 0.999976852298516 0.001689801765956 0.001689821323847
2013-01-21 0.999971852514249 0.001712329482169 0.001712353581617
2013-01-22 0.999966729319540 0.001734857198388 0.001734886059131
2013-01-23 0.999961482716284 0.001757384914604 0.001757418760534
2013-01-24 0.999956112706425 0.001779912630822 0.001779951690026
2013-01-25 0.999950742806688 0.001797931836784 0.001797976119136
2013-01-26 0.999945274205812 0.001815951042743 0.001816000734693
2013-01-27 0.999939706905415 0.001833970248707 0.001834025539353
2013-01-28 0.999934040907148 0.001851989454667 0.001852050535799
2013-01-29 0.999928276212688 0.001870008660625 0.001870075726686
2013-01-30 0.999922412823743 0.001888027866586 0.001888101114699
2013-01-31 0.999916450742048 0.001906047072547 0.001906126702502
2013-02-01 0.999910729508426 0.001916775523170 0.001916861085282
2013-02-02 0.999904949527094 0.001927503973795 0.001927595586134
2013-02-03 0.999899110799072 0.001938232424417 0.001938330205998
2013-02-04 0.999893213325388 0.001948960875042 0.001949064945830
2013-02-05 0.999887257107084 0.001959689325665 0.001959799806567
2013-02-06 0.999881242145208 0.001970417776288 0.001970534789163
2013-02-07 0.999875168440823 0.001981146226912 0.001981269894563
2013-02-08 0.999869035994998 0.001991874677534 0.001992005123713
2013-02-09 0.999862844808814 0.002002603128159 0.002002740477565
2013-02-10 0.999856594883364 0.002013331578783 0.002013475957063
2013-02-11 0.999850286219750 0.002024060029405 0.002024211563149
2013-02-12 0.999843918819084 0.002034788480030 0.002034947296780
2013-02-13 0.999837492682488 0.002045516930652 0.002045683158898