Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7509
  • Last Modified:

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? ;-)

Thanks in advance...
0
fluffnfur
Asked:
fluffnfur
  • 4
  • 4
1 Solution
 
mcmonapCommented:
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
 
fluffnfurAuthor 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
 
HilaireCommented:
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

SO instead of
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HilaireCommented:
0
 
fluffnfurAuthor 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
 
HilaireCommented:
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
 
fluffnfurAuthor 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
 
HilaireCommented:
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
 
fluffnfurAuthor Commented:
Perfect Hilaire, thankyou very, very much for your assistance!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now