Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server XIRR function using 30/360 day-count


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.
·         Available in XLeratorDB / financial 2008 only

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

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service