XNPV
Updated: 5 August 2010
XNPV returns the net present value for a schedule of cash flows that is not necessarily periodic.
Syntax
SELECT [westclintech].[wct].[XNPV] (
<@Rate, float,>
,<@CashFlows_TableName, nvarchar(4000),>
,<@CashFlows_ColumnName, nvarchar(4000),>
,<@CashFlows_GroupedColumnName, nvarchar(4000),>
,<@CashFlows_GroupedColumnValue, sql_variant,>
,<@CashFlowDates_ColumnName, 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_TableName
the name, as text, of the table or view which contains the cash flow values to be used in the net present value calculation.
@CashFlows_ColumnName
the name, as text, of the column in table which contains the cash flow value to be used in the net present value calculation.
@CashFlows_GroupedColumnName
the name, as text, of the column in the table which contains the cash flows to group the results on.
@CashFlows_GroupedColumnValue
the column value to do the grouping on.
@CashFlowDates_ ColumnName
the name of column in the @CashFlows_Table Name to which contains the date values to be used in the IRR calculation. The column values must evaluate to datetime.
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 c.proj_no,
wct.XNPV(.10,'cf2','cf_amt','proj_no',proj_no, 'cf_date') as [NPV]
from cf2 c
group by c.proj_no
order by c.proj_no
Here is the result set
proj_no NPV
---------------------- ----------------------
1 40865.7902879521
2 44760.4665502211
3 42299.4660151055