XIRR with non-contiguous data

[rwilson200]

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. It shows some formulas that work and some that don't, along with columns used for showing correct XIRRs for various time periods.

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 is 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.

Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]

Month
ending
​

[/TD]
[TD]

monthly cashflow
deposits (+) withdrawals (-)
​

[/TD]
[TD]

ending
balance
​

[/TD]
[TD]

monthly
return​

[/TD]
[TD][/TD]
[TD]

overall
cashflow
​

[/TD]
[TD]

12 month
cashflow
​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]

1/31/16​

[/TD]
[TD]

84,620​

[/TD]
[TD]

84,620​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

84,620
​

[/TD]
[TD]

-84,620​

[/TD]
[TD]

-84,620
​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]

2/28/16​

[/TD]
[TD]

0​

[/TD]
[TD]

84,460​

[/TD]
[TD]

-0.2%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]

3/31/16​

[/TD]
[TD]

0​

[/TD]
[TD]

85,800​

[/TD]
[TD]

1.6%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

85,800
​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]

4/30/16​

[/TD]
[TD]

-5,500​

[/TD]
[TD]

90,720​

[/TD]
[TD]

12.1%​

[/TD]
[TD][/TD]
[TD]

-5,500​

[/TD]
[TD]

-5,500​

[/TD]
[TD]

-5,500​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]

5/31/16​

[/TD]
[TD]

0​

[/TD]
[TD]

93,500​

[/TD]
[TD]

3.1%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]

6/30/16​

[/TD]
[TD]

9,000​

[/TD]
[TD]

84,990​

[/TD]
[TD]

-18.7%​

[/TD]
[TD][/TD]
[TD]

9,000​

[/TD]
[TD]

9,000​

[/TD]
[TD]

9,000​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]

7/31/16​

[/TD]
[TD]

-1,000​

[/TD]
[TD]

86,070​

[/TD]
[TD]

2.4%​

[/TD]
[TD][/TD]
[TD]

-1,000​

[/TD]
[TD]

-1,000​

[/TD]
[TD]

-1,000​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]

8/31/16​

[/TD]
[TD]

-2,700​

[/TD]
[TD]

87,510​

[/TD]
[TD]

4.8%​

[/TD]
[TD][/TD]
[TD]

-2,700​

[/TD]
[TD]

-2,700​

[/TD]
[TD]

-2,700​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD]

9/30/16​

[/TD]
[TD]

0​

[/TD]
[TD]

89,930​

[/TD]
[TD]

2.8%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​

[/TD]
[TD]

10/31/16​

[/TD]
[TD]

0​

[/TD]
[TD]

87,530​

[/TD]
[TD]

-2.7%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​

[/TD]
[TD]

11/30/16​

[/TD]
[TD]

0​

[/TD]
[TD]

87,640​

[/TD]
[TD]

0.1%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​

[/TD]
[TD]

12/31/16
​

[/TD]
[TD]

0​

[/TD]
[TD]

92,850​

[/TD]
[TD]

5.9%​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​

[/TD]
[TD]

1/31/17​

[/TD]
[TD]

300​

[/TD]
[TD]

94,980​

[/TD]
[TD]

2.0%​

[/TD]
[TD][/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[TD]

94,980
​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​

[/TD]
[TD]

2/28/17​

[/TD]
[TD]

300
​

[/TD]
[TD]

96,510
​

[/TD]
[TD]

1.3%​

[/TD]
[TD][/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

[/TD]
[TD]

3/31/17​

[/TD]
[TD]

300​

[/TD]
[TD]

94,030
​

[/TD]
[TD]

-2.9%​

[/TD]
[TD][/TD]
[TD]

-94,030
​

[/TD]
[TD]

-94,030​

[/TD]
[TD]

-94,030​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​

[/TD]
[TD]these work:[/TD]
[TD]source data is in red above and aren't to be changed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Columns F-H aren't part of original spreadsheet and are for showing correct XIRRs.
Data in purple has been changed from source at left to calculate XIRR for the period.
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​

[/TD]
[TD]

12 mo annualized TWR (4/1/16 - 3/31/17):​

[/TD]
[TD]

7.357%​

[/TD]
[TD]{=PRODUCT(1+OFFSET(D16,-11,0, 12, 1))-1}[/TD]
[TD][/TD]
[TD][/TD]
[TD]

9.121%​

[/TD]
[TD]

9.176%​

[/TD]
[TD]

-7.856%
​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​

[/TD]
[TD]

12 mo annualized TWR (4/1/16 - 3/31/17):​

[/TD]
[TD]

7.357%​

[/TD]
[TD]{=PRODUCT(1+D5:D16)-1}[/TD]
[TD][/TD]
[TD][/TD]
[TD]

=XIRR(F2:F16,$A$2:A16)​

[/TD]
[TD]

=XIRR(G4:G16,A4:A16)​

[/TD]
[TD]

=XIRR(H14:H16,A14:A16)​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​

[/TD]
[TD]

YTD TWR (1/1/17 - 3/31/17):​

[/TD]
[TD]

0.316%​

[/TD]
[TD]{=PRODUCT(1+OFFSET(D16,-MONTH($A$16)+1,0,MONTH($A$16)))-1}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​

[/TD]
[TD]

YTD TWR (1/1/17 - 3/31/17):​

[/TD]
[TD]

0.316%​

[/TD]
[TD]{=PRODUCT(1+D14:D16)-1}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​

[/TD]
[TD]

12 mo IRR (4/1/16 - 3/31/17):​

[/TD]
[TD]

0.734%​

[/TD]
[TD]=IRR(cashflow1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​

[/TD]
[TD]

Annualized IRR:​

[/TD]
[TD]

9.177%​

[/TD]
[TD]=((1+B22)^12)-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​

[/TD]
[TD]

XIRR for entire history:​

[/TD]
[TD]

9.121%​

[/TD]
[TD]{=XIRR(IF(ROW($2:16)< ROW(A16),$B$2:B15,-C16), A2:A16)}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]in above formula, for rows 2-15, XIRR uses values in C2:C15, but for row 16, how does XIRR use D16?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​

[/TD]
[TD]these don't work:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​

[/TD]
[TD]

12 mo XIRR (4/1/16 - 3/31/17):​

[/TD]
[TD]

0.000000298%​

[/TD]
[TD]{=XIRR(OFFSET(B16,-12,0,13),OFFSET(A16,-12,0,13),0.1)}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]30[/COLOR]​

[/TD]
[TD]

12 mo XIRR (4/1/16 - 3/31/17):​

[/TD]
[TD]

#NUM!​

[/TD]
[TD]=IRR(cashflow2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]31[/COLOR]​

[/TD]
[TD]

Annualized IRR:​

[/TD]
[TD]

#NUM!​

[/TD]
[TD]=((1+B30)^12)-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]32[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]33[/COLOR]​

[/TD]
[TD]

YTD XIRR (1/1/17 - 3/31/17):​

[/TD]
[TD]

#VALUE!​

[/TD]
[TD]{=XIRR(($B$14:B15,C16), A14:A16)}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]34[/COLOR]​

[/TD]
[TD]

YTD XIRR (1/1/17 - 3/31/17):​

[/TD]
[TD]

#NUM!​

[/TD]
[TD]{=XIRR(OFFSET(B16,-MONTH($A$16)+1,0,MONTH($A$16)),OFFSET(A16,-MONTH($A$16)+1,0,MONTH($A$16)))}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]35[/COLOR]​

[/TD]
[TD]

YTD XIRR (1/1/17 - 3/31/17):​

[/TD]
[TD]

1.84901E+15​

[/TD]
[TD]{=XIRR(IF(ROW(OFFSET(B16,-MONTH($A$16)+1,0,MONTH($A$16),1))< ROW(A16),OFFSET(B16,-MONTH($A$16)+1,0,MONTH($A$16), 1),-C16),OFFSET(A16,-MONTH($A$16)+1,0,MONTH($A$16),1))}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]36[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]37[/COLOR]​

[/TD]
[TD]

IRR for entire history (1/31/16 - 3/31/17):​

[/TD]
[TD]

#VALUE!​

[/TD]
[TD]=IRR((B2:B15),-C16)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]38[/COLOR]​

[/TD]
[TD]

Annualized IRR:​

[/TD]
[TD]

#VALUE!​

[/TD]
[TD]=((1+B37)^12)-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: worksheet[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this:

B37 (for example): =XIRR(B21:B33,A21:A33)

Period: =B41

C44: =XIRR(IF(ROW(INDIRECT(B44-Period & ":" & B44))=B44-Period,INDEX(MyData,B44-Period,3),INDEX(MyData,B44-Period,2):INDEX(MyData,B44,2)-IF(ROW(INDIRECT(B44-Period & ":" & B44))=B44,INDEX(MyData,B44,3))),INDEX(MyData,B44-Period,1):INDEX(MyData,B44,1))

Book1
	A	B	C	D
1	Date	Cash flow	Balance
2	31 Jan 16	84,620	84,620
3	28 Feb 16	0	84,460
4	31 Mar 16	0	85,800
5	30 Apr 16	-5,500	90,720
6	31 May 16	0	93,500
7	30 Jun 16	9,000	84,990
8	31 Jul 16	-1,000	86,070
9	31 Aug 16	-2,700	87,510
10	30 Sep 16	0	89,930
11	31 Oct 16	0	87,530
12	30 Nov 16	0	87,640
13	31 Dec 16	0	92,850
14	31 Jan 17	300	94,980
15	28 Feb 17	300	96,510
16	31 Mar 17	300	94,030
17
18
19	XIRRs in detail .	Jan	Feb	Mar
20
21	31 Jan 16	84,620
22	28 Feb 16	0	84,460
23	31 Mar 16	0	0	85,800
24	30 Apr 16	-5,500	-5,500	-5,500
25	31 May 16	0	0	0
26	30 Jun 16	9,000	9,000	9,000
27	31 Jul 16	-1,000	-1,000	-1,000
28	31 Aug 16	-2,700	-2,700	-2,700
29	30 Sep 16	0	0	0
30	31 Oct 16	0	0	0
31	30 Nov 16	0	0	0
32	31 Dec 16	0	0	0
33	31 Jan 17	-94,680	300	300
34	28 Feb 17		-96,210	300
35	31 Mar 17			-93,730
36
37	XIRRs	12.16%	13.84%	8.82%
38
39	XIRRs from original table
40	(non-contiguous)
41	Period	12	months
42
43	XIRRs to	Helper
44	31 Jan 17	13	12.16%
45	28 Feb 17	14	13.84%
46	31 Mar 17	15	8.82%
Sheet1

 

[StephenCrump]

... and

MyData: =A2:C16

 

[rwilson200]

Steve,

Thanks for offering a solution. I'll have to spend a little more time studying the details of your suggestion. Perhaps I've missed something, but it appears that you have created a separate helper table from which to pull data into the formula in C44. If that is the case, unfortunately, the number of accounts, the extent of the actual data in each account, and the dynamic nature of the data makes creating such a work area impractical. Please correct me if I've misinterpreted your solution. As I mentioned in my original posting, one of my preferences is to avoid having to create this type of work area. Ultimately, my goal is to generate XIRRs for the 12, 36, and YTD periods directly from the original data (in this small sample, A2:C16). I'll keep looking at your suggestion.

Thanks, again, for all your help.
Robert

 

[StephenCrump]

Thanks.

My suggested solution picks up non-contiguous cells from the original data set, as you requested.

The secondary table is just to demonstrate that these calculations replicate a simple XIRR using a helper table. You can delete this table without affecting my formulae.

The table also shows the numbers actually used in my XIRR calculations, which are slightly different to those in your helper table. Hence, for example, my XIRR of 8.82% for the 12 months to 31 March 2017 vs your 9.18% (because we have used 31 March 2017 cashflows of -93,730 and -94,030 respectively).

I did use one helper cell per calculation, and forgot to show this formula above:

C44: =XIRR(IF(ROW(INDIRECT(B44-Period & ":" & B44))=B44-Period,INDEX(MyData,B44-Period,3),INDEX(MyData,B44-Period,2):INDEX(MyData,B44,2)-IF(ROW(INDIRECT(B44-Period & ":" & B44))=B44,INDEX(MyData,B44,3))),INDEX(MyData,B44-Period,1):INDEX(MyData,B44,1))

where:

Helper cell B44: =MATCH(A44,INDEX(MyData,,1),)

PS - also noticed the 28 Feb 2016 end of month date and wondered whether this should be 29th?

 

[rwilson200]

I finally had a chance to look further at your suggestions, along with your follow-up explanation from yesterday. I see that your helper table was completely independent of your XIRR calculation and I'm sorry that I didn't pick up on that earlier. Also, your helper cell B44 does help when I add it to the spreadsheet. Your work is quite complicated. Would it be possible for you to give some sort of conceptual overview of what is happening within the formula in C44? I've used Excel's Formula Evaluation tool, but that only gets me so far.

I should clarify the relationship of the values in columns B and C of my original table. Column C is the ending balance for each month and is the balance after the cash flow in column B during that month. For example, the 31 Mar 16 end balance of 85,800 is after March's cash flow (which happens to be 0). It will also be the value at the start of the next day, 1 Apr 2016, the first day of the 12 month period being considered. It follows then that the 31 Mar 17 end balance of 94,030 is after March's 300 deposit and is the final value (in the negative) that should be used in calculating XIRR. Your helper table is correct for the first values for calculating XIRR. However, I think that the final values in column C of your helper table (cells B33, C34, D35) were derived by subtracting the deposits in column B from the corresponding balances in the original table. I believe that these should be the correct values:

B33=-94,980
C34=-96,510
D35=-94,030
B37=12.52%
C37=14.19%
D37=9.18%

So, I'm not quite sure what has to change in C44 to eliminate the subtraction of the last cash flow. I worked on it for awhile but couldn't get anything to work.

After making the appropriate changes, will any of this work for YTD? I'm not sure how rows 44, 45, and 46 enter the picture, but if it helps simplify anything, I am only interested in the most recent 12, 36, and YTD periods, so the last month considered will always be the last month of data.

Lastly, yes, you are correct about 29 Feb 2016. I inadvertently missed considering leap year.

I hope that I haven't complicated matters too much.

Thanks,
Robert