Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server XNPV using a compounding frequency


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.
·         Available in XLeratorDB / financial 2008 only

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

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service