title: TSQL Internal Rate of Return (IRR) from row instead of columns
body: <p>I have a data table like this:</p>
  
  <pre><code>Group   ID    M0    M1    ...    M20
  ------------------------------------
      1    1    10    -2    ...     54
      1    2    -8    10    ...    -20
      2    6   -10    12    ...      0
      2    4    45    52    ...      0
      2    5    12   102    ...      0
  </code></pre>
  
  <p>I'm grouping my data by Group like so:</p>
  
  <pre><code>SELECT Group
       , round(sum(M0+M1+M2+...+M20)/count(ID),2) as profit
       , round(sum(M0),2) as M0
       , round(sum(M1),2) as M1
       , ...
       , round(sum(M20),2) as M20
  FROM
      data_table
  GROUP BY
      Group
  </code></pre>
  
  <p>What I need to add is IRR based on M0 to M20 columns.</p>
  
  <p>I found an IRR function that looks like this:</p>
  
  <pre><code>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 &gt; 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) &lt;= 0 THEN 0.00001 ELSE @guess END
  
      SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
      WHILE @NPV &gt; 0 
        BEGIN
          SET @guess = @guess + 0.00001
          SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
        END
      RETURN @guess
    END
  </code></pre>
  
  <p>Sample usage looks like this:</p>
  
  <pre><code>SELECT  dbo.ufn_IRR('-4000,1200,1410,1875,1050',0.00001)
  </code></pre>
  
  <p>But in my case I would like to avoid creating string from my columns, as this:</p>
  
  <pre><code> SELECT  dbo.ufn_IRR(
      cast(round(sum([M0]), 2) AS NVARCHAR)+','
     +cast(round(sum([M1]), 2) AS NVARCHAR)+','
     +cast(round(sum([M2]), 2) AS NVARCHAR)+','
     +cast(round(sum([M3]), 2) AS NVARCHAR)+','
     +cast(round(sum([M4]), 2) AS NVARCHAR)+','
     +...+','
     +cast(round(sum([M20]), 2) AS NVARCHAR)
   ,0.00001)
  </code></pre>
  
  <p>I think it is pointless to first create a string and then inside function cut it into table.<br />
  I would like to modify IRR function so than I can pass my columns, but I don't have Idea how I should do that :/</p>