XIRRT
Updated 2023-10-09 13:11:28.747000
Syntax
SELECT [westclintech].[wct].[XIRRT](
<@CF_Amt, float,>
,<@Time, float,>
,<@Guess, float,>)
Description
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.
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.
@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.
@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.
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.
{"columns":[{"field":"XIRRT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XIRRT":"0.24925670279801"}]}
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.
{"columns":[{"field":"XIRRT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XIRRT":"0.249465703484357"}]}
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.
{"columns":[{"field":"XIRRT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XIRRT":"0.249592697878872"}]}
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.
{"columns":[{"field":"XIRRT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"XIRRT":"0.249859433472794"}]}
See Also
IRR - Internal rate of return for irregular periodic cash flows
LRATE - annual interest rate for an annuity with an odd first period
MIRR - Modified internal rate of return for periodic irregular cash flows
RATE - Rate of an annuity given number of periods, periodic payment, present value, and future value
XIRR - Internal rate of return for irregular cash flows
XIRR30360 - Internal rate of return for irregular cash flows using a 30/360 day-count convention