Running XIRR in Excel via a User Defined Function

pranavsawjiany · #1 07-03-2009, 02:40 AM

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

shg · #2 07-03-2009, 03:00 AM

Can you use the XIRR function successfully as a UDF in the 2007 add-in?

Try

Code:

FilterXIRRNew = myAddIn!xIRR(TrueCF, TrueDates, GuessIRR)

pinnacle · #3 01-21-2010, 02:43 AM

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

rwgrietveld · #4 01-21-2010, 03:48 AM

pinnacle,

Please start your own new thread! and refer to this one if necessary.

**teylyn** · #5 01-21-2010, 03:58 AM

Hi pinnacle,

welcome to the forum. As rwgrietveld points out ...

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Welcome to the Excel Forum

Forum Statistics