I am seeking to create a number of variables (dynamically) and then input those variables into the Finance (XIRR) function. I am starting with a table with 200K rows full of static origination information for consumer loans with a number of early, irregular cash flows (i.e. loan amount, term, scheduled payment, etc.). Specifically, let's assume I have variables 1 through 5 and dates 1 - 5 (v1, v2,v3,v4,v5 and d1,d2,d3,d4,d5) already established for my output table. Each row in my output table will then need to dynamically add additional "v" and "d" variables based on the value of another existing variable (loan term). For example, if Loan Term is 36, then I need to dynamically create v6 - v41 and d6 - d41. My next row of data might have a Loan Term of 24, so I would then only need to create variables v6 - v30 and d6-d30. The final step in the process for each row would be to then evaluate the variables and dates in the finance function. Using the Loan Term of 36 as an example, I would need: finance('xirr',v1,v2,v3,v4,v5,v6,.....v41,d1,d2,d3,d4,d5,d6,....d41).
... View more