DFINTERP

Updated 2024-02-22 17:09:22.287000

Syntax

SELECT [westclintech].[wct].[DFINTERP] (
  <@DFdate, datetime,>
 ,<@DF, float,>
 ,<@NewDate, datetime,>
 ,<@StartDate, datetime,>
 ,<@RV, nvarchar(4000),>)

Description

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:

df=df_1^{(1-\alpha)*d\slash{d_1}}*df_2^{\alpha*d\slash{d_2}

\alpha=\frac{d-d_1}{d_2-d_1}

Whered number of days from @StartDate to @NewDated1 number of days from @StartDate to MAX(@DFDate) <= @NewDated2 number of days from @StartDate to MIN(@DFDate) > @NewDatedf1 discount factor for d1df2 discount factor for d2

Arguments

@RV

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

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

@DFdate

the discount factor date. @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.

Return Type

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

{"columns":[{"field":"Discount Factor","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.

{"columns":[{"field":"Discount Factor","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Zero Coupon","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CC Zero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Discount Factor":"0.998709482483852","Zero Coupon":"0.00283941624767274","CC Zero":"0.00284125037852567"}]}