CFCONVEXITY

Updated 2023-10-06 13:42:32.197000

Syntax

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

Description

Use the aggregate function CFCONVEXITY to calculate the convexity of a series of cash flows. The cash flow convexity is calculated as the second derivative of the present value of the cash flows divided by the present value of the cash flows. The unit of convexity is years squared.

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

float

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 convexity for that investment.

SELECT wct.CFCONVEXITY(.050602946, '2012-12-21', cfamt, cfdate) as CONVEXITY

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":"CONVEXITY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CONVEXITY":"22.6933974996726"}]}

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

SELECT inv
,wct.CFCONVEXITY(.05,'2012-12-21',cfamt,cfdate) as CONVEXITY
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":"CONVEXITY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"inv":"investment1","CONVEXITY":"2.97339353004929"},{"inv":"investment2","CONVEXITY":"1.64169597626245"},{"inv":"investment3","CONVEXITY":"0.991368134802005"},{"inv":"investment4","CONVEXITY":"7.23529738258903"}]}