Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server cash flows modified duration


CFMDURATION

Updated: 21 December 2012


Use 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.
Syntax
CFMDURATION 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 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.
             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.
inv                      MDURATION
----------- ----------------------
investment1      0.427592398671723
investment2      0.345022865700141
investment3     0.0821137309788492
investment4      0.873205709429475
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service