• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XIRR for Non Contiguous Data [SOLVED]

Status
Not open for further replies.
J

juzzvinay

Member
Hi, I have been an avid follower of this site and the tips have helped to a great extent (short of the cape and spandex pants!)


I would appreciate if you someone could help me out with a couple of issues I am facing.


For a portfolio management sheet, the data available is

Col 1 - Outflow Date; Col 2 - Outflow Amount; Col 3 - Inflow Date; Col 4 - Inflow Amount


Now the problem is XIRR wants the data - both values and dates - to be in single column which will be extremely difficult and time consuming to change due to additional data in the columns to the right of column 4 which has to be arranged row wise.


Further rows are constantly added (daily about 20 rows) hence, it becomes an hassle to manually copy and paste the 4 required columns in an external sheet and compute portfolio XIRR.


Any suggestions on how to implement XIRR for non contiguous data using Index/ Offset/ Sumproduct/ Arrays/ Pivot Tables - I have tried my best but I think my level of understanding is not high enough for this.


Finally, I would also love to be able to compute XIRR for filtered data for ex, if I want XIRR from Outflow Date - 1-Jan-2012 till date.


If a sample workbook or further data is required, please tell me.
 
Hi Vinay ,


I have no idea about XIRR , but googling your problem brought up the following links ; in case you haven't already gone through them , please do so , and if you still don't have a solution by the end of today , please post your workbook , preferably to a site like SpeedyShare ( http://speedy.sh/ ).


1. http://westclintech.com/Blog/tabid/132/EntryId/35/More-problems-with-the-XIRR-function-in-EXCEL.aspx


2. http://www.pcreview.co.uk/forums/irr-data-different-ranges-t1779221.html


3. http://www.tushar-mehta.com/excel/tips/multi_area_argument.html


Narayan
 
Hi,


I have already gone through multiple websites including the ones sent by you and short of UDF using VBA (which I dont understand at all) there doesnt seem to be any solution.


IRR accepts non contiguous ranges, but not XIRR. I am not sure if Excel 2010 or 2013 supports this function and would appreciate if anyone in the know can clarify.


I have attached a sample file at http://speedy.sh/PJmgn/Portfolio-Sample-Workbook.xlsx


For the filtered data XIRR, I think have found an array function


{SUBTOTAL(3,INDEX(B:B,ROW(B6:B12),1))*INDEX(B:B,ROW(B6:B12),1)}


for B6:B12 in the arguments of the XIRR function, which needs to be confirmed with Ctrl-Shift-Enter.

(http://www.mrexcel.com/forum/excel-questions/378571-how-apply-formula-visible-cells-only-without-using-subtotal.html)


However, I have not yet found an answer to the first and more important question of computing XIRR for non contiguous ranges.


Trust the sample workbook will help someone figure out the solution.
 
Hi Vinay ,


Can you check out this file ?


http://speedy.sh/xMuGF/Portfolio-Sample-Workbook-Revised.xlsx


The technique used is the same one which is available in the second link I had posted initially.


Narayan
 
Narayan,

Thanks a ton! I tried the same approach by naming ranges, somehow it didnt work for me.


I will compare both files and see where I was going wrong. But this definitely seems like it works well - I only have to try and implement this over 3000 rows and see if that complicates matters.


Really appreciate your help. Will reply back after going through your solution in detail.


Vinay
 
Hi Narayan,


I had named the ranges but was not using either of TRANSPOSE/ MOD/ ROW/ INT. In fact with simple OFFSET I was getting the same #NUM! error.


Can you take me through why we are using the above functions? I would really appreciate a step by step explanation so I understand properly and will be able to replicate the same. Also, for any of these do I need to use CTRL+SHIFT+ENTER while entering?


Further, I am not able to understand how the formula should be modified if the columns are shifted


XIRR OutflowDate XIRR OutflowAmount XIRR InflowDate XIRR InflowAmount

Column AI AJ AK AL


Rows are 1126 in one document and 3214 in the other. How would the ranges read in both the cases?


I presume the 2* and 11* are for the number of columns to the right in the OFFSET function, but any change I make ensures XIRR result comes "#NUM!"


Vinay
 
Hi Vinay ,


Offhand , I think it will not work if both the ranges do not have the same number of rows ; since you have 1126 in one range and 3214 in another , I think it will not work.


Why can you not have formulae to ensure that these two ranges are combined to form one physical range ? At present , the XIRR formula is combining them to form one virtual range , which cannot be done if the two ranges have differing number of rows in them.


Can you upload your actual workbook ?


Narayan
 
Narayan,


I think you have misunderstood me. You are absolutely correct that if the dates and values differ in number, XIRR will not work. There are multiple portfolios for which I need the XIRR calculated. One of the portfolios has 1126 rows and the other has 3214. The number of entries of dates and values for each portfolio tally 100%.


You have seen the structure of the document. All entries are made in rows because a lot of other portfolio information like brokerages/ tax liability/ holding account/ trade decision, etc is common for both inflow and outflow decision. Hence, even if I effectively transpose the outflows and inflows in a way that is compatible for XIRR, I lose a lot of other information which will then have to be separately recorded which is extremely cumbersome.


The approach which you have used is doing the job perfectly for the small sample, but I am unable to replicate it across multiple portfolios since I am myself not very clear on the reason for using TRANSPOSE/ OFFSET/ MOD/ ROW and INT functions. Can you explain it step by step? As in, are any of these array functions? Further, why are we using MOD and INT instead of putting a row/ column number in OFFSET function - why MOD(ROW(Sample1:52)-1,26) when there are only 26 rows. Further, why use 2* INT function instead of entering column number directly and again divide by 26. Do I need to change the 26 to the number of rows? Do I need to change the Sample 1:52 command to (2* number of rows)? What is the logic?


Also in the sample the columns were different as I had extracted the data from the actual workbook. Actually the columns are AI, AJ, AK, AL. Hence your named ranges need to be changed and adjustment made for the different columns and rows which are 1126 and not just 26. Which, if I am able to understand your approach, I should be able to make the adjustments.


Unfortunately I dont think I can upload the actual workbook as it includes actual client transactions.
 
Hi Vinay ,


What I meant was that if you wanted to include two ranges in your XIRR formula , one of which had 1126 rows , and the other 3214 , it would not be possible.


However , if you want to do the XIRR calculations twice , once with a range having 1126 rows , and another time with a range having 3214 rows , that is certainly possible.


Regarding the explanation , can you please wait till tomorrow , since I will not be able to devote the time to doing this tonight ?


Narayan
 
Hi Vinay ,


Now that Haseeb has given an elegant solution , I think an explanation of the earlier formula may not be necessary ?


Narayan
 
Haseeb - that is seriously brilliant! Simple and elegant.. Thanks!


Narayan - I would still love to understand the formula you suggested if you can spare the time for it.


Any ideas for getting XIRR for filtered data though?
 
Hi Vinay ,


First , one correction in the formulae I had posted ; the TRANSPOSE is unnecessary.


The two ranges have the following formulae :


1. XIRR_Amounts - =(N(OFFSET(Sample!$L$2:$L$27,MOD(ROW(Sample!1:52)-1,26),2*INT((ROW(Sample!1:52)-1)/26),1,1)))


2. XIRR_Dates - =(N(OFFSET(Sample!$B$2:$M$27,MOD(ROW(Sample!1:52)-1,26),11*INT((ROW(Sample!1:52)-1)/26),1,1)))


There are only two techniques used here :


1. ROW(1:52) , which creates an array of numbers from the starting number to the ending number ; here the start number is 1 , while the end number is 52. To check out how this works , create a named range , say temp , and in the Refers To box , put in the formula =ROW($1:$52)


In any unused cell , put in the formula =temp ; press F2 and F9 ; you should see the array as follows :

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52}


If you change the Refers To formula by including the TRANSPOSE function ( =TRANSPOSE(ROW(Sample!$1:$52)) ) , you should see the following array :

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52}


The first array is an array of 1 column and 52 rows ; the second one has 1 row and 52 columns. As far as your XIRR formula is concerned , both will work ; the TRANSPOSE is not necessary.


(To be contd.)


Narayan
 
Dear Narayan,


Awaiting followup to your previous message..


Haseeb, Narayan, anyone,


Any ideas on how to compute XIRR from filtered data as mentioned above?
 
Hi Vinay ,


Now that the idea behind generating an array of numbers is clear , let us see how this can be put to use.


Consider the syntax of the OFFSET function :


OFFSET(reference,rows,cols,height,width)


Reference : is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells.


Rows : is the number of rows, up or down, that you want the upper-left cell to refer to. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).


Cols : is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).


Height : is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.


Width : is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.


But what does OFFSET do ?


It returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.


What this means is that unlike a function like SUM which returns a value , OFFSET returns a cell or a range address. So OFFSET can be used in a cell provided it returns a cell address ; if it returns a range ( more than one cell ) address , using it in one cell will return an error value.


Consider the following formula :


=OFFSET(A1,0,0)


entered in a cell Z10 ; the reference address is A1 , which is in the first row and the first column ; since the row offset is 0 , it means that we remain on the first row ; similarly , since the column offset is 0 too , we remain in the first column. The result is that the reference returned by the OFFSET function is A1 itself.


So =OFFSET(A1,0,0) is the same as =A1


Now let us consider :


=OFFSET(H7,3,9)


The reference address is H7 ; the row offset being 3 means we move 3 rows down from 7 ( the row of the reference address ) ; we get 10. The column offset is 9 which means we move 9 columns to the right of column H ( the column of the reference address ; we get Q. Thus the return reference of the OFFSET function is the address Q10.


So =OFFSET(H7,3,9) is the same as =Q10


What do we need to be careful about when using OFFSET in this fashion ?


1. Since OFFSET moves away from the reference address , the resultant address has to be a valid address ; if the resultant address will be outside the limits of the worksheet , Excel returns an error. Thus , a formula such as =OFFSET(H7,-7,0) will be invalid , since the resultant address obtained by moving 7 rows above H7 will result in H0 ( without considering the column offset ) , which is an invalid address. The same will happen in a formula =OFFSET(H7,-3,-8)


2. Since the return address of the OFFSET function is always calculated with respect to the reference address , the formula can unwittingly generate a circular reference ; suppose the formula =OFFSET(H7,-3,-6) is entered in a cell ; what does it return ? -3 rows above row 7 is row 4 ; -6 columns to the left of column H is column B ; the return address of the above OFFSET formula will therefore be B4. So suppose this formula is entered in the cell B4 ; even though the formula itself appears to have nothing to do with it , in reality it will generate a circular reference.


(To be contd.)


Narayan
 
Narayan,


I have tried to put your explanation to good use in finding filtered XIRR from the data, but am still in a mess. I have uploaded the sample file with my 3 best attempts at using CHOOSE/ OFFSET and Array Functions to solve the problem.


File uploaded to

http://speedy.sh/UrVqd/Portfolio-Sample-Workbook-Revised-Filtered-XIRR.xlsx


Can you and others please have a look and suggest changes. Data is same as last time.

Formulas used are

1 - =XIRR(CHOOSE({1,2},(SUBTOTAL(3,INDEX($AJ$2:$AJ$27,ROW($AJ$2:$AJ$27),1))*INDEX($AJ$2:$AJ$27,ROW($AJ$2:$AJ$27),1)),SUBTOTAL(3,INDEX($AL$2:$AL$27,ROW($AL$2:$AL$27),1))*INDEX($AL$2:$AL$27,ROW($AL$2:$AL$27),1)),CHOOSE({1,2},(SUBTOTAL(3,INDEX($AI$2:$AI$27,ROW($AI$2:$AI$27),1))*INDEX($AI$2:$AI$27,ROW($AI$2:$AI$27),1)),SUBTOTAL(3,INDEX($AK$2:$AK$27,ROW($AK$2:$AK$27),1))*INDEX($AK$2:$AK$27,ROW($AK$2:$AK$27),1)),5%)


2 - =XIRR(CHOOSE({1,2},$AJ$39,$AL$39),CHOOSE({1,2},$AI$39,$AK$39),5%) (Using INDEX and ROW)


3 - =XIRR(CHOOSE({1,2},$AJ$43,$AL$43),CHOOSE({1,2},$AI$43,$AK$43),5%) (Using OFFSET and ROW)


I hope you continue your explanation of the initial formula as time permits.


Thanks.
 
Hi Vinay ,


Sorry I could not check out your problem ; today I opened your file , but I am not able to understand your concept of filtered data ; can you explain on which values you want the XIRR to be calculated ? Since I have your workbook , if you can just indicate the range references , it will make things clearer.


Narayan
 
Dear Narayan,


Suppose if I want to filter the worksheet data for investments made since calendar year 2005 to date (Column AI to be filtered by selecting 2005, 2007, 2008 and 2009 years - Total Outflow will be Rs. 91,575 and Current Value/ Inflows Total will be Rs 117,380), then the XIRR computed as per above formula given by Haseeb does not change and calculate the XIRR from the filtered data.


How should we go about calculating XIRR from filtered data within this data structure?


Something like Subtotal command for Average/ Count/ Sum, etc but for XIRR.


Hope this clarifies?


Thanks

Vinay
 
Hi Vinay ,


I tried out a few things , and what I find is that the XIRR function needs to be understood in more depth , which unfortunately I cannot do. So , I'll just tell you what I did.


1. I copied and pasted the amounts and dates in two unused columns.


2. I used a direct referenced formula , e.g. =XIRR(AA2:AA53,AC2:AC53,15%) ; the guess does not have a great significance , except that it should be somewhat close ; using 5 , 15 or even 25 does not have too much of an impact on the output.


3. I started replacing the amounts from the earliest dated amount ( October 13 , 1998 , -2375 ) by 0 , one by one.


4. At a particular stage , the XIRR output becomes #NUM! ; till that stage , replacing the actual amounts by 0 makes the XIRR output increase.


5. I now took the original data , and sorted it date-wise. The XIRR output gave the original 18 %.


6. I repeated step 3 above on this sorted data. The moment I replaced the earliest amount of -2375 by 0 , the XIRR output became 0.000000447 %. Replacing the other amounts dated prior to 2005 did not make any difference to this output i.e. it remained 0.000000447 %.


7. I now started replacing the amounts starting from the last date prior to 2005 ( December 20 , 2004 , 12000 ) by 0 , in the reverse order ; the XIRR output now did not go to 0.000000447 % straightaway ; it fluctuated up and down , and only when I zeroed the first amount of -2375 ( October 13 , 1998 ) , did it go to 0.000000447 %.


8. Lastly , I changed the formula to refer to the range satisfying your criteria i.e. only those amounts for 2005 and later ; this made the formula =XIRR(AF19:AF53,AH19:AH53,15%) ; this gave the same result 0.000000447 %.


The first lesson I can draw from this exercise is that the XIRR function behaves predictably if the dates are in sorted order ; if at all we have to use named ranges within the XIRR function , they will have to be sorted ; I tried out ascending order ; you can try descending order and see if it gives similar results.


I'll continue to work on this , and let you know if I get anywhere.


Narayan
 
Dear Narayan,


Seems to be that XIRR is pretty buggy and is prone to many errors, most notably, 2.98 error when empty cells, zero-valued date cells are entered, be they at the beginning, middle or end of the value and date ranges.


http://answers.microsoft.com/en-us/office/forum/office_2007-excel/using-xirr-gives-incorrect-values/5e3cb525-6b51-e011-8dfc-68b599b31bf5


offers some solutions which I am trying to implement in this query.


http://www.sumproduct.com/thought/irreverent-irr offers some more examples of XIRR errors and lack of reliability


Let me know if you get some new ideas.
 
Hi Vinay ,


Thanks for the links , though as a non-finance person , I am not too keen ! However , it's good to have them as resources for the future.


Narayan
 
Narayan,


Thanks for all the suggestions till now. Appreciate it.


Till you get any fresh ideas, I will assume this thread is resolved.


Vinay
 
Happy to inform that I found a solution for my query as above. This is for anyone else reading the thread and facing similar issue.

Hope it is ok to link to external forum (below). If not, mod please edit and I will ensure it does not recur.


For calculating XIRR for data set A2:H22

Formula is

'=XIRR(H2:H22,A2:A22)', but this takes into account all rows (whether they are visible or not)


If trying to apply the XIRR formula to data selected by a filter, use Array Formula (Ctrl+Shift+Enter) as below,

'=XIRR(N(OFFSET(H2:H22,SMALL(IF(SUBTOTAL(3,OFFSET(A2:A22,ROW(A2:A22)-ROW(A2),0,1)),ROW(A2:A22)-ROW(A2)),ROW(INDIRECT("1:"&SUBTOTAL(3,A2:A22)))),0,1)),N(OFFSET(A2:A22,SMALL(IF(SUBTOTAL(3,OFFSET(A2:A22,ROW(A2:A22)-ROW(A2),0,1)),ROW(A2:A22)-ROW(A2)),ROW(INDIRECT("1:"&SUBTOTAL(3,A2:A22)))),0,1)))'


I don't exactly understand the formula step by step but I was able to modify it for my data set and yes it works brilliantly!


For original thread, please check out http://www.mrexcel.com/forum/excel-questions/708616-xirr-only-visible-cells-filtered-data.html
 
Status
Not open for further replies.
Back
Top