DFINTERP
Updated: 11 Mar 2013
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