1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

XIRR and rolling data

Discussion in 'Ask an Excel Question' started by Robert Wilson, Mar 16, 2018.

  1. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Although there have been helpful discussions about Excel 2013's XIRR function using non-contiguous cells, I can't get any suggestions to work in my particular situation. Below is an excerpt from a large spreadsheet covering a number of years. Attached is a file (sample for XIRR.xlsx) that shows some formulas that work and some that don't, along with columns used for showing correct XIRRs for various time periods.

    ending cashflow balance
    1/31/16 84,620 84,620
    2/28/16 0 84,460
    3/31/16 0 85,800
    4/30/16 -5,500 90,720
    5/31/16 0 93,500
    6/30/16 9,000 84,990
    7/31/16 -1,000 86,070
    8/31/16 -2,700 87,510
    9/30/16 0 89,930
    10/31/16 0 87,530
    11/30/16 0 87,640
    12/31/16 0 92,850
    1/31/17 300 94,980
    2/28/17 300 96,510
    3/31/17 300 94,030

    I would like to calculate XIRR for rolling YTD, 12 month, and 36 month periods. For example, for the 12 month period ending 3/31/17, I would like to use the starting value in cell C4, the cashflow range B5:B15, and the ending value -C16 (note the negative). For the next new month, I insert a row above row 16, copy the 3/31/17 data to the newly inserted row, then add new data for 4/30/17.

    XIRR can't handle multiple ranges but I found a workaround that can deal with input from two non-contiguous ranges. However, this workaround can't handle three ranges or make C16 negative. Furthermore, I'm puzzled how the multiple ranges seem work in the XIRR formula shown in C24.

    IRR could be annualized to get the return I'm looking for and it works with multiple ranges if they are assigned a range name. So, I could use an assigned name (e.g. =IRR(cashflow)), but the ranges vary monthly and the ending value must be made negative. I don't know how to deal with these variables.

    Some of my attempts used OFFSET to select the last 12 months of data, but I'm still stuck on trying to pick the three ranges that I need. I imagine the solution will involve CHOOSE but I can't seem to figure out how to make it work.

    A couple preferences: 1) the extent of the spreadsheet prevents me from using nearby cells for placeholders/work areas and 2) I would prefer to avoid VBA if possible.

    GoogleSheets appears to handle multiple ranges perfectly, even changing some of them negative inside the formula, but using GoogleSheets isn't possible in my situation.

    Any suggestions will be greatly appreciated.

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Maybe,

    B26,

    =IRR(N(OFFSET(OFFSET(A16,-11,1),COLUMN(A1:L1)-1,{1,0,0,0,0,0,0,0,0,0,0,1}))*{1,1,1,1,1,1,1,1,1,1,1,-1})

    Regards
    Bosco
  3. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Thanks, Bosco, for your quick response. I'm trying to step through your formula to see how it works, but at least I see how you "picked" values from the appropriate columns and how you made the last value negative. It also works when I add a new row for the next month's data. I had previously experimented with something similar but couldn't get it to work, so I appreciate your offering a solution. Do I assume that this notation doesn't work for XIRR?

    I made a slight change in order to get the starting value for the XIRR calculation to be the end balance of the previous month:

    =IRR(N(OFFSET(OFFSET(A16,-12,1),COLUMN(A1:M1)-1,{1,0,0,0,0,0,0,0,0,0,0,0,1}))*{1,1,1,1,1,1,1,1,1,1,1,1,-1})

    A similar formula works for 36 months, but it takes 35 zeros and ones, so I'm wondering if there is a more elegant solution. Now, the challenge is to get it to work for YTD. Again, any suggestions would be quite welcome.

    Thanks, again, very much for your help.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    For XIRR, try,

    =XIRR(INDEX(N(OFFSET(OFFSET(A16,-12,1),COLUMN(A1:M1)-1,{1,0,0,0,0,0,0,0,0,0,0,0,1}))*{1,1,1,1,1,1,1,1,1,1,1,1,-1},0),OFFSET(A16,0,0,-13))

    Regards
    Bosco
    Last edited: Mar 17, 2018
    Thomas Kuriakose likes this.
  5. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Thank you again. I tried out your solution and it worked perfectly. Now that 12 and 36 month IRRs and XIRRs have been worked out (although I'll eventually try to find a more streamlined way of reducing the 70+ array constants), I've been struggling with YTD formulas. The challenge is that the range isn't a fixed number of cells. I'll keep working on it.

    Thanks,
    Robert
  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Regarding XIRR and YTD formulas :

    1] Increase your source data to 25 lines with dummy value for testing 12 months and 24 months cash flow purpose.

    2] G30, H30 and I30 enter : 12, 24 and 2017 (and with custom format setting)

    3] XIRR for 12 month cash flow, G33 copied right to H33

    =XIRR(INDEX(N(OFFSET(INDEX($B:$B,MATCH(9^9,$A:$A)-G30),COLUMN(INDIRECT("C1:C"&(G30+1),))-1,(MOD(COLUMN(INDIRECT("C1:C"&(G30+1),)),G30+1)=0)+(MOD(COLUMN(INDIRECT("C1:C"&(G30+1),)),G30+1)=1)))*(COLUMN(INDIRECT("C1:C"&(G30+1),))^0-(2*(MOD(COLUMN(INDIRECT("C1:C"&(G30+1),)),G30+1)=0))),0),OFFSET(INDEX($A:$A,MATCH(9^9,$A:$A)),0,0,-G30-1))

    4] 2017 YTD cash flow I36 enter :

    =XIRR(INDEX(N(OFFSET(OFFSET(A26,1-SUMPRODUCT(0+(YEAR(A2:A26)=I30)),1),COLUMN(INDIRECT("C1:C"&SUMPRODUCT(0+(YEAR(A2:A26)=I30)),0))-1,YTD1))*YTD2,0),OFFSET(A26,,,-SUMPRODUCT(0+(YEAR(A2:A26)=I30))))

    2 define names used : YTD1 and YTD2, please checked with "Name Manager"

    Regards
    Bosco

    Attached Files:

  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Explain how to generate {1,0,0,0,0,0,0,0,0,0,0,1} and {1,1,1,1,1,1,1,1,1,1,1,-1} in formula

    1] This part of 12 months formula generate {1,0,0,0,0,0,0,0,0,0,0,1}

    =(MOD(COLUMN(INDIRECT("C1:C"&(G30+1),)),G30+1)=0)+(MOD(COLUMN(INDIRECT("C1:C"&(G30+1),)),G30+1)=1)

    since : G30+1 =12+1 =13

    >> =(MOD(COLUMN(INDIRECT("C1:C"&13,)),13)=0)+(MOD(COLUMN(INDIRECT("C1:C"&13,)),13)=1)

    >> =(MOD({1,2,3,4,5,6,7,8,9,10,11,12,13},13)=0)+(MOD({1,2,3,4,5,6,7,8,9,10,11,12,13},13)=1)

    >> =({1,2,3,4,5,6,7,8,9,10,11,12,0}=0)+({1,2,3,4,5,6,7,8,9,10,11,12,0}=1)

    >> ={0,0,0,0,0,0,0,0,0,0,0,0,1}+{1,0,0,0,0,0,0,0,0,0,0,0,0}

    >> ={1,0,0,0,0,0,0,0,0,0,0,0,1}

    If works for 36 months, G30 =36, the formula will give 35 zeros + 2 ones

    2] This part of 12 months formula generate {1,1,1,1,1,1,1,1,1,1,1,-1}

    =COLUMN(INDIRECT("C1:C"&(G30+1),))^0-2*(MOD(COLUMN(INDIRECT("C1:C"&(G30+1),)),G30+1)=0)

    since : G30+1 =12+1 =13

    >> =COLUMN(INDIRECT("C1:C"&13,))^0-2*(MOD(COLUMN(INDIRECT("C1:C"&13,)),13)=0)

    >> ={1,2,3,4,5,6,7,8,9,10,11,12,13}^0-2*(MOD({1,2,3,4,5,6,7,8,9,10,11,12,13},13)=0)

    >> ={1,1,1,1,1,1,1,1,1,1,1,1,1}-2*({1,2,3,4,5,6,7,8,9,10,11,12,0}=0)

    >> ={1,1,1,1,1,1,1,1,1,1,1,1,1}-{0,0,0,0,0,0,0,0,0,0,0,0,2}

    >> ={1,1,1,1,1,1,1,1,1,1,1,1,-1}

    If works for 36 months, G30 =36, the formula will give 36 ones + 1 negative one

    Regards
    Bosco
  8. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Bosco, what you've done is quite impressive! I've followed your suggestions and the results are perfect. Your follow-up explanation of how you came up with the equivalent of the array constants is helpful, but I'll save my full understanding for later. I won't burden you (now) with trying to explain the rest of what is going on in these two formulas. For now, having the results I'm looking for is helpful enough.

    I'm stuck, however, on understanding the formula for YTD's XIRR. I gather that the YTD1 dynamic named range picks which year and rows are used for calculating the YTD return and that YTD2 picks which column is used (i.e. cashflow or ending balance). I also assume that the formula's first value used in the calculation is the first ending balance (i.e. January) for whatever year is in I30. But I'm confused, for example, in what period is considered when I30=2016. It doesn't appear to be January through December 2016 or January 2016 through the most recent month. If it helps in simplifying anything, I am only interested in the most recent YTD period, so there may be no need for cell I30. Am I correct in saying that the other XIRRs consider only the most recent 12, 24, etc. month periods?

    Another issue is that, since all dates are end-of-month, I would like the first value used in the calculation to be December's ending balance (therefore, the beginning balance for the next year) instead of January's ending balance.

    I apologize if this complicates matters.

    Thanks
    Robert
  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Regarding YTD's XIRR, here is the formula revision.

    1] Manual YTD's XIRR calculation for 2015, 2016 and 2017 put in cell I28, J28 and K28 for checking purpose

    2] Click dropdown list in cell I30 for selection of 2015, 2016 or 2017

    3] In I36,YTD's XIRR formula revised to :

    =XIRR(INDEX(N(OFFSET(OFFSET(INDEX(A2:A26,MATCH(2,INDEX(1/(YEAR(A2:A26)=I30),0))),1-SUMPRODUCT(0+(YEAR(A2:A26)=I30)),1),COLUMN(INDIRECT("C1:C"&SUMPRODUCT(0+(YEAR(A2:A26)=I30)),0))-1,YTD1))*YTD2,0),OFFSET(INDEX(A2:A26,MATCH(2,INDEX(1/(YEAR(A2:A26)=I30),0))),,,-SUMPRODUCT(0+(YEAR(A2:A26)=I30))))

    Regards
    Bosco

    Attached Files:

  10. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Thank you for providing an update to your last sample worksheet. I think we are getting very close to what I am looking for. Just a couple changes:

    1. The starting date for the YTD calculation should be one month earlier (i.e. end of December, which will be the same as the value at the start of the next day, 1 January). As currently written, the formulas in I36 and I28 have the initial value for the end of January. The initial dates for the 12 and 24 month periods appear to be correct.

    2. I don't know if it helps simplify the formulas in G33, H33, and I36, but I am only interested in the most recent 12, 24, YTD, etc. periods, so the last month to be considered will always be the last month of data. In our current example, it is 31/3/17, but that will change as new months are added. I won't need XIRRs for prior calendar years nor will I need to change the year in cell I30. It will always be the current year.

    I tried making some changes to various formulas myself but couldn't get anything to work.

    I appreciate all your efforts.

    Thank you,
    Robert
  11. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Bosco, over the past few days, I've been trying to find a solution for adding one more month to the beginning of the values for calculating XIRR for YTD as mentioned in my first point above.

    I don't know if it's true, but I think the issue is perhaps connected with portions of the XIRR formula, including definitions of YTD1 and YTD2 named ranges, that include "... YEAR(worksheet!$B$2:$B$26) ...". This portion of the statement seem to limit the range to a calendar year (first data period ending January 31). I believe what is needed is for the range to somehow start one month earlier (first data period ending December 31 of the prior year).

    If any of this is true, then changing the formulas to redefine the ranges through something other than the YEAR function seems to be required. However, I'm at a loss as to what to do. If you have any suggestions, I would certainly value your feedback.

    Thank you,
    Robert
  12. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Hi,

    I am not working in the financial field, and don't understand what is XIRR. It is difficult to know what do you want in the above long description.

    It is advice you to attach a file with example data, the expected result and described what to work out the expected result.

    Regards
    Bosco
    Last edited: Mar 28, 2018
  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Cross posted

    We are not saying that you cannot Cross-Post. Please provide links to ALL of the places where you have posted this question.

    Regards
    Bosco
  14. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    First, let me apologize for not being clear in describing the problem I am trying to solve. You have done remarkably well for not having experience in the financial field. Also, I apologize if I have not followed proper protocol in cross-posting. The only other posting has been on https://www.mrexcel.com/forum/excel-questions/1047776-xirr-non-contiguous-data.html#post5034616, but has not led to a solution.

    Everything on your previous spreadsheet (XIRR(3)) is working quite well. The attached file includes a number of explanations, which I hope are helpful. It also highlights the only two areas that are not working properly: 1) XIRR for YTD and 2) XIRR for total cashflow. I have added a new column J to show the correct range to be included for the YTD XIRR calculation. Hopefully, my explanations are clear enough.

    Again, thank you for your help.

    Robert

    Attached Files:

  15. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Hi,

    As per your attachment, it is clear in understand what do you want.

    Enclosed revised file with formula fixed in "XIRR for YTD" (I38) and "XIRR for total cash flow" (F36)

    Regards
    Bosco

    Attached Files:

  16. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Bosco,

    Your changes worked perfectly. Now that you have developed formulas for this small sample of data, I have begun applying it to my actual much larger amount of data going back almost 20 years. Everything generally seems to work well when I add new monthly data. However, I noticed that the YTD calculation won't accept new data for 2018 (there are only options for 2015, 2016, and 2017). Also, when columns B and C are moved or when new columns are added between B and C or when new rows are added above row 1, none of the formulas work.

    To help explain what I am talking about, I have added comments on the attached spreadsheet. So that it is easier to understand, I have cleared all the cells that I think are not needed. Ultimately, I would like the spreadsheet to be relatively flexible and be able to accept new columns and rows to accommodate future changes. Perhaps the complexity of the formulas will make that too difficult.

    I don't mean to burden you with all this. Although I have been working with Excel for many years, I don't think I would ever be able to make the necessary changes on my own. So, I really appreciate everything you have done.

    Regards,
    Robert

    Attached Files:

  17. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Hi,

    Please clarify your specification for latest year YTD calculation range to be added one more row or not ??

    1] In your post #.1 dated 16-Mar-2018 file [XIRR] show that 2017 YTD calculation range to be only for the Year of 2017 >> XIRR(H14:H16,A14:A16)
    upload_2018-3-29_21-33-37.png

    2] In your post #.14 dated 28-Mar-2018 file [XIRR(4)] show that 2017 YTD calculation range to be add one more row of Dec 2016 >> XIRR(H23:H29,A23:A29)
    upload_2018-3-29_21-34-21.png

    3] But, in your post #.16 dated 29-Mar-2018 file [XIRR(6)] show that 2018 YTD calculation come back for the Year of 2018 only >> XIRR(O36:O38,A36:A38) ???
    upload_2018-3-29_21-35-30.png

    Regards
    Bosco
    Last edited: Mar 30, 2018
  18. Robert Wilson

    Robert Wilson New Member

    Messages:
    9
    Bosco,

    My apologies. I can see why there is confusion. This is one reason why I am trying to find standard formulas that will avoid having to manually select ranges for XIRR. In my 16-Mar-2018 the "sample for XIRR" post was incorrect. The beginning date for YTD should have been 31/12/16 instead of 31/1/17. That mistake carried through "XIRR(2) and XIRR(3). It was corrected in "XIRR(4)" and "XIRR(5)". Then, as I was creating the helper ranges yesterday for "XIRR(6)" to help explain everything, I made the same mistake. I have updated the beginning date to 31/12/17 in the attached file, "XIRR(6a)". I hope I haven't made any other errors.

    Again, sorry for the confusion.

    Regards,
    Robert

    Attached Files:

Share This Page