Solved

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

Posted on 2004-09-23
9
6,440 Views
Last Modified: 2008-07-18
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
Comment
Question by:fluffnfur
  • 4
  • 4
9 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 12131617
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 Comment

by:fluffnfur
ID: 12131719

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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12131754
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12131772
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:fluffnfur
ID: 12131970

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
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12132654
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
 

Author Comment

by:fluffnfur
ID: 12132745

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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12132904
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 Comment

by:fluffnfur
ID: 12140798
Perfect Hilaire, thankyou very, very much for your assistance!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now