XIRRT
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.
Syntax
SELECT [wct].[XIRRT](
<@CF_Amt, float,>
,<@Time, float,>
,<@Guess, float,>)
Arguments
@CF_Amt
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.
@Time
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.
@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_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.
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.
See Also