USE [DWH2]
GO
/****** Object: UserDefinedFunction [IRR].[irr] Script Date: 04/28/2010 13:39:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [IRR].[irr] ( @ACCOUNT_RK varchar(30), @d datetime )
RETURNS float
as
BEGIN
DECLARE @irrPrev float set @irrPrev = 0
DECLARE @irr float set @irr = 0.1
DECLARE @pvPrev float
DECLARE @pv float
DECLARE @i int set @i = 0
DECLARE @irrEND float set @irrEND = 0

SET @pvPrev = ( SELECT sum(CF)
FROM STG2.IRR.Stream_of_Payment_IRR
WHERE ACCOUNT_RK = @ACCOUNT_RK)
if @pvPrev<0 return NULL

SET @pv = ( SELECT SUM(CF/power(1e0+@irr,cast(DATE-@d as float)/360e0))
FROM STG2.IRR.Stream_of_Payment_IRR
WHERE ACCOUNT_RK = @ACCOUNT_RK )

if @pv = @pvPrev return NULL

WHILE abs(@pv) >= 0.00000000001 OR @i<=200000
begin
declare @t float
set @t = @irrPrev
set @irrPrev = @irr
set @irr = @irr + (@t-@irr)*@pv/(@pv-@pvPrev)

if ABS(@irr - @irrEND)<= 0.00000000001 return @irr

set @irrEND = @irr
set @pvPrev = @pv
set @i = @i+1
set @pv = ( select sum(CF/power(1e0+@irr,cast(DATE-@d as float)/365e0))
from STG2.IRR.Stream_of_Payment_IRR
where ACCOUNT_RK = @ACCOUNT_RK)
if @pv=@pvPrev return @irr
if @irr>10 return NULL
end
return @irr

END