XNPV
Updated 2024-02-29 21:28:59.140000
Syntax
SELECT [westclintech].[wct].[XNPV] (
<@Rate, float,>
,<@CF_Amt, float,>
,<@CF_Date, datetime,>)
Description
Use the aggregate function XNPV to calculate the net present 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 earliest cash flow in the group using the same rate.
Given a set of cash flows in ascending date order, the equation for XNPV is:
XNPV=\sum_{i=1}^nc_i*r^\frac{\left(d_1-d_i\right)}{365}
WhereN = Number of cashflowsci = Cashflow amountdi = Date of the cash flowR = 1 + discount rate## Arguments
@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.
@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.
@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 XNPV 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.
There can be multiple cash flows with the same date.
The discount rate (@Rate) must remain constant for the GROUP of the aggregate.
If the discount rate (@Rate) is less than or equal to -1, a NULL will be returned.
@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.
Examples
In this example we have cash flows occuring in sporadically from 30-Nov-2011 through to 06-Feb-2014. The result reflects the discounting of the cash flows, using a rate of 6%, to 30-Nov-2011.
SELECT wct.XNPV(.06, cf_amt, cf_date) as NPV
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.
NPV
----------------------
43568.8517245775
In this example, we will use the same cash flows as the previous example, but instead of discounting the cash flows to the 30th of November, 2011, we will discount them to the 31st of January, 2011. Since XNPV is insensitive to order, we can just add another row at the end of the derived table.
SELECT wct.XNPV(.06, cf_amt, cf_date) as NPV
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.
NPV
----------------------
41511.5337068439
We could have achieved the same result with a variety of different syntaxes. Here’s another way to arrive at the same result.
SELECT wct.XNPV(.06, cf_amt, cf_date) as NPV
FROM ( SELECT 0 as cf_amt,
'01/31/2011' as cf_date
UNION
SELECT *
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) ) m
This produces the following result.
NPV
----------------------
41511.5337068439
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.XNPV(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/2013', .0240),
(0, '01/31/2011', .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 1A .NET Framework error occurred during execution of user-defined routine or aggregate "XNPV":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
If you want to calculate the present value of future cash flows using different rate for different periods, you can use the SUM function in conjunction with the XPV function.
In this example, we set the rate equal to -1.
SELECT wct.XNPV(-1.0, cf_amt, cf_date) as NPV
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/2013'),
(0, '01/31/2011')) n (cf_amt, cf_date)
This produces the following message result.
NPV
----------------------
NULL
Msg 6522, Level 16, State 2, Line 1A .NET Framework error occurred during execution of user-defined routine or aggregate "ENPV":Rate cannot vary from its original value of 0.0075
If you want to calculate the present value of future cash flows using different rates for different periods, you can use the SUM function with the NPV function.
In this example, we set the rate equal to -1.
SELECT wct.NPV(-1.0, cf_amt, per) as NPV
FROM
(
VALUES
(-10000, 1),
(2000, 2),
(1500, 5),
(3000, 6),
(3800, 10),
(2500, 14),
(2500, 14)
) n (cf_amt, per);
This produces the following result.
{"columns":[{"field":"NPV"}],"rows":[{"NPV":"NULL"}]}
XNPV 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 2011 and 2012 as at the end of 2010. We will use the CALCDATE function to convert the quarters to datetime values.
SELECT region,
cf as cf_amt,
wct.CALCDATE(yr, quar * 3 + 1, 1) - 1 as cf_date
INTO #t
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);
INSERT INTO #T
SELECT DISTINCT REGION,
0,
'12/31/2010'
FROM #T;
SELECT REGION,
wct.XNPV(.06, cf_amt, cf_date) as NPV
FROM #T
GROUP BY REGION;
This produces the following result.
REGION NPV
----------- ----------------------
MidAtlantic 34485.683450933
New England 48958.3186196824
Southeast 30948.5929130596
In this example, we will have different rates for different regions and use the XPV function to discount to 31-Dec-2011.
;with mycte
as (SELECT *
FROM ( VALUES ('New England', .0600),
('MidAtlantic', .0575),
('Southeast', .0550)) n (region, rate) )
SELECT n.region,
wct.XPV(
mycte.rate,
'2010-12-31',
MIN(wct.CALCDATE(yr, quar * 3 + 1, 1) - 1),
wct.XNPV(mycte.rate, N.cf, wct.CALCDATE(yr, quar * 3 + 1, 1) - 1)) AS XNPV
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 n.region = mycte.region
GROUP BY n.region,
mycte.rate
This produces the following result.
region XNPV
----------- ----------------------
MidAtlantic 34567.3912690366
New England 48958.3186196824
Southeast 31117.7864263186
See Also
XNFV - Net Future Value for irregular cash flows
XDCF - Discounted cash flow value
XPV - Calculate the discounted value of a cash flow between two dates.
XIRR - Internal rate of return for irregular cash flows
NPV - Net Present Value for irregular periodic cash flows using the Excel definition