Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server XIRR function using a compounding frequency


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

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service