?
Solved

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

Posted on 2004-09-23
9
Medium Priority
?
7,094 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 26

Expert Comment

by:Hilaire
ID: 12131772
0
 

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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 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