XIRR30360
Updated: 13 March 2015
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.
Syntax
SELECT [wct].[XIRR30360](
<@CF, float,>
,<@CFDate, datetime,>
,<@Freq, int,>
,<@DAYS360tpye, int,>
,<@Guess, float,>)
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.
@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.
@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.
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.
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)
This produces the following result.
See Also