Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server XNPV using a 30/360 day-count


XNPV30360

Updated: 05 March 2015


Use the aggregate function XNPV30360 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—using a 30/360 day-count convention. The net present value is calculated as:


Where

N
=
the number of cash flows
ci
=
@CFi
ti
=
T360 (@CFdatemax,@CFdatemin,@CFdatei,@Freq,@DAYS360type)
r
=
1 + @Rate / @Freq


Syntax
SELECT [wct].[XNPV30360](
  <@Disc_rate, float,>
 ,<@CF, float,>
 ,<@CFdate, datetime,>
 ,<@Freq, int,>
 ,<@DAYS360type, int,>)

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
the cash flow amounts to be used in the calculation. @CF is an expression of type float or of a type that can be implicitly converted to float.
@CFdate
the date associated with @CF. @CFdate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Freq
the compounding frequency to used in the calculation. @Freq must be of type int or of a type that can be implicitly converted to int.
@DAYS360type
the 30/360 day-count convention to be used in the calculation. @DAYS360type must be of a type int or of a type that can be implicitly converted to int.
 
Return Type
float

Remarks
·         @CF and @CFdate 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.
·         Set @DAYS360type to 0 for the US convention (also known as 30/360 US).
·         Set @DAYS360type to 1 for the Special German convention (also known as 30E/360, 30/360 ICAM, and Eurobond).
·         Set @DAYS360type to 2 for the German convention (also known as 30E360 ISDA).
·         If @DAYS360type is NULL then @DAYS360type is set to 0.
·         Set @Freq to 1 for annual compounding.
·         Set @Freq to 2 for semi-annual compounding.
·         Set @Freq to 4 for quarterly compounding.
·         Set @Freq to 12 for monthly compounding.
·         If @Freq is NULL then @Freq = 2.
·         Available in XLeratorDB / financial 2008 only

Examples
SELECT
   wct.XNPV30360(.06,amt_cf,date_cf,2,0) as XNPV30360
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.
 


This example demonstrates how to adjust the inputs to get the same result out of
XNPVT and XNPV30360.
DECLARE @disc_rate as float = .06
DECLARE @freq as int = 2
DECLARE @Days360type as int = 0
 
SELECT
   wct.XNPV30360(@disc_rate,amt_cf,date_cf,@freq,@Days360type) as XNPV30360
,wct.XNPVT(@disc_rate/@freq,amt_cf,wct.T360('2014-02-26','2011-11-30',date_cf,@freq,@Days360type)) 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

 

See Also

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service