Hi there. I've got a project where i need to calculate the Internal Rate of Return for non periodic payments. I'm using the XIRR Finance function to do it but i can't seem to find how to use it with variable input, or even if it's possible. I've got a dataset with lots of rows and i need to calculate the XIRR for each row. The problem is that every row as a different number of periods and the function doesn't seem to accept arguments like amt(1)-amt(n), neither i can use syntax like amt1-amtn as "n" is different in each row. Consider the following example: data a; input amt1-amt5 per1-per5; cards; -100 101 102 103 . 1 2 3 4 . -100 101 102 103 105 1 2 3 4 5 run; For the first row i need to call the function for the first 4 amounts and periods: IRR=FINANCE('XIRR',amt1,amt2,amt3,amt4,per1,per2,per3,per4); For the second row i need to call the function for the first 5 amounts and periods: IRR=FINANCE('XIRR',amt1,amt2,amt3,amt4,amt5,per1,per2,per3,per4,per5); Now imagine this with a lot of input rows, each one with different amounts and periods. At the moment i'm using a giant if trying the value for "n", generated by macro code based on the max periods i have in the input data which, as you can imagine, is heavy dutty! Any ideas? Regards, PA.
... View more