TSQL 实现IRR功能

字符串形式

CREATE proc [dbo].[ufn_IRR]
  (
   @strIDs table(a int),
   @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



--SELECT  dbo.ufn_IRR('-4000,1200,1410,1875,1050',0.00001)

表形式

go
DECLARE @t_IDs as IRRTable
insert @t_IDs values(0,-69773.84)
insert @t_IDs values(1,5172.49049333333)
insert @t_IDs values(2,5156.81061666667)
insert @t_IDs values(3,5141.13074)
insert @t_IDs values(4,5125.45086333333)
insert @t_IDs values(5,5109.77098666667)
insert @t_IDs values(6,5094.09111)
insert @t_IDs values(7,5078.41123333333)
insert @t_IDs values(8,5062.73135666667)
insert @t_IDs values(9,2608.98148)
insert @t_IDs values(10,2601.02215833333)
insert @t_IDs values(11,2593.06283666667)
insert @t_IDs values(12,2585.103515)
insert @t_IDs values(13,2577.14419333333)
insert @t_IDs values(14,2569.18487166667)
insert @t_IDs values(15,2561.22555)
insert @t_IDs values(16,2553.26622833333)
insert @t_IDs values(17,2545.30690666667)
insert @t_IDs values(18,2537.347585)
insert @t_IDs values(19,2529.38826333333)
insert @t_IDs values(20,2521.30894166667)

SELECT dbo.[ufn_IRR] (@t_IDs,0.000001)

go 
create type IRRTable as Table
(id int,value DECIMAL(30, 10))

go 

CREATE FUNCTION [dbo].[ufn_IRR]
  (
   @t_IDs IRRTable READONLY,
   @guess DECIMAL(30,10)
  )
RETURNS DECIMAL(30, 10)
as
begin
  
    DECLARE @NPV DECIMAL(30, 10)
    
	
    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*12
  
  end

  go

字符串SQL版本

CREATE Proc [dbo].[DSF_IRR]
  (
   @sql Nvarchar(max),
   @guess DECIMAL(30,10)
  )

as
begin
  
    DECLARE @NPV DECIMAL(30, 10)
    create table #tmp(id INT IDENTITY(0, 1),value decimal(38,10))
	DECLARE @t_IDs table(id int,value decimal(38,10))

	set @sql='insert into #tmp (value)'+ @sql
	
	

	exec(@sql)

	insert into @t_IDs
	select * from #tmp 


    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
    select @guess*12 as IRR
  
  end


go
exec [dbo].[DSF_IRR] 
'select INSTM as value from [DFSA].[dbo].[PaymentSchedule] WHERE [Category]=''Posting7'' AND [Restructure]=''R'' order by TNR'
,0.0001