Excel XIRR Function in T-SQL

Hi There,
I want to use Excel XIRR function in one of my Stored Procedure.
What might be most efficient way to accomplish this task.
Do we have any mechanism to use Excel function straight away in T-SQL.

Any feed back will be appreciated.

Thanks and regards,
M2K

Don't think you can call excel function directly in t-sql sp.

Sure,
Then what should be the most efficient way to get it working.
One of the option which I am planning is to
Create a small utility in .Net with Excel object and using its XIRR formula.

What do you think about that.

Thanks,
M2K

Not familiar with excel functions.

create your own user defined function or procedure that could give internal rate of return with irregular payments.

First of all, my sources tell me that the XIRR function is supported within Excel 97 and above (in most cases, as part of the Analysis ToolPak add-in) . Specifically for Excel 2003, (and likely to be applicable for later versions, at least in concept), here is a resource for replicating formulas to SQL Server:

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

(Read this closely, because it suggests more than one approach, and a cursory read may not make this apparent - and you may have to detail the function out into its constituent parts ... ).

Another option you have, of course, is to create a function within MSSQL Server itself, based upon the logic for the XIRR function - this should not be difficult to do, as the XIRR function is closely related to IRR, which is commonly used as a means of calculating the internal rate of return for a series of values - XIRR simply supports the specific determination of the internal rate of return when there are a number of irregular payments associated with an investment.

So, worst case, you'll need to get the actual formula and put it into the definition of a function within MSSQL Server, which you can then use against fields, etc. There are, doubtless, other approaches as well - I have frequently applied calculations within views and so forth to bring about similar intended ends. Particularly with financial formulae...

Let us know how it goes, or if you need specific help with the steps. The first approach I suggest may be even more appealing if you expect to do this sort of thing with other Excel formulas / functions - posssibly tricky, but useful in a recurring way ...

Good luck!

Bill

Hi, M2K,

I know this is an old thread, but I have an update to your question. There is a company that has created many Excel functions like IRR, XIRR, NPV, CUMPRINC, etc. for SQL Server. The product suite is called XLeratorDB by WestClinTech (www.westclintech.com). I have used their products with great success for bank reporting projects.

They're not free, but the time saved and code simplification made buying XLeratorDB Excel functions an easy decision.

Here's the syntax for running the XIRR Excel function in T-SQL (source)

Code:

---

SELECT [westclintech].[wct].[XIRR] (
  <@CashFlows_TableName, nvarchar(4000),>
 ,<@CashFlows_ColumnName, nvarchar(4000),>
 ,<@CashFlows_GroupedColumnName, nvarchar(4000),>
 ,<@CashFlows_GroupedColumnValue, sql_variant,>
 ,<@CashFlowDates_ColumnName, nvarchar(4000),>
 ,<@Guess, float,>)

---

As you can see, it works as an aggregate function. So, here's how you would call it on a table of irregular cash flows called cf2 containing a project number ([proj_no]), cash flow dates ([cf_date]), and cash flow amounts ([cf_amt]):

Code:

---

SELECT cf2.proj_no
  ,wct.XIRR('cf2','cf_amt','proj_no',proj_no,'cf_date', NULL) as XIRR
FROM cf2
GROUP BY cf2.proj_no

---

Lastly, here is the result:

Code:

---

proj_no                XIRR
---------------------- ----------------------
1                      1.47538054052834
2                      5.78085888700177
3                      2.11913826387648

---

How cool is that? :cool:

Awesome. We are glad.