SQL Server XIRR2005 Function

Updated: 10 September 2010

Syntax

SELECT [wct].[XIRR2005] (
   <@CashFlows_TableName, nvarchar(max),>
  ,<@CashFlows_ColumnName, nvarchar(4000),>
  ,<@CashFlows_GroupedColumnName, nvarchar(4000),>
  ,<@CashFlows_GroupedColumnValue, sql_variant,>
  ,<@CashFlowDates_ColumnName, nvarchar(4000),>
  ,<@Guess, float,>)

Description

Use scalar function XIRR2005 for legacy implementations prior to SQL Server 2008. This function is for campatability purposes only.

Arguments

@CashFlows_TableName

the name, as text, of the table or view which contains the cash flow values to be used in the internal rate of return calculation.

@CashFlows_ColumnName

the name, as text, of the column in table which contains the cash flow value to be used in the internal rate of return calculation.

@CashFlows_GroupedColumnName

the name, as text, of the column in the table which contains the cash flows to group the results on.

@CashFlows_GroupedColumnValue

the column value to do the grouping on.

@CashFlowDates_ColumnName

the name of column in the @CashFlows_Table Name to which contains the date values to be used in the IRR calculation. The column values must evaluate to datetime.

@Guess

the user-supplied initial guess used in the first iteration of the internal rate of return calculation. @Guess is an expression of type float or of a type that can be implicitly converted to float or is NULL.

Return Type

float

Remarks

• For more information see the XIRR aggregate funcion

Example

Create a table to store cash flow projections, by date, for a variety of projects:

CREATE TABLE cf2
(
    proj_no float NOT NULL
  , cf_date datetime NOT NULL
  , cf_amt float NOT NULL
  ,
  PRIMARY KEY
  (
      [proj_no] ASC
    , [cf_date] ASC
  )
)

--Insert the data for 3 projects into the table.
INSERT INTO cf2
VALUES
(1, '11/01/2008', -25000)
INSERT INTO cf2
VALUES
(1, '01/31/2009', 5000)
INSERT INTO cf2
VALUES
(1, '06/01/2009', 10000)
INSERT INTO cf2
VALUES
(1, '10/30/2009', 15000)
INSERT INTO cf2
VALUES
(1, '04/29/2010', 20000)
INSERT INTO cf2
VALUES
(1, '11/26/2010', 25000)
INSERT INTO cf2
VALUES
(2, '11/01/2008', -25000)
INSERT INTO cf2
VALUES
(2, '01/31/2009', 25000)
INSERT INTO cf2
VALUES
(2, '06/01/2009', 20000)
INSERT INTO cf2
VALUES
(2, '10/30/2009', 15000)
INSERT INTO cf2
VALUES
(2, '04/29/2010', 10000)
INSERT INTO cf2
VALUES
(2, '11/26/2010', 5000)
INSERT INTO cf2
VALUES
(3, '11/01/2008', -25000)
INSERT INTO cf2
VALUES
(3, '01/31/2009', 5000)
INSERT INTO cf2
VALUES
(3, '06/01/2009', 25000)
INSERT INTO cf2
VALUES
(3, '10/30/2009', 10000)
INSERT INTO cf2
VALUES
(3, '04/29/2010', 20000)
INSERT INTO cf2
VALUES
(3, '11/26/2010', 15000)

--Enter a SELECT statement to calculate XIRR for the 3 --projects so as to compare the results
select cf2.proj_no
     , wct.XIRR2005('cf2', 'cf_amt', 'proj_no', proj_no, 'cf_date', NULL) as IRR
from cf2
group by cf2.proj_no

Here is the result set

{"columns":[{"field":"rn"},{"field":"IRR"}],"rows":[{"proj_no":1.000000000000000e+000,"IRR":1.475380540527314e+000},{"proj_no":2.000000000000000e+000,"IRR":5.780858886998478e+000},{"proj_no":3.000000000000000e+000,"IRR":2.119138263706021e+000}]}
```table