Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server net future value function


XNFV

Updated: 07 February 2011


Use XNFV to calculate the net future value of a series of irregular cash flows—cash flows of varying amounts occurring on various dates. All cash flows in a group are compounded to the latest cash flow in the group.
Syntax
SELECT [wct].[XNFV] (
  <@Rate, float,>
 ,<@CF_Amt, float,>
 ,<@CF_Date, datetime,>)
Arguments
@Rate
the rate to be used for compounding the cash flows in calculating the net future value. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@CF_Amt
the cash flow amounts. @CF_Amt is an expression of type float or of a type that can be implicitly converted to float.
@CF_Date
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 XNFV aggregate function requires a series of cash flows (@CF_Amt) and the dates on which those cash flows occurred (@CF_Date) as input. As a result, 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.
·         The rate (@Rate) must remain constant for the aggregate GROUP.
·         If the rate (@Rate) is equal to -1, the result will be the same of the cash flows for the latest date.
·         @Rate is the annual rate.
·         Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.
·         Available in XLeratorDB / financial 2008 only
Examples
In this example we have cash flows occuring in sporadically from 30-Nov-2011 through to 06-Feb-2014. The result reflects the compounding of the cash flows, using a rate of 6%, to 06-Feb-2014.
SELECT wct.XNFV(.06, cf_amt, cf_date) as NFV
FROM (VALUES
            (-100000, '11/30/2011'),
            (-50000, '3/15/2012'),
            (-2500, '7/18/2012'),
            (12500, '11/30/2012'),
            (37500, '1/23/2013'),
            (75000, '4/30/2013'),
            (90000, '2/6/2014')          
            ) n(cf_amt, cf_date)
This produces the following result.
                   NFV
----------------------
      49496.1812211834
 
(1 row(s) affected)


In this example, we will use the same cash flows as the previous example, but instead of the earliest cash flows being the 30th of November, 2011, we will make the earliest cash flow the 31st of January, 2011. Since XNFV is insensitive to order, we can just add another row at the end of the derived table. Since there is no change to the end date, this should produce the same result.
SELECT wct.XNFV(.06, cf_amt, cf_date) as NFV
FROM (VALUES
            (-100000, '11/30/2011'),
            (-50000, '3/15/2012'),
            (-2500, '7/18/2012'),
            (12500, '11/30/2012'),
            (37500, '1/23/2013'),
            (75000, '4/30/2013'),
            (90000, '2/6/2014'),   
            (0, '01/31/2011')
            ) n(cf_amt, cf_date)
This produces the following result.
                   NFV
----------------------
      49496.1812211834
 
(1 row(s) affected)


This produces exactly the same result as the previous example. This is because the cash flows are compounded from the date of the cash flow to the maximum date in the GROUP. Adding an earlier cash flow with a zero amount has no impact, as the end date of the set has not changed and the future value of zero is still zero.
If we change the end date, the result will change, even if the cash flow amount for that date is zero. In this example, we will change the end date 31-Dec-2014.
SELECT wct.XNFV(.06, cf_amt, cf_date) as NFV
FROM (VALUES
            (-100000, '11/30/2011'),
            (-50000, '3/15/2012'),
            (-2500, '7/18/2012'),
            (12500, '11/30/2012'),
            (37500, '1/23/2013'),
            (75000, '4/30/2013'),
            (90000, '2/6/2014'),   
            (0, '12/31/2014')
            ) n(cf_amt, cf_date)
This produces the following result.
                   NFV
----------------------
      52156.9643149462
 
(1 row(s) affected)


If it is not practical to add another row to the input data set, you could use the XFV function to extend the end date of calculation.
SELECT wct.XFV(MIN(cast(cf_date as datetime))
      ,MAX(cast(cf_date as datetime))
      ,wct.CALCDATE(2014,12,31)
      ,.06
      ,.06
      ,wct.XNFV(.06, cf_amt, cf_date)) as NFV
FROM (VALUES
            (-100000, '11/30/2011'),
            (-50000, '3/15/2012'),
            (-2500, '7/18/2012'),
            (12500, '11/30/2012'),
            (37500, '1/23/2013'),
            (75000, '4/30/2013'),
            (90000, '2/6/2014')    
            ) n(cf_amt, cf_date)
This produces the following result.
                   NFV
----------------------
      52156.9643149462
 
(1 row(s) affected)
 
In this example, we will add another column to the derived table from the previous example, which will allow for a different rate for each period. This will return an error message, as the function requires that the rate value be the same for all cash flows.
SELECT wct.XNFV(rate, cf_amt, cf_date)
FROM (VALUES
            (-100000, '11/30/2010', .0060),
            (-50000, '3/15/2011', .0090),
            (-2500, '7/18/2011', .0110),
            (12500, '11/30/2011', .0140),
            (37500, '1/23/2012', .0160),
            (75000, '4/30/2012', .0220),
            (90000, '2/6/2014', .0240),
            (0, '12/31/2014', .0005)     
            ) n(cf_amt, cf_date, rate)
This produces the following message and will not return a result.
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "XNFV":
XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:
[Product version 1.7 Build: 0126.208]
An error occurred in Aggregate Function. (Accumulate) - Invalid Rate - Rate parameter cannot vary
Function: XNPV
Passed Parameters:
    'Disc_rate' = System.Data.SqlTypes.SqlDouble:0.009
    'CF_Amt' = System.Data.SqlTypes.SqlDouble:-50000
    'CF_Date' = System.Data.SqlTypes.SqlDateTime:3/15/2011 12:00:00 AM
 
Internal refs:
    'd_rate' = System.Double:0.006
    'disc_amt' = System.Double:-100000
    'start_date' = System.Double:40512
*** Extra Information: Invalid Rate - Rate parameter cannot vary
XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:
   at XLeratorDB_financial2008.XNPV.HandleException(String Method, String Ref, XNPV& Merge, FunctionParamsClass& Params, Exception& Ex)
   at XLeratorDB_financial2008.XNPV.Accumulate(SqlDouble Disc_rate, SqlDouble CF_Amt, SqlDateTime CF_Date)
.
 

If you want to caclute the present value of future cash flows using different rates for different periods, you can use the SUM function with the XNFV function.
In this example, we set the rate equal to -1. It will return a zero, because the cash flow for the latest date is zero.
SELECT wct.XNFV(-1.0, cf_amt, cf_date) as NFV
FROM (VALUES
            (-100000, '11/30/2010'),
            (-50000, '3/15/2011'),
            (-2500, '7/18/2011'),
            (12500, '11/30/2011'),
            (37500, '1/23/2012'),
            (75000, '4/30/2012'),
            (90000, '2/6/2014'),
            (0, '12/31/2014')
            ) n(cf_amt, cf_date)
This produces the following result.
                   NFV
----------------------
                     0
 
(1 row(s) affected)
 


XNFV 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 compund quarterly cash flow projection by quarter for 2011 and 2012 as at the end of 2012. We will use the CALCDATE function to convert the quarters to datetime values.
SELECT region
      ,wct.XNFV(.06, cf, wct.CALCDATE(yr, quar * 3 + 1, 1) - 1) as NFV
FROM (
      VALUES
            ('New England',2011,1,9589),
            ('New England',2011,2,5866),
            ('New England',2011,3,9530),
            ('New England',2011,4,6805),
            ('New England',2012,1,2567),
            ('New England',2012,2,9403),
            ('New England',2012,3,5607),
            ('New England',2012,4,2469),
            ('MidAtlantic',2011,1,4366),
            ('MidAtlantic',2011,2,5488),
            ('MidAtlantic',2011,3,5955),
            ('MidAtlantic',2011,4,5450),
            ('MidAtlantic',2012,1,7255),
            ('MidAtlantic',2012,2,2458),
            ('MidAtlantic',2012,3,1738),
            ('MidAtlantic',2012,4,3866),
            ('Southeast',2011,1,1671),
            ('Southeast',2011,2,4383),
            ('Southeast',2011,3,4913),
            ('Southeast',2011,4,8209),
            ('Southeast',2012,1,1476),
            ('Southeast',2012,2,3056),
            ('Southeast',2012,3,3931),
            ('Southeast',2012,4,5476)
      ) n(region, yr, quar, cf)
GROUP BY REGION
This produces the following result.
region                         NFV
----------- ----------------------
MidAtlantic       38754.3002008636
New England       55018.3492757592
Southeast         34779.3907652592
 
(3 row(s) affected)


In this example, we will have a differente rate for each region.
;with mycte as (
SELECT *
FROM (
      VALUES
      ('New England', .0300),
      ('MidAtlantic', .0270),
      ('Southeast', .0240)
      ) n(region, rate)
)
SELECT n.region
      ,wct.XNFV(mycte.rate, cf, wct.CALCDATE(yr, quar * 3 + 1, 1) - 1) as NFV
FROM (
      VALUES
            ('New England',2011,1,9589),
            ('New England',2011,2,5866),
            ('New England',2011,3,9530),
            ('New England',2011,4,6805),
            ('New England',2012,1,2567),
            ('New England',2012,2,9403),
            ('New England',2012,3,5607),
            ('New England',2012,4,2469),
            ('MidAtlantic',2011,1,4366),
            ('MidAtlantic',2011,2,5488),
            ('MidAtlantic',2011,3,5955),
            ('MidAtlantic',2011,4,5450),
            ('MidAtlantic',2012,1,7255),
            ('MidAtlantic',2012,2,2458),
            ('MidAtlantic',2012,3,1738),
            ('MidAtlantic',2012,4,3866),
            ('Southeast',2011,1,1671),
            ('Southeast',2011,2,4383),
            ('Southeast',2011,3,4913),
            ('Southeast',2011,4,8209),
            ('Southeast',2012,1,1476),
            ('Southeast',2012,2,3056),
            ('Southeast',2012,3,3931),
            ('Southeast',2012,4,5476)
      ) n(region, yr, quar, cf), mycte
WHERE mycte.region = n.region
GROUP BY n.REGION
This produces the following result.
region                         NFV
----------- ----------------------
MidAtlantic       37551.9727299143
New England        53419.820026477
Southeast         33778.4395819462
 

(3 row(s) affected)



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service