Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server discount factor interpolation function


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.

XLeratorDB formula for INTERPDFACT discount factor interpolation for SQL Server
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
 


Copyright 2008-2018 Westclintech LLC         Privacy Policy        Terms of Service