XNPV30360

Updated 2024-02-29 21:35:20.157000

Syntax

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

Description

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:

XNPV30360=\sum_{i=1}^nc_i\times{r^{
-t_i}}

WhereN = the number of cash flowsci = @CFiti = T360(@CFdatemax, @CFdatemin, @CFdatei, @Freq, @DAYS360Type)r = 1 + @Rate / @Freq

Arguments

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

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

@CFdate

the date associated with @CF. @CFdate is an expression of type datetime or of a type that can be implicitly converted to datetime.

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

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

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.

{"columns":[{"field":"XNPV30360","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XNPV30360":"43346.5387538406"}]}

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);