XIRR30360

Updated 2023-10-09 13:10:41.967000

Syntax

SELECT [westclintech].[wct].[XIRR30360](
  <@CF, float,>
 ,<@CFDate, datetime,>
 ,<@Freq, int,>
 ,<@DAYS360tpye, int,>
 ,<@Guess, float,>)

Description

Use the aggregate function XIRR30360 to calculate an internal rate of return for a series of irregular cash flows using a 30/360 day-count convention.

Arguments

@Guess

a user-supplied suggestion as to a rate of return to use as the starting point in solution process. @Guess 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.

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

@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 @Guess must remain constant for the GROUP of the aggregate.

If @Guess is NULL then @Guess = 0.1.

XIRR30360 is related to XNPV30360 in that XIRR30360 is solving for a value of @Disc_rate such that the value returned by XNPV30360 is approximately zero, which is defined as having an absolute value of less than .0001.

XIRR30360 requires at least one positive cash flow and one negative cash flow.

If XIRR30360 is unable to find a solution then NULL is returned.

Examples

SELECT wct.XIRR30360(amt_cf, date_cf, 2, 0, NULL) as XIRR30360

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":"XIRR30360","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XIRR30360":"0.235942247765748"}]}

This example demonstrates how to adjust the inputs and outputs to get the same result out of XIRRT and XIRR30360 .

SELECT wct.XIRR30360(amt_cf, date_cf, 2, 0, NULL) as XIRR30360,

       wct.XIRRT(amt_cf, wct.T360('2014-02-26', '2011-11-30', date_cf, 2, 0), NULL)

                 * 2 as XIRRT

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