Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server cash flow duration


CFDURATION

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.
Syntax
CFDURATION function for SQL Server
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).
·         Available in XLeratorDB / financial 2008 only
Examples
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
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service