Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server discounted cash flow function


XDCF

Updated: 21 December 2012


Use the aggregate function XDCF to calculate the discounted cash flows value of a series of irregular cash flows—cash flows of varying amounts occurring on various dates. All cash flows in a group are discounted to the settlement date using the same rate.
Syntax
XLeratorDB syntax for XDCF function for SQL Server
Arguments
@Disc_Rate
the rate to be used for discounting the cash flows in calculating the net present value. @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 being discounted. @VDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@CFAmt
the cash flow amounts. @CFAmt is an expression of type float or of a type that can be implicitly converted to float.
@CFDate
the date on which the cash flow occurred. @CF_Date is an expression of type datetime or of a type that can be implicitly converted to datetime.
Return Type
float
Remarks
·         The XDCF aggregate function requires a series of cash flows (@CFAmt) and the dates on which those cash flows occurred (@CFDate) as input; the order of the cash flows is not important.
·         Dates in which the cash flow is zero, or in which there is no cash flow, do not have to be included.
·         There can be multiple cash flows with the same date.
·         @Disc_Rate must remain constant for the GROUP of the aggregate.
·         @VDate must remain constant for the GROUP of the aggregate.
·         If @Disc_Rate is less than or equal to -1, a NULL will be returned.
·         @Disc_Rate is the annual rate.
·         Available in XLeratorDB / financial 2008 only
Examples
In this example the cash flows are being discounted, using a rate of 6%, to 21-Dec-2012.
SELECT wct.XDCF(.06,'2012-12-21',cfamt,cfdate) as DCF
FROM (VALUES
      ('2016-04-19',-87268.05),
      ('2019-11-13',70231.96),
      ('2021-06-13',-457.02),
      ('2016-10-29',-40603.18),
      ('2021-03-16',83598.77),
      ('2014-03-15',-30994.43),
      ('2022-03-30',34666.49),
      ('2020-04-30',49029.24),
      ('2018-02-25',-93685.98),
      ('2015-04-10',-59199.55)
      )n(cfdate,cfamt)
This produces the following result.
                   DCF
----------------------
     -103643.043186434
 
XDCF is an AGGREGATE function and supports all the syntax available to any SQL Server AGGREGATE function. Here is an example, using a GROUP BY, where we want to discount quarterly cash flow projection by quarter for 2013 and 2014 as at the end of 2012. We will use the CALCDATE function to convert the quarters to datetime values.
-- Put some data into a table #t
SELECT region
      ,cf as cfamt
      ,wct.CALCDATE(yr, quar * 3 + 1, 1) - 1 as cfdate
INTO #t
FROM (
      VALUES
            ('New England',2013,1,9589),
            ('New England',2013,2,5866),
            ('New England',2013,3,9530),
            ('New England',2013,4,6805),
            ('New England',2014,1,2567),
            ('New England',2014,2,9403),
            ('New England',2014,3,5607),
            ('New England',2014,4,2469),
            ('MidAtlantic',2013,1,4366),
            ('MidAtlantic',2013,2,5488),
            ('MidAtlantic',2013,3,5955),
            ('MidAtlantic',2013,4,5450),
            ('MidAtlantic',2014,1,7255),
            ('MidAtlantic',2014,2,2458),
            ('MidAtlantic',2014,3,1738),
            ('MidAtlantic',2014,4,3866),
            ('Southeast',2013,1,1671),
            ('Southeast',2013,2,4383),
            ('Southeast',2013,3,4913),
            ('Southeast',2013,4,8209),
            ('Southeast',2014,1,1476),
            ('Southeast',2014,2,3056),
            ('Southeast',2014,3,3931),
            ('Southeast',2014,4,5476)
      ) n(region, yr, quar, cf)
-- Calculate the DCF by region as of 2012-12-31
SELECT region
,wct.XDCF(.06,'2012-12-31',cfamt,cfdate) as DCF
FROM #t
GROUP BY region
-- Clean up
DROP TABLE #t
This produces the following result.
region                         DCF
----------- ----------------------
MidAtlantic       34487.9198333274
New England       48961.2344980044
Southeast         30950.6038208605
In this example, we will have different rates for different regions and use the XDCF function to discount to 31-Dec-2012.
SELECT n.region
,wct.XDCF(n.rate,'2012-12-31',m.cf,wct.CALCDATE(m.yr, m.quar * 3 + 1, 1) - 1) as DCF
FROM (
   VALUES
   ('New England', .0600),
   ('MidAtlantic', .0575),
   ('Southeast', .0550)
   ) n(region, rate)
CROSS APPLY(VALUES
      ('New England',2013,1,9589),
      ('New England',2013,2,5866),
      ('New England',2013,3,9530),
      ('New England',2013,4,6805),
      ('New England',2014,1,2567),
      ('New England',2014,2,9403),
      ('New England',2014,3,5607),
      ('New England',2014,4,2469),
      ('MidAtlantic',2013,1,4366),
      ('MidAtlantic',2013,2,5488),
      ('MidAtlantic',2013,3,5955),
      ('MidAtlantic',2013,4,5450),
      ('MidAtlantic',2014,1,7255),
      ('MidAtlantic',2014,2,2458),
      ('MidAtlantic',2014,3,1738),
      ('MidAtlantic',2014,4,3866),
      ('Southeast',2013,1,1671),
      ('Southeast',2013,2,4383),
      ('Southeast',2013,3,4913),
      ('Southeast',2013,4,8209),
      ('Southeast',2014,1,1476),
      ('Southeast',2014,2,3056),
      ('Southeast',2014,3,3931),
      ('Southeast',2014,4,5476)
      ) m(region, yr, quar, cf)
WHERE n.region = m.region
GROUP BY n.region
This produces the following result.
region                         DCF
----------- ----------------------
New England       48961.2344980044
MidAtlantic       34569.5447876255
Southeast         31119.6493935861
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service