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
Solved

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

Posted on 2004-09-23
9
6,683 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
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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql need help on t-sql 10 26
SQL create line numbers for data sampling 11 27
TSQL XML Namespaces 7 23
SQLCMD Syntax 2 14
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 …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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