XNPVT
Updated: 13 March 2015
Use the aggregate function XNPVT to calculate the net present value for a series of cash flows with irregular time periods—cash flows of varying amount occurring at various points in time. The net present value is calculated as:
Where
|
N
|
=
|
the number of cash flows
|
|
ci
|
=
|
cash flow amount
|
|
ti
|
=
|
time
|
|
r
|
=
|
1 + discount rate
|
Syntax
SELECT [wct].[XNPVT](
<@Disc_rate, float,>
,<@CF_Amt, float,>
,<@Time, float,>
Arguments
@Disc_rate
the rate to be used in the calculation. @Disc_rate is an expression of type float or of a type that can be implicitly converted to float.
@CF_Amt
the cash flow amounts to be used in the calculation. @CF_Amt is an expression of type float or of a type that can be implicitly converted to float.
@Time
the time (expressed in periods) associated with the @CF_Amt. @Time is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
· @CF_Amt and @Time are passed in as pairs, but they can be passed into the function in any order.
· The @Disc_rate must remain constant for the GROUP of the aggregate.
· @Disc_rate should be in the same units as @Time.
Examples
In this example, we demonstrate the similarity between the XNPV function and the XNPVT function. This requires using the YEARFRAC function to calculate @Time setting the basis parameter = 3.
SELECT
wct.XNPV(.06,amt_cf,date_cf) AS XNPV
,wct.XNPVT(.06,amt_cf,wct.YEARFRAC('2011-11-30',date_cf,3)) as XNPVT
FROM (VALUES
(-100000,'2011-11-30')
,(-50000,'2012-03-15')
,(-2500,'2012-07-18')
,(12500,'2012-11-30')
,(37500,'2013-01-23')
,(75000,'2013-04-30')
,(90000,'2014-02-06')
)n(amt_cf, date_cf)
This produces the following result.
In this example, we calculate the net present value using the actual/actual day-count convention rather than an actual/365 day-count convention.
SELECT
wct.XNPVT(.06,amt_cf,wct.YEARFRAC('2011-11-30',date_cf,1)) as XNPVT
FROM (VALUES
(-100000,'2011-11-30')
,(-50000,'2012-03-15')
,(-2500,'2012-07-18')
,(12500,'2012-11-30')
,(37500,'2013-01-23')
,(75000,'2013-04-30')
,(90000,'2014-02-06')
)n(amt_cf, date_cf)
This produces the following result.
In this example, we calculate the net present value using NL/365 day-count convention, where leap years are counted as having 365 days.
SELECT
wct.XNPVT(.06,amt_cf,wct.DAYSNL('2011-11-30',date_cf)/cast(365 as float)) as XNPVT
FROM (VALUES
(-100000,'2011-11-30')
,(-50000,'2012-03-15')
,(-2500,'2012-07-18')
,(12500,'2012-11-30')
,(37500,'2013-01-23')
,(75000,'2013-04-30')
,(90000,'2014-02-06')
)n(amt_cf, date_cf)
This produces the following result.
In this example we use the T360 function to calculate the net present value using a 30/360 day-count convention and assuming semi-annual compounding.SELECT
wct.XNPVT(SQRT(1.06)-1,amt_cf,wct.T360('2014-02-06','2011-11-30',date_cf,2,0)) as XNPVT
FROM (VALUES
(-100000,'2011-11-30')
,(-50000,'2012-03-15')
,(-2500,'2012-07-18')
,(12500,'2012-11-30')
,(37500,'2013-01-23')
,(75000,'2013-04-30')
,(90000,'2014-02-06')
)n(amt_cf, date_cf)
This produces the following result.
Unlike the XNPV function where cash flows are discounted to the earliest date in the dataset, XNPVT supports discounting the cash flows to any arbitrary date, much like the XDCF function.SELECT
wct.XNPVT(.06,amt_cf,wct.YEARFRAC('2011-06-30',date_cf,3)) as XNPVT
,wct.XDCF(.06,'2011-06-30',amt_cf,date_cf) as XDCF
FROM (VALUES
(-100000,'2011-11-30')
,(-50000,'2012-03-15')
,(-2500,'2012-07-18')
,(12500,'2012-11-30')
,(37500,'2013-01-23')
,(75000,'2013-04-30')
,(90000,'2014-02-06')
)n(amt_cf, date_cf)
This produces the following result.
See Also