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