Updated: 5 August 2010

Use IRR to calculate the internal rate of return for a series of cash flows.

SELECT [westclintech].[wct].[IRR_q] (

<@Cashflows_RangeQuery, nvarchar(4000),>

,<@Guess, float,>)

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

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

float

Â· For IRR 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 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 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.

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

proj_no IRR

---------------------- ----------------------

1 0.381617314697722

2 0.60736715747069

3 0.46064727712306