Updated: 21 December 2012

Use CFDURATION to calculate the duration of a series of cash flows. The cash flow duration is calculated as the first derivative of the present value of the cash flows divided by the present value of the cash flows multiplied by 1 + discount rate. The unit of duration is years.

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

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

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

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

float

Â· 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).

Â· Available in XLeratorDB / financial 2008 only

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

SELECT wct.CFDURATION(.050602946,'2012-12-21',cfamt,cfdate) as DURATION

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.

DURATION

----------------------

4.38864089754494

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.CFDURATION(.05,'2012-12-21',cfamt,cfdate) as DURATION

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 DURATION

----------- ----------------------

investment1 0.448972018605309

investment2 0.362274008985148

investment3 0.0862194175277917

investment4 0.916865994900949