SQL Server Performance

XIRR Function

Discussion in 'General Developer Questions' started by patel_mayur, Nov 21, 2006.

  1. patel_mayur New Member

    Hi All,

    I want to use XIRR function of Excel using SQL Server.

    For example, i have following data in my table.
    Col1 Col2
    ---- ---------------
    -10000 January 1, 2008
    2750 March 1, 2008
    4250 October 30, 2008
    3250 February 15, 2009
    2750 April 1, 2009


    Now, i have transfered this data to Excel Sheet using DTS. and its looks like this in C:Test.xls

    A B
    1. Values Dates
    2. -10,000 January 1, 2008
    3. 2,750 March 1, 2008
    4. 4,250 October 30, 2008
    5. 3,250 February 15, 2009
    6. 2,750 April 1, 2009

    If somehow i can put "=XIRR(A2:A6,B2:B6,0.1)" in C:Test.xls in any cell then i can get the value of this mathematical function. And i will again transfer this data from Excel file to SQL Server with XIRR result.

    How can i do this in better way ?
    I dont have access of Operrowset functions.

    Thx.



  2. Adriaan New Member

    What does the XIRR function do?
  3. FrankKalis Moderator

    That's the Excel function for calculating the internal rate of return for non-regular cash flows. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />But to me it is not clear what you are trying to achieve?<br />You have your data transferred from Excel to SQL Server and know want to push it back to get the result of XIRR(), which in turn you will pull back into SQL Server?<br /><br />Some time ago I wrote a function to calculate the yield-to-maturity, which is nothing but the internal rate of return. You might want to check it out, to see if the result are "good enough" for your problem:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DROP TABLE Cash_Flows<br />CREATE TABLE Cash_Flows ( cashflow DECIMAL(8,2), valuta DATETIME )<br /> INSERT INTO Cash_Flows VALUES (-10000,'20080101')<br /> INSERT INTO Cash_Flows VALUES (2750,'20080301')<br /> INSERT INTO Cash_Flows VALUES (4250, '20081030')<br /> INSERT INTO Cash_Flows VALUES (3250,'20090215')<br /> INSERT INTO Cash_Flows VALUES (2750,'20090401')<br />GO<br />CREATE FUNCTION yield_to_maturity(@issue_date SMALLDATETIME)<br />RETURNS DECIMAL(15,14) <br />AS <br />BEGIN <br /> DECLARE @ytm_tmp FLOAT<br /> DECLARE @ytm FLOAT<br /> DECLARE @pv_tmp FLOAT<br /> DECLARE @pv FLOAT<br /><br /> SET @ytm_tmp = 0<br /> SET @ytm = 0.1<br /> SELECT @pv_tmp = SUM(cashflow) FROM Cash_Flows<br /> SET @pv = <br /> (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta)* 30 + DAY(valuta)-DAY(@issue_date) <br /> - <br /> CASE <br /> WHEN(@issue_date)&gt;=30 AND DAY(valuta) = 31 <br /> THEN 1 <br /> ELSE 0 <br /> END)/ 360.0 ))<br /> FROM Cash_Flows)<br /><br /> WHILE ABS(@pv) &gt;= 0.000001<br /> BEGIN<br /> DECLARE @t FLOAT<br /> SET @t = @ytm_tmp<br /> SET @ytm_tmp = @ytm<br /> SET @ytm = @ytm + (@t-@ytm)*@pv/(@pv-@pv_tmp)<br /> SET @pv_tmp = @pv<br /> SET @pv =<br /> (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta) * 30 + DAY(valuta)-DAY(@issue_date) <br /> -<br /> CASE<br /> WHEN(@issue_date)&gt;=30 AND DAY(valuta) = 31 <br /> THEN 1 <br /> ELSE 0 <br /> END) /360.0))<br /> FROM Cash_Flows)<br /> END<br /> RETURN @ytm <br />END<br />GO<br />SELECT dbo.yield_to_maturity('20080101')<br />DROP FUNCTION dbo.yield_to_maturity<br /> <br />----------------- <br />.37336632856546<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />When comparing to Excel, you'll note that Excel reports: 0.373362535 while my function returns: 0.37336632856546. I also use the day convention 30/360, which might be not appropriate. Anyway, that's why I've mentioned to see if results are "good enough".<br /><br />And to be honest, this is just for fun, If you need to call this UDF frequently, it might slow down performance, due to the approximation looping.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  4. patel_mayur New Member

    Thanks a lot Frank.

    Let me check your function with more data. I am sure it will work out for me.


    quote:
    But to me it is not clear what you are trying to achieve?
    You have your data transferred from Excel to SQL Server and know want to push it back to get the result of XIRR(), which in turn you will pull back into SQL Server?

    I have my data with SQL Server only. I was planning to transfer it to EXCEL to calculate XIRR and again import it to SQL. But now i am not required to do that. [8D]

Share This Page