NPV
Updated: 5 August 2010
Use NPV to calculate the net present value of an investment based on a series of periodic cash flows and a discount rate
Syntax
SELECT [westclintech].[wct].[NPV] (
<@Rate, float,>
,<@Cashflows_TableName, nvarchar(4000),>
,<@Cashflows_ColumnName, nvarchar(4000),>
,<@Cashflows_GroupedColumnName, nvarchar(4000),>
,<@Cashflows_GroupedColumnValue, sql_variant,>)
Arguments
@Rate
The rate to be used for discounting the cash flows in calculating the net present value. @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.
Return Type
float
Remarks
· For the results to be meaningful, the cash flows should be equally spaced with respect to time and occur at the end of each period.
· Order matters, so make sure that your SELECT statement uses a meaningful index or specify the order of the cash flows explicitly in the WHERE clause.
· NPV is related to the IRR function in that IRR is the value which when used to discount the cash flows returns an NPV of zero.
· It is important to be consistent with the units for @Rate and @Nper. For example if payments are to be paid monthly, then @Rate should be the monthly rate, which can be specified as the annual rate divided by 12. If payments are made quarterly, divide the annual rate by 4. If payments are made semi-annually, divide the annual rate by 2.
· Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.
· NPV returns results consistent with the EXCEL NPV function, which assumes that the first cash flow is discounted at the discount rate; in most financial texts the first cash flow is not discounted. Use the ENPV function for that treatment. The ENPV result divided by the NPV result should be equal to 1 plus the discount rate
Example
Create a table to store cash flow projections, by year, for a variety of projects:
CREATE TABLE [dbo].[cf1](
[proj_no] [float] NOT NULL,
[period] [float] NOT NULL,
[cf_amt] [float] NOT NULL,
CONSTRAINT [PK_cf1] PRIMARY KEY CLUSTERED
(
[proj_no] ASC,
[period] 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 cf1 VALUES(1,1,-25000)
INSERT INTO cf1 VALUES(1,2,5000)
INSERT INTO cf1 VALUES(1,3,10000)
INSERT INTO cf1 VALUES(1,4,15000)
INSERT INTO cf1 VALUES(1,5,20000)
INSERT INTO cf1 VALUES(1,6,25000)
INSERT INTO cf1 VALUES(2,1,-25000)
INSERT INTO cf1 VALUES(2,2,25000)
INSERT INTO cf1 VALUES(2,3,10000)
INSERT INTO cf1 VALUES(2,4,15000)
INSERT INTO cf1 VALUES(2,5,10000)
INSERT INTO cf1 VALUES(2,6,5000)
INSERT INTO cf1 VALUES(3,1,-25000)
INSERT INTO cf1 VALUES(3,2,5000)
INSERT INTO cf1 VALUES(3,3,25000)
INSERT INTO cf1 VALUES(3,4,10000)
INSERT INTO cf1 VALUES(3,5,20000)
INSERT INTO cf1 VALUES(3,6,15000)
Enter a SELECT statement to calculate the NPV for the 3 projects so as to compare the results, assuming a 10% rate:
select c.proj_no
,wct.NPV(.10
,'cf1'
,'cf_amt'
,'proj_no'
,c.proj_no) as NPV
from cf1 c
group by c.proj_no
order by c.proj_no
Here is the result set
proj_no NPV
---------------------- ----------------------
1 25693.5832297053
2 24723.8170178729
3 27903.4986658659