XIRR using Dynamic Data Range

[MichaelBur]

To start, I'm using Windows 7/ Excel 2007

I need to calculate IRRs for many "investments" that have varying numbers of transactions. The data is listed in four columns A-D (Name, Transaction Type, Date, Amount) and is sorted by Name first and Date second. There are well over 5,000 records for nearly 200 investments (if this makes a difference). There are three transaction types (IN, OUT and MV). Each investment has only one Market Value which is always the last record for that investment, and the sign for the MV is always the same as the sign of the Inflow transaction type.

I'd like to have a fifth E column "XIRR" where I could type the formula into the first record's row and copy it down. I want the formula to find rows that have the transaction type "MV" and perform the function there (or perform the function everywhere and only be visible there).

I've been using the IF function to first only show the formula on the rows with MV records, then performing the XIRR function and last I come in and adjust the data range from there. It gets a little cumbersome to do that with 5,000 records.

The formula I use is =IF(B2="MV",XIRR(D1:D2,C1:C2),"")

Can I do this without code? If not, can you help me with the code?

I'm sorry I can't post an example. I tried to figure out how to in the FAQ section but wasn't successful.

Thanks!

 

[anandvarma]

sample data please

 

[loweyecue]

To start, I'm using Windows 7/ Excel 2007

I need to calculate IRRs for many "investments" that have varying numbers of transactions. The data is listed in four columns A-D (Name, Transaction Type, Date, Amount) and is sorted by Name first and Date second. There are well over 5,000 records for nearly 200 investments (if this makes a difference). There are three transaction types (IN, OUT and MV). Each investment has only one Market Value which is always the last record for that investment, and the sign for the MV is always the same as the sign of the Inflow transaction type.

I'd like to have a fifth E column "XIRR" where I could type the formula into the first record's row and copy it down. I want the formula to find rows that have the transaction type "MV" and perform the function there (or perform the function everywhere and only be visible there).

I've been using the IF function to first only show the formula on the rows with MV records, then performing the XIRR function and last I come in and adjust the data range from there. It gets a little cumbersome to do that with 5,000 records.

The formula I use is =IF(B2="MV",XIRR(D1:D2,C1:C2),"")

Can I do this without code? If not, can you help me with the code?

I'm sorry I can't post an example. I tried to figure out how to in the FAQ section but wasn't successful.

Thanks!

Yes it can be done, I actually do something similar. I compute the XIRR results on a different page. I use combination of MATCH and OFFSET functions to tell it which row to start and stop counting the range for the XIRR function based on the stock symbol. No need to do XIRR on each row, that would slow it down a lot.

Csn you paste some sample data here?

 

[Domaldhino]

Understand this is an old thread but its exactly what I am trying to do, but struggling to finalise the last step.

I am using the following formula to get the XIRR on individual stocks:

=XIRR(OFFSET(AC$9,MATCH(AA9,AA$9:AA$17,0)-1,0,COUNTIF(AA$9:AA$17,AA9)),OFFSET(AB$9,MATCH(AA9,AA$9:AA$17,0)-1,0,COUNTIF(AA$9:AA$17,AA9)))

The Formula works fine if the current value of the stock, (the negative Numbers), are in the same Column as the purchases, but I have my current values in an alternate column (As shown in snap) I am sure there is a simple solution, can anyone offer some suggestions? Appreciate your time.

Sample Data

[TABLE="width: 334"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Stock[/TD]
[TD]Date[/TD]
[TD]Purchased[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/10/2012[/TD]
[TD="align: right"]$2,520[/TD]
[TD="align: right"]$3,000[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]6/10/2014[/TD]
[TD="align: right"]$5,004[/TD]
[TD="align: right"]$5,789[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]14/04/2015[/TD]
[TD="align: right"]-$8,789[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]13/01/2015[/TD]
[TD="align: right"]$5,055[/TD]
[TD="align: right"]$4,000[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5/11/2015[/TD]
[TD="align: right"]$2,651[/TD]
[TD="align: right"]$2,500[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]16/09/2015[/TD]
[TD="align: right"]$2,580[/TD]
[TD="align: right"]$2,400[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]22/10/2007[/TD]
[TD="align: right"]$835[/TD]
[TD="align: right"]$818[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]23/01/2008[/TD]
[TD="align: right"]$647[/TD]
[TD="align: right"]$600[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]20/12/2015[/TD]
[TD="align: right"]-$10,318[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Domaldhino

 

[BiocideJ]

XIRR requires the values to be all in a single range. You will need to either create a new column that has a row added that does something like a -SUMIF([Values_for_that_stock) to add in the sum of the values as a negative value (as the current value) or revise your formula and change your data layout slightly. I am going to show you a way to do the latter.

The simplest way I could devise to accomplish this was to create a formula that didn't care if the stocks were grouped together (as in your example) by the following adaptation of your formula:

=SUMPRODUCT(XIRR(INDEX(AC$9:AC$17,MATCH(AA9,AA$9:AA$17,0),0):AC$17*(INDEX(AA$9:AA$17,MATCH(AA9,AA$9:AA$17,0),0):AA$17=AA9),INDEX(AB$9:AB$17,MATCH(AA9,AA$9:AA$17,0),0):AB$17))

This formula will work identically as your formula for your set of data posted but will also calculate identically if the two negative sum values at the bottom of the set (e.g. rows 16 and 17)

You can then put the SUMIF formula for each stock into its own row at the bottom and it will calculate as needed. Play around with it and let me know if you have any problems.

NOTE: Also, in the future, please create a new thread with your question. The reason posting on old threads like this is discouraged is because no one will generally ever see them. If you want to, paste a link-back to the thread you are referencing in your new thread for reference.

An example of my data set (sorted by date [US formatted])

[TABLE="width: 267"]
<tbody>[TR]
[TD]Stock
[/TD]
[TD]Date
[/TD]
[TD]Purchased
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]10/22/2007
[/TD]
[TD="align: right"]835
[/TD]
[TD="align: right"]818
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]1/23/2008
[/TD]
[TD="align: right"]647
[/TD]
[TD="align: right"]600
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]10/1/2012
[/TD]
[TD="align: right"]2520
[/TD]
[TD="align: right"]3000
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]10/6/2014
[/TD]
[TD="align: right"]5004
[/TD]
[TD="align: right"]5789
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]1/13/2015
[/TD]
[TD="align: right"]5055
[/TD]
[TD="align: right"]4000
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]9/16/2015
[/TD]
[TD="align: right"]2580
[/TD]
[TD="align: right"]2400
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]11/5/2015
[/TD]
[TD="align: right"]2651
[/TD]
[TD="align: right"]2500
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]4/14/2015
[/TD]
[TD="align: right"]-8789
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]12/20/2015
[/TD]
[TD="align: right"]-10318
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Also, the formula in AB16 would be
=-SUMIF(AA$9:AA$17,AA16,AD$9:AD$17)

 

[Domaldhino]

Thanks for taking the time to get back to me, great solution.

Do the SUMIF formula need to be at the bottom of the array? I have summary data above my detailed data that could be used but I get the #NUM! error when I try and reorganise the data with the SUMIF above the detail data.

 

[BiocideJ]

Yes. The SUMIF summary values will need to be at the bottom.