IRR calculation in SSRS

# IRR calculation in SSRS

• Monday, August 06, 2012 2:27 PM

I'm attempting to develop a report in SSRS with a computed IRR.  I have converted my data stream to Double and have included an IRR function in the report.  I am getting the following warning.

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘IRR.Paragraphs[0].TextRuns[0]’ contains an error: Unable to cast object of type 'System.Double' to type 'System.Double[]'. C:\Users\####\Documents\Visual Studio 2008\Projects\#####\SSRS1\SSRS1\Report1.rdl 0 0

Does anyone know how to resolve this?

### All Replies

• Monday, August 06, 2012 9:39 PM
Moderator

Hi There

Thanks for your posting. Can you please put your code here for calculating IRR. It seems like you are trying to cast double with the array of double.

Please pot your code so that someone might be able to help up out

Many thanks

Syed Qazafi Anjum

You can also try to use this code on SQL Server side

CREATE FUNCTION dbo.Calculate_IRR(@commaseparatedStringIDs varchar(8000), @guessAmount decimal(30,10))
RETURNS decimal(30,10)
AS
BEGIN
DECLARE @t_IDs table(id int identity(0,1), value decimal(30,10))
DECLARE @strID varchar(12), @sepPos int, @NPV decimal(30,10)
set @commaseparatedStringIDs = coalesce(@commaseparatedStringIDs + ',', '')
set @sepPos = charindex(',', @commaseparatedStringIDs)
WHILE @sepPos > 0  BEGIN
SET @strID = LEFT(@commaseparatedStringIDs, @sepPos - 1)
INSERT INTO @t_IDs(value) SELECT (CAST(@strID AS decimal(20,10))) where isnumeric(@strID)=1
SET @commaseparatedStringIDs = RIGHT(@commaseparatedStringIDs, DATALENGTH(@commaseparatedStringIDs) - @sepPos)
set @sepPos = charindex(',', @commaseparatedStringIDs)
END
set @guessAmount = case when isnull(@guessAmount, 0) <= 0 then 0.00001 else @guessAmount end
select @NPV = SUM(value/power(1+@guessAmount, id)) from @t_IDs
WHILE @NPV > 0
BEGIN
set @guessAmount = @guessAmount + 0.00001
select @NPV = SUM(value/power(1+@guessAmount, id)) from @t_IDs
END
RETURN @guessAmount
END
go
-- values to compare with excel (note that for excel 0.00001 is 0.1%)
select dbo.Calculate_IRR('-90,13,14,15,16,17,18', 0.00001) as irr