Sorry for the delay... Bonus IRR function

Sorry for the delay in posts, I have been working on another aspect of this project that does not involve RETS. I will be returning to the RETS code later this week. The project I am working on requires me to produce an IRR (Internal Rate of Return) for an investment. An IRR is, in simple terms a way to judge the return the you are getting on your money when you put up an initial investment, then recieve cash flow out on a regular basis. I was suprised that I could not find a Coldfusion function out there floating around, but I did find a SQL function, simply create a CFC that calls it and you are in business. You pass in your list of cashflows, the first of which will always be a negative because it is your initial investment. You also pass in a "guess" at the return, which eases the load on the fuction as it must loop from the guess until it resolves the equation. So, if you buy a rental property and finance $10k in the process, and you have $400 per month in cash flow after expenses, you would pump in something like; -'10000,400,400,400,400,400,400,400,400,400,400,400,.10', with 10% being your guess.

drop FUNCTION dbo.ufn_IRR
go

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 > 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

go