XNPV_q
Updated 2024-02-29 21:30:44.043000
Syntax
SELECT [westclintech].[wct].[XNPV_q] (
<@Rate, float,>
,<@CashFlows_RangeQuery, nvarchar(4000),>)
Description
Use the scalar function XNPV_q to calculate the net present value for a schedule of cash flows that is not necessarily periodic using dynamic SQL.
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
@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 determining the cash flows and cash flow dates to be used in this function.
Return Type
float
Remarks
XNPV_q is related to the XIRR function in that XIRR is the value which when used to discount the cash flows returns an XNPV_q of zero.
Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.
Examples
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.
{"columns":[{"field":"proj_no","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NPV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"proj_no":"1","NPV":"40865.7902879521"},{"proj_no":"2","NPV":"44760.4665502211"},{"proj_no":"3","NPV":"42299.4660151055"}]}