Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

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:

NPV formula 

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


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service