Results 1 to 8 of 8

Thread: Excel XIRR Function in T-SQL

  1. #1
    Join Date
    Jun 2007
    Posts
    3

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Don't think you can call excel function directly in t-sql sp.

  3. #3
    Join Date
    Jun 2007
    Posts
    3

    Xirr in SQL

    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Not familiar with excel functions.

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create your own user defined function or procedure that could give internal rate of return with irregular payments.

  6. #6
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Possible Approaches to using the Internal Rate of Return Formula You Specify ...

    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

  7. #7
    Join Date
    Mar 2009
    Posts
    1

    Excel Functions for SQL Server (T-SQL)

    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?

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •