# IRR / NPV Calculations - T-SQL functionality for equivalent VBA functions?

Hi All - My first ever post!

I have a client who wants a stored procedure to return a dataset that includes calculated NPV and IRR values.

Office components *cannot* be installed on the server, so I can't do the obvious and pull the VBA functions in.

Has anybody seen sprocs or user-defined functions that correlate to the equivalent IRR and NPV functions (and return the same results, of course? ;-)

###### Who is Participating?

x

Commented:
I get roughly the same results than in Excel using the user-defined functions above

drop FUNCTION dbo.ufn_NPV
go

CREATE FUNCTION dbo.ufn_NPV(@rate decimal(30,10), @strIDs varchar(8000))
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
set @strIDs = coalesce(@strIDs + ',', '')
set @sepPos = charindex(',', @strIDs)
WHILE @sepPos > 0  BEGIN
SET @strID = LEFT(@strIDs, @sepPos - 1)
INSERT INTO @t_IDs(value) SELECT (CAST(@strID AS decimal(20,10))) where isnumeric(@strID)=1
SET @strIDs = RIGHT(@strIDs, DATALENGTH(@strIDs) - @sepPos)
set @sepPos = charindex(',', @strIDs)
END
RETURN (select SUM(value/power(1+@rate, id)) from @t_IDs)
END
go

drop FUNCTION dbo.ufn_IRR
go

CREATE FUNCTION dbo.ufn_IRR(@strIDs varchar(8000), @guess 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 @strIDs = coalesce(@strIDs + ',', '')
set @sepPos = charindex(',', @strIDs)
WHILE @sepPos > 0  BEGIN
SET @strID = LEFT(@strIDs, @sepPos - 1)
INSERT INTO @t_IDs(value) SELECT (CAST(@strID AS decimal(20,10))) where isnumeric(@strID)=1
SET @strIDs = RIGHT(@strIDs, DATALENGTH(@strIDs) - @sepPos)
set @sepPos = charindex(',', @strIDs)
END
set @guess = case when isnull(@guess, 0) <= 0 then 0.00001 else @guess end
select @NPV = SUM(value/power(1+@guess, id)) from @t_IDs
WHILE @NPV > 0
BEGIN
set @guess = @guess + 0.00001
select @NPV = SUM(value/power(1+@guess, id)) from @t_IDs
END
RETURN @guess
END

go

-- values to compare with excel (note that for excel 0.00001 is 0.1%)
select dbo.ufn_irr('-90,13,14,15,16,17,18', 0.00001) as irr,
dbo.ufn_npv(0.001, '-90,13,14,15,16,17,18') as npv

HTH

Hilaire
0

Commented:
Hi fluffnfur,

If you have the formula for these calculations then you can easily create a user defined function to calculate, it may even be possible to add have the calculation inline.  Post the calculation that you want to use for these functions for a more detailed answer.
0

Author Commented:

I agree I could code the functions quite easily if I had the formulae - the formula for NPV appears in the Excel help topic for the NPV function (unfortunately as a graphic, so I can't easily cut & paste it :-(), the IRR one doesn't seem to be there.

I'm not sure that I'd trust my maths sufficiently to engineer the NPV one, and the result has to correlate with that returned by the equivalent VBA functions.

Does this help at all?
0

Commented:
You could post the URL to make the formula available for all

IMHO, the problem is that these function expect a variable number of parameters, or an arry of values.
This can't be achieved in SQL Server unless you pass the array

NPV(arrayofvalues amount, guess amount) returns percentage
in SQL Server you'd have
NPV(commaseparatedvalues string, guess amount) returns percentage

which means you'll have to build the string before calling the function ...

Hilaire
0

Commented:
0

Author Commented:

Thanks for posting the formula for NPV!   I can't locate the formula for IRR though...

I agree with you regarding the requirement for an input array, and the necessity to build that as an input string for a UDF - what I'm looking for is a tried & tested form of the tsql, to save me the build & test time...

I would've thought that this might have been addressed before somewhere...
0

Author Commented:

Thanks!

I have a "strategy" meeting from now til about six GMT - will have a play with these then.

You say "roughly" the same results - what sort of deviance did you experience?

Again, thankyou!
0

Commented:
dbo.ufn_irr('-90,13,14,15,16,17,18', 0.00001) as irr --> .0089700000 in sql, 0,8962% in Excel

dbo.ufn_npv(0.001, '-90,13,14,15,16,17,18') as npv -->2.6579358400 in sql, 2,655281 € in excel

The difference might be due to rounding errors, the float datatype is less precise than decimal (30,10),
and differences in the increment step, or max number of iterations, in the IRR calculation.

I can't uncompile Excel to get closer ;-)

Hilaire
0

Author Commented:
Perfect Hilaire, thankyou very, very much for your assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.