Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server 2005 internal rate of return function


IRR_q
Updated: 5 August 2010
 
Use IRR to calculate the internal rate of return for a series of cash flows.
Syntax
SELECT [westclintech].[wct].[IRR_q] (
   <@Cashflows_RangeQuery, nvarchar(4000),>
 ,<@Guess, float,>)
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 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.
Example
 
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


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service