Updated: 13 March 2015

Use the aggregate function XIRRT to calculate an internal rate of return for a series of cash flows with irregular time periodsâ€”cash flows of varying amount occurring at various points in time.

SELECT [wct].[XIRRT](

<@CF_Amt, float,>

,<@Time, float,>

,<@Guess, float,>)

Arguments

the cash flow amounts to be used in the calculation. *@CF_Amt* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the time (expressed in periods) associated with the *@CF_Amt*. *@Time* is an expression of type **float** or of a type that can be implicitly converted to **float**.

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_Amt* and *@Time* 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.

Â· The solutionwill be returned in the same units as *@Time*.

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

Â· XIRRT requires at least one positive cash flow and one negative cash flow.

Â· If XIRRT is unable to find a solution then NULL is returned.

Â· Available in XLeratorDB / financial 2008 only

Examples

In this example, we calculate the internal rate of return for a series of cash flows where time has been calculated using the actual/365 day-count convention. This requires using the YEARFRAC function to calculate *@Time* setting the basis parameter = 3.

SELECT

wct.XIRRT(amt_cf,wct.YEARFRAC('2011-11-30',date_cf,3),NULL) 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.

In this example, we calculate the internal rate of return using the actual/actual day-count convention rather than an actual/365 day-count convention.

SELECT

wct.XIRRT(amt_cf,wct.YEARFRAC('2011-11-30',date_cf,1),NULL) 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.

In this example, we calculate the internal rate of return using NL/365 day-count convention, where leap years are counted as having 365 days.

SELECT

wct.XIRRT(amt_cf,wct.DAYSNL('2011-11-30',date_cf)/cast(365 as float),NULL) 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.

In this example we use the T360 function to calculate the internal rate of return using a 30/360 day-count convention and assuming semi-annual compounding. We need to adjust the solution to turn it into an annual rate.

SELECT

POWER(1+wct.XIRRT(amt_cf,wct.T360('2014-02-06','2011-11-30',date_cf,2,0),0),2)-1 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.

Â· RATE - Rate of an annuity given number of periods, periodic payment, present value, and future value