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

## No comments:

Post a Comment