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.
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