CFMDURATION

Updated 2023-10-06 13:45:29.297000

Syntax

SELECT [westclintech].[wct].[CFMDURATION] (
  <@Disc_rate, float,>
 ,<@VDate, datetime,>
 ,<@CFamt, float,>
 ,<@CFdate, datetime,>)

Description

Use the aggregate function CFMDURATION to calculate the modified duration of a series of cash flows. The cash flow modified duration is calculated as the first derivative of the present value of the cash flows divided by the present value of the cash flows. The unit of duration is years.

Arguments

@CFamt

the amount of the cash flow. @CFamt is an expression of type float or of a type that can be implicitly converted to float.

@VDate

the date to which the cash flows are discounted. @VDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@CFdate

the date of the cash flow. @CFDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@Disc_rate

the annual interest rate used to discount the cash flows to @vDate. @Disc_Rate is an expression of type float or of a type that can be implicitly converted to float.

Return Type

Remarks

If @VDate IS NULL then @vDate is set equal to GETDATE().

@Disc_rate cannot change within a GROUP BY.

@VDate cannot change within a GROUP BY.

@Disc_rate must be greater than -1.

Each cash flows is discounted using the formula @CFamt * POWER(1+@Disc_rate, -DATEDIFF(d,@VDate,@CFdate) / 365).

Examples

In this example we have a series of cash flows for a single investment and we will calculate the modified duration for that investment.

SELECT wct.CFMDURATION(.050602946, '2012-12-21', cfamt, cfdate) as MDURATION

FROM

(

    VALUES

        ('2013-05-15', 2.5),

        ('2013-11-15', 2.5),

        ('2014-05-15', 2.5),

        ('2014-11-15', 2.5),

        ('2015-05-15', 2.5),

        ('2015-11-15', 2.5),

        ('2016-05-15', 2.5),

        ('2016-11-15', 2.5),

        ('2017-05-15', 2.5),

        ('2017-11-15', 102.5)

) n (cfdate, cfamt);

This produces the following result.

{"columns":[{"field":"MDURATION","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MDURATION":"4.17725927216746"}]}

In this example we have a series of cash flows for 4 different investments and we will calculate the duration for each investment.

SELECT inv,

       wct.CFMDURATION(.05, '2012-12-21', cfamt, cfdate) as MDURATION

FROM

(

    VALUES

        ('investment1', 17659.92, '2014-07-01'),

        ('investment1', -14811.78, '2021-09-11'),

        ('investment1', 11485.2, '2021-05-30'),

        ('investment1', 45711.8, '2013-12-26'),

        ('investment1', -1941.99, '2016-02-02'),

        ('investment1', -37878.39, '2020-07-08'),

        ('investment1', 45889.5, '2021-08-19'),

        ('investment1', 16993.05, '2019-12-29'),

        ('investment1', 28732.54, '2019-12-02'),

        ('investment1', 693170, '2012-12-31'),

        ('investment2', 44508.37, '2020-08-23'),

        ('investment2', -26538.03, '2021-08-16'),

        ('investment2', -7866.52, '2020-02-03'),

        ('investment2', 30232.5, '2018-02-22'),

        ('investment2', 30534.46, '2018-03-27'),

        ('investment2', 5895.6, '2015-07-03'),

        ('investment2', -2623.81, '2017-08-13'),

        ('investment2', 46485.74, '2015-03-30'),

        ('investment2', -8797.36, '2018-04-17'),

        ('investment2', 953681.79, '2012-12-31'),

        ('investment3', -30554.89, '2013-08-10'),

        ('investment3', -33472.78, '2016-01-30'),

        ('investment3', 2932.91, '2022-01-01'),

        ('investment3', 7631.93, '2018-05-19'),

        ('investment3', 39219.87, '2021-01-25'),

        ('investment3', -16996.11, '2021-12-29'),

        ('investment3', 48313.54, '2014-04-02'),

        ('investment3', 45421.88, '2013-07-04'),

        ('investment3', -45801.98, '2015-03-29'),

        ('investment3', 736541.38, '2012-12-31'),

        ('investment4', 38567.37, '2020-07-31'),

        ('investment4', 42848.96, '2018-12-12'),

        ('investment4', -24150.02, '2020-03-26'),

        ('investment4', -34389.75, '2017-05-26'),

        ('investment4', 46295.35, '2013-11-30'),

        ('investment4', 5623.47, '2015-09-19'),

        ('investment4', 39886.29, '2022-11-07'),

        ('investment4', 38997.02, '2019-11-04'),

        ('investment4', 541.91, '2016-02-03'),

        ('investment4', 600426.57, '2012-12-31')

) n (inv, cfamt, cfdate)

GROUP BY inv;

This produces the following result.

{"columns":[{"field":"inv"},{"field":"MDURATION","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"inv":"investment1","MDURATION":"0.427592398671723"},{"inv":"investment2","MDURATION":"0.345022865700141"},{"inv":"investment3","MDURATION":"0.0821137309788492"},{"inv":"investment4","MDURATION":"0.873205709429475"}]}