Here we are:
1. We create a sample table
create table txns
insert into txns
union select 1000,'20080705'
union select 2000,'20090105'
2. Then we calculate your XIRR as per Excel (almost):
DECLARE @nGuessRate NUMERIC(18,10)
SET @nGuessRate = 0.1 -- 10% - as per Excel XIRR default
DECLARE @dtFirstPmtDate DATETIME
DECLARE @nRateChange NUMERIC(18,10)
DECLARE @nResidual NUMERIC(18,10)
DECLARE @nPrevResidual NUMERIC(18,10)
DECLARE @nXIRR NUMERIC(18,10)
SET @nRateChange = 0.1 -- starting point of the rate change
SELECT @dtFirstPmtDate=MIN(PmtDate) FROM txns
SELECT @nResidual = 10
,@nPrevResidual = 1
,@nXIRR = @nGuessRate
DECLARE @nIteration SMALLINT
SET @nIteration = 0 -- 100 is a maximum number of calc. iterations as per Excel XIRR
WHILE @nIteration < 100 AND ABS((@nPrevResidual - @nResidual) / @nPrevResidual) > 1.0/POWER(10,8)
SET @nPrevResidual = @nResidual
SET @nResidual = 0
SELECT @nResidual = @nResidual + PmtAmount/ POWER((1.0+@nGuessRate),(DATEDIFF(d,@dtFirstPmtDate,PmtDate)/365.0))
SET @nXIRR = @nGuessRate
IF @nResidual >= 0
SET @nGuessRate = @nGuessRate + @nRateChange
SET @nRateChange = @nRateChange / 2
SET @nGuessRate = @nGuessRate - @nRateChange
SET @nIteration = @nIteration + 1
-- Calculation result
SELECT @nXIRR AS XIRR
Now, you can wrap it into stored proc or sql function.
Remember to check that your payment range contains at least one negative and one positive payment, otherwise you should return some error, check Excel XIRR documentation for other error conditions if any.
BUT! It may not be what you want. You most likely want XIRR to be an aggregate function. You cannot implement it in T-SQL, but you can do it in .NET as CLR function. Or, buy the product I have told you about in the previous post.
I might write some article about it if I have spare time...
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help