# SQL Server 2005 net present value of irregular cash flows

XNPV_q

Updated: 5 August 2010

XNPV_q returns the net present value for a schedule of cash flows that is not necessarily periodic

Given a set of cash flows in ascending date order, the equation for XNPV is:

Where
N = Number of cashflows
ci = Cashflow amount
di = Date of the cash flow
r = 1 + discount rate
Syntax
SELECT [westclintech].[wct].[XNPV_q] (
<@Rate, float,>
,<@CashFlows_RangeQuery, nvarchar(4000),>)
Arguments
@Rate
the annual interest rate to be used to discount the cash flows. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@CashFlows_RangeQuery
the select statement, as text, used in determing the cash flows and cash flow dates to be used in this function.

Return Type
float
Remarks
Â·         XNPV is related to the XIRR function in that XIRR is the value which when used to discount the cash flows returns an XNPV of zero.
Â·         Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.
Example

Create a table to store cash flow projections, by date, for a variety of projects:
CREATE TABLE [dbo].[cf2](
[proj_no] [float] NOT NULL,
[cf_date] [datetime] NOT NULL,
[cf_amt] [float] NOT NULL,
CONSTRAINT [PK_cf2] PRIMARY KEY CLUSTERED
(
[proj_no] ASC,
[cf_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert the data for 3 projects into the table.
INSERT INTO cf2 VALUES (1,'11/01/2008',-25000)
INSERT INTO cf2 VALUES (1,'01/31/2009',5000)
INSERT INTO cf2 VALUES (1,'06/01/2009',10000)
INSERT INTO cf2 VALUES (1,'10/30/2009',15000)
INSERT INTO cf2 VALUES (1,'04/29/2010',20000)
INSERT INTO cf2 VALUES (1,'11/26/2010',25000)
INSERT INTO cf2 VALUES (2,'11/01/2008',-25000)
INSERT INTO cf2 VALUES (2,'01/31/2009',25000)
INSERT INTO cf2 VALUES (2,'06/01/2009',20000)
INSERT INTO cf2 VALUES (2,'10/30/2009',15000)
INSERT INTO cf2 VALUES (2,'04/29/2010',10000)
INSERT INTO cf2 VALUES (2,'11/26/2010',5000)
INSERT INTO cf2 VALUES (3,'11/01/2008',-25000)
INSERT INTO cf2 VALUES (3,'01/31/2009',5000)
INSERT INTO cf2 VALUES (3,'06/01/2009',25000)
INSERT INTO cf2 VALUES (3,'10/30/2009',10000)
INSERT INTO cf2 VALUES (3,'04/29/2010',20000)
INSERT INTO cf2 VALUES (3,'11/26/2010',15000)

Enter a SELECT statement to calculate XIRR for the 3 projects so as to compare the results
select b.proj_no,
wct.XNPV_q
(
.10,
'SELECT a.cf_amt
,a.cf_date
from cf2 a
where a.proj_no = ' + convert(char, b.proj_no) + '
order by 2'

) as NPV
from cf2 b
group by b.proj_no
Here is the result set
proj_no                NPV
---------------------- ----------------------
1                      40865.7902879521
2                      44760.4665502211
3                      42299.4660151055