DFINTERP
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
d1 number of days from @StartDate to MAX(@DFDate) <= @NewDate
d2 number of days from @StartDate to MIN(@DFDate) > @NewDate
df1 discount factor for d1
df2 discount factor for d2
Syntax
Arguments
@DFdate
the discount factor date. @DFdate must be of the type datetime or of a type that implicitly converts to datetime.
@DF
the discount factor. @DF must be of the type float or of a type that implicitly converts to float.
@NewDate
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.
@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.
@RV
the return value; discount factor, zero-coupon rate, or continuously compounded zero coupon rate.
Return Types
float
Remarks
· 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.
Examples
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