Thread: Running XIRR in Excel via a User Defined Function

I created a user defined function called FilterXIRRNew in Excel 2003 that takes in a bunch of arrays, does some processing and filtering and finally calls the built in XIRR function in excel to calculate the IRR for a series of cash flows. The function worked fine in Excel 2003

Code:

Public Function FilterXIRRNew(FilteredCF, FilteredDates, FilteredVal, valDate, InvestMultiple)
    
' Bunch of code here followed by calling the XIRR function

            FilterXIRRNew = Application.Run("xIRR", TrueCF, TrueDates, GuessIRR)


End Function

This function worked fine in Excel 2003. Now that I have opened the file in 2007, FilterXIRRNew is no longer working properly. The code works fine upto the line where it calls the in built XIRR function. I have enabled Macros in Excel 2007 and also installed the Add-In that contains the built-in XIRR function. The XIRR function works properly byitself. I am only having trouble with my user defined FilterXIRR function.

Any help would be very much appreciated.
Thanks.

Using XIRR for the a series of all stocks is pretty straightforward when you have recorded all transactions in terms of cashflows with their respective dates.

But if I want to calculate returns for exixting stocks within that portfolio, then I need to match and pass arguments to the xirr function only for those specific assets.

Something like SUMIF, but unfortunately there is no XIRR if.

How do I get this done. Its been driving me nuts. I guess there is a way to write a macro or VBA function to pass arguments of that specific stock (and sorted in the ascending order of dates as XIRR needs) to the XIRR function.

But I dont know much of macros or VBA to do this.!!!...pls help