collate cash flow series by dates

**team_qq** · 05-20-2010, 02:31 AM

I have 3 large series of cash flows, each with its own unique set of correlating dates. I am trying to figure out a way to collate or merge the cash flow series' in order to calculate the return using the XIRR formula. If the cash flow series were small enough, I could stack them, since the XIRR formula doesn’t require cash flows be in order, or cut/paste and data sort. However I am looking for an elegant solution that ideally can collate the three cash flow series with dates into one cash flow series with dates (without requiring manual cutting/pasting/sorting), or some way to apply the XIRR function simultaneously to all three cash flows.

I have attached sample cash flow series in problem form and how I'd like the solution to look.

**pb71** · 05-21-2010, 02:55 PM

Would the attached work for you? I've written a UDF MRANGE that combines 3 ranges that you input. You can then use it as:

=XIRR(MRANGE(range1,range2,range3),MRANGE(range1,range2,range3))

It gives the same result as you have in your example for the combined data.

**pb71** · 05-21-2010, 04:46 PM

I've updated the function to allow for optional ranges (Range2 and Range3) and reattached the workbook in the previous post.

**team_qq** · 05-21-2010, 06:00 PM

Brilliant! Great solution pb71! Much appreciated.

**pb71** · 05-21-2010, 07:16 PM

You're welcome.

Just noticed that you use Excel 2003. Guess you had the compatibilty pack.

**team_qq** · 05-26-2010, 06:42 PM

pb71 -

I may have spoken too soon. Since I was able to use the formula in the worksheet you sent, I didn't realize that the MRANGE function was user defined (I must have glazed over UDF- lol). I am now trying to recreate that function in a different workbook. I don't seem to be able to see how you coded this UDF. Any way you can send the VBA code?

Thanks again.

**shg** · 05-26-2010, 06:50 PM

It's in the posted workbook in Module1.

**team_qq** · 05-26-2010, 07:17 PM

pb71 -

Cancel that - I figured out how to extract the VBA code myself.

One follow up question that may be more interesting to answer - is it possible to make the MRANGE formula agnositic to the data being arrayed vertically or horizontally? Currently, the UDF appears to work for vertically arrayed data, but the results are off if the data is arrayed horizontally. I have data sets arrayed both ways that I'd love use simultaneously in one XIRR function. Any ideas?

I've uploaded a worksheet to demonstrate.

**team_qq** · 05-26-2010, 07:44 PM

There also appear to be limitations to the MRANGE formula on the size of data ranges it can handle. It will work fine for me on relatively small ranges used for demonstration purposes, but when I apply it to large data ranges with 1500+ cells, the XIRR function using nested MRANGE functions appears to not function well, returning #VALUE!.

ideas?

**shg** · 05-26-2010, 08:40 PM

Look at the values for 1/1/2011 and 1/1/2012. They're not the same in all series.

**pb71** · 05-29-2010, 03:08 PM

team_qq,

As shg pointed out, your XIRR figures would be different in the MRANGE Explain workbook you posted because the values in your horizontal and vertical ranges are different. Your example workbook did not have the MRANGE UDF in it, so I have copied the UDF to Module1 in the attached workbook, and using the example you posted, I have corrected the values in the horizontal and vertical ranges so that they are consistent, and the function works for vertical, horizontal and combined vertical and horizontal ranges.

When you say you get a #VALUE! error using the XIRR function with nested MRANGE functions for large ranges of data (1500+ rows), have you tried using just the XIRR function on the same data (having rearranged the separate ranges of values and dates into single ranges of values and dates first, like you did originally)? If you have tried this already, could you post an example workbook?

**pb71** · 05-29-2010, 05:54 PM

I have tested XIRR with nested MRANGE functions on 3000+ rows of data (see attached workbook) and I do not get an error. MRANGE will error if you only use a single range of data and if this is the case you would not need to use MRANGE.

collate cash flow series by dates

LinkBack

Thread Tools

Rate This Thread

Display

collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Re: collate cash flow series by dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions