CFCONVEXITY
Updated: 21 December 2012
Use 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.
Syntax
Arguments
@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.
@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.
@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.
@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.
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.
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.
inv CONVEXITY
----------- ----------------------
investment1 2.97339353004929
investment2 1.64169597626245
investment3 0.991368134802005
investment4 7.23529738258903