Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server function to calculate the interpolated discount factor


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:

XLeratorDB formula for DFIMTERP discount factor interpolation for SQL Server
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
XLeratorDB syntax for DFINTERP function for discount factor interpolation for SQL Server 2008
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
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service