XIRR pe SQL Server

Recent am avut de facut un raport nu neaparat foarte complex (ce-i dept fac niste calcule in vreo 3 pasi…) dar bomboana pe coliva era de simulat functia XIRR (internal rate of return) care merge bine-mersi in Excel.

Definitie IRR (in engleza ca nu am gasit in limba RO) :

XIRR calculates and returns the internal rate of return of an investment that has not necessarily periodic payments. This function is closely related to the net present value function (NPV and XNPV). The XIRR is the interest rate for a serie of cash flow where the XNPV is zero.

@values contains the serie of cash flow generated by the investment. @dates contains the dates of the payments. The first date describes the payment day of the initial payment and thus all the other dates should be after this date. The optional @guess is the initial value used in calculating the XIRR. You do not have to use that, it is only provided for the Excel compatibility.

Ati inteles ceva? Eu nu. Dar sa continuam.

Circa trei zile mi-a luat sa:

1. Gasesc ceva acceptabil pe internet

2. Sa potrivesc functia gasita cu ceea ce am eu

3. Sa verific datele

Astfel, avind o investitie de X la data D1 si o incasare Y la data D2, pun in tabela tmp_IRR(dayz int, cashflow money) valorile:

0, X

D2-D1, Y

Challenge-ul a fost sa potrivesc calculele din functie ca sa imi iasa fix ca in Excel. Ideea e ca unii calculeaza o medie a zilelor din an ca 360, altii 365 altii 365.25 (ca sa compenseze ziua din anii bisecti). Aparent in Excel se foloseste 365.25 .

alter function ufn_irr (@rateguess real) returns real
begin
declare @delta real — rate delta in 2-point formula
declare @epsilon real — criteria for success, npv must be within +/- epsilon of zero
declare @maxtry smallint — number of iterations allowed
declare @irr real — return value

–set @rateguess=0.1
set @delta=.0001 /*– .0001 equals one hundreth of one percent */
set @epsilon=.005 — .005 equals one half cent
set @maxtry=1000
set @irr=null — assume failure

declare @rate1 real
declare @rate2 real
declare @npv1 real
declare @npv2 real
declare @done smallint
declare @try smallint

set @done=0
set @try=0
while @done=0 and @try<@maxtry
begin
set @rate1 = @rateguess
select @npv1 = SUM(cashflow*power(1+@rate1,-dayz/365.25)) FROM dbo.tmp_IRR

if abs(@npv1) < @epsilon
begin
— success
set @done=1
set @irr=@rate1
end
else
begin
— try again with new rateguess
set @rate2 = @rate1 + @delta
select @npv2 = SUM(cashflow*power(1+@rate2,-dayz/365.25)) FROM dbo.tmp_IRR

set @rateguess = @rate1 – @npv1*(@rate2-@rate1)/(@npv2-@npv1)
set @try = @try + 1
end
end

return @irr

end