IRR fuction

[Jamal]

Hi Friends,

I need your help please, I struggle with the IRR function, here is my  sql server table:

I want to calculate the the internal rate  (IRR) for each Projet. I ve used =RangeIRR($(vinvet),$(repeat(vpayment &',',$(vNumber-of-payment)))) but is not working. I need to get those variable from SQLServer and calculate the IRR. thank you very much

projet	invest	payment	Number-of- payment
A	-5000	200	12
B	-3000	600	25
C	-7000	500	36

[tincholiver · Accepted solution]

Hi Jamal, try with this scripting:

Result is: 

Payments:
LOAD
'Pays' as Pays,
projet,
payment as CashFlow,
payment,
[Number-of- payment]
FROM [$(Data)Libro.xlsx](ooxml, embedded labels, table is Hoja4);

TempTable:
LOAD
Max([Number-of- payment]) as MaxPay,
Min([Number-of- payment]) as MinPay,
Pays
Resident Payments Group By Pays;

LET vMax= Peek('MaxPay',0,'TempTable');
LET vMinPay= Peek('MinPay',0,'TempTable');
SET vMin='1';

NoConcatenate
Final:
LOAD
'$(vMax)' as MaxPay,
'$(vMinPay)' as MinPay,
projet,
//invest,
CashFlow,
payment,
[Number-of- payment]
Resident Payments;

Left Join
LOAD
(IterNo()+$(vMin)-1) as Pagos
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

DROP Table TempTable;
DROP Table Payments;
STORE Final into $(Data)Payments.qvd(QVD);
DROP Table Final;

Payments:
LOAD
projet,
invest as CashFlow,
invest
FROM [$(Data)Libro.xlsx](ooxml, embedded labels, table is Hoja4);

Concatenate
LOAD
MaxPay,
MinPay,
projet,
CashFlow,
payment,
[Number-of- payment],
Pagos
FROM [$(Data)Payments.qvd] (qvd) Where Pagos<=[Number-of- payment] ;

let me know if the solution helped you

[Jamal]

Hi Tincholiver,
Yes the solution is working perfectly, Thank you very much for your help.
if I want to calculate this IRR for table with 126 000 client, I think it s gonna take a lot of memory and time.

[tincholiver]

Hi Jamal, it shoudn't because the scrip just load data, not make complex calculation.

Actually I try in my machine with 100.000 and 200.000 records and works good.

If it seemed a good solution, could you mark it as resolved so we can use it in furute cases?

[Jamal]

Hi Bro,

Ok, thank you very much. I appreciate your help.

[tincholiver]

your are welcome.
Regards.

Wicham..