IRR_q

Updated 2023-10-09 13:06:29.877000

Syntax

SELECT [westclintech].[wct].[IRR_q] (
   <@Cashflows_RangeQuery, nvarchar(4000),>
 ,<@Guess, float,>)

Description

Use the scalar function IRR_q to calculate the internal rate of return for a series of cash flows using dynamic SQL.

Arguments

@Cashflows_RangeQuery

a select statement, as text, which specifies the cash flow values to be used in the internal rate of return calculation.

@Guess

a user-supplied suggestion as to a rate of return to use as a starting point in the iteration calculations. If you do not wish to supply a guess, make this argument NULL. @Guess is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

For IRR_q calculations, order matters, so the order in which the cash flows are returned, matters. Make sure that you either use a meaningful index or specify the order in the WHERE clause.

IRR_q and NPV are related in that the IRR is solving for the value that makes the cash flows, as sent to NPV, equal to zero.

IRR_q solves for NPV equal to zero, iteratively with a maximum of 100 iterations. If IRR fails to resolve to zero within the maximum number of iterations, it will return an error.

Examples

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

CREATE TABLE [dbo].[cf1]

(

    [proj_no] [float] NOT NULL,

    [period] [float] NOT NULL,

    [cf_amt] [float] NOT NULL,

    CONSTRAINT [PK_cf1]

        PRIMARY KEY CLUSTERED (

                                  [proj_no] ASC,

                                  [period] ASC

                              )

        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

                  ALLOW_ROW_LOCKS = ON,

              ALLOW_PAGE_LOCKS = ON

             ) ON [PRIMARY]

) ON [PRIMARY];

Insert the data for 3 projects into the table.

INSERT INTO cf1

VALUES

(1, 1, -25000);

INSERT INTO cf1

VALUES

(1, 2, 5000);

INSERT INTO cf1

VALUES

(1, 3, 10000);

INSERT INTO cf1

VALUES

(1, 4, 15000);

INSERT INTO cf1

VALUES

(1, 5, 20000);

INSERT INTO cf1

VALUES

(1, 6, 25000);

INSERT INTO cf1

VALUES

(2, 1, -25000);

INSERT INTO cf1

VALUES

(2, 2, 25000);

INSERT INTO cf1

VALUES

(2, 3, 10000);

INSERT INTO cf1

VALUES

(2, 4, 15000);

INSERT INTO cf1

VALUES

(2, 5, 10000);

INSERT INTO cf1

VALUES

(2, 6, 5000);

INSERT INTO cf1

VALUES

(3, 1, -25000);

INSERT INTO cf1

VALUES

(3, 2, 5000);

INSERT INTO cf1

VALUES

(3, 3, 25000);

INSERT INTO cf1

VALUES

(3, 4, 10000);

INSERT INTO cf1

VALUES

(3, 5, 20000);

INSERT INTO cf1

VALUES

(3, 6, 15000);

Enter a SELECT statement to calculate the IRR for the 3 projects so as to compare the results:

select d.proj_no,

       wct.IRR_q('SELECT c.cf_amt

                  from cf1 c

                  where c.proj_no = ' + convert(char, d.proj_no), null) as IRR

from cf1 d

group by d.proj_no;

Here is the result set.

{"columns":[{"field":"proj_no","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"proj_no":"1","IRR":"0.381617314697722"},{"proj_no":"2","IRR":"0.60736715747069"},{"proj_no":"3","IRR":"0.46064727712306"}]}