Updated: 11 Mar 2013

*Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.*

Click here for the SQL2005 version of the DFINTERP function

Use the aggregate function DFINTERP to calculate the interpolated discount factor given a date. DFINTERP uses log-linear interpolation to calculate the interpolation discount factor. DFINTERP is not sensitive to order. The interpolation formula is:

Where

d number of days from *@StartDate* to *@NewDate*

d_{1} number of days from *@StartDate* to MAX(*@DFDate*) <= *@NewDate*

d_{2} number of days from *@StartDate* to MIN(*@DFDate*) > *@NewDate*

df_{1} discount factor for d_{1}

df_{2} discount factor for d_{2}

the discount factor date. *@DFdate* must be of the type **datetime** or of a type that implicitly converts to **datetime**.

the discount factor. *@DF* must be of the type **float** or of a type that implicitly converts to **float**.

the new date value used to calculate the interpolated discount factor. *@DFdate* must be of the type **datetime** or of a type that implicitly converts to **datetime**.

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**.

the return value; discount factor, zero-coupon rate, or continuously compounded zero coupon rate.

float

· If *@StartDate* is NULL, *@StartDate* is set to the current date.

· If *@NewDate* is NULL and error will be returned.

· If a *@DFDate-@DF* pair contains a NULL, then the pair is not used in the interpolation calculation.

· *@StartDate* must remain invariant for the GROUP.

· *@NewDate* must remain invariant for the GROUP.

· @RV must be either 'DF' (discount factor), 'ZC' (zero coupon), or 'CC' (continuously compounded zero coupon).

· In situations where you want to calculate interpolated results for multiple dates, consider using the INTERPDFACT table-valued function.

· For straight-line interpolation of the discount factors, consider using the INTERP function.

· For cubic spline interpolation of the discount factors, consider usign the SPLINE function.

In this example, we put some discount factors int a table, #x, with a start date of 2013-01-15 and calculate the interpolated discount factor for 2013-03-20.

SELECT cast(dfdate as datetime) as dfdate

,df

INTO #x

FROM (

SELECT '2013-01-16',0.999995555575309 UNION ALL

SELECT '2013-01-17',0.999991111170370 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.985084783883980 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 wct.DFINTERP(

dfdate --@DFdate

,df --@DF

,'2013-03-20' --@NewDate

,'2013-01-15' --@StartDate

,'DF' --@RV

) as [Discount Factor]

FROM #x

This produces the following result.

Discount Factor

----------------------

0.999555003248605

Using the same data, we can return the discount factor, zero coupon rate, and continuously compounded zero coupon for 2013-06-30.

SELECT wct.DFINTERP(dfdate,df,'2013-06-30','2013-01-15','DF') as [Discount Factor]

,wct.DFINTERP(dfdate,df,'2013-06-30','2013-01-15','ZC') as [Zero Coupon]

,wct.DFINTERP(dfdate,df,'2013-06-30','2013-01-15','CC') as [CC Zero]

FROM #x

This produces the following result.

Discount Factor Zero Coupon CC Zero

---------------------- ---------------------- ----------------------

0.998709482483852 0.00283941624767274 0.00284125037852567